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.
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
— Get the publication name based on article
, p.publication publication_name
, ss.srvname subscription_server
, 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 (
IF DATABASEPROPERTYEX ( db_name() , ”IsPublished” ) = 1
insert into #tmp_replcationInfo
, 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
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
SELECT distinct publisher, publisher_db, publication
ORDER BY 1,2,3