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

Опубликовано: 30 Сентябрь 2024
на канале: JBSWiki

348

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 @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'SECONDARY'
BEGIN
ALTER AVAILABILITY GROUP [JBSAG] FAILOVER;
END