Compatible from SQL Server 2012
You want to execute script against database only if actual database is primary replica? Sometimes you need to continue execution of batch only when actual database is primary replica on current SQL Server instance. Handy in automation or SQL Agent jobs.
Its results depends on replica of AG you are connected to. Throwing error when given database is secondary replica.
-- check AG replica state for given database DECLARE @DatabaseName NVARCHAR(256) = N'
' DECLARE @HadrRole INT DECLARE @Message NVARCHAR(MAX) -- Return role status from sys.dm_hadr_availability_replica_states SELECT @HadrRole = ars.role FROM sys.dm_hadr_availability_replica_states ars INNER JOIN sys.databases dbs ON ars.replica_id = dbs.replica_id WHERE dbs.name = @DatabaseName; IF @HadrRole <> 1 BEGIN SET @Message = 'Skipping fixing on this server as database ' + @DatabaseName + ' is secondary replica here.' RAISERROR(@Message,16, -1, @@SERVERNAME ) END
Script messages after execution:
-- from secondary replica Msg 50000, Level 16, State 1, Line 16 Skipping fixing on this server as database DW is secondary replica here. -- from primary replica Commands completed successfully.