Scripts to get Replication Configuration Information

Using Replication Scripts to Get Replication Configuration Information:

The scripts may be run from a variety of databases to pull this data, and if you have a lot of servers for replication, as we do, you can use the Multi Server Query feature to query numerous servers at once. Run the same SQL Server query on multiple servers at the same time using Central Management Servers.

SQL 2005, 2008, and 2012 are all supported by these scripts.

A Summary of the Situation

There are three scripts to pick from:

This script should be performed on the Distribution database and delivers completed setup replication information.
This script queries the Publisher database and produces a list of newly produced publications.
This script retrieves the article(s) that are currently being replicated to the subscriber database. I also use this method to locate any orphaned subscribers.


Script to be executed on the Distribution database.

This script returns information about the completed replication setup. Unless an orphan article exists, this will return a complete set of replication information. To demonstrate how straightforward it is to pull in extra configuration data, I also included the name of the distribution agent task. This should be turned into a stored process and then into a Reporting Services report so that anybody can see it.

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 be executed on the Publisher database

The publications that have been created are displayed by this script. This command will walk through all of the published databases and provide information whether replication is enabled in the database. I sometimes just want to see the publication name and subscriber server names (no articles) to see which servers are being used for replication, but other times I want all of the information, thus I created a variable called @Detail, which returns data with the article list when set to ‘Y’. If any other option is specified, only the publisherDB, publisherName, and SubscriberServerName will be returned.

-- 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 the database of Subscribers

This script displays which article(s) are currently being replicated to the subscriber database. This is also how I locate orphaned subscribers. Because there isn’t much data to pull, this is an easy task.

— Use the Subscriber Database as a starting point.

Use Subscriber Database
GO
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.