Sql-server – Automatic failover (Clustering/Always On AG)

availability-groupsclusteringfailoversql server

Let's says we have 2 nodes (N1, N2) clustered (say C1) on-site. And an Always On AG is configured between this cluster C1 and a new SQL server instance N3 (N3 is off-site).

Now, if there is a problem (failure) with N1 then N2 will become active (or vice versa) and continue to serve requests. This is due to clustering concept.

However, if there is a failure of the cluster, then as per SQL 2012 documentation, there cannot be an automatic failover as this is not supported between clustered node and separate server instance. DBA will have to manually do the failover.

Please can you confirm the following:

  1. Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?

  2. So as to be able to use the automatic failover feature we have only 3 options:

    a) Cluster all the server instances into 1 cluster and setup a disk backup strategy (since single shared disk is used in clustering concept)

    b) Don't cluster at all and only setup AG between SQL server instances.

    c) Cluster all server instances into 1 cluster and setup AG between them – however bit confused in practicality of this scenario – What is the purpose of having AG within cluster?

Any guidance and reference links will be very helpful.

Best Answer

Is this feature (automatic failover with clustered and separate server instance) supported in newer SQL versions (2016,2017)?

No it is not. SQL Server 2019 CTP has also been launched and I am not sure whether the functionality has changed in CTP version.

So as to be able to use the automatic failover feature we have only 3 options:

For automatic failover to occur you should make sure the replicas are configured for "automatic failover" and the configuration is set to be "synchronous commit". You can select both options when configuring AG with GUI or you can use T-SQL and use options like AVAILABILITY_MODE = SYNCHRONOUS_COMMIT and FAILOVER_MODE = AUTOMATIC For example( Code Source)

CREATE AVAILABILITY GROUP MyAg   
   WITH (  
      AUTOMATED_BACKUP_PREFERENCE = SECONDARY,  
      FAILURE_CONDITION_LEVEL  =  3,   
      HEALTH_CHECK_TIMEOUT = 600000  
       )  

   FOR   
      DATABASE  ThisDatabase, ThatDatabase   
   REPLICA ON   
      'COMPUTER01' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01:5022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  --mmode is set to sync commit
         FAILOVER_MODE = AUTOMATIC,  ----failover mode is set to automatic
         BACKUP_PRIORITY = 30,  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = NO,   
            READ_ONLY_ROUTING_URL = 'TCP://COMPUTER01:1433' ),
         PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,   
            READ_ONLY_ROUTING_LIST = (COMPUTER03) ),  
         SESSION_TIMEOUT = 10  
         ),

I would highly recommend you to read Availability Groups:When Is My Secondary Failover ready

As for your question some are vague. You simply cannot have automatic failover in AG if you are deploying AG on FCI. This is because it will conflict with failover mechanism of FCI. As to why the MS has given option to configure AG in FCI this is simply because of complex environment people have and they wanted AG to be set in almost all HA scenarios. Configuring AG in FCI would give you both OS level protection and database level protection just like you used to have mirroring configured for database which was residing on FCI.

Related Question