This will ultimately need tidying up. You can run this query on the distribution node in a SQL replication environment. It’ll give you all the publisher servers and databases, along with the subscriber servers and databases. It will also give you the article count.
Some notes on the code
First, I think this is pretty cool. I added the article count so I can quickly see if there are differences between duplicate publications to either the same subscriber or another. It gives a easy ‘catch’ to check for slightly different publications if you need them to be the same.
Second, and the reason why I’m posting this is I was trying to find the server name for the publisher and subscriber. The internet told me to JOIN the sys.servers table, which gives you the *wrong* data. Instead it’s the MSreplservers table in the distribution database itself. Hey I found this interesting as it caused me to waste time trying to figure it out, hence the post.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT a.c AS 'article_count', s.subscriber_id, ss1.srvname, p.publisher_db AS publisher_server, p.publication, ss2.srvname AS subscriber_server, s.subscriber_db, da.name AS job_name FROM MSpublications p --ON a.publication_id = p.publication_id JOIN MSsubscriptions s ON p.publication_id = s.publication_id JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id JOIN MSreplservers ss1 ON p.publisher_id = ss1.srvid JOIN MSreplservers ss2 ON s.subscriber_id = ss2.srvid CROSS APPLY (SELECT COUNT(*) as c, publication_id FROM MSArticles WHERE s.publication_ID = publication_id GROUP BY publication_id) a GROUP BY p.publisher_db, p.publication, s.subscriber_db, da.name, a.c, ss1.srvname, ss2.srvname, s.subscriber_id |