Browse: Wetory / Scripting library / Check actual replica

SQL Server - Check actual replica

by Tomáš RybnickýNo Comments

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

Results

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.

Leave a Reply