SQL Server Performance Forum – Threads Archive

Active/Passive Cluster to Active/Active cluster

Hi.
I am about to reconfigure a Cluster from Active/Passive to Active/Active.
The Current setup contains two SQL Instances.
And it is now required to run each instance on a separte server in the cluster. Howerver, I would like to configure the cluster so that if a node fails, then the one remaining node can still run both SQL Instances. The Server nodes are Microsoft Server 2003, running SQL Server 2000 SP4.
Windows Server 2003 SP1 could be installed prior to cluster reconfiguration if advisable. The current setup of Active/Passive Clustering is as follows. Cluster Group :-
Cluster IP Address Resource
Cluster Name Resource
Quorum Disk Resource SQL Instance 1 Group :-
Dedicated Disk Reources
SQL IP Resource
SQL Network Name Resource
SQL Server Resource
SQL Agent Resource
SQL FullText Indexing Resource SQL Instance 2 Group :-
Dedicated Disk Reources
SQL IP Resource
SQL Network Name Resource
SQL Server Resource
SQL Agent Resource
SQL FullText Indexing Resource All disk resources are on a SAN server. The Cluster configuration I want to achieve is Active/Active.
Each server in the cluster servicing its own SQL Instance.
Eg. Node 1 controls SQL Instance 1
Node 2 controls SQL Instance 2 I would also like to achieve a configuration that enable a single node to run both instances if the other node fails. Are there any definitive instructions for converting Active/Passive to Active/Active clustering. Also, there is the question of memory allocation to enable failover.
E.g. 20% memory of each server goes to the System.
40% memory is predifined as SQL Instance memory.
Therefore if a nodes fails and the SQL Instance is started up on the remaining node there is 40% memory available to run in. If anyone has any applicable information or guidlines I would be very gratefull. Thankyou to anyone who responds.

1. Configure the SQL max memory on each instance to half the RAM in the nodes. If you have 8 GB in the nodes set max ram to 4 GB. I assume you already have something like this in place since you are currently running two instances on one node. 2. Right click on one of the sql server groups in cluster administrator and select "Move Group". You are now in active/active [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] 3. Update your licenses. Running active/active will double your license costs.

I dont believe it. Is that it. [:0] I Thought changing to active/active would need to reconfigure in order to share the quorum disk? and the general cluster group. If thats all there is to it, I appologise for not just doing it before checking. [:I][:I][:I] Thanks for the input. We do actually have 8GB per server, but the performance is struggling. so hope to convince client to spend a couple of pence and make each server 16gb Then allow each instance 7gb. Then if failover occurs 2gb will be left for system. Thanks again for help. Will try and make next topic more challenging.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Garry, take Argyle’s advice. Keep in mind that active/passive just means that an instance runs on one node and not the other. Currently, you are running two instances on one node, but it is still active/passive because only one node is being used to run both instances of SQL Server. Active/Active just means that you are running SQL Server on both nodes instead of one. So, as Argyle has suggested, you just move one instance from one node to another, you in effect have active/active setup. You amy want to change the preferred node owners of each group, but it is not required. Keep in mind, in regard to performance, that hardware my not be the problem. Be sure you look for problem queries and fix them before spending any money on hardware. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Thanks to Argyle and Brad.
Job done and cluster is active/active.
I also change the preferred node owners for each SQL Instance.
Failover of both nodes has also been tested successfully. I have asked this forum for help twice now, and both times I have been very pleased with the results and the speed of response. Thanks again.

Hi,
I did follow the instructions above, "move group" but nothing changed, i see all the resources changing owner from node 1 to node 2…
I have a 2 node in active/passive mode, in the groups i have "group 0" and "cluster group", group 0 has all the sql server resources, cluster group had MSDTC, cluster name and ip, quorum.
Cluster Running on Windows 2003 Ent. What I am doing wrong/missing? thank you –korfut

What are entries in SQL error log, cluster log and event viewer log? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

korfut you are not missing anything.
" i see all the resources changing owner from node 1 to node 2…" Thats exactly what happens. Node 2 becomes the owner of the specific group you just moved. You will need two SQL groups/instances to be able to run active/active (one seperate sql instance running on each node). If you only have one SQL group/instance its not possible to run active/active. Note that active/active doesn’t mean load balancing or data sharing in any form. Each SQL instance is seperate from all others with its own network name, ip and disks.

Hi,
no errors in log.
Argyle, basically you are telling me that SQL Server 2000 (2005 as well?) does NOT support load balancing? nor does Windows Server 2003? and the term Active/Active is just to run 2 instances of SQL server and in case of failure of one node both will run on 1 server, correct? If i understood correctly… I can’t see a real advantage to cluster SQL then, I mean, why spend lots of $$ to a cluster, if one server is just sleeping, one gets all the load, and, in the end everything relies on a disk array? So a 4 nodes what’s supposed to do? How SQL Server plan to manage a TB sized database? Are there any way to load balance sql server? thank you. –korfut

Correct, SQL failover clustering is not about load balancing on instance or database level. If you on the other hand have multiple instances, you can manually distribute the load on them by running some on one node and others on another node. The main point of SQL failover clustering is high availiblity. If a node crash with say memory error, cpu error, local disk error, broken fans etc, the cluster service will detect this and start the up the resources that were located on that node on another node in the cluster. Minimizing downtime compared to a stand alone server that you would have to repair (or rebuild and restore data on). It also makes maintenance easier. If you need to install a patch that requires a reboot, you can failover to one node while you install and reboot the other. You can also take down a node and upgrade say the memory in it while the instances keeps running on the other node. This is useful when you have high uptime requirements on your environment. There would be no real use to have 4 nodes unless you run at least 3 seperate instances, (active/active/active/passive) unless you for some reason want to have multiple passive nodes. Just because a database is TB in size it does not mean it would have more load than a smaller database. If you talk about Datawharehousing/OLAP this is more a question of a fast disk sub system (and partitioning and correct indexes). If you want to achive some form of load balancing, you have to program your application for it. For example you could have your database in read/write mode on a failover cluster (for high availiblity) and then you could logship or restore databases in read only mode on multiple servers. Your application could then use some form of round robin algorithm to choose which server to read from. Any write activity would go to the central database on the cluster. You could even load balance the read only databases with Microsoft NLB.

Hello All, I am relatively new to clustering technology. I had actually gone through, windows 2003 Clustering Deployment documentation and did not find any different configurations specified for an Active/Active or Active/Passive deployment. From what I Understand after reading this post is that, its only the active resources on the secondary node that decides whether the secondary node is a Active node or Passive node. Correct me if i am wrong. If there are different methods of implementing the Active/Active or Active/PAssive configuration while setting up clustering at the Server level or Application level then can someone point me to some links or forward me some Articles/Docs on the same. Thanks in advance Mani

Welcome to the forum.
Setting Active/Active is different to Active/Passive setup where you need to install twice on both the nodes having different virtual name & IP which is lengthy process to and this link http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1232340,00.html should give you more idea in addition to the updated books online for SQL Server.
http://sqlserver-qa.net/blogs/perftune/archive/2008/02/29/3576.aspx too.

Hi Satya, Thanks a million for your inputs and the links that you provided were quite nice for a novice DBA like me. Regards
Mani

]]>