Proc FB_AGFailover

Previous Split Brain Scenarios	Proc FB_AGPostFailover Next

The FB_AGFailover procedure performs a failover of the following types of availability group, and can be run on either the Primary server or a Secondary server:

• Distributed Availability Group (dAG)
• Use of FB_AGFailover is not yet tested for Classic Availability Group (AG) and Basic Availability Group (bAG) failover

The FB_AGFailover procedure encapsulates all of the processes needed to complete a failover that can be performed within SQL Server.

FB_AGFailover Pre-Requisites

The following items must be in place in order for FB_AGFailover to be used:

1. Install FB_AGFailover on all servers within the availability group

2. Ensure Linked Server definitions exist for all the servers in the availability group

   The linked server name must be the same as the AG or bAG name. For a dAG, the linked server name must be the same as the underlying AG name

   All servers in the availability group must have the full set of linked server names, in order to allow failover to or from any of the servers

3. dAG Failovers: DNS Alias exists

   A dAG does not have a listener, so routing application connections for a dAG cannot be performed within SQL Server and this process must be performed manually.

   It is recommended that a DNS alias is created with the same name as the dAG that points to the current Primary server, with all application connections configured to use the DNS alias name. The DNS alias can then be manually updated to the new primary server after a failover, allowing all applications to connect to the new primary server without any changes being needed to their connection strings.

FB_AGFailover Execution

EXEC master.dbo.FB_AGFailover 'AvailabilityGroupName'

If FB_AGFailover is run on the Primary server, it will fail over to the first Secondary server found when sorting the names of thre secondary servers.

If FB_AGFailover is run on a Secondary server it will fail over to make that secondary server into the Primary server

Do not use any of the other parameters found within FB_AGFailover, they are used to perform the various tasks required for the failover process

FB_AGFailover Processing

1. Gather Data

   FB_AGFailover gathers data about the availability group to be failed over, in order to control which processes should be used to perform the failover

   A summary of the data gathering results are printed

2. Ensure Primary And Secondary Servers Synchronised

   If asynchronous replication is being used, FB_AGFailover will issue the following commands to put the availability group into synchronous replication. FB_AGFailover will then wait until the Primary server reports all databases are synchronised:

   ALTER AVAILABILITY GROUP [AvailabilityGroup] 
    MODIFY AVAILABILITY GROUP ON 
    'PrimaryServer' WITH (AVAILABILITY_MODE=SYNCHRONOUS_COMMIT),
    'SecondaryServer' WITH (AVAILABILITY_MODE=SYNCHRONOUS_COMMIT);
   

3. Reset Server Roles

   If a dAG is being failed over, FB_AGFailover will process the following command to reverse the role of the Primary server. FB_AGFailover will then wait until the Secondary server reports all databases are synchronised:

   ALTER AVAILABILITY GROUP [AvailabilityGroup] SET (ROLE=SECONDARY);
   

4. Perform Failover

   a. Perform Failover: not dAG

   If a normal AG is being failed over, FB_AGFailover will process the following command to perform the failover:
   

   ALTER AVAILABILITY GROUP [AvailabilityGroup] FAILOVER;
   

   b. Perform Failover: dAG only

   If a Distributed AG is being failed over, FB_AGFailover will process the following command to perform the failover:
   

   ALTER AVAILABILITY GROUP [AvailabilityGroup] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

5. Reset Synchronisation Mode

   If the availability group was originally using asynchronous replication, FB_AGFailover will process the following command to re-establish asynchronous replication.

   This step is bypassed if the availability group was originally using synchronous replication

   ALTER AVAILABILITY GROUP [AvailabilityGroup] 
    MODIFY AVAILABILITY GROUP ON 
    'PrimaryServer' WITH (AVAILABILITY_MODE=ASYNCHRONOUS_COMMIT),
    'SecondaryServer' WITH (AVAILABILITY_MODE=ASYNCHRONOUS_COMMIT);
   

6. Report Results

   A summary of the results of the failover are printed.

   If a dAG was failed over, a reminder to update the DNS alias is also printed

Copyright FineBuild Team © 2020. License and Acknowledgements

Previous Split Brain Scenarios	Top	Proc FB_AGPostFailover Next