Clustering Basic Questions

[KingSexy182]

Im studying sql server clustering at the moment and have come across many points which confuses me. Can any SQL Clustering Guru clarify some of them for me or point me in the right direction. thanks.

1) Single Instance clustering now replaces Active/Passive clustering? If so, doesn't this mean the you cant run an 'Active/Active Cluster' by installing 2 instances of sql server on each node?

2) Multiple Instance clustering now replaces Active/Active clustering?

3) You can only have 1 instance of SQLSERVER on each node in a single instance cluster (2 nodes). However u have more than 1 sqlserver in a multiple instance cluster?

And last but not least, do u know where i can get the latest resources on sql server clustering? apart from microsoft.com. thanks.

[skhanal]

1) Single Instance clustering now replaces Active/Passive clustering? If so, doesn't this mean the you cant run an 'Active/Active Cluster' by installing 2 instances of sql server on each node?

MS has changed the terminology in SQL 2K. Equivalent of Active/Active cluster is running an instance in each node.

2) Multiple Instance clustering now replaces Active/Active clustering?

This also is terminology change. You can have as many instances in each each node of the cluster (depending on server resources).

3) You can only have 1 instance of SQLSERVER on each node in a single instance cluster (2 nodes). However u have more than 1 sqlserver in a multiple instance cluster?

For each SQL Server you need a separate instance installed. If you want two sql servers running, install 2 instances. Only limitation is you can only have 1 default instance in a windows cluster.

[KingSexy182]

*So your saying only the terminology has changed, however you could still create the old 'active/active cluster' on a "single instance cluster" by installing 2 instances of sql server on each of the 2 nodes?
running nt4 enterprise edition or w2k advanced server?

*If so, then installing more than 1 instance of sql server on all nodes - Is this widely practiced?



thanks heaps again skhanal, much appreciated.

[rmiao]

Yes, you can install only one instance of sql on whole cluster (active/passive), or install one instance of sql on each node of the cluster (active/active), or install more than one instance of sql on one node (but it's not good practice since you drive one node crazy while leave other nodes idle). If you need more than one sql instance on the cluster, ensure every nodes in the cluster can handle those instances in case of failover. By the way, you don't want to use nt4 anymore.

[skhanal]

Your active/active configuration will be

Node1
SQLInstance1 (active instance)
SQLInstance2 (passive instance)

Node2
SQLInstance2 (active instance)
SQLInstance1 (passive instance)

You can create SQLInstance1 or SQLInstance2 as named instances or one default and one named will also work.

[KingSexy182]

Sorry guys, the more I read about clustering the more confused i get.

rmiao, if its not good to install more than 1 instance of sql server on 1 node (and im with you on that note; plus ive read somewhere in the MS Resource Kit that you should only use more than one instance of sql on a server is when using a one-node cluster for testing and developing.) how do u explain skhanal's response?

skhanal, how would node 1 and node 2 react if their is a failure on Node 1 and SQLInstance1 ?

very very confused.......



Let me put it this way? Am I on the right track by saying:


A) Single Instance Clustering (Active/Passive)

2 Nodes: Node S1 and Node S2

Node S1 (1 physical computer)
SQLSERVERINSTANCE_A (Active - the default instance of SQL Server is installed and running and taking client requests)

Node S2 (1 physical computer)
SQLSERVERINSTANCE_B (Passive - the default instance of SQL Server is installed but is not running,however waiting for a failover)

?The VIRTUAL SERVER IS CURRENTLY HOSTED BY NODE S1 WHICH IS 'ACTIVE'


B) Multiple Instance Clustering (Active/Active)

2 Nodes: Node M1 and Node M2

Node M1 (1 physical computer)
SQLSERVERINSTANCE_C (Active - default SQL Server is installed. No other named instance is installed. This instance is running and taking requests)

Node M2 (1 physical computer)
SQLSERVERINSTANCE_D (Active - default SQL Server is installed. No other named instance is installed. This instance is running and also taking requests)

? 2 VIRTUAL SERVERS EXIST IN THIS 2 NODEs MULTIPLE INSTANCE CLUSTER, HOSTED ON NODE M1 AND NODE M2


***AM I getting warmer? Ive a got feeling this is all wrong. This active/active model seems like a mechanism for load balancing

[skhanal]

skhanal, how would node 1 and node 2 react if their is a failure on Node 1 and SQLInstance1 ?

SQLInstance1 fails over to node 2, both instances (in fact virtual servers) run on node 2.

A. You are correct.

B. You are almost correct, only thing that is wrong is - you can't install more than 1 default instance in a cluster. This SQL Server documentation in this is very confusing. If you try to install second default instance the option is disabled in the installation wizard.

[rmiao]

In fact, A is wrong. On active/passive cluster, you only install one sql instance on the cluster. You don't run sql install on more than one node in this case. Since when you install sql on cluster, sql instance is created on that cluster, not on node of the cluster.

[skhanal]

I misread the instance name, the instance name will be same in node 2 and as rmiao pointed out, no installation is required in the second node, the installation on first node will copy all necessary files and create services in node 2.

[KingSexy182]

thanks for all your feedback rmiao & skhanal. You guys are GREAT!!! I think i am getting there now.

Armed with the info you guys have provided (much appreciated), i will re-read the articles on the MS website - this time with a better understanding of the archietecture of clustering.

thanks.



One more thing skhanal -
quote:
--------------------------------------------------------------------------------
skhanal, how would node 1 and node 2 react if their is a failure on Node 1 and SQLInstance1 ?
--------------------------------------------------------------------------------



SQLInstance1 fails over to node 2, both instances (in fact virtual servers) run on node 2.


---> therefore Node 2 is now hosting both virtual servers, and SQLInstance1 and SQLInstance 2 on Node2 is carrying all the load, until SQLInstance1 on Node1 can be fixed and brought back online?

[skhanal]

That's right. You may not have all the horse power for two nodes but at least they are available.

[KingSexy182]

skhanal & rmiao - you two great.
thanks for helping me, as i now have a clearer understanding.