Automatic Failover of SQL Agents - Always-On Availability Groups

Dear colleagues

I am working on a large customer, wanting to migrate from a single SQL instance to a HA/DR setup using Always-On Availability Groups.

The tricky part is the "HA" part, as setting up Availability Groups is a painless proces, and so is automatic failover.

The problem rises from the SQL Server Agents. These cannot natively failover, not for OneIdentity at least. This ruins a true HA setup, unless you do something about it.SQL Agents are defined on the server-level, so if an availability-group is failed over, the agents do not follow.

Instead you have to let the agents detect when they are running on the primary replica. you can do that by adding queries into the automatically generated agents. But is this the right way to handle it, or what is your experiences with this challenge?

I have tried to make a first attempt at a logic that both 1. Knows if the database is in a cluster (state=3) and 2. knows if it is the primary in the cluster. If you can use it GREAT! if you have a better solution, even GREATER! Slight smile

-- Check current state of database
DECLARE @dbName NVARCHAR(100) = 'databaseNameHere'
DECLARE @dbStatus INT = 0  
SET @dbStatus = (SELECT ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
       LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
             ON AG.group_id = agstates.group_id
       INNER JOIN master.sys.availability_replicas AS AR
             ON AG.group_id = AR.group_id
       INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
             ON AR.replica_id = arstates.replica_id
       INNER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
             ON AR.replica_id = dbrs.replica_id
       INNER JOIN master.dbo.sysdatabases AS db
             ON db.[dbid] = dbrs.database_id
       where db.[name] = @dbName
       and AR.replica_server_name = @@SERVERNAME)

if @dbStatus in (1,3)
       -- We are in a replicated scenario (1), and we are primary, or we are not in a replicated state (3).
       -- Either way we continue execution
       SELECT 1
else if @dbStatus = 2
       -- We are secondary. Do nothing (just here for testing)
       SELECT 2

Parents Reply Children
No Data