When asking which databases are writable on any given server, I've been using the below snippet to list
PRIMARY replica databases and databases that do not participate in an AG.
Now that I've deployed a Distributed Availability Group, it's not working anymoar! Since the local forwarder reports as a
PRIMARY replica on the
sys.dm_hadr_availability_replica_states DMV, my old query is telling me a database is writable when it is not.
How do I change this query to exclude databases in a local forwarder replica?
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' union all select 'LOCAL_ONLY' ,@@servername ,[name] from sys.databases d where not exists ( select 1 from sys.availability_databases_cluster adc where adc.[database_name] = d.[name] );
...or more simply...
How do I list all local forwarder databases?