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!
-- 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) begin -- 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 end else if @dbStatus = 2 begin -- We are secondary. Do nothing (just here for testing) SELECT 2 end