SQL Azure Sync CTP2 – Synchronize SQL Azure with On Premise SQL Server and vice versa– How Sync Framework Tracks changes in databases – Part 3

In the previous posts, we saw How to download, install and configure SQL Azure Sync Agent and then How to define Sync Group, Manually run the sync and How to define Schedules to run the Sync automatically

In this post, we will How SQL Azure Sync provisions the databases and How it identifies the changes in the data and upload or download the changes

Open Management Studio and connect to SQL Azure server which we used for Sync Group


And then connect to on premise SQL Server and open Countries database, you can see the Tracking Triggers and Stored Procedures for each tables



If you look at the list of the tables, For each table we added for Sync, a new table has been added for tracking

Ex. DataSync.city_dss_Tracking


This table will be populated based on the Trigger created on the Main Data table


Let us insert a new record and see how it works

To verify that there is no city exists with name “Parisppany” we will run the below query on SQL Azure server


The above query returns 0 rows


Now insert a new record for City table in on premise SQL Server, if you see the Output Messages, you will see 3 messages, its due to Adding record in both Tracking table and Main table

Let us check the Main table


Now let us check the Tracking table


The tracking table ID column refers to the Primary Key of main data table and it contains timestamp information to identify when the record was created or updated or deleted

When record is deleted “Sync_Row_Is_TombStone” will be set to 1

Other Tracking Tables:

There are 3 other tracking tables created during Sync Provisioning process

1. [DataSync].[scope_config_dss]

2. [DataSync].[scope_info_dss]

3. [DataSync].[schema_info_dss]

[DataSync].[scope_config_dss] table contains the schema of the tables which are syncronized as XML data


If you open and view the XML Data, you will see the Tables as Adapters, along with details of Tracking tables, Procedures and Triggers used for tracking


I hope this post gives you an idea of How SQL Azure Sync service identifies the changes and updates the data.

Please post your comments !!!


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:

WordPress.com Logo

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

Facebook photo

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

Connecting to %s

%d bloggers like this: