Problem
My company uses SQL Server replication for moving data between servers and we have several publications with many articles being replicated. Therefore, I get questions all of the time about what tables are being replicated, what databases are published, what servers are subscribers, etc… This information can be pulled using SQL Server Management Studio, but it takes a long time to do manually since there are so many properties for each publication. In this tip I will show you three basic scripts that I use to help answer these questions.
Solution
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. See the Execute same SQL Server query across multiple servers at the same time using Central Management Servers article for more information.
These scripts work for SQL 2005 and SQL 2008.
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
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3
Next Steps
- These are basic scripts that you can use to start and then begin to add more information as needed.
- You can use a Multi Server Query to pull information from multiple servers at one time.
- Think about exposing the data from the queries using Reporting Servers reports so developers can access this data easily
- You can also get additional related information like replication history by adding a few more columns to the output.
- Check out these other replication tips
