Checking Latency on SQL Server 2000

Wednesday Dec 1st 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Latency in a replication environment is the time it takes for a transaction to be transmitted from a publisher to the distributor and from the distributor to a subscriber. Learn how to find and monitor the latency frequency in a transactional replication environment.



Latency in a replication environment is the time it takes for a transaction to be transmitted from a publisher to the distributor and from the distributor to a subscriber. In this article, I am going to discuss how to find and monitor the latency frequency in a transactional replication environment.



Pre-requisite


  1. Both Publisher and Subscriber are on SQL Server 2000 with latest service packs
  2. At least one object is being replicated from the Publisher to the Subscriber
  3. System clock on both publisher and the subscriber are in synch


Let's consider that we have a database named "Employee" on the publisher and subscriber as shown in the script, (Refer to Fig. 1.4), and the articles "Employee" and "Department" are already being replicated (transactional replication) to the subscriber. Here the publishing server is "EBONY" and the distributor/subscriber is "MAK." (Refer Fig 1.0, 1.1, 1.2, 1.3)



Publisher



[Fig 1.0]


[Fig 1.1]

Subscriber


[Fig 1.2]

Distributor


[Fig 1.3]

Publishing Database [Fig 1.4]

use master
go
create database Employee
go
use Employee
go
create table department (deptid int constraint department_pk primary key, 
  departmentname varchar(250), Location varchar(100))
go
create table emp(empid int constraint emp_pk primary key, Empname varchar(100), 
Dept int constraint emp_dept_FK foreign key references department(deptid))
go

Steps involved in checking latency in a replication environment

  1. Create the heartbeat table and heartbeat stored procedure on the publishing database
  2. Create the heartbeat job on the publishing server
  3. Create the heartbeat table, heartbeat stored procedure, stored procedure to write file and latency check stored procedure in the subscribing database
  4. Create the Latency check job on the subscribing server
  5. Add the heartbeat stored procedure as an article to the publisher so that the execution of that procedure can be replicated to the subscriber.

Steps explained

Step 1

Create the heartbeat table and heartbeat stored procedure on the publishing database using the script below.

Download Heartbeat.sql.

Click for full code

Step 2

Create the heartbeat job on the publishing server using the script below.

Download HeartbeatJob.sql

Click for full code

This will create the job shown in the Fig 1.5 and 1.6


[Fig 1.5]


[Fig 1.6]

Step 3

Create the heartbeat table, heartbeat stored procedure, stored procedure to write file and latency check stored procedure in the subscribing database using the script below.

Download Latency.sql

Click for full code

Step 4

Create the Latency check job on the subscribing server.

Download LatencyCheckjob.sql

Click for full code

This will create a job shown in Fig 1.7 and 1.8.


[Fig 1.7]


[Fig 1.8]

Step 5

Add usp_heartbeat to the existing publication using the script below.

Download Addarticle.sql

use Employee			--change to your publishing databasename
go
sp_addarticle 
@publication ='Employee',		--change to your publication 
@pre_creation_cmd='none',
@article =  'usp_heartbeat' ,
@destination_table =  'usp_heartbeat',
@type ='proc exec',
@schema_option=0x01,
@destination_owner ='dbo',
@source_owner ='dbo',
@source_object='usp_heartbeat',
@force_invalidate_snapshot =0,
@status=0
go
exec sp_addsubscription 
@publication = N'Employee', 	--change to your publication
@article = N'usp_heartbeat', 
@subscriber = N'MAK', 		-- Change to your subscriber name
@destination_db = N'Employee',  -- Change to your subscription database name
@sync_type = N'none', 
@update_mode = N'read only' ,
@subscription_type = 'PULL' --change this to push or PULL depending on your environment

go

sp_refreshsubscriptions 'Employee'	--change to your publisher name
go

This will add the execution of usp_heartbeat to the publication and subscription databases. [Refer Fig 1.9, 2.0]


[Fig 1.9]


[Fig 2.0]

Soon after executing the above script, execution of the procedure usp_heartbeat will start replicating.

Now let's check the heartbeat table on the Publishing database

use employee
go
select * from repl_heartbeat
go

Result

Sourceserver
name

SourceDatabase
Name

TargetServer
Name

TargetDatabase
Name

SourceTimeStamp

TargetTimeStamp

EBONY

Employee

NULL

NULL

"2004-11-05
18:55:00.373"

NULL

Now let us check heartbeat table in the Subscribing database

use employee
go
select * from repl_heartbeat
go

Sourceserver
name

SourceDatabase
Name

TargetServer
Name

TargetDatabase
Name

SourceTimeStamp

TargetTimeStamp

EBONY

Employee

MAK

Employee

"2004-11-05
18:55:00.373"

"2004-11-05
18:55:26.570"

The job "Job_Checklatency" creates a log file "Latency.log" on the distributor as shown in Fig 2.1.


[Fig 2.1]

The contents of the log file, "Latency.log," are shown below:

Replication from EBONY..Employee to MAK..Employee with latency  
  26 secs and threshold 120secs :Date =Nov  5 2004  7:05:00:013P
Replication from EBONY..Employee to MAK..Employee with latency  
  22 secs and threshold 120secs :Date =Nov  5 2004  7:06:00:013P
Replication from EBONY..Employee to MAK..Employee with latency  
  10 secs and threshold 120secs :Date =Nov  5 2004  7:07:00:980P
Replication from EBONY..Employee to MAK..Employee with latency  
  26 secs and threshold 120secs :Date =Nov  5 2004  7:08:00:247P
Replication from EBONY..Employee to MAK..Employee with latency  
  26 secs and threshold 120secs :Date =Nov  5 2004  7:09:00:123P

When the latency exceeds the threshold of 120secs, it create another log file "Error.log" as shown in Fig 2.2.

Click for larger image

[Fig 2.2]

The content of the file, "Error.log," is shown below.

Latency Warning :Replication from EBONY..Employee to MAK..Employee with latency  229 secs 
  exeeded the threshold 120 secs  :Date =Nov  5 2004  7:10:18:907P

The stpred procedure, "usp_checklatency," can be tweaked a little to alert the Database Administrators using xp_sendmail when the latency exceeds the threshold.

if @latency>@latencythreshold 
begin
xp_sendmail ..............
end

Conclusion

As mentioned earlier, the main intent of this article is to find and monitor the latency in a transactional replication environment.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved