SQL Server Availability Group

Automatic Failover of Availability Group to the Preferred Node – SQL Agent Job

Objective:

This post information is created to automate the Failover of Availability Group to the Preferred Node via SQL Agent Job.

There is a variable of TIME assigned to the automation SQL Server stored procedure to failover the Availability Group to the preferred Replica. When the procedure runs at the specified time, for eg. With a value of 3, this will automate at the specified time of 3 AM. The time will be from 0 to 23.

If the procedure is executed against master database on the instance using any scheduler like SQL Server Agent jobs and others, then this procedure execution will automatically failover the AG (Availability Group) to the preferred node which is set through configuration table as explained in the procedure. The job script is to run the AG failover automation.

STEP I:

This step will create the T_AG table to hold the configuration settings.

 

STEP II:

This step will populate the configuration data in to the table T_AG.

STEP III:

This step will create the procedure and that will be responsible for failover the instance to the preferred node.

STEP IV:

This step for the below SQL Code to create the SQL Server Agent Job for the purpose of automating the AG failover to the Preferred Node. The job T-SQL should act against msdb database.

 

Leave a Reply

Your email address will not be published. Required fields are marked *