AZURE SQL Data Sync with a on-premise SQL Server

Posted: December 6, 2012 in Architecture, Windows Azure
Tags:

SQL server DataSync is one of the most important features when we have the need to sync our on premise data with Azure SQL  Services  and vice verse  In this post we will define a step by step  method to accomplish the task of sync the Azure SQL services with the one onPremise SQL Server.

datasync scheme

To start we will consider that you already have a Database running on the Azure SQL Services and a configured SQL database server.  Already having a database on Azure and a pre-configurated SQL server Machine, we start by creating a  Azure Sync Group. We can define a Azure Sync Group “as a collection of SQL Database instances, and SQL Server databases that are configured for mutual synchronization by the SQL Data Sync service. A Sync Group is comprised of a “hub” database and one or more “member” databases”. Be careful to create your SQL Data Sync server in an appropriate region.

Data Sync is one of that features that is not yet avaable in the new portal of windows azure so the first step is to revert to the old management portal of Windows Azure, you can anchieve that by clicking on your name at the right top of the screen and select to go to the old portal.

oldportal

Once in the old portal you should select Data Sync Option, and after that on the main pane select Provision Data Sync preview Server

datasyncoption

After that it will be asked to choose a subscription and a location.

selectregion

After the provision of our sync server we will create our Syncronization Group

syncdatabasegroup

After that we choose a name to our Sync group and select the Azure Database we intend to be the Data Hub

synchub def

After configure the source Azure Database, let’s choose or on premise server

synchub def2

WE select the option to have a new sql database since we consider that we have not yet a Sync Agent installed int the on-premised SQL server, we can choose also the direction of the sync in this case we had selected to have the sql azure as the source of the Synchronization.

synchub def3installagent

After clicking next you will download the agent, after that install the agent on you on-premise server. After installing the agent launch the Microsoft SQL Data Sync Agent, in the management console of the agent we should insert the key that we had receive previously.

clip_image022[4]

 

After defining the agent key we should register the appropriate database on the server.

 

conf agent

 

After register the on-premise database be acrefull to validate the satet of the database ( it should appear in the reachable state).

confdb 2

 

Now that we already had install and configure the Sync Agent on our SQL Server let’s go back to the portal and follow the rest of the steps

download the agent

 

After the last step the on-premise database will be connected to Windows Azure, it’s now tim to configure the schedule (since this is a lab and we need quick results let’s use a sync interval of 5 minutes). pay attention to the conflict resolution combo and select the must interesting option for your case.

clip_image030[4]

Next we define what  data will be sync. After all this configuration is time to deploy the Sync Group, by clicking in deploy.

deploysync The Sync Group will be provisioned and the first data synchronization will run. After a few seconds the status of the two databases should transition to good. And now you had ended your synchronization configuration. Hope this post had help you to a better understanding of the needed configuration needed to sync your Windows azure services with on-premises SQL server.  No it’s time for you to explore more this powerful mechanism, if you have questions feel free to ask.

Advertisements
Comments
  1. Russ says:

    Good article. Windows Azure SQL Data Sync is now available on the new portal. It would be nice to see this article redone working from the new portal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s