How to Verify Writable Copies of Databases in an SQL Server Availability Group
This script is useful to check before executing any automation processes, ensuring you're writing to a valid, writable replica.
Checking the Writable Replica for a Specific Database
Before performing any database automation, it's critical to verify that you're operating on a writable replica. Below is a T-SQL script that can be used to check whether a database is on a writable replica. If the database is not writable, the script will exit gracefully:
if (dbo.fn_is_writeable_replica('DatabaseName') = 0)
BEGIN
PRINT 'EXITING GRACEFULLY';
THROW 51000, 'This is not a writable replica', 1;
END
List Writable Databases from All Availability Groups
To list all the writable databases in your Availability Groups, use the following query. This script retrieves databases that are marked as primary and not part of any distributed availability group:
-- List writable databases 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]
)
This query will list all the writable databases in your SQL Server Availability Groups, ensuring that you're only interacting with the primary replica that is not part of any distributed groups.
