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 !!!