Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Replication Scripts to get Replication Configuration Information


By:   |   Last Updated: 2009-08-04   |   Comments (15)   |   Related Tips: 1 | 2 | 3 | 4 | More > Replication

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


Last Updated: 2009-08-04


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, September 12, 2016 - 10:03:47 AM - HB Back To Top

I think you want to change the first script to be as follows:

 

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.dbo.sysservers ss ON s.subscriber_id = ss.srvid 

JOIN master.dbo.sysservers srv ON srv.srvid = p.publisher_id 

JOIN MSdistribution_agents da ON da.id = s.agent_id --changed this join

ORDER BY 1,2,3  

 


Wednesday, March 30, 2016 - 2:51:25 AM - Azim Back To Top

Following join may improve bit furthar

JOIN MSdistribution_agents da ON da.publication = p.publication AND da.subscriber_id = s.subscriber_id AND da.publisher_database_id = s.publisher_database_id


Wednesday, June 17, 2015 - 1:55:48 PM - Del Lee Back To Top

Your statement that the script to run on the subscriber database that returns what articles are being replicated seems innacurate.  By articles, I would expect to see what tables, stored procs, views, or udf's have been set up to be replicated.  Your query only returns a server instance, the database itself, and the publication job.  Perhaps that is useful as you say, but it would be nice to see a detailed list of articles without going into the properties to take a look at what is checked for publication.


Friday, October 24, 2014 - 9:53:34 AM - Jeff Back To Top

 

There are a few joins missing from the first query. Below is the query without the DISTINCT keywork and a couple more joins. 

SELECT  

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 a.publication_id = s.publication_id 

and a.article_id = s.article_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 

AND da.publisher_database_id = s.publisher_database_id

ORDER BY 1,2,3  

 

Thursday, July 24, 2014 - 6:30:56 AM - Natraj Back To Top

Hi Kun,

Very nice artical it is helping me a lot, I have one query.

How can we find which replication ( type ) is configured through query.

Thanks

Natraj.A

 

 

 

 


Thursday, November 21, 2013 - 9:11:38 AM - Kun Back To Top

Dear Anand,

 

Thank you so much for the script for Merge!! I didn't noticed until now. I am so sorry about late response.

 

Best regards,

Kun


Thursday, November 21, 2013 - 9:10:57 AM - Kun Back To Top

Snehasis

 

I would guess that would be correct statement. First, it is Oracle so you won't be able to run any TSQL but PLSQL obviously. So, the best bet would be running script from distributor and also subscribers.

 

Again, I can research on it but since I don't have server to confirm and I haven't touch Oracle for a long time.

 

BTW, this script isn't about configuring the replication. It is about getting information out of existing replication. I just want to double check and I believe you already followed below to setup the configuration. Correct? 

http://msdn.microsoft.com/en-us/library/ms151243.aspx

 

Thank you,

Kun

 


Wednesday, November 20, 2013 - 8:24:08 PM - Snehasis Back To Top

 

Hi Kun,

 

Thanks for your response.I am confused as the Steps you have mentioned to run in the publisher are SQL procedure i.e sp_msforeachdb and as my publisher is Oracle so these Procedure doesn't Exists in oracle.So how can i go about that.I am struck Since a month in it.

Your Reponse will be a great help for me.

 

Thanks,


Wednesday, November 20, 2013 - 1:02:08 PM - Kun Lee Back To Top

Dear Snehasis

 

I am so sorry for late response. Unfortuatly, we don't have Oracle database in house so I can't really test it out until I setup a lab which will take longer time for me to do. I hope you can twick the query to get the similar information as the distributor still should be SQL.

I will be curious about that so let me see if I can find any environment that has that.

Regards,

Kun


Wednesday, November 13, 2013 - 3:42:58 PM - snehasis Back To Top

Hi Kun,

My Bussiness had that samerequirements to setup the SQL Replication but in our case the publisher is Oracle Database and distributer and subscriber are SQL Server.So in that case how i will run the queries which you have provided earlier as it belongs to SQL Serveron publisher.So can you provide me the scripts to Configure Sql Replication if my publisher is Oracle.

Thats shall be a great help for me.

Awaiting for you Reply.

Thanks,

Snehasis.


Tuesday, September 10, 2013 - 7:10:05 PM - sanjay Back To Top

Is the first script missing some joins ?  Looks like the articles are listed multiple time with different agent names.


Tuesday, May 07, 2013 - 1:39:17 PM - Anand Back To Top

Very nice script Kun. This script works for Snapshot and Transactional replicaiton but not for merge.
 
To find details of Merge replication run the below mentioned couple of queries

--Provide the name of the database on which merge replication is setup
select name from sys.databases where is_merge_published = 1

--run the below query on merge replicated database
SELECT smp.publisher, smp.publisher_db, smp.name, smp.distributor, sma.Name, sma.destination_object,
sma.destination_owner ,
sms.subscriber_server, sms.db_name, sms.subscriber_type,sms.sync_type, sms.last_sync_date, sms.last_sync_status
FROM
sysmergearticles sma inner join sysmergepublications smp on
sma.pubid = smp.pubid inner join
sysmergesubscriptions sms on sms.pubid = smp.pubid

Thanks
Anand


Tuesday, May 07, 2013 - 1:37:47 PM - Anand Back To Top

Very nice script Kun. This script works for Snapshot and Transactional replicaiton but not for merge.

To find details of Merge replication run the below mentioned couple of queries

--Provide the name of the database on which merge replication is setup

 

 

select

 

 

 

namefromsys.databaseswhereis_merge_published= 1

 

--run the below query on merge replicated database

 

 

SELECT

smp.publisher,smp.publisher_db,smp.name,smp.distributor,sma.Name,sma.destination_object,

 

sma

.destination_owner,

 

sms

.subscriber_server,sms.db_name,sms.subscriber_type,sms.sync_type,sms.last_sync_date,sms.last_sync_status

 

FROM

 

 

sysmergearticles

smainnerjoinsysmergepublicationssmpon

 

sma

.pubid=smp.pubidinnerjoin

 

sysmergesubscriptions

smsonsms.pubid=smp.pubid

 

 


Tuesday, March 19, 2013 - 12:09:50 PM - Srinath Back To Top

Thank you Kun for such great scripts !! Looking forward for more such articles from you!


Monday, September 24, 2012 - 2:35:12 AM - Gau Back To Top
The only way can think of to do it is to cast the NULL values as stohmeing else. In Access that would be nz(column_name,-9999) or stohmeing. I agree about Access in general, I'm forced to deal with a similar situation apps built in Access prior to SQL Server being installed with eccentric coding practices, little documentation, etc. The worst part is they never migrated completely to SQL Server so I have Access dbs interacting with SQL Server and vice-versa. Joy! Good luck!

Learn more about SQL Server tools