Hello my followers  in the previous post i started my new series in (SQL Server DMV Replication Monitoring) and i explained very interested point for any DBA WORKING on replication solutions How we can know the current status of our Publication and subscription is it Active , In Active by simple DMV depend on dynamic Query technique and on SQL Server cursor TECHNOLOGY .

 

Summary of the previous post:
1- Publication Status
2- Subscription Status
3- Send Email by the Current status

 Replication in SQL Server Series  

1     2      3       4 

DMV Replication Monitoring Series  1     2

Today is  will complete this series to be this part is part two and i will add 2 new point very helpful for our daily WORK :

• Replication information for the Complete setup.
  
• Find publication article.

Replication information for the Complete setup:

IF you going for Health check or for any new server you should know the Configuration setup for the SQL Server instance , SQL Server database, and any Disaster recovery solution build on your instance one of this solutions is Replication by the below DMV you can know the highlights for your replication setup

[sql]

----completed setup replication information
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

[/sql]

Based on this script we can know the below point :

• Publication Server name .
• Publisher Databases name.
• Publication Name .
• Tables name in the replication (Article).
• destination OBJECT name for each (Article).
• Subscription server name.
• subscription databases name.
• subscription AGENT JOB name.

Find publication article:

As usual we RECEIVED lot of changes from developers some of this changes on table level and as we know we can not do any change on any table if this table part from replication so we need to remove it at the frist then do our update then return it again to the replication and at the end we need to do reinitialize for our replication  {don’t do this direct on Production do it first on testing or al least on your staging environment to estimate the impact and the time of the SQL Server replication reinitialize }

based on that i do one DMV as stored Procedure {Find_puplication_article} to do this operation for us this sp take one parameter table name if this table joined any replication the result  will be the replication name the table joined it.

[sql]

Create Proc Find_puplication_article (@articleName Nvarchar(200))
AS
begin
Set nocount on
--Return all the List of article for each puplication
IF @articleName = '' or @articleName IS NULL
begin
SELECT
 MSP.publication AS 'Publication Name'
 ,MSA.publisher_db AS 'Database Name'
 ,MSA.article AS 'Article Name'
 ,MSA.source_owner AS 'Schema Name'
 ,MSA.source_object AS 'Table Name'
FROM
 DBO.MSarticles AS MSA
INNER JOIN DBO.MSpublications AS MSP
 ON MSA.publication_id = MSP.publication_id
 ORDER BY
 MSP.publication, MSA.article;
END
ELSE IF @articleName IS NOT NULL
begin
SELECT
 MSP.publication AS 'Publication Name'
 ,MSA.publisher_db AS 'Database Name'
 ,MSA.article AS 'Article Name'
 ,MSA.source_owner AS 'Schema Name'
 ,MSA.source_object AS 'Table Name'
FROM
 DBO.MSarticles AS MSA
INNER JOIN DBO.MSpublications AS MSP
 ON MSA.publication_id = MSP.publication_id
 WHERE MSA.article = @ArticleName
 ORDER BY
 MSP.publication, MSA.article;
END
END

[/sql]

Execute Stored Procedure

[sql]

Use distribution
GO
Exec Find_puplication_article 'Address'

[/sql]

Follow me to know more SQL Server DMV in Replication Monitoring

Follow  the Author :

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.