SQL Server Always On Series - Always On Availability Group Automatic Failover to a Particular Node

SQL Server Always On Series - Always On Availability Group Automatic Failover to a Particular Node @jbswiki #alwayson #sqlserver

We will discuss on workarounds to keep an Always on Availability group on a particular node in this video.

The requirement is that the Availability group should reside only on server JBSAG1. Even if the availability group fails from JBSAG1 to server JBSAG2, it should be automatically failed back to server JBSAG1 as soon as possible. Downtime during the fail back is not a cause of concern.

This seems to be a strange requirement and understood that that Server JBSAG1 hosts SSD drives which performs many times better than the SAS drives hosted on server JBSAG2.

I configured a SQL Server agent job on JBSAG1 to execute below query every 1 minute,

DECLARE @RoleDesc NVARCHAR(60)

IF @RoleDesc = 'SECONDARY'
BEGIN
ALTER AVAILABILITY GROUP [JBSAG] FAILOVER;
END