locked
SQL AG multiple instances on each AG member RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am looking for information on setting up multiple instances of SQL 2014 on a single server and then adding each of those instances into availability groups. Is this supported? Is this a good idea? Has anyone done it? Are there best practices for this?

    Currently I have a test environment with 3 SQL 2014 servers and a domain controller. The 3 SQL servers are in a WSFC and I have an availability group setup for 3 databases. This setup works great. Now to add another level of complexity to this, I would like to install another SQL instance on each of the 3 SQL servers and then build another availability group for this new instance. Is this possible? I have been asked to research this setup so that we could possibly find a way to prevent our SQL sprawl that we are currently experiencing. 

    Any advice or guidance would be greatly appreciated.

    Thanks,

    TD

    Wednesday, July 15, 2015 6:37 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes. This is possible. you can go a head and try in your test environment.

    Fail over cluster works with multiple instances in the same way AlwaysOn Also works with multiple instances.


    Thursday, July 16, 2015 1:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote


    Ok great. Now how do I setup the second instances? Do the 2nd instances get a different AG? Is there documentation on setting this up that I can read?

    Thursday, July 16, 2015 1:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi SQL 2014,

    You can install the second instances on all the three nodes, and then configure a different availability group for the databases that reside on the second instances. The process is similar with that you set up stand-alone instances and set up availability group last time.

    There are no particular documents describing this setup, however, you can refer to the following similar threads to get more information.

    SQL Server 2012 AlwaysON multiple instance multiple listeners
    Multiple named Instances with Always On - SQL Server 2012


    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support




    Friday, July 17, 2015 6:14 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Lydia is correct. i do not think a separate document is required. 

    Treat this as a new named instance and set up the AG in the same way you setup for your first instance.


    Friday, July 17, 2015 6:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for your feedback. Here is where I am at. I installed the initial SQL  instance using the default port (1433) on SQL1 and SQL2. Then I setup the availability group for it. I can connect to the AG listener from Studio Manager and see the databases. It seems everything is working fine here. 

    Then I installed the second instance on SQL1 and SQL2 using port 1435. I created an AG to use port 1435 and the endpoints to use port 5023 (instead of the 5022 from the original instance). The database that I created on SQL1 is replicated to SQL2 and everything seems to work fine. When I go to connect to the listener I created for the second instance I only see the databases that were created in the first SQL instance. I don't see the database created in the second instance. But if I connect to each of the individual server/instance you can see the correct databases on the correct server/instance. 

    It seems that there is a disconnect on the AG listener for the second instance but I am not how to correct it. I am hoping that this is an easy fix that that I over looked something simple. Please let me know.

    Thanks,

    TD


    Tuesday, July 21, 2015 2:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I was able to fix my issue by reading the following post

    https://social.msdn.microsoft.com/Forums/en-US/741a05a7-55f1-4a44-a199-a5077e54cb3f/sql-server-fails-to-listen-on-the-specified-port

    Basically the issue is was resolved by ensuring that all AG listeners are set to port 1433 and each SQL instance is using a unique port.

    Tuesday, July 21, 2015 3:46 PM