Configuring an MSCS 2003 Active \ Active\ Passive SQL Cluster

[Riffraff666]

I am looking for tips, hints, whitepapers on how to configure a 3 node Active, Active, Passive SQL cluster. Anyone have documents, best practise, how to guides?

 

[WhoKilledKenny]

FYI: SQL Server can only run in a two node cluster. Unless there is something new in SQL 2005 that I have not read about yet.

 

[mrdenny]

You can run SQL on a three node cluster. You can only have one SQL Server per Cluster group, and each SQL instance can only run on a single node at a time, but there's nothing stopping you from setting up SQL in a 3 node cluster.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[WhoKilledKenny]

Cluster Nodes

A node is a server within the cluster. Windows NT Server 4.0, Enterprise Edition and Windows 2000 Advanced Server and Window 2003 Advanced Server both support two-node clustering, and Windows 2000 Datacenter Server supports up to four-node clustering and Windows 2003 supports up to eight node clustering however you are limited to four nodes if SQL Server 2000 clustering is to be used. For more information, see Knowledge Base article "811054 PRB: Virtual SQL Server 2000 Installation Fails on Cluster That Has Eight Nodes"

http://support.microsoft.com/?id=811054.

So in a sense mrdenny is correct if you are usind Datacenter addition of Windows.

But more than likely you are not, so 2003 enterprise only supports a two-node cluster.

 

[WhoKilledKenny]

using Datacenter edition..." wow to many margaritas.

 

[WhoKilledKenny]

Sorry posted wrong article above....

This is what I meant to post, still don't know if new features are availble in SQL 2005. What I can find online, does not change in the Windows 2003 OS.

What Are the Types of Clustering?

When you decide you want to cluster SQL Server, you have a choice of configuring what is called Active/Active or an Active/Passive cluster. Each has its own pros and cons. Let’s look at each, in the context of a two-node SQL Server cluster.

An Active/Active SQL Server cluster means that SQL Server is running on both nodes of a two-way cluster. Each copy of SQL Server acts independently, and users see two different SQL Servers. If one of the SQL Servers in the cluster should fail, then the failed instance of SQL Server will failover to the remaining server. This means that then both instances of SQL Server will be running on one physical server, instead of two.

As you can imagine, if two instances have to run on one physical server, performance can be affected, especially if the server’s have not been sized appropriately.

An Active/Passive SQL Server cluster refers to a SQL Server cluster where only one instance of SQL Server is running on one of the physical servers in the cluster, and the other physical server does nothing, other that waiting to takeover should the primary node should fail.

From a performance perspective, this is the better solution. On the other hand, this option makes less productive use of your physical hardware, which means this solution is more expensive.

Personally, I prefer an Active/Passive configuration as it is easier to set up and administer, and overall it will provide better performance. Assuming you have the budget, this is what I recommend.


Two- or Four-Node Clustering?

SQL Server can be clustered using two nodes (using Windows 2000 Advanced Server), or it can be clustered using more than two nodes (using Windows 2000 Datacenter). Since I don’t personally have any experience is three or four node clustering, I won’t be discussing it here. But for the most part, what I say about two-node clustering also applies to three- or four-node clustering.