Setting up Azure Data Sync Service with Federations in Windows Azure SQL Database For Reference Data Replication

Setting up Azure Data Sync Service with Federations in Windows Azure SQL Database For Reference Data Replication

Rate This
  • Comments 4

In a previous post, I talked about the ability to use Data Sync Service with Federation Members. In this post, like to walk you through the details.

The scenario here is to sync a reference table called language_codes_tbl across federation members. The table represents language codes for the blogs_federation in my BlogsRUs_DB. In my case the topology I created has the root database as a hub and all members defined as regular edge databases. Here is what you need to do to get the same setup

1. Create a “sync server” and a “sync group” called sync_codes

2. Add the root database as the hub database; blogsrus_db with conflict resolution set to “Hub Wins” and schedule set to every 5 mins.

3. Define the Sync dataset as the dbo.language_code_tbl


      4. Add federation member databases into the sync_codes sync group and “deploy” the changes.


        With this setup, replication happens bi-directionally. This means, I can update any one of the federation member dbs and the changes will first get replicated to the root db copy of my reference table and then will be replicated to all other federation member dbs automatically by SQL Data Sync. SQL Data Sync provides powerful control over the direction of data flow and conflict resolution to create the desired topology for syncing reference data in federation members.


        Handling Federation Repartitioning Operations

        This will work as long as you don’t reconfigure these members with an operation like ALTER FEDERATION … SPLIT. With SPLIT we drop the existing member database and create 2 new member databases that contain the redistributed data based on the new split point of the federation. Lets assume we issue the following statement to split the existing range 350000-400000.

        alter federation blogs_federation split at (id=355000)

        With that, you will notice that the sync group will start reporting an error on the member is impacted by the split operation – see the red error indicator marked below the database icon.

        1. Given this database no longer exists, you need to deprovision the database from the sync group. To do this first remove the database with the “remove database” button above the topology area. You finalize the operation by deploying the change using the “deploy” button above. Since the database is dropped, you will need to do a forced removal after the deploy.


        2. Next you need to add in the 2 new member names that are created by the SPLIT operation. we do this by running the following script.

        use federation blogs_federation(id=350000) with reset
        select db_name()
        use federation blogs_federation(id=355000) with reset
        select db_name()

        Once you have the new database names for the members covering the new ranges 350000-355000 and 355000 – 400000, you can follow step #4 above to add the names to the sync group and deploy. This will reestablish the replication of the reference tables.

        Limitations with Azure Data Sync Service:

        There are a few limitation to be aware of SQL Data Sync however; First the service has 5 min as its lowest latency for replication. There is no scripting support for set up of the data sync relationships. This means you will need to populate all the db names through the UI by hand. SQL Data sync also does not allow synchronization between more than 30 databases in sync groups in a single sync server at the moment. You can only create a single sync server with DSS today. SQL Data Sync is currently in preview mode and is continuously collecting feedback.  Vote for your favorite request or add a new one at SQL Data Sync Feature Voting website!

        -cihan biyikoglu

        • Forgot to put the script for the section Handling Federation Repartitioning Operations step #2?

          For ALTER FEDERATION … DROP, you can simply do Handling Federation Repartitioning Operations step #1 to remove the member database right?



        • Corrected. Thanks June!

          You will need to follow the same steps for DROP that is listed for SPLIT. DROP would cause the db_name to change as well. There is a known issue where drop does not rename the db but that will be corrected soon.



        • That is to say ,can i use Data Sync Service with Federations now??

        • yes

        Page 1 of 1 (4 items)