How to check the writeable copy of the database in the availability group SQL server

This scripts is useful to check before write any automation process.

if (dbo.fn_is_writeable_replica('DatabaseName')=0)
    BEGIN
          PRINT 'EXITING GRACEFULLY';
          THROW 51000, 'This is not a writeable replica', 1;
    END

Writable copy of all availability group

--->> LIST WRITABLE DB FROM ALL AVAILABILITY GROUPS
SELECT ars.role_desc
	,ag_name = ag.[name]
	,adc.[database_name]
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ars.group_id = ag.group_id
JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id
WHERE ars.is_local = 1
	AND ars.role_desc = 'PRIMARY'
	AND NOT EXISTS (
		SELECT 1
		FROM sys.availability_groups dag
		JOIN sys.availability_replicas fwd ON fwd.group_id = dag.group_id
		JOIN sys.availability_groups ag2 ON ag2.name = fwd.replica_server_name
		JOIN sys.availability_databases_cluster db ON db.group_id = ag2.group_id
		WHERE dag.is_distributed = 1
			AND db.[database_name] = adc.[database_name]
		)
Spread the love

Leave a Comment