How to Monitor Replication - Automate Replication Monitoring

Script to Automate The Replication Monitoring

Usually we need to monitor replication periodically through out the day. I have designed a solution to monitor replication form T-SQL. It'll send a mail to DBA team that replication is failing. Create the below two objects (Table and PROC) and create a job which calls the stored procedure for every 5 minutes.

This will save us a lot of time and effort for sure

CREATE TABLE dbo.repmonitor (

[ID] INT NOT NULL IDENTITY,

[status] int null,

warning int null ,

subscriber sysname null ,

subscriber_db sysname null ,

publisher_db sysname null ,

publication sysname null ,

publication_type int null ,

subtype int null ,

latency int null ,

latencythreshold int null ,

agentnotrunning int null ,

agentnotrunningthreshold int null ,

timetoexpiration int null ,

expirationthreshold int null ,

last_distsync datetime null ,

distribution_agentname sysname null ,

mergeagentname sysname null ,

mergesubscriptionfriendlyname sysname null ,

mergeagentlocation sysname null ,

mergeconnectiontype int null ,

mergePerformance int null ,

mergerunspeed float null ,

mergerunduration int null ,

monitorranking int null ,

distributionagentjobid binary(30) null ,

mergeagentjobid binary(30) null ,

distributionagentid int null ,

distributionagentprofileid int null ,

mergeagentid int null ,

mergeagentprofileid int null ,

logreaderagentname sysname null

)

--EXEC usp_replmonitor_Create

/*** Create the SPOC on Monitor Database ***/

/*** It requires the Publisher Name as parameter ***/

CREATE PROC usp_replmonitor_Create (@Publisher_Name VARCHAR(200))

AS

BEGIN

SET NOCOUNT ON

DECLARE @Pub_Type TINYINT

DECLARE @C INT

DECLARE @DBProfile VARCHAR(100)

DECLARE @ReplM TABLE

([ID] INT NOT NULL IDENTITY,

subscriber sysname null ,

publication sysname null ,

last_distsync datetime null

)

SELECT @DBProfile=Name FROM msdb.dbo.sysmail_profile WHERE Profile_ID=1

/*** Clears the previous data ***/

TRUNCATE TABLE repmonitor

/*** For Transactional Replication ***/

SET @Pub_Type=0

INSERT INTO repmonitor

EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]

@publisher = @Publisher_Name,

@publication_type = @Pub_Type

/*** For Snapshot Replication ***/

SET @Pub_Type=1

INSERT INTO repmonitor

EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]

@publisher = @Publisher_Name,

@publication_type = @Pub_Type

/*** For Merge Replication ***/

SET @Pub_Type=2

INSERT INTO repmonitor

EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]

@publisher = @Publisher_Name,

@publication_type = @Pub_Type

/*** Check if any subscription is failing ***/

SET @C=0

SELECT @C=COUNT(1) FROM repmonitor WHERE Status=6

IF(@C>0)

BEGIN

DECLARE @String varchar(8000)

SET @String='Replication is Failing @ '+@Publisher_Name+

' for below Subscribers'+CHAR(13)+CHAR(13)

SET @String=@String+' Subscriber'+' '+' Publication'+char(13)

SELECT @String =@String+subscriber+' '+publication+char(13)

FROM repmonitor WHERE Status=6

/*** Notify DBA Group ***/

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @DBProfile,

@recipients = 'DBAGroup@SQLServer.com',

@body = @String,

@subject = 'Replication is Failing';

END

END

Export to Excel from SQL Server database.

Export to Excel from SQL Server database.

We can use

Import / Export Wizard

BCP

OPENROWSET

Below stored procedure will make the task easy.

USE MASTER

/*** Make sure that the below are enabled on your server ***/

/*** Ad Hoc Remote Quires

XP_CMDSHELL

***/

/****** Object: StoredProcedure [dbo].[usp_ExportData_Excel]

Script Date: 10/05/2009 08:39:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*** Execution Example

EXEC [usp_ExportData_Excel] @dbName = 'Master',

@sql = 'select * from master.sys.sysdatabases',

@fullFileName = 'D:\Table_Details.xls' ,

@InstanceName ='',

@User = '',

@pwd = ''

***/

CREATE PROC [dbo].[usp_ExportData_Excel]

(

@dbName VARCHAR(100) = 'master',

@sql VARCHAR(8000) = 'select * from master.sys.sysdatabases',

@fullFileName VARCHAR(100) = 'D:\Table_Details.xls',

@InstanceName VARCHAR(100) = '',

@User VARCHAR(20) = '',

@Pwd VARCHAR(20) = ''

)

AS

BEGIN

SET NOCOUNT ON

IF @sql = '' or @fullFileName = ''

BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

-- if DB isn't passed in set it to master

SELECT @dbName = 'use ' + @dbName + ';'

IF object_id('##TempExportData') is not null

DROP TABLE ##TempExportData

IF object_id('##TempExportData2') is not null

DROP TABLE ##TempExportData2

-- insert data into a global temp table

DECLARE @columnNames VARCHAR(8000),

@columnConvert VARCHAR(8000),

@tempSQL VARCHAR(8000)

SELECT @tempSQL = left(@sql, charindex('from', @sql)-1) +

' into ##TempExportData ' +

substring(@sql, charindex('from', @sql)-1, len(@sql))

EXEC(@dbName + @tempSQL)

IF @@error > 0

BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

-- build 2 lists

-- 1. column names

-- 2. columns converted to nvarchar

SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,

@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'

+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'

WHEN data_type in ('numeric', 'decimal') then ',128'

WHEN data_type in ('float', 'real', 'money', 'smallmoney') then ',2'

WHEN data_type in ('datetime', 'smalldatetime') then ',120'

ELSE ''

END + ') as ' + column_name

FROM tempdb.INFORMATION_SCHEMA.Columns

WHERE table_name = '##TempExportData'

-- execute select query to insert data and column names into new temp table

SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from

(select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData

union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t

order by [temp##SortID]'

exec (@sql)

-- BUILD FULL BCP QUERY

DECLARE @bcpCommand VARCHAR(8000)

SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'

SET @bcpCommand = @bcpCommand +' '+@fullFileName+' -S'+

@InstanceName+' -c -w -T -U '+@User+' -P '+@pwd+'","-CRAW'

EXEC master..xp_cmdshell @bcpCommand

IF @@ERROR > 0

BEGIN

SELECT 0 AS ReturnValue -- failure

RETURN

END

DROP TABLE ##TempExportData

DROP TABLE ##TempExportData2

SET @columnNames =' '

SET @columnConvert =' '

SET @tempSQL =' '

SELECT 1 as ReturnValue

END