Anybody who has talked with me about replication or heard me present about it knows that I recommend using a dedicated remote distributor for anything beyond light replication workloads. Unfortunately neither SSMS nor Replication Monitor provide an easy "one view to rule them all" way at the distributor (or anywhere else) to show every transactional publication, subscriber, and article they're subscribed to. The only way to gather that information using SSMS is to script out each publication and visually parse the scripts. I manage hundreds of publications & subscriptions and that's not a reasonable option for me so I've written this script to show me everything at once:
-- Show Transactional Publications and Subscriptions to articles at Distributor -- Run this on the DISTRIBUTOR -- Add a WHERE clause to limit results to one publisher\subscriber\publication\etc SELECT publishers.srvname AS [Publisher] , publications.publisher_db AS [Publisher DB] , publications.publication AS [Publication] , subscribers.srvname AS [Subscriber] , subscriptions.subscriber_db AS [Subscriber DB] , articles.article AS [Article] FROM sys.sysservers AS publishers INNER JOIN distribution.dbo.MSarticles AS articles ON publishers.srvid = articles.publisher_id INNER JOIN distribution.dbo.MSpublications AS publications ON articles.publisher_id = publications.publisher_id AND articles.publication_id = publications.publication_id INNER JOIN distribution.dbo.MSsubscriptions AS subscriptions ON articles.publisher_id = subscriptions.publisher_id AND articles.publication_id = subscriptions.publication_id AND articles.article_id = subscriptions.article_id INNER JOIN sys.sysservers AS subscribers ON subscriptions.subscriber_id = subscribers.srvid -- Limit results to subscriber --WHERE subscribers.srvname = '[Subscriber Server Name]' ---- Limit results to publisher and publication --WHERE publishers.srvname = '[Publisher Server Name]' -- AND MSpublications.publication = '[Publication Name]' ORDER BY publishers.srvname , subscribers.srvname , publications.publication , articles.article;
This script also works for distributors running SQL 2000; just substitute master.dbo.sysservers in place of sys.sysservers.
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
1 comment
I'm in the same boat, couple thousand publications across dozens of publishers, and have written a similar query in the past. But I've added something to show when the subscriber table name is not the same as the published version (because having publisher table = subscriber table would just be too easy to keep straight). Then export it to Excel, set up filtering/drop-downs, and other users can quickly see what tables are going where.
Post a Comment