SQL Server Performance Forum – Threads Archive

Multiple Instances on a Node

Hi, Was wondering if someone could help me with a possible scenario for installing multiple SQL Server instances on individual nodes of a SQL Server 2005 cluster?? We are moving towards SQL Server 2005 clustering with decent hardware and a question arised if installing multiple instances on a node would be beneficial?? Apparently most of the articles I have read on clustering suggests not doing so for performance issues. Appreciate your help and thanks in advance.. Thanks

Yes, I believe you can install multiple instances on individual cluster node…
So that it is called multi intance cluster not any more active/active cluster… Always there may be performance issue in this scenorio but if you have good HW with enough memory, you can try it but make sure you test it properly…
http://msdn2.microsoft.com/en-us/library/aa174516(SQL.80).aspx
http://www.sql-server-performance.com/bm_cluster_sql_server_2005_2.asp
http://groups.google.com/group/micr…ailover Cluster&rnum=2&hl=en#331ea80dff024f20
http://groups.google.com/group/micr… instances on cluster&rnum=3#a19b6a8474ea280e MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

What is the high availability option for thsoe instances or applications on that server?
If you see that all the applications working against that server has inter-relation in the case of failover and failback then it is good to have multiple instance, otherwise you have to differentiate the required applications on cluster and others on usual SQL instance mode. If you are looking for performance then clustering is not a solution and having decent optimized hardware and database should get you there. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Appreciate your response guys!!! We have HealthCare apps that will be requiring 24X7 access to data. We would like to have Clustering and Mirroring together as a High Availability solution by mirroring data from one of our Co-Lo with Cluster setup to the other. There is no inter-relation between the apps. If Failover /Failback happens at the node level and not at the multiple instance level running on the same node, I still don’t see the advantage of running multiple instances on the same node. Thanks

SQL server supports mulitple instances, usage has to be depends on the business needs… If you are planningt to use MS clustering failover solution from one colo facility to another colo… then keep in mind it has distance limitations… And mirroring can be done but not advisable in Syncronous mode and it is database level not instance level…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes you can deploy the clustering with database mirroring, before that ensure the application is effective to choose or switch between the nodes & databases if any failover happens instantly. As explained the SQL Server supports the multiple instances but as per the business requirement if it is not required then choose carefully in setting up the high availability solution. Recently I helped a company in this regard and ifyou need any help with regard to planning a project for high availability let me know. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks MohammedU/Satya.. I found the following in MCTS Self-paced Training Kit: "Determining When to Use Multiple Instances of SQL Server 2005–
Using multiple instances of SQL Server 2005 increases administration overhead and causes duplication of components. Additional instances of the SQL Server and SQL Server Agent services require additional computer resources: memory and processing capacity. However, in the following scenarios, using multiple instances has advantages over using only a single instance of SQL Server: * When testing multiple versions of SQL Server on the same computer
* When testing service packs and development databases and applications
* When different customers require their own system and user databases along with full administrative control of their SQL Server instance
* When the desktop engine is embedded in applications because each application can install its own instance independent of instances installed by other applications" I have been reading on High Availability solutions for our Co-Lo setup and looks like Clustering at the site and Peer-to-Peer Replication between Sites is a good solution upon WAN/Internet failover.. As always Thanks for you input…

High Availability and Multiple instances will work upto the extent, but when it comes to the problem then it will have few problems. The kit you have got is a good one to go and make sure to test them what you have been trained. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>