Get Replication Details
Applicability:
SQL Server 2000: N/A
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Not Tested
Credits:
Author:ChillyDBA
Date: 6 Jun 2020
Description
Get a list of replication Publications, Subscriptions and Articles
Must be run against the server hosting the Replication Distribution database
Useful for identifying databases/tables on the source server that might be involved in replication.
Further planning can then be undertaken to ensure that the relevant publications and/or subscriptions are also recreated on the migrated DB
NOTE: Replication publisher can only be +/- 2 SQL Server versions different from the publisher, so this can sometimes put a real spanner in the works :-)
GENERATE script on: n/a
EXECUTE script on: DISTRIBUTION SERVER
** There is some additional code included below, which was created on SQL Server 2008 many years ago, and which gets a slightly different set of metadata about replication. Included here for completeness
Code
IF EXISTS (SELECT 1 FROM master..sysdatabases WHERE name = 'Distribution')BEGIN -- Get the publication name based on article SELECT DISTINCT p.publication AS Publication_Name ,srv.srvname AS Publication_Server ,a.publisher_db AS Publication_Database ,a.article AS Publication_Table_Name ,ss.srvname AS Subscription_Server ,s.subscriber_db AS Subscription_Database ,a.destination_object AS Subscription_Table_Name ,da.subscriber_login AS Subscription_Login ,da.name AS Distribution_Agent_Job_Name FROM Distribution..MSArticles a JOIN Distribution..MSpublications p ON a.publication_id = p.publication_id JOIN Distribution..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 Distribution..MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id ORDER BY 1,2,3 END
Additional Code:
--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, publicationFROM dbo.MSreplication_subscriptionsORDER BY 1,2,3