Scripts to get Replication Configuration Information

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
-- Get the publication name based on article
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, 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
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
db_name() PublisherDB
, as PublisherName
, 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
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
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: