SQL Azure Data Sync CTP2 My first experience.

SQL Azure Data Sync enables creating and scheduling regular synchronizations between SQL Azure and either SQL Server or other SQL Azure databases. It is a cloud-based data synchronization service built on the Microsoft Sync Framework technologies. It provides bi-directional data synchronization and data management capabilities allowing data to be easily shared across SQL Azure databases within multiple data centers. The current release is Community Technical Preview 2 (CTP2).

For a Proof of Concept I want to synchronize a subset of my on premise database to a SQL Azure database. In this blogpost I will explain what need to be done to synchronize on premise data to a SQL Azure database.

Requirements before you can start:

• You must have a Windows Live ID
• You must have a SQL Azure account
• SQL 2005 SP2 or later.
• Request a registration code for SQL Azure Data Sync CTP. This can be requested here.  After registration you can start using SQL Azure Data Sync
• On premise SQL database.

The synchronization of data is handled by the SQL Azure Data Sync Agent. In the SQL Azure Data Sync UI you can define the agent.

The agent itself can be installed with the AgentServiceSetup.MSI. This MSI can be found at the bottum of the SQL Azure Data Sync UI where you have defined your agent. After installation of this agent a local service is created named: SQL Azure Data Sync Agent.

The UI of this services can be started via Start, All Programs, Microsoft SQL Azure Data Sync, Microsoft SQL Azure Data Sync.

To configure the agent, press the Edit Agent Key and paste the agent key which is generated when you created the agent in the  SQL Azure Data Sync UI
Use the Add Member button to add your on premise database to the agent.
Use the Ping Sync Service to test the agent.

In the SQL Azure Data Sync UI in the Tab Databases, you will see your on premise database. Press the add button to add your SQL Azure database.

Now we are ready to create and configure a Sync Group.A Sync Group is a collection of SQL Azure and SQL Server databases that are configured for mutual synchronization by the SQL Azure Data Sync Services

• Press the New Sync Group button to add a Sync Group.
• Add your on premise database and SQL Azure database to the Sync Group.
• Press the next button and select the tables you want to synchronize. Some tables are not possible to select because:
• They do not have primary keys defined.
• They have clustered indexes defined on non-primary key columns.
• Enable the Sync Group and configure the synchronization schedule.

As mentioned not all tables are possible to synchronize. In a later blog I will explain what you can do for these tables and also how you can synchronize a subset of a table.

What is not possible in CTP2:

1. Changes in data model are not supported. For instance in my on premise database we create a new column to a table which is synchronized. This new column can't be added to the synchronization.
2. Changes to a Sync group can't be made. If you want to add an additional table to the synchronization, you can't add it to the existing synchronization group.
3. Synchronize tables for which the clustered index is not the primary key. In a lot of situations the primary key will differ from the clustered index. This is because of performance reasons.

Overall conclusion:
My first impression of the SQL Azure Data Sync is good. Big progress is made. It is easy to setup. To use it in a commercial product I think that the 3 things I mentioned are essential for a commercial solution.
: Tweet