Replication Latency Script - SQL 2005

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

RICuserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mnachuCommented:
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
marat-ozCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.