Scripts to get Replication Configuration Information

Replication Scripts to get Replication Configuration Information :

The scripts can be run from various databases to pull this information and if you have multiple servers for replication like we do, you can use the Multi Server Query feature to query several servers at one time.  Execute same SQL Server query across multiple servers at the same time using Central Management Servers .

These scripts work for SQL 2005 , SQL 2008 and SQL 2012.

General Overview

I have three different scripts that you can use:

  • Script to run on Distribution database – This script returns completed setup replication information.
  • Script to run on Publisher database – This script returns what publications have been setup.
  • Script to run on Subscriber database – This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find any orphaned subscribers.

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information. I recommend making this a stored procedure and then creating a Reporting Services report, so that anyone can easily access this data.

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 run on Publisher database

This script returns what publications have been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = ‘Y’ it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName

— 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 Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

— Run from Subscriber Database

Use Subscriber Database

GO
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.