How to Sync Two SQL Azure Databases

In my previous article I explained the Azure Traffic Manager, how to configure it and how it works. Now in this article I will explain Azure SQL Data Sync. I am assuming you have a basic understanding of SQL Server and SQL Azure Databases. Although this feature is still in the preview stage, it's very important for Disaster Recovery Concepts.

Data Sync Services

Sync Services is provided by Microsoft Azure where you can keep a SQL Azure database synchronized  with another database. You can take a backup of a database from one region to another region. There is a simple procedure you need to follow to configure this service. If not a complete database then you can at least keep selected tables or selected rows of tables synchronized.

This service provides the following two ways to synchronize:

1. Synchronize a SQL Azure database from one SQL Azure server to another SQL Azure server.
2. Synchronize a SQL Azure Database from an Azure Server to a Local Server database.

Here we will first see a way to synchronize databases. How to synchronize a SQL Azure database from one server to another sever (SQL Azure Server).

Prerequisites for this demo: Create two SQL Azure databases with two regions (data centers).

1. Once you login into Azure Portal go to the SQL Database tab. Here you will find four options Sync (this service is in the preview) Image 1.1. Here you will find a list of Sync services that are already created.
   
   
   Image 1.1
   
   
2. To create a new service click on the option Add Sync. There are the following two options:
   
   
   • New Sync Group: Synchronize SQL Azure database from one Azure server to another Azure server
   • New Sync Agent: Synchronize SQL Azure database from one server to a local on-premises server
     
     
     
     
   Here you need to select the New Sync Group option.
   
   
3. Need the following details
   
   Name: Sync Group Name
   
   Region: It's an available datacenter list where you need to create your Sync group.
   
   (Select the region where your source database server is present.)
   
   Click on "Next".
   
   
   
   
4. Here is the Hub and Reference database. Hub is basically the main (Source) database and the reference database is where the Hub is synchronized with this database. Enter the following details.
   
   Hub Database: You will get a list of databases with different servers; select one database as the Hub.
   
   Hub User Name: Login user name of selected Hub database.
   
   Hub Password: Login password of selected Hub database.
   
   Conflict Resolution: If conflicts happen between this hub and the source database then it will go with the selected database. (The Hub wins: the Hub data will be accepted.)
   
   
   
   
5. Add a reference database, enter the following details :
   
   Reference Database: You will get a list of databases with different servers; select one database as the reference.
   
   Reference User Name: Login user name of the selected Reference database.
   
   Reference Password: Login password of the selected Reference database.
   
   Sync Direction: Select Sync Direction Hub to reference or Reference to Hub. You can select Bi-Direction to synchronize both databases with each other.
   
   
   

With this procedure your Sync service creation is completed. You can see the status of your service on the Sync dashboard. Now the next step is to configure this Sync service.

Sync Service Configuration

Once you complete this Sync Service group you need to configure some important details for this service.

Automatic Sync: (On / off) this is the trigger if you keep this on then it will automatically start the Sync process in the defined time.

Sync Frequency: Here you will specify the time period for the next Sync process start. The minimum time frequency is 5 minutes and the maximum frequency is 1 month.



Sync rules

In Sync rules you need to select a list of tables / columns from source database. The first time you click on Sync rules it will show you the following message. Click on Define Sync Rules.



You will get a list of tables and columns from the source database. Select the appropriate tables or columns that you want to synchronize with the other database. Save your Rules.



Now to test you can manually synchronize this database using the Sync button that is given in the following panel.



After completion of the Sync process, check your reference and Hub database, you will find the data synchronized depending on your Sync Rules.