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

HOwever, replication is really something that you’re not going to learn through a book. You’re going to learn by working with replication in a real environment. And chances are a real replication environment is going to use more than one publication against groupings of subscribers.

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.

Happy scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s