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
- Both
Publisher and Subscriber are on SQL Server 2000 with latest service packs - At
least one object is being replicated from the Publisher to the Subscriber - 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