Database distribution in AlwaysOn

Question

Good morning all, I have an on Always On SQL server configuration where transactional replication is configured If I open SSMS I find that the distribution database is set to AlwaysOn on then I cannot find the synchronized word. thanks

Answer 1

I would not rely on Object Explorer (or the AG dashboard) in SSMS to determine the status. Use the DMVs directly (or create your own dashboards against them). Something like this:

SELECT 
  d.name, 
  AGName = COALESCE(ag.name, 'N/A'), 
  drs.synchronization_state_desc, 
  ar.replica_server_name
FROM sys.databases AS d
LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS drs
  ON d.database_id = drs.database_id
LEFT OUTER JOIN sys.availability_groups AS ag
  ON drs.group_id = ag.group_id
LEFT OUTER JOIN sys.availability_replicas AS ar 
  ON drs.replica_id = ar.replica_id;

I suspect this specific limitation is just that they didn't add the display sync status logic into the code path that renders the System Databases node. The fact that is it missing is documented here, but without an explanation why:

SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.