Windows Azure is the public cloud computing platform Microsoft provides. With the Windows Azure platform, they also provide a set of a tools to work on the platform and to integrate with on-premise solutions.

One of integration features provided with the Windows Azure platform is Windows Azure Data Sync

Gotten for the Azure Data Sync on MSDN:

SQL Azure Data Sync is an Azure service that enables you to easily synchronize geographically disbursed SQL Server and SQL Azure databases. Data Sync provides an intuitive UI with optional tutorials that guide you through the process of creating database groupings (sync groups) that are synchronized together. You define exactly what data from each sync group is synchronized – tables, columns and rows (using row filters), as well as how frequently the synchronization jobs are performed.

SQL Azure Data Sync uses a hub-spoke topology with the hub always being a SQL Azure database.

Our company has three departments around the world and each of these work with their own database, since connecting to a central database would issue too much latency for the departments. For that reason, they all use an identical database, but it is local to the department and it contains the information related to the department. Now the departments want to share the information between departments, but they want to keep their database local for latency issues. This means we can not set up a central database to which the departments will connect to, so we need to set up a synchronization mechanism. There are multiple synchronization mechanisms like for example SQL Server replication and so forth. We want to use the Windows Azure Data Sync service to synchronize the information between the different departments, on a 1 hour base. One of the good things about Windows Azure Data Sync is that we do not have to do any coding, we do not have to set up any synchronization agent and so forth, we just have to set up the Data Synchronization in the Windows Azure portal.

To use Windows Azure or Windows Azure Services you will need an active Windows Azure subscription. Windows Azure Data Sync can be found in the portal:

Go to the Data Sync service in the portal and hit the Provision button in your feature bar on top of the portal:

Obviously you have to agree with the Terms of Use if you want to use the Windows Azure Data Sync service:

Chose the subscription you want to create the Azure Data Sync service on:

Chose the region you want to create the Azure Data Sync service at and press Finish:

After your provisioned your SQL Azure Data Sync Preview account, the left bar will look like this:

We will start by creating a Sync Group:

You will get a screen like the following:

The Data Sync Group screen allows you to set a name of the Synchronization Group, define the topology and define what frequency the topology should be synchronised. Notice the 2nd database icon in the topology is stated as a “Sync Hub”. Windows Azure Data Sync uses a Hub Topology to synchronise databases defined in the topology:

One of the requisites of using Windows Azure Data Sync is that you use a SQL Azure database as a Hub database. With other words, this means you need to have an active SQL Azure database present that will act as Hub database. Since we do not have defined an SQL Azure database yet which we can use as Hub Database, we will first create a new SQL Azure database which we will use as Hub Database to synchronize our three local departmental databases. Go to the Database tab on the services menu:

If you have multiple Windows Azure subscriptions, select the subscription you want to create a SQL Azure database server on and select the Create button in the top menu bar:

Chose a region where you want the SQL Azure database server to be hosted:

Specify an Administrator login and password:

Next screen allows you to configure what IP addresses have access to the SQL Azure database server:

Check the “Allow other Windows Azure services to access this server”, which will be needed for the Data Sync agent to access the SQL Azure database server. It will add the 0.0.0.0 IP range to the enabled firewall rules. You will also need to press the Add button to add another firewall rule to give your own IP address access:

The screen will show what your current IP address is, so you can put in your own IP address and specify a rule name for it. Press OK and you should have 2 firewall rules present in the your screen:

Press Finish to add the SQL Azure database server. After it has been created, your left service menu should look like something like this:

The generated name of your SQL Azure database server will not be the same. This name is unique for each generated database, so yours will be a different name.
At the right you can find the properties of the SQL Azure database server:

Now that we added the SQL Azure database server, let’s add a SQL Azure database which we can use as Hub Database for our synchronization topology. Select the SQL Azure database server in the left services menu and hit the Create database in the top menu bar, which indicates creating a new SQL Azure database:

Chose a name for the SQL Azure database we want to create and select a database size: (Note the bigger the database size, the higher the monthly price)

After you pressed OK to create the database, you will see a database got created in the left menu:

If you would like to create tables, permissions and so forth in the SQL Azure database, you can do that by hitting the Manage button in the top menu which will bring you to the database management portal:

However this is not within the scope of the Windows Azure Data Sync and is more of use when you want to use a SQL Azure database as normal database instead of just using it as Hub Database for synchronization. Now that we have created a SQL Azure database, we can go back to the Windows Azure Data Sync wizard screen (Go back to Data Sync service feature).

We will start by giving the Synchronization Group a name:

To set the SQL Azure synchronization database hub, hit the following image in the screen:

It will open the following screen:

At the Server Name you place the Fully Qualified DNS Name of the SQL Azure database server, which is servername.database.windows.net, which is what I highlighted 7 screenshots back when we created the SQL Azure database server. If you do not remember it, just go back to the Database services tab and go see what the SQL Azure database server DNS name is. The database name matches the SQL Azure database name we created on the previous created SQL Azure database server. The credentials I use are the credentials I created for the administrator for the SQL Azure database server. You could also create custom credentials only for the DepartmentalDataSyncHub database and use those credentials. This would obviously be a lot more secure.

Just a note, but the discovery of SQL Azure database servers and SQL Azure databases did not work for me. I had to manually type in the information. Finally make sure to hit the Test button to be sure that the connection succeeds. (If you try to connect to the SQL Azure database shortly after you created the SQL Azure database server, the connection might be unsuccessful. It sometimes takes a few minutes until the firewall rules are set.) After the connection succeeds, add the database. You will have the following screen:

We already defined the SQL Azure database hub that will be used in the Data Synchronization. Now we need to define the databases of our 3 company departments. For demoing purposes I created 3 SQL Server databases on my local SQL server, which we will use to demonstrate the 3 geographical departmental databases:

Each of these databases has a table called “Employees”, which has a very easy scheme:

Each of the departments has their own employees registered in the database. For demoing purposes, each department has 1 employee now:

Since our companies would like to automatically exchange employees information, we will set up a Windows Azure Data Sync that will exchange employee information between the departments automatically, on a 1 hour base synchronization.

In our Windows Azure Data Sync wizard we already added the SQL Azure database Hub, so now we need to add the 3 departmental databases which we want to synchronize with each other. Click the image of the SQL Server database in the screen to add an on-premise SQL Server database to the synchronization topology:

The following screen will appear:

Select the “Add a new SQL Server database to the sync group” option. You can also define a Sync Direction for the on-premise SQL server you add to the synchronization topology. You can chose between Bi-Directional, Sync from the Hub and Sync to the Hub. You can read what each option exactly means in the screenshot. In our case we will take Bi-Directional, since we want to upload the changes to the employees in the local departmental database to the SQL Azure Hub database and we want also to get the changes the other departmental offices uploaded to the SQL Azure Hub Database, so we need to retrieve the added or changed information from the Hub again, which results in a Bi-Directional synchronization. Press Next and you will get following screen:

To use Windows Azure Data Sync between on-premise databases, you need to have a Client Sync Agent installed on the machine that the on-premise SQL server database is on. This Client Sync Agent will talk to the Azure Sync agent to do the synchronization. Without the Client Sync Agent, the Azure Sync Agent would not have access to an on-premise SQL server and would not be able to orchestrate the synchronization. Since I do not have an Azure Data Sync Client Sync Agent installed on my pc, I chose to option to Install a new Agent on my machine. In case you would have a Client Sync Agent installed already, you can chose the installed agent through an existing Agent.

If you press Next, you end up at the following screen:

Start by hitting the Download button to download the Client Sync Agent so we can install it on the machine the SQL server is on. In my case the 3 departmental databases are on my local machine, so I only need to install the Client Sync Agent on my local machine. In the real world the 3 departmental SQL server databases are on 3 separate machines, so you would have to install the Client Sync Agent three times, once on each machine. Clicking the download button will bring you to the download page of the Client Sync Agent. Hit the download button for the .msi:

After you download it, run the .msi:

Press Next, select the “I Agree” on the license agreement and press Next again:

You need to define a username and password of a user on the machine that has network access. The network access is of course needed to communicate in the orchestration of the data synchronization with the azure synchronization agent. After you selected a user and password with the necessary rights, press Next, select the location where you want to install the agent on the disk and press Next again to start the installation of the Client Sync Agent. Hopefully for you you will end up with a screen like this:

As it says in the installation complete screen, launch the SQL Azure Data Sync Agent Preview on your machine. You will get the following application running:

Now go back to the Windows Azure portal and specify a name of the Agent we just installed:

After you provided a name for the Agent, the Generate Agent Key button will activate, which allows us to generate an Agent Key. Press the Generate Agent Key button:

Copy the key from the Windows Azure portal and hit the Submit Agent Key in the Client Sync Agent application you have running on the machine:

Provide the key that was generated in the Windows Azure portal:

After it has been added, the other menu buttons should become activated. If you press the Ping Sync Service, you should get an alert that the ping was successful.

If you are able to succesfully ping the Azure Sync Service, press the Register button to register local SQL server databases with the Client Sync Agent:
I try to register the Devoteam BE database first with my Client Sync Agent and Test the connection to make sure it is succesful:

In case you get an error, make sure your user you used to make the Client Sync Agent run on, has appropriate rights to the database you want to expose to the Client Sync Agent.

I registered the databases for the Devoteam UK and Devoteam FR as well:

Now go back to the Windows Azure portal to where we left our work:

Press Next. On the next screen hit Get Database List and after it has loaded the database list you registered with the Client Sync Agent, select the database you want to register to the synchronization topology and press Finish:

On the topology our local Devoteam BE department database will be registered in the Synchronization topology, together with the SQL Azure Hub database that is needed for the synchronization.

Now we hit the image to add another on-premise SQL server database to the synchronization topology to add the other 2 on-premise departmental databases:

We also select Bi-Directional synchronization for both the Devoteam UK and Devoteam FR database. Our syncronization topology should finally look like this:

We defined 1 SQL Azure Hub database which is necessary for synchronization and we defined 3 on-premise departmental databases which will synchronize information between each other.

Our left side menu will look like this when expanded:

The only remaining thing to set is the configuration for the synchronization:

We define a sync frequency for the Sync Group of 1 hour. You can set this to minutes, hours, days or months. You can also disable automatically synchronization. This could be of use when you only want to synchronize manually. One important factor to set is the Conflict Resolution:

In our case we will chose the Client Wins Conflict Resolution. We also need to set what we want to synchronize. What tables and what filters we want to use:

We select the database we want to import the schema from. In our case it does not matter since each of the departmental databases has the schema already. But in case you want to synchronize data from a database with data to a database that hasn’t got the schema yet, you obviously pick the database that has the schema and where you want to synchronize from.

When we get the Latest Schema, only 1 table is available. We select the table Employees, meaning we want to synchronise the Employees information between the synchronization topology. We select all fields of the table. You can also set filters to filter out certain records in the synchronization, but we don’t need this. If you want to use a filter, you can set a filter on each column by checking the Filter checkbox at the column and specifying the filter at the bottom of the screen for that column. Finally our configuration for our Sync Group should look like this:

Now that we are finished setting up the synchronization group, let’s deploy the synchronization group:

When you deploy the synchronization group, it will make sure each database in the synchronization group has the correct schema present. For each database it will also add some tables that are used for record tracking and data synchronization:

After the synchronization group is deployed, you will also be able to manually synchronize the synchronization group. Ofcourse the synchronization group also synchronizes every hour automatically, as you defined in the configuration of the synchronization group:

Hit the Sync Now button and wait until the synchronization group is ready. If you now check the Employees present in the Devoteam BE database:

You will find that the 1 employee that was present at the Devoteam BE database is synchronized to the databases of the other departments. The employees that were present at the other departments, are now synchronized with the other departments in the synchronization group. A new department gets launched in the future, we simply add the department SQL server database to the Synchronization Group and the information of the other departments will be provided automatically to the local SQL server database of the department.

One little remark to finish with, sometimes the synchronization of all data between the departments might only be done after the 2nd synchronization and not on the first synchronization. It synchronizes database 1 to upload the changes to the hub and then download the changes from the hub. Then it does the same for database 2 and finally it does the same for database 3 for example. However this means the changes or creation of records that happened at database 3 were uploaded to the hub at the end of the synchronization, so database 1 and 2 would not have the changes yet, even though the hub will contain the changed information. Next synchronization the when the first and second database download the information from the hub, they will retrieve the changed information that was uploaded to the hub from database 3.

Any suggestions, remarks or improvements are always welcome.
If you found this information useful, make sure to support me by leaving a comment.

Cheers and have fun,

Robbin