locked
Always ON Multiple Instance RRS feed

  • Question

  • Hi Team,

    I am implementing an Always ON Project, where we need to create 5 Always ON Instance(notice I am asking about Always ON Instance , Not Availability Groups),

    We planned to setup multiple always on instance because we wanted to allocate resources separately. We don't want any Database load impact another database performance so we want to deploy MULTIPLE INSTANCE ALWAYS ON.

    We have 2 physical server ,please guide whether I need to create 5 Windows Cluster services...???  OR only 1 cluster services I can use in 5 Always ON Instance. please guide immediately.

    How I can Implement 5 Instance always ON in 2 BOX , how many Cluster services will required to configure the same.

    Thanks

    Gaurav Chauhan

    Thursday, November 10, 2016 2:38 PM

Answers

  • But is this One time activity OR I can use RUHAALWAYSON Cluster Name on another AO Instance as well..??

    you have to do this on all the nodes ( on each instance) Only once. 

    If I wanted to implement 5 no of Always ON Instances , Please suggest Should I repeat your steps in all times while enable the always ON Feature on all instance...??? is this feasible ,, pls guide as I never applied anywhere multiple AO instance.

    Install SQL Instance on all the Nodes 

    example 

    Node1  : Named instances: SQLINS1, SQLINS2
     
    Node2 : Named instances: - SQLINST3,SQLINST4,SQLINST5

    Availability Group :AO1,AO2,AO3

     

    Replicas of AO1: SQLINS1, SQLINS2
    Replicas of AO2: SQLINS3, SQLINS4

    Replicas of AO3: SQLINS2, SQLINS5




    • Marked as answer by Gaurav_Chauhan Monday, November 21, 2016 8:25 AM
    • Edited by ShabSQL Monday, November 21, 2016 10:00 AM
    Monday, November 21, 2016 7:09 AM

All replies

  • Hi Gaurav,

    5 AlwaysOn Instance means you talking about 5 diff SQL server instances?

    Between two Physical there will be Only one Windows Cluster and Further SQL server Instances implemented on these Physical server will have AG Group created. So Only One cluster.

    Please re think your requirement of having multiple Instances on two physical machines. 

    Also what is your motive of having AlwaysOn, Is it for DR, HA?


    Kindly mark the reply as answer if they help

    Friday, November 11, 2016 10:47 AM
  • I worked on a similar topology. It is madness. The best solution to isolate a database, or group of databases so that a workload will not degrade the workload going on one database, or different databases is Resource Governor.

    Such a topology will experience severe memory and cpu contention under load.

    Install Windows Failover cluster Services. Create the cluster in Failover cluster manger. Add each SQL Server service to the Windows Cluster in SQL Server Configuration Manager (right click on the service, properties, always on, enable it). Do this for each SQL Server instance services in SQL Server Configuration manager.

    Then in each SQL Server using SQL Server Management Studio setup your Always on Groups.

    • Proposed as answer by Uri DimantMVP Sunday, November 13, 2016 6:44 AM
    Friday, November 11, 2016 11:09 AM
  • Hi Sunil,

    Actually I don't wanted to implement availability groups , however I wanted to use Always ON Feature and wants to create multiple instance so that I can tune my resource , tempdb and sizing as per our application and database standards..

    Please guide me , Can I create multiple always ON instance on 2 physical machines..??    if possible then how many windows cluster I need to create..??

    Our requirement is to consolidate our different application database on centralized environment , But no any database can impact another performance , that's why we wanted to use Multiple Always ON.

    Thursday, November 17, 2016 4:43 AM
  • Gaurav,

    For consolidation and workload distribution, I do not think you really need AlwaysOn. AlwaysOn groups are for High Availablity and Disaster Recovery purpose and You can also offload your reporting application to ReadOnly secondary Replica in AlwaysOn. AlwaysOn will not help you to implement multiple instances.

    I think, If I understood your requirement; you want to install multiple standalone instance on these two physical machines and host your databases on a particular SQL server instance for a particular application so that its resources are isolated from other application(s).  You can limit the memory and CPU used by each SQL server instance and further you can have databases created in diff drive for diff SQL instances to isolate Disk IO.

    But remember the physical host are only two.

    You also have resource governor which enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests.

    Still I am not clear why you thought of AlwaysOn for this requirement. 

    Cheers!!



    Kindly mark the reply as answer if they help

    Thursday, November 17, 2016 5:54 AM
  • Based on your last post its clear your requirements.

    I think your case preferably to setup only 5 FCI and accommodate them in single WSFC only and not necessarily to setup Always AGs.  But if you need to  have Always on features then you have to setup at least one AG in each instance and can have listener too.

    Of course its possible to build up multiple instances in each node. at same time each instance you can have one or more AGs if you wish too.  And finally you can only have a single windows Cluster Service in each node and all these AGs are (from various instances) resides in single cluster Service Role.

    Windows Cluster Service Role A (Node1 & Node2)

                        AG1 form SQL instance1

                        AG2 from SQL instance1

                        AG3 from SQL instance 2

                        Etc.

    Regards

    Sajid

    Thursday, November 17, 2016 7:01 AM
  • We are planning to build large scale multiple application database on central and as planned we will move 2-3 database only on each instance and TUNE as per the BEST Practice, We don't wanted to use Resource governor.

    some application load is heavy and we don't want to interrupt other database performance hosted on same availability groups.

    So we are going to separate multiple instance so that we can control and tune as per our requirement.

    Is it possible to Implement multiple always instance on 2 physical hosts..?? if yes then how ,, should we have to install multiple cluster for each instance OR only 1 cluster can operate multiple always ON instance??

    Thursday, November 17, 2016 7:17 AM
  • Our requirement is to SET consolidation with High availability and resource TUNE Load Balance approach , is it possible in SINGLE Always ON Instance..??

    Thursday, November 17, 2016 7:19 AM
  • Hi Sajid,

    Thanks for your kind information to provide such a informative massage that Multiple instance of AlwaysON can be hosted on Single FCI(failover cluster instance),

    can you please elaborate the Process , Because the node already been a part of Cluster can be use on another Always ON Instance as well...??

    Means after cluster configuration , when we go to the "SQL Configuration manager" property and we TICK(Select) check box for "Always ON Feature Enable" , So it will configure first time.......  But in second instance installation , How I will be identify that because that CHECKBOX automatically detects the failover cluster instance itself...

    Your quick review will appriciatable....!!!!

    Thursday, November 17, 2016 8:46 AM
  • Dear G C

    still i could n't get clear idea about your current environments &  your expaected plan.

    Regarding Always on check box to uncheck, still possible but be cautious before proceed so.  you will revoke entire granted role and promote as state of standalone instance. (you can review the attached figure)     

    Regards

    Sajid

    Sunday, November 20, 2016 9:42 AM
  • HI sajid,

    Thanks for your response, As per above Image , Enable Always ON feature will be repeat both node instance one by one to activate always ON Feature ,, thanks for this image.

    But is this One time activity OR I can use RUHAALWAYSON Cluster Name on another AO Instance as well..??

    My still question is that ,, CAN I USE Multiple Always ON Instance on single Failover Cluster instance...??

    If I wanted to implement 5 no of Always ON Instances , Please suggest Should I repeat your steps in all times while enable the always ON Feature on all instance...??? is this feasible ,, pls guide as I never applied anywhere multiple AO instance.

    Thanks

    your last answer was appreciated.

    Monday, November 21, 2016 5:25 AM
  • But is this One time activity OR I can use RUHAALWAYSON Cluster Name on another AO Instance as well..??

    you have to do this on all the nodes ( on each instance) Only once. 

    If I wanted to implement 5 no of Always ON Instances , Please suggest Should I repeat your steps in all times while enable the always ON Feature on all instance...??? is this feasible ,, pls guide as I never applied anywhere multiple AO instance.

    Install SQL Instance on all the Nodes 

    example 

    Node1  : Named instances: SQLINS1, SQLINS2
     
    Node2 : Named instances: - SQLINST3,SQLINST4,SQLINST5

    Availability Group :AO1,AO2,AO3

     

    Replicas of AO1: SQLINS1, SQLINS2
    Replicas of AO2: SQLINS3, SQLINS4

    Replicas of AO3: SQLINS2, SQLINS5




    • Marked as answer by Gaurav_Chauhan Monday, November 21, 2016 8:25 AM
    • Edited by ShabSQL Monday, November 21, 2016 10:00 AM
    Monday, November 21, 2016 7:09 AM
  • I think it will will be more meaning full if  he really want Always on HA features for all his five instances .

    * Must Install all five named instances in both cluster nodes.

    * Configure at least one AG / AGs  for each instances.

    *  then 2 or 3 primary AG can be set in first Node1 & the remaining AGs as primary in Second Node.

     (preferable all secondaries better "Readable - Secondary")

    * create five listener for each AG (optional).   

       

    Regards

    Saajid

     
    Monday, November 21, 2016 11:09 AM