Using Replication Scripts to Get Replication Configuration Information:
The scripts may be run from a variety of databases to pull this data, and if you have a lot of servers for replication, as we do, you can use the Multi Server Query feature to query numerous servers at once. Run the same SQL Server query on multiple servers at the same time using Central Management Servers.
SQL 2005, 2008, and 2012 are all supported by these scripts.
A Summary of the Situation
There are three scripts to pick from:
This script should be performed on the Distribution database and delivers completed setup replication information.
This script queries the Publisher database and produces a list of newly produced publications.
This script retrieves the article(s) that are currently being replicated to the subscriber database. I also use this method to locate any orphaned subscribers.
Script to be executed on the Distribution database.
This script returns information about the completed replication setup. Unless an orphan article exists, this will return a complete set of replication information. To demonstrate how straightforward it is to pull in extra configuration data, I also included the name of the distribution agent task. This should be turned into a stored process and then into a Reporting Services report so that anybody can see it.
USE Distribution GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Get the publication name based on article SELECT DISTINCT srv.srvname publication_server , a.publisher_db , p.publication publication_name , a.article , a.destination_object , ss.srvname subscription_server , s.subscriber_db , da.name AS distribution_agent_job_name FROM MSArticles a JOIN MSpublications p ON a.publication_id = p.publication_id JOIN MSsubscriptions s ON p.publication_id = s.publication_id JOIN master..sysservers ss ON s.subscriber_id = ss.srvid JOIN master..sysservers srv ON srv.srvid = p.publisher_id JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id ORDER BY 1,2,3
Script to be executed on the Publisher database
The publications that have been created are displayed by this script. This command will walk through all of the published databases and provide information whether replication is enabled in the database. I sometimes just want to see the publication name and subscriber server names (no articles) to see which servers are being used for replication, but other times I want all of the information, thus I created a variable called @Detail, which returns data with the article list when set to ‘Y’. If any other option is specified, only the publisherDB, publisherName, and SubscriberServerName will be returned.
-- Run from Publisher Database -- Get information for all databases DECLARE @Detail CHAR(1) SET @Detail = 'Y' CREATE TABLE #tmp_replcationInfo ( PublisherDB VARCHAR(128), PublisherName VARCHAR(128), TableName VARCHAR(128), SubscriberServerName VARCHAR(128), ) EXEC sp_msforeachdb 'use ?; IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 insert into #tmp_replcationInfo select db_name() PublisherDB , sp.name as PublisherName , sa.name as TableName , UPPER(srv.srvname) as SubscriberServerName from dbo.syspublications sp join dbo.sysarticles sa on sp.pubid = sa.pubid join dbo.syssubscriptions s on sa.artid = s.artid join master.dbo.sysservers srv on s.srvid = srv.srvid ' IF @Detail = 'Y' SELECT * FROM #tmp_replcationInfo ELSE SELECT DISTINCT PublisherDB ,PublisherName ,SubscriberServerName FROM #tmp_replcationInfo DROP TABLE #tmp_replcationInfo
Script to run on the database of Subscribers
This script displays which article(s) are currently being replicated to the subscriber database. This is also how I locate orphaned subscribers. Because there isn’t much data to pull, this is an easy task.
— Use the Subscriber Database as a starting point.
Use Subscriber Database GO SELECT distinct publisher, publisher_db, publication FROM dbo.MSreplication_subscriptions ORDER BY 1,2,3