Improve company productivity with a Business Account.Sign Up

x
?
Solved

Replication Latency Script - SQL 2005

Posted on 2010-08-22
3
Medium Priority
?
534 Views
Last Modified: 2012-08-14
Hi Experts,
I am trying to come up with a simple script (MS SQL 2005) to check the latency of my replication.
The case:
There is a publication database called PubDB and a replica of PubDB called ReplDB.
I need to query a table in PubDB  called TABLE1,  column LastUpdate , then do the same on the ReplDB and compare the two values (the two resulting dates).
If the difference between PubDb and ReplDB is more than 90 min an email alert should send an email.
Please feel free to build upon the provided code  snippets or provide even a better, more elegant solution.

Thanks,
RICUser


--This runs from PubDB

USE PubDB

select top 1 lastupdate

from TABLE1 (NOLOCK)

order by lastupdate desc 

 

/*
The next does the same in the replicated ReplDB via Linked Server.Wouldn't it be it be  better here to use OPENQUERY, so I can use the (NOLOCK) hint ?
*/

select top 1 lastupdate

from [REM_SERVER].[ReplDB].dbo.TABLE1

order by lastupdate desc 

--now the two results will look like that (just an example)

--PubDB
2010-08-21 03:30:000

--ReplDB
2010-08-21 01:30:000

--Now the comparison should follow,  and if  PubDB minus --ReplDB >90 min. an email should notify DBAONCALL@ACME.com

Open in new window

0
Comment
Question by:RICuser
3 Comments
 
LVL 44

Accepted Solution

by:
pcelba earned 2000 total points
ID: 33494865
If I understand it well, you just need to subtract two datetime values and it is easy.

The mail sending SP is described at many places, e.g. http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure  or  http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

--This runs from PubDB

DECLARE @PubTime datetime, @ReplTime datetime

USE PubDB

SET @PubTime = (select top 1 lastupdate from TABLE1 (NOLOCK) order by lastupdate desc) 


/*
The next does the same in the replicated ReplDB via Linked Server.Wouldn't it be it be  better here to use OPENQUERY, so I can use the (NOLOCK) hint ?
*/

SET @ReplTime = 
 (select top 1 lastupdate from [REM_SERVER].[ReplDB].dbo.TABLE1 order by lastupdate desc)


--now the two results will look like that (just an example)

--PubDB
2010-08-21 03:30:000

--ReplDB
2010-08-21 01:30:000

--Now the comparison should follow,  and if  PubDB minus --ReplDB >90 min. an email should notify DBAONCALL@ACME.com

IF DATEDIFF(minute, @ReplTime, @PubTime) > 90
  EXEC sp_SMTPMail ....

Open in new window

0
 
LVL 3

Expert Comment

by:mnachu
ID: 33496629
We do a similar stuff only thing is instead of the linked server we used SSIS to do it:

1. Connect  to Server A
2. Runs the queries and have it in variables.
3. Connect to Server B
4. Compare two values
5. Send mail.

Another reason we did this is, we had multiple replicated servers were we wanted to check/compare. All we had to do was change the configuration when running the SSIS using a SQL job. Also we didn't have to use linked servers which our DBA doesn't encourage having on either server.

Regards,
Nachi
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 33506808
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Are you working to mount the dismounted Exchange 2013 database? Then the best course of action is to analyze the causes of Database issue, their probable solutions and decide for the appropriate course of action.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question