Environment

-> One of my client had an Alwayson setup as depicted above. They advised me that all their Availability group should reside only on server JBSERVER1.JB.NET which is in Primary datacentre. Even if it fails over to server JBSERVER2.JB.NET in secondary datacentre, it should be immediately failed back to server JBSERVER1.JB.NET and they are not worried about the downtime during the fail back.

-> I felt it to be a strange requirement and requested them for reason. They advised me that Server JBSERVER1.JB.NET hosts SSD drives which performs many times better than the SAS drives hosted on server JBSERVER2.JB.NET.

-> I configured a SQL Server agent job to execute below query and that took care of my Client’s requirement.

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 [JBAG] FAILOVER;
END

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.