June 4, 2015 at 4:05 am
I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.
Please let me know if any one you have this code.
Thank you in Advance.
June 4, 2015 at 10:23 am
Perhaps something like this would be useful for you. Seems like it's doing nearly exactly what you want.
/****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]
AS
/* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"
- meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs
It will produce a list of the offending
DBs that will require attention and also pass the query results of the non-synced tables in the email
*/
--Create a threshold for how much of a difference we can tolerate between indentity gaps:
--We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:
Declare @Threshold INT
Set@Threshold = 10
--Drop our temp processing table if it exists
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
--Build the table:
Create Table #TempProcessing (
DatabaseName varchar(255)
,[Table] varchar(255)
,[ProductionValue] INT
,[ReplicatedValue] INT
)
---------------------------------------------------------------------------------------------------------------------------
-------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a userid discrepencay
;With UserBucket as
(
select top 1
''User''as [Table]
,uup.UserID as ProductionValue
,uuR.UserID as ReplicatedValue
from [Prod].['+sys.Name+'].[User]. uuP
--Bring in the top userID from replicated data
cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR
order by uup.UserID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.EventID as ProductionValue
,uuR.EventID as ReplicatedValue
from [Prod].['+sys.Name+'].[Event].[Event] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR
order by uup.EventID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.InteractionID as ProductionValue
,uuR.InteractionID as ReplicatedValue
from [Prod].['+sys.Name+'].[Interaction].[Interaction] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR
order by uup.InteractionID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ExclusionID as ProductionValue
,uuR.ExclusionID as ReplicatedValue
from [Prod].['+sys.Name+'].[User].[Exclusion] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR
order by uup.ExclusionID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
-------------------------------------------------------------------------------------------------------
------------Populate an exceptions table---------------------------------------------------------------
select *
into ##TempExceptions_uspReplicationSyncMonitor
from (
select *
,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]
from #TempProcessing
)sub
where [Difference] = 1
-----------------------------------------------------------------------------------------------
-------------Fire off an email warning with the exception DatabaseNames:-----------------------------
--Only fire the email if we actually have any exceptions:
IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1
Begin
--Variable to hold DatabaseName list string
Declare @ExceptionDatabaseName varchar(max)
--Set to a concatenated list of DatabaseName may be out of sync:
Set @ExceptionDatabaseName =
('The following potential replication issues have been discovered for DatabaseName(s):
' +
STUFF((SELECT Distinct ',' + DatabaseName
from ##TempExceptions_uspReplicationSyncMonitor
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
+
'.
Please investigate ASAP.'
)
--Let us build our query of issues an html table to insert into the email:
--Build the body of the email
Declare @BodyQueryvarchar(MAX)
Set @BodyQuery = cast( (
select td =
cast(DatabaseNameas varchar(300)) + '</td><td>' +
cast([Table]as varchar(300)) + '</td><td>' +
cast(ProductionValueas varchar(25)) + '</td><td>' +
cast(ReplicatedValue as varchar(25))
from
(
select
DatabaseName
,[Table]
,ProductionValue
,ReplicatedValue
from ##TempExceptions_uspReplicationSyncMonitor
) sub
for xml path( 'tr' ), type ) as varchar(max) )
set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>
<table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'
+ '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'
+ replace( replace( @BodyQuery, '<', '<' ), '>', '>' )
+ '</table>'
Set @ExceptionDatabaseName = @ExceptionDatabaseName + '
' +@BodyQuery
--Print @ExceptionDatabaseName
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileNameHere',
--Change Email addresses here:
@recipients='Test123@Test.com ;Test456@Test.com',
@subject='Urgent!: Replication may be out of sync',
@body= @ExceptionDatabaseName,
@body_format = 'HTML'
END
Else
Begin
Print 'No Issues.'
END
--Drop our temp processing table when we are done:
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
June 4, 2015 at 10:13 pm
Hi.. Thank you for your reply and your code.
I ran the code in below steps but i didn't get any result.
1. I change email address.
2. I ran the code from Publisher server - But no result found.
3. I stooped my subscriber server then i ran the code from Publisher side - But no result found.
4. I ran the code from Subscriber server - But no result found.
5. I stooped the Publisher server then i ran the code from Subscriber server - But no result found.
Please help me on this.
June 5, 2015 at 8:08 am
What was the result when you ran the code?
A couple things to consider...
Did you change the name of the linked server mentioned in the code to the linked server you have?
..This ran in the subscriber DB, hence my linked server to the publishing DB was named 'PROD'
Did you adjust the code in the dynamic SQL portion of the procedure to point to your specific tables?
Do you have a mail profile set up and configured for db.sendmail to work?
June 5, 2015 at 10:17 am
Hi Thanks for reply.
My result is - Command(s) completed successfully.
Yes i have changed link server name , My link server name is [FOR_PROD]
- I ran this code in subscriber DB
-- the dynamic SQL portion of the procedure to point to your specific tables ----> I have not change this part because i am not sure what to change. Please help me on this part
-- mail profile - IS already setup and is working.
June 5, 2015 at 10:18 am
---->>>>>> Please find my code which i am using <<<<<<<< -----------------
/****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULLDROP PROCEDURE uspReplicationSyncMonitor;
go
CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]
AS
/* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"
- meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs
It will produce a list of the offending
DBs that will require attention and also pass the query results of the non-synced tables in the email
*/
--Create a threshold for how much of a difference we can tolerate between indentity gaps:
--We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:
Declare @Threshold INT
Set@Threshold = 10
--Drop our temp processing table if it exists
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
--Build the table:
Create Table #TempProcessing (
DatabaseName varchar(255)
,[Table] varchar(255)
,[ProductionValue] INT
,[ReplicatedValue] INT
)
---------------------------------------------------------------------------------------------------------------------------
-------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a userid discrepencay
;With UserBucket as
(
select top 1
''User''as [Table]
,uup.UserID as ProductionValue
,uuR.UserID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[User]. uuP
--Bring in the top userID from replicated data
cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR
order by uup.UserID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.EventID as ProductionValue
,uuR.EventID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[Event].[Event] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR
order by uup.EventID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.InteractionID as ProductionValue
,uuR.InteractionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[Interaction].[Interaction] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR
order by uup.InteractionID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ExclusionID as ProductionValue
,uuR.ExclusionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[User].[Exclusion] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR
order by uup.ExclusionID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
-------------------------------------------------------------------------------------------------------
------------Populate an exceptions table---------------------------------------------------------------
select *
into ##TempExceptions_uspReplicationSyncMonitor
from (
select *
,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]
from #TempProcessing
)sub
where [Difference] = 1
-----------------------------------------------------------------------------------------------
-------------Fire off an email warning with the exception DatabaseNames:-----------------------------
--Only fire the email if we actually have any exceptions:
IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1
Begin
--Variable to hold DatabaseName list string
Declare @ExceptionDatabaseName varchar(max)
--Set to a concatenated list of DatabaseName may be out of sync:
Set @ExceptionDatabaseName =
('The following potential replication issues have been discovered for DatabaseName(s):
' +
STUFF((SELECT Distinct ',' + DatabaseName
from ##TempExceptions_uspReplicationSyncMonitor
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
+
'.
Please investigate ASAP.'
)
--Let us build our query of issues an html table to insert into the email:
--Build the body of the email
Declare @BodyQueryvarchar(MAX)
Set @BodyQuery = cast( (
select td =
cast(DatabaseNameas varchar(300)) + '</td><td>' +
cast([Table]as varchar(300)) + '</td><td>' +
cast(ProductionValueas varchar(25)) + '</td><td>' +
cast(ReplicatedValue as varchar(25))
from
(
select
DatabaseName
,[Table]
,ProductionValue
,ReplicatedValue
from ##TempExceptions_uspReplicationSyncMonitor
) sub
for xml path( 'tr' ), type ) as varchar(max) )
set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>
<table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'
+ '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'
+ replace( replace( @BodyQuery, '<', '<' ), '>', '>' )
+ '</table>'
Set @ExceptionDatabaseName = @ExceptionDatabaseName + '
' +@BodyQuery
--Print @ExceptionDatabaseName
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileNameHere',
--Change Email addresses here:
@recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',
@subject='Urgent!: Replication may be out of sync',
@body= @ExceptionDatabaseName,
@body_format = 'HTML'
END
Else
Begin
Print 'No Issues.'
END
--Drop our temp processing table when we are done:
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
June 5, 2015 at 12:11 pm
This is the code you're using:
/****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULL DROP PROCEDURE uspReplicationSyncMonitor;
go
CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]
AS
/* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"
- meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs
It will produce a list of the offending
DBs that will require attention and also pass the query results of the non-synced tables in the email
*/
--Create a threshold for how much of a difference we can tolerate between indentity gaps:
--We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:
Declare @Threshold INT
Set @Threshold = 10
--Drop our temp processing table if it exists
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
--Build the table:
Create Table #TempProcessing (
DatabaseName varchar(255)
,[Table] varchar(255)
,[ProductionValue] INT
,[ReplicatedValue] INT
)
---------------------------------------------------------------------------------------------------------------------------
-------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a userid discrepencay
;With UserBucket as
(
select top 1
''User'' as [Table]
,uup.UserID as ProductionValue
,uuR.UserID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[User]. uuP
--Bring in the top userID from replicated data
cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR
order by uup.UserID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.EventID as ProductionValue
,uuR.EventID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[Event].[Event] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR
order by uup.EventID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.InteractionID as ProductionValue
,uuR.InteractionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[Interaction].[Interaction] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR
order by uup.InteractionID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ExclusionID as ProductionValue
,uuR.ExclusionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[User].[Exclusion] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR
order by uup.ExclusionID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
-------------------------------------------------------------------------------------------------------
------------Populate an exceptions table---------------------------------------------------------------
select *
into ##TempExceptions_uspReplicationSyncMonitor
from (
select *
,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]
from #TempProcessing
)sub
where [Difference] = 1
-----------------------------------------------------------------------------------------------
-------------Fire off an email warning with the exception DatabaseNames:-----------------------------
--Only fire the email if we actually have any exceptions:
IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1
Begin
--Variable to hold DatabaseName list string
Declare @ExceptionDatabaseName varchar(max)
--Set to a concatenated list of DatabaseName may be out of sync:
Set @ExceptionDatabaseName =
( 'The following potential replication issues have been discovered for DatabaseName(s):
' +
STUFF((SELECT Distinct ',' + DatabaseName
from ##TempExceptions_uspReplicationSyncMonitor
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
+
'.
Please investigate ASAP.'
)
--Let us build our query of issues an html table to insert into the email:
--Build the body of the email
Declare @BodyQuery varchar(MAX)
Set @BodyQuery = cast( (
select td =
cast(DatabaseName as varchar(300)) + '</td><td>' +
cast([Table] as varchar(300)) + '</td><td>' +
cast(ProductionValue as varchar(25)) + '</td><td>' +
cast(ReplicatedValue as varchar(25))
from
(
select
DatabaseName
,[Table]
,ProductionValue
,ReplicatedValue
from ##TempExceptions_uspReplicationSyncMonitor
) sub
for xml path( 'tr' ), type ) as varchar(max) )
set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>
<table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'
+ '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'
+ replace( replace( @BodyQuery, '<', '<' ), '>', '>' )
+ '</table>'
Set @ExceptionDatabaseName = @ExceptionDatabaseName + '
' +@BodyQuery
--Print @ExceptionDatabaseName
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileNameHere',
--Change Email addresses here:
@recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',
@subject='Urgent!: Replication may be out of sync',
@body= @ExceptionDatabaseName,
@body_format = 'HTML'
END
Else
Begin
Print 'No Issues.'
END
--Drop our temp processing table when we are done:
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
In the dynamic SQL portion of the code (the code that's in red) you must change the structure of the select statements to point to the tables you want to monitor. I'm sure you don't have a table called User.User, Event.Event, Interaction.Interaction, and User.Exclusion...those were just sample table names I've provided for you. This particular select statement is dynamic and wrapped in a cursor because it loops through multiple databases (that all have an identical schema) running those select statements and inserting the results into a Temp Table each time.
You also need to use your profile name for the dbo.sp_send_dbmail stored procedure to work.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='YourProfileNameHere', <<<<-----Change this to your Database Mail profile name***
--Change Email addresses here:
@recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',
@subject='Urgent!: Replication may be out of sync',
@body= @ExceptionDatabaseName,
@body_format = 'HTML'
June 8, 2015 at 9:29 am
I have changed my code dynamic SQL portion.
Database Mail profile = DB_MAIL
I have 4 table = [dbo].[t1],
[dbo].[t2],
[dbo].[t3],
[dbo].[t4]
I have changed the code and result is - Command(s) completed successfully.
Please see the code below.
June 8, 2015 at 9:34 am
/****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULL DROP PROCEDURE uspReplicationSyncMonitor;
go
CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]
AS
/* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"
- meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs
It will produce a list of the offending
DBs that will require attention and also pass the query results of the non-synced tables in the email
*/
--Create a threshold for how much of a difference we can tolerate between indentity gaps:
--We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:
Declare @Threshold INT
Set @Threshold = 10
--Drop our temp processing table if it exists
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
--Build the table:
Create Table #TempProcessing (
DatabaseName varchar(255)
,[Table] varchar(255)
,[ProductionValue] INT
,[ReplicatedValue] INT
)
---------------------------------------------------------------------------------------------------------------------------
-------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a userid discrepencay
;With UserBucket as
(
select top 1
''User'' as [Table]
,uup.UserID as ProductionValue
,uuR.UserID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP
--Bring in the top userID from replicated data
cross apply (select top 1 UserID from ['+sys.Name+'].[dbo].[t1] order by UserID desc) uuR
order by uup.UserID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.EventID as ProductionValue
,uuR.EventID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 EventID from ['+sys.Name+'].[dbo].[t2] order by EventID desc) uuR
order by uup.EventID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.InteractionID as ProductionValue
,uuR.InteractionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 InteractionID from ['+sys.Name+'].[dbo].[t3] order by InteractionID desc) uuR
order by uup.InteractionID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ExclusionID as ProductionValue
,uuR.ExclusionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ExclusionID from ['+sys.Name+'].[dbo].[t4] order by ExclusionID desc) uuR
order by uup.ExclusionID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
-------------------------------------------------------------------------------------------------------
------------Populate an exceptions table---------------------------------------------------------------
select *
into ##TempExceptions_uspReplicationSyncMonitor
from (
select *
,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]
from #TempProcessing
)sub
where [Difference] = 1
-----------------------------------------------------------------------------------------------
-------------Fire off an email warning with the exception DatabaseNames:-----------------------------
--Only fire the email if we actually have any exceptions:
IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1
Begin
--Variable to hold DatabaseName list string
Declare @ExceptionDatabaseName varchar(max)
--Set to a concatenated list of DatabaseName may be out of sync:
Set @ExceptionDatabaseName =
( 'The following potential replication issues have been discovered for DatabaseName(s):
' +
STUFF((SELECT Distinct ',' + DatabaseName
from ##TempExceptions_uspReplicationSyncMonitor
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
+
'.
Please investigate ASAP.'
)
--Let us build our query of issues an html table to insert into the email:
--Build the body of the email
Declare @BodyQuery varchar(MAX)
Set @BodyQuery = cast( (
select td =
cast(DatabaseName as varchar(300)) + '</td><td>' +
cast([Table] as varchar(300)) + '</td><td>' +
cast(ProductionValue as varchar(25)) + '</td><td>' +
cast(ReplicatedValue as varchar(25))
from
(
select
DatabaseName
,[Table]
,ProductionValue
,ReplicatedValue
from ##TempExceptions_uspReplicationSyncMonitor
) sub
for xml path( 'tr' ), type ) as varchar(max) )
set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>
<table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'
+ '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'
+ replace( replace( @BodyQuery, '<', '<' ), '>', '>' )
+ '</table>'
Set @ExceptionDatabaseName = @ExceptionDatabaseName + '
' +@BodyQuery
--Print @ExceptionDatabaseName
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DB_MAIL',
--Change Email addresses here:
@recipients='mssqlserverdba87@gmail.com;rajeshjaiswalraj@gmail.com',
@subject='Urgent!: Replication may be out of sync',
@body= @ExceptionDatabaseName,
@body_format = 'HTML'
END
Else
Begin
Print 'No Issues.'
END
--Drop our temp processing table when we are done:
IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;
IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;
June 9, 2015 at 11:42 am
Again...you need to adjust to code for YOUR database.
The code below in the dynamic SQL portion needs to be altered to match YOUR schema. Transactional replication is primary key based so this part below is just comparing the latest primary key for each respective table in each database. The select statements need to be adjusted to select your primary keys from each table (t1, t2, t3, t4). I'm sure your tables don't have 'UserID', 'InteractionID', etc... as primary keys so you need to change this to match your tables.
Once you've updated the code below to match your table structures...execute this select * from #TempProcessing (with your changes)
and run this and let me know what you see...this is the Temp Table that the code below should populate with your table primary key values across multiple databases.
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a userid discrepencay
;With UserBucket as
(
select top 1
''User'' as [Table]
,uup.UserID as ProductionValue
,uuR.UserID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP
--Bring in the top userID from replicated data
cross apply (select top 1 UserID from ['+sys.Name+'].[dbo].[t1] order by UserID desc) uuR
order by uup.UserID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.EventID as ProductionValue
,uuR.EventID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 EventID from ['+sys.Name+'].[dbo].[t2] order by EventID desc) uuR
order by uup.EventID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.InteractionID as ProductionValue
,uuR.InteractionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 InteractionID from ['+sys.Name+'].[dbo].[t3] order by InteractionID desc) uuR
order by uup.InteractionID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ExclusionID as ProductionValue
,uuR.ExclusionID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ExclusionID from ['+sys.Name+'].[dbo].[t4] order by ExclusionID desc) uuR
order by uup.ExclusionID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
June 10, 2015 at 10:19 am
Hi... I changed my code
i have four table t1,t2,t3 and t4 and in all table Primary key is ID.
When i changed the code and ran it again its giving below error.
Msg 7314, Level 16, State 1, Line 3
The OLE DB provider "SQLNCLI10" for linked server "FOR_PROD" does not contain the table ""ReportServer$PROD_DR"."dbo"."t1"". The table either does not exist or the current user does not have permissions on that table.[/color]
Please see my code below ... and help me.
June 10, 2015 at 10:59 am
Hi...
In my case i have four table t1,t2,t3, and t4 for all primary key is (ID)
I have changed the code again as you have given.
But code executed with error.
Msg 7314, Level 16, State 1, Line 3
The OLE DB provider "SQLNCLI10" for linked server "FOR_PROD" does not contain the table ""ReportServer$PROD_DR"."dbo"."t1"". The table either does not exist or the current user does not have permissions on that table.
Please find my code below and help me again.
June 10, 2015 at 11:00 am
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a id discrepencay
;With UserBucket as
(
select top 1
''User'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP
--Bring in the top ID from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t1] order by ID desc) uuR
order by uup.ID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t2] order by ID desc) uuR
order by uup.ID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t3] order by ID desc) uuR
order by uup.ID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t4] order by ID desc) uuR
order by uup.ID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb')
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
June 12, 2015 at 7:59 am
The problem is most likely that you're trying to run this script in DBs that don't have those tables. Again...like I said, this script is dynamic so it runs in all DBs except for the ones specific in the where clause...you need to adjust it to what you want. See my new comment in in the code below.
SET NOCOUNT ON
DECLARE @procName VARCHAR(MAX)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
--Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in
--prod and in the replicated DB so we can compare them
--PROD is the NAME of the linked server that links to Production data
--This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion
--You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB
SELECT
'
--Select on the user table in prod and replicated data and see if there is a id discrepencay
;With UserBucket as
(
select top 1
''User'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP
--Bring in the top ID from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t1] order by ID desc) uuR
order by uup.ID desc
)
--Select on the event table in prod and replicated data and see if there is a eventID discrepencay
,EventBucket as
(
select top 1
''Event'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP
--Bring in the top eventid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t2] order by ID desc) uuR
order by uup.ID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,InteractionBucket as
(
select top 1
''Interaction'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP
--Bring in the top interactionid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t3] order by ID desc) uuR
order by uup.ID desc
)
--Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay
,ExclusionBucket as
(
select top 1
''Exclusion'' as [Table]
,uup.ID as ProductionValue
,uuR.ID as ReplicatedValue
from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP
--Bring in the top exclusionid from replicated data
cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t4] order by ID desc) uuR
order by uup.ID desc
)
--Combine all the previous buckets into our last select:
,CombineSelect as
(
select * from UserBucket
union
select * from EventBucket
union
select * from InteractionBucket
union
select * from ExclusionBucket
)
Insert into #TempProcessing (
DatabaseName
,[Table]
,[ProductionValue]
,[ReplicatedValue]
) ' +
'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]
from CombineSelect'
from sys.databases sys
--Exclude the DBs you do not care about here:
where name not in ('master','tempdb','model','msdb') --Change this to 'where name in' and list the DBs you want
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@procName)
--Print @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply