Replication Comparison between Different Servers


How do I compare the replication articles between two servers?


USE Distribution
SELECT prodPub.Publication AS [Prod Publication], 
      prodArticle.Article AS [Prod Article], 
      dev.Publication AS [Dev Publication], 
      dev.Article AS [Dev Article]
FROM MsArticles prodArticle
      JOIN MsPublications prodPub ON prodPub.Publication_ID = prodArticle.Publication_ID
      LEFT JOIN ( 
            SELECT devPub.Publication, devArticle.Article 
            FROM DevServer.Distribution.dbo.MsArticles devArticle 
                  JOIN DevServer.Distribution.dbo.MsPublications devPub ON devPub.Publication_ID = devArticle.Publication_ID 
            ) dev ON dev.Publication = prodPub.Publication AND dev.Article = prodArticle.Article
ORDER BY prodPub.Publication, prodArticle.Article

This only works if you run it on production and have linked servers to development.

Here's the scenario, you have two servers. The first server is production. The second server is development. You want to compare what you are replicating in production to what you are replicating on development.

Running this query shows the publications and articles between the two servers.


I am not a DBA, but I play one at work. I welcome your feedback.