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


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 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:

--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





ċ
Andy Hughes,
Jun 6, 2020, 10:51 AM
Comments