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 
( 
CustomerID, 
CustomerName, 
CustomerAddress, 
Comments, 
Value 
) 
VALUES 
( 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], 
[CustomerName], 
[CustomerAddress], 
[Comments], 
[Value] 
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. View all posts by Arunraj