Monitoring Transaction Replication : Replicating On-Premise SQL Server databases to SQL Azure databases

In previous post, we saw How to setup Transactional Replication to replicate On-Premise SQL Server databases to SQL Azure databases, in this post we will how to monitor the Replication process and make changes to data in On-Premise and verify the changes in SQL Azure database

Replication Monitoring

Step 1: Right click on Replication –> Local Publications –> Launch Replication Monitor


Step 2: We can see the summary of subscriptions, current average performance and current worst performance


Step 3: Expand the Publication to see the list of subscribers, we can see the list of subscribers and each of their Performance, Latency and Last Synchronization time


Step 4: If we need to review the status of each Agent status, we can use Agents tab and see their status


The different types of Agents status available are as follows


Step 5: To view the details of a particular publisher/subscriber, Please right click on the Publication/Subscription and click on “View Details


In details view we can see the completed (Success and Failure) and current sessions, if we click on a particular session, we can also see the steps ran on that session

Details of the publication session


Details of the Subscriber session


On-Premise Data Update verification in SQL Azure database

We will make data changes using below query in On-Premise server

Query to insert a new record and update one record in On-Premise SQL Server database

INSERT INTO dbo.Customers 
( 80001, -- CustomerID - int 
'CustomerName80001', -- CustomerName - char(100) 
'CustomerAddress80001', -- CustomerAddress - char(100) 
'Comments80001', -- Comments - char(185) 
80001 -- Value - int 

UPDATE dbo.Customers 
SET Comments = 'Update Comments 80000' 
WHERE CustomerID = 80000; 

After 2 minutes, we ran the below query in SQL Azure database

Query to verify data changes in SQL Azure database

SELECT [CustomerID], 
FROM [dbo].[Customers] 
WHERE CustomerID >= 80000; 

Output: We can see the changes made in On-Premise server is replicated to SQL Azure database


Hope you find this post helpful !!!


Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: