sql server clustering: I have 2 DELL Windows 2003 Enterprise server machines with SQL 2000
Enterprise Edition and 1 PowerVault 220S SCSI Array set in Cluster mode
hooked up to both of these machines.

I want to know is it possible to use this configuration with SQL in a ACTIVE
/ ACTIVE state.

By Active/Active, I am looking to have both physical nodes actively serving
updates to the same database. A change via node1 is instantly accessible by
node2. If one node fails, the remaining node picks up the load (doubling load
roughly).

I was assuming that the actual MDF of the database file was shared in this
case (a partition on the shared SCSI Array that hosts this file that can be
viewed and updates from either SQL Node). I am not sure if this is the case.
If it is, what happens with the LDF file?

Are there any examples or documents about how to setup such a senario or the
alternative that would suit this case?

In a two-instance scenario, each instance has access only to its own files.
The instances can run on either physical node. If one node fails, the
instance on which it was running fails over to the other node. Now, you are
running both instances on the same node. The "failed" instance is now up
and running and has access to its own files. An instance runs on only one
node at a time.

The BOL have a lot to say on clustering.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com


[quoted text, click to view]
I have 2 DELL Windows 2003 Enterprise server machines with SQL 2000
Enterprise Edition and 1 PowerVault 220S SCSI Array set in Cluster mode
hooked up to both of these machines.

I want to know is it possible to use this configuration with SQL in a ACTIVE
/ ACTIVE state.

By Active/Active, I am looking to have both physical nodes actively serving
updates to the same database. A change via node1 is instantly accessible by
node2. If one node fails, the remaining node picks up the load (doubling
load
roughly).

I was assuming that the actual MDF of the database file was shared in this
case (a partition on the shared SCSI Array that hosts this file that can be
viewed and updates from either SQL Node). I am not sure if this is the case.
If it is, what happens with the LDF file?

Are there any examples or documents about how to setup such a senario or the
alternative that would suit this case?

With SQL Server clustering, only one node can own a database at a time. If
the node that is hosting the SQL Server fails, the instance of SQL Server
fails over to the second node. At this point this node has access to the
database.

Rand
This posting is provided "as is" with no warranties and confers no rights.

Peter,

You wrote "By Active/Active, I am looking to have both physical nodes actively serving
updates to the same database. A change via node1 is instantly accessible by
node2. If one node fails, the remaining node picks up the load (doubling load
roughly)."

You cannot do this with SQL Server 2000 Failover Clustering. i.e You cannot have both nodes actively serving updates to the same database.

Each SQL Server has its own databases. They are not shared by the SQL instances. Also, the databases are stored on a shared disk. At any time only one node can
own the shared disk as Microsoft Cluster Server use Shared Nothing model. Both nodes cannot access the same shared disk at the same time.

Additional Information
======================

SQL Server Books Online

An Overview of Windows Clustering Technologies: Server Clusters and Network Load Balancing
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/winclstt.mspx

Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx

Microsoft SQL Server 2000 High Availability (MS Press)
ISBN: 0-7356-1920-4


HTH,

Best Regards,

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Yes, it is possible only through implementing custom logic inside youre
database. Previous posts are all true ... and you can build real
Active/Active cluster if you have two virtual servers, one database
partitioned horizontaly and implemented distributed partitioned views,
triggers....
But if you think more about this youre come to conclusion that this is not
worth since youre gonna spend too much time building this architecture and
benefits wont be so great...

rgds
Sinisa

[quoted text, click to view]