Using SQL Azure Data Sync – Data Synchronization in Cloud
SQL Azure database is a cloud database from Microsoft. Using SQL Azure, you can definitely have some cost savings specially if you are a small business that does not want a full scale SQL Server setup in-house. With SQL Azure, the greatest advantage of using this cloud database from Microsoft is built-in functionality of high availability, scalability, multi-user support and Fault tolerance capabilities. For a small to medium business, all these critical technologies can be challenging not only to configure but also to implement.
Today we are going to look at another SQL Azure technology, SQL Azure Data Sync. This is a service built on Microsoft Sync framework technology. When you use SQL Azure data sync, you can not only synchronize databases between an on-premise SQL server and another one in the SQL Azure cloud environment, you can also synchronize two or more SQL Azure databases between different cloud servers. When we talk about database synchronization, we are primarily talking about keeping the data in sync in between the databases in different physical SQL Azure locations. In such a manner, it is similar in concept to Replication on a local on-premise SQL Server.
Benefits of SQL Azure Data Sync:
Using SQL Azure data sync will give you the following benefits:
-If you have a distributed database environment, having SQL Azure data sync may be a cost-effective solution to manage the architecture
-Azure data sync will give your end-users located at separate physical offices easy and up to date access to database in the Azure cloud environment.
-The master (HUB) database could be at a physical On premise SQL Server which pushes the changes to one or more separate MS Azure cloud member databases.
-Azure Data sync also incorporates running a maintenance schedule at a regular time which modifies the data changes and keeps all SQL Server databases synchronized.
Setting up SQL Azure Data Sync between Cloud Servers
We are going to walk you through setting up SQL Azure data sync between two separate databases in the cloud.
The first thing you need to do is to go to this website which is SQL Azure labs, http://sqlAzurelabs.com
Next go ahead and click on SQL Azure data sync in the left navigation bar. This is shown in the screen capture right below
Next it will ask you to log into SQL Azure portal. It will do this by bringing up Live.com website. After you are logged in, you will be prompted for SQL Azure data sync service registration. Go ahead and check the agreement and then click Register. After that you will be taken to the Azure data sync management area.
We have included a screen shot on it as follows.
Here you can click Add New to add in new Azure Sync Group. An Azure Sync Group is really a collection of cloud MS SQL azure databases on premise MS SQL server databases. In our case we named it “Northwind–local–cloud” even though in our case we will be synchronizing between two online cloud Azure SQL databases. Click on Register New Server under Member Information. After you click Next you can add your primary (Hub) server information. Next we click on Save. After that
We have included what our primary Hub server looks like right below.
After you have added the primary Azure server you have to select the database to synchronize and then click on Add HUB to make this the primary server. This is going to be the primary Azure Server that all the member Azure servers will be synchronizing data with.
Next we are going to add a Member SQL Azure server so we redo the process as shown above and include the member server information. Click on Save to commit the changes.
Here is our member server shown below:
In a similar fashion to the HUB server we have to pick an Azure database and then click Add Member. The screen capture below illustrates this point on Azure data sync.
After you complete the process, you should have two SQL Azure servers, one Hub and one member.
This is what the screens look like after we’re done with both our SQL Azure cloud servers
At this point we are able to select the particular SQL tables we need to synchronize. For our case we are using Northwind cloud database so we will pick CUSTOMERS and ORDERS tables by click on the Right Green arrow. Finally we click on Finish to complete the Azure Sync Group Procedure.
This is shown right below for your understanding.
Now comes the true test as we put the data sync to work. Before we setup the Data synchronization schedule, we are going to log into the Member server and make sure the CUSTOMERS table does not exist.
We have included that and the following screen capture from our computer. Notice that we are getting an error with invalid object.
Next we go ahead and switch back to the online SQL Azure data sync management area. We click on Schedule Sync and then select a daily schedule for 12:00 AM in the morning. And summary this will synchronize the two tables from Northwind database on the SQL azure server to the Northwind database on this SQL azure server.
Here is what the Schedule Sync looks like for us.
If you click run Dashboard option on the bottom, it will take you to a new screen. This is shown below and is titled Scheduled Synch jobs. Notice that we set up our Azure sync job and it is scheduled.
In order to test we are going to actually click on Sync Now. This will kick off the Azure sync process. Next we click on View log option to see that are changes from the primary Azure server actually cascade down to the secondary Azure server.
Here is a screen capture of what the Sync job log looks like.
After the Sync process completes we are going to login to SQL Azure member server and make sure the data is synchronized. We logged into the server using SQL Server Management Studio and verify that the data is actually there.
This is shown right below
Notice now the data at the member server sbyfn2c9rh.database.windows.net is synchronized with the Hub server.