Synchronize or Replicate Databases in SQL Azure across Servers

SQL Azure Data Sync is an incubation project in SQL Azure Labs, which provides Hub – Member model based to synchronize databases from one SQL Azure server to another. You can replicate data from one Hub server to multiple member servers.

You can sign up for free beta account in http://www.sqlazurelabs.com/

In this post, we will see how to use SQL Azure Data Sync to synchronize Northwind_DB from Server A to Server B

Step 1 : Once signed up and setup your account, click on “SQL Azure Data Sync” tab

Step 2: Click “Add New” to create a new Synchronization group

Enter Sync Group Name and click Next

Step 3: Click on “Register New Server” to add Hub Server

Repeat Step 3 to add Member Server

Step 4: Select the Hub Server and Member Server to participate in Sync Group and then select the database as well

Once defined the servers and database, click “Next” to proceed

For one Sync Group job, you can have only one Hub server, but you can define multiple jobs to use the Member server as Hub Server in another job

Step 5: Select the tables you want to Sync and click “Finish”

Now you have successfully defined the Sync Group job

Now you can manually run the Sync for first time by clicking on “Sync Now” button

To automatically setup the Sync job to run on specific schedules, click on “Schedule Sync” option

You can setup the job, to run Daily at specific time or Monthly on specific day and time, or Hourly or Weekly on specified days at specified time, once selected your preferred schedule, click “Ok” to proceed

If you double click on the “Sync Demo Group”, you can view the details and log of the job

Click “View Log” to see detailed log

Starting new job. Job ID = 2ccec979-3848-48f5-b0f6-73a90a8ae1d2

Retrieved ScopeName as 625dec91-cd29-465c-98de-e1fe4be221b9

Retrieved Hubendpoint as SERVERA.database.windows.net,Northwind_DB,625dec91-cd29-465c-98de-e1fe4be221b9

Retrieving DbSyncScopeDescription from Hub

Checking to see if Scope 625dec91-cd29-465c-98de-e1fe4be221b9 exists in endpoint SERVERB.database.windows.net,Northwind_DB

    Scope doesnt exist. Provisioning server.

Synchronizing Endpoint SERVERB.database.windows.net,Northwind_DB ==> HUB. Conflict Endpoint wins
    Total Changes Transferred = 0, Total Changes Failed = 0.
    Sync time (in seconds): 5.9.

Synchronizing HUB ==> Endpoint SERVERB.database.windows.net,Northwind_DB. Conflict HUB wins
    Total Changes Transfered = 217, Total Changes Failed = 0.
    Sync time (in seconds): 6.2.

I hope you all find this information useful. Please don’t use this yet for production, since its an incubation project.