Wednesday, August 17, 2016

Check if Primary Replica before running a SQL Job

I needed way to tell if my SQL job was running on the Primary or the secondary replica (fail if equals a secondary).

This will still work if runs on a server/instance that isn't Always On

If exists (SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id WHERE b.role=2)
 raiserror ('Not primary',16,1)
else
 Print 'OK'


If you find this article useful please leave me a comment.

No comments :

Post a Comment