• Steve Jones - SSC Editor - Friday, June 15, 2018 10:05 AM

    You can use Standard Edition with basic availability groups. There is a chance that one group might fail over with a database and not others, but you can write scripts to ensure you have a full failover.

    If you want to ensure that SQL Server is in the same data centre as IIS, you'll need some monitoring and scripting that finds the active nodes and fails the other service(s) over if it has moved. For example,

    SQL 1 - Data Center 1
    SQL2 - Data Center 2
    IIS 1 -4 Data Center 1
    IIS 5-8  Data Center 2.

    If IIS 1 fails, then  do you move everything to Data Center 2? Think about this and map things out. If that's the case, then you need a rule that looks to see if IIS is active on 1-5 or 5-8, and then moves SQL appropriately. The same thing is for SQL server moving, then you need to fail over or move IIS to the other nodes.

    SQL Server AGs  are consistent between nodes.

    Standard Edition isn't going to work for the OP - they have multiple databases in a single instance that will need to be part of the availability group.  That will require Enterprise Edition - so all databases in that group fail over as one.

    You will need a listener created for this configuration - and all of your connections will be set to the listener address and not the instance.  You also will need to update the connection strings to identify the partner nodes.  This will allow IIS to automatically reference the current active node - regardless of which data center is hosting the databases.  And you will need either a file share quorom disk - or a witness node to manage quorom votes in the cluster - or 3 nodes in the cluster (node majority).

    To insure that access is failed over to the appropriate data center - you will need a load balancer setup for your web servers.  If there is a failover event - you would update the configuration to utilize the web servers in the other data center.  Work with the team that manages your load balancer - they should have it setup and configured to failover also.  You may want this to be a manual step for all but a total data center outage - where you down the local web servers and bring up the remote web servers manually.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs