SQL Server Replication: Finding Duplicate Articles in Publications
I’ve been working with SQL Server Replication recently, and there are plenty of resources available online to help you get started, including
One such risk with using multiple publications is that you could end up with articles in more than one publication. Having articles in more than one publication will increase the number of commands to be replicated in the distributor. Whilst sometimes this is intentional, this can put a strain on your distributor, particularly during busy times.
There’s no obvious way to find articles that are in more than one publication through the UI, so I wrote a script to run against the Distributor database to discover any duplicated articles:
--Find articles in more than one publication ;with cte (ArticleName, Total) AS ( select a.article, COUNT (1) from msarticles a group by a.article HAVING COUNT (1) > 1 ) select c.ArticleName, pub.publication from cte c inner join MSarticles art on art.article = c.ArticleName inner join MSpublications pub on pub.publication_id = art.publication_id order by c.ArticleName asc
Any excuse to use a CTE! Whilst the Replication Monitor is great for checking the environment health right now, the ms* tables in the distributor are extremely useful in gathering information on your replication environment. It’s important to understand that the article name does not necessarily reconcile to the object in the database, so if this is the case then use source_object instead of article.