SQL Server DMV Replication Monitoring Part 1

Hello My followers today i will took in very important subject for any DBA How we can monitor our Replications !! for any replication i have publication and subscription , How i can now is it working or it is active or down or what is the status of it now , based on that i created one DMV build with technique of Dynamic Query and using SQL Server cursor but before you go for Monitoring (advanced level) you should know some points at the beginning :

Replication in SQL Server Part 1

What is SQL Replication?
Types of SQL Replication
Replication Overview

Replication in SQL Server Part 2

How to create Transaction Replication

Replication in SQL Server Part 3

How to Add New Publisher Server to the Distributor Server

Replication in SQL Server Part 4

Disable Publishing and Distribution Servers

Now after we explained the main highlights for the replication lets see How we can monitor the status of the publication and subscription? and How we can receive email if any one of them is InActive :

First i return all databases used in replication

[sql]

SELECT
 MSA.publisher_db AS 'Database Name'
FROM
 distribution.DBO.MSarticles AS MSA
OPEN db_cursor
[/sql]

Second declared some parameters i will used it in my DMV

[sql]

Declare @Publisher_Status VARCHAR (50) ,@subscriber_Status VARCHAR (50) ,
@servername varchar (100) =@@servername,@Publication_name varchar (100),
@ERROR_MESSASGE nvarchar (4000),@TotalMessage nvarchar (4000),
@Mailsubject varchar (4000),@mail_profile_name varchar(200)=
(select top 1 name from msdb..sysmail_profile)

[/sql]

third Start the cursor to loop in each databases to fill the Parameters i created it in the second step

[sql]

select @Publication_name= publication,@Publisher_Status=Publisher_Status ,@subscriber_Status=subscriber_Status
from (
Select p.name As 'publication'
 , p.description
 , s.srvname As 'subscriber'
 , s.dest_db As 'suscriber_db'
 , Count(a.artid) As 'articles'
 , Case
 When p.status = 0 Then 'Inactive'
 When p.status = 1 Then 'Active'
 End as 'Publisher_Status '
 , Case
 When s.status = 0 Then 'Inactive'
 When s.status = 1 Then 'Subscribed'
 When s.status = 2 Then 'Active'
 End as 'subscriber_Status'
From syspublications As p
Join sysarticles As a
 On p.pubid = a.pubid
Join syssubscriptions As s
 On a.artid = s.artid
Group By p.name
 , p.description
 , s.srvname
 , s.dest_db
 , Case
 When p.status = 0 Then 'Inactive'
 When p.status = 1 Then 'Active'
 End
 , Case
 When s.status = 0 Then 'Inactive'
 When s.status = 1 Then 'Subscribed'
 When s.status = 2 Then 'Active'
 End) s

select @servername, @Publication_name,@Publisher_Status,@subscriber_Status

[/sql]

Four i checked the status for each Database replication if i found the @Publication_name = ‘inactive’ OR @subscriber_Status in (‘Inactive’,’Subscribed’) Send email by this status to the DBA Support level one

[sql]

if @Publisher_Status = 'inactive' or @subscriber_Status in ('Inactive' ,'Subscribed' )
begin
SET @ERROR_MESSASGE = ERROR_MESSAGE()
set @TotalMessage = 'Kindly pay attention as the Publication exists on the publisher
'+@servername+' named with ' +@Publication_name+'
is '+@Publisher_Status +' and its subscriber is '+@subscriber_Status
set @subject =N'Failure of Replication on '+@servername
EXECUTE msdb.dbo.sp_notify_operator @name= N'dbalerts',@subject=@subject ,@body=@TotalMessage
end

[/sql]

By this way we can do one SQL SERVER JOB to run every 5 second to check for me the status for all the replication on my production server and if something happened wrong in the replication i will receive email direct by the current status of the replication

Script disclaimer, for people who need to be told this sort of thing:

DISCLAIMER: Do not run code you find on the internet in your production Environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code.

You can also download a copy of the script DMV_Replication_Monitor Stored Procedures.

[sql]

USE MSDB
GO
Create Proc DMV_Replication_Monitor
As
begin
/*=============================================
 File: DMV_Replication_Monitor.sql

 Author: Mustafa EL-Masry, https://mostafaelmasry.wordpress.com/about-me/

 Summary: This script will return the following items:
 1- Publication Status
 2- Subscription Status
 3- Send Email by the Current status

 Variables:
 None

 Date: March 24tu, 2015

 SQL Server Versions: SQL2008R2, SQL2012, SQL2014

 You may alter this code for your own purposes. You may republish
 altered code as long as you give due CREDIT.

 THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
 OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
 LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
 FITNESS FOR A PARTICULAR PURPOSE.

=============================================*/
SET NOCOUNT ON
DECLARE @name VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
SELECT
 MSA.publisher_db AS 'Database Name'
FROM
 distribution.DBO.MSarticles AS MSA
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
-------T-SQL
Declare @SQL Nvarchar(MAX) ='USE ' + QUOTENAME(@Name)
 + ';
Set NOCOUNT ON
Declare @Publisher_Status VARCHAR (50) ,@subscriber_Status VARCHAR (50) ,
@servername varchar (100) =@@servername,@Publication_name varchar (100),
@ERROR_MESSASGE nvarchar (4000),@TotalMessage nvarchar (4000),
@Mailsubject varchar (4000),@mail_profile_name varchar(200)=
(select top 1 name from msdb..sysmail_profile)

select @Publication_name= publication,@Publisher_Status=Publisher_Status ,@subscriber_Status=subscriber_Status
from (
Select p.name As ''publication''
 , p.description
 , s.srvname As ''subscriber''
 , s.dest_db As ''suscriber_db''
 , Count(a.artid) As ''articles''
 , Case
 When p.status = 0 Then ''Inactive''
 When p.status = 1 Then ''Active''
 End as ''Publisher_Status ''
 , Case
 When s.status = 0 Then ''Inactive''
 When s.status = 1 Then ''Subscribed''
 When s.status = 2 Then ''Active''
 End as ''subscriber_Status''
From syspublications As p
Join sysarticles As a
 On p.pubid = a.pubid
Join syssubscriptions As s
 On a.artid = s.artid
Group By p.name
 , p.description
 , s.srvname
 , s.dest_db
 , Case
 When p.status = 0 Then ''Inactive''
 When p.status = 1 Then ''Active''
 End
 , Case
 When s.status = 0 Then ''Inactive''
 When s.status = 1 Then ''Subscribed''
 When s.status = 2 Then ''Active''
 End) s

select @servername, @Publication_name,@Publisher_Status,@subscriber_Status
if @Publisher_Status = ''inactive'' or @subscriber_Status in (''Inactive'' ,''Subscribed'' )
begin
SET @ERROR_MESSASGE = ERROR_MESSAGE()
set @TotalMessage = ''Kindly pay attention as the Publication exists on the publisher
''+@servername+'' named with '' +@Publication_name+'' is ''+@Publisher_Status +''
and its subscriber is ''+@subscriber_Status
set @Mailsubject =N''Failure of Replication on ''+@servername
EXEC sp_send_dbmail
@profile_name=@mail_profile_name ,
@recipients =''Eng.Mostafa_Elmasry@WindowsLive.Com'',
@subject=@Mailsubject,
@body=@TotalMessage
end'
Exec SP_ExecuteSQL @SQL
 FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

[/sql]

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