SQL Server Performance

Multiple Instances on a Node

Discussion in 'SQL Server 2005 Clustering' started by gujsa01, Apr 17, 2007.

  1. gujsa01 New Member

    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
  2. MohammedU New Member

    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.
  3. satya Moderator

    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.
  4. gujsa01 New Member

    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


  5. MohammedU New Member

    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.
  6. satya Moderator

    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.
  7. gujsa01 New Member

    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...




  8. satya Moderator

    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.

Share This Page