Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting started with SQL Data Sync


By:   |   Last Updated: 2013-11-12   |   Comments (6)   |   Related Tips: More > Azure

Problem

SQL Data Sync is a feature of Windows Azure SQL Database which synchronizes data across multiple SQL Server and Windows Azure SQL Database instances without writing any code. How do you get started with using this feature and how does it work?

Solution

SQL Data Sync is a feature of Windows Azure SQL Database built on Microsoft Sync Framework technologies. With the use of this feature, you can either setup single direction or bi-directional data synchronization and data management to allow easy data sharing across Windows Azure SQL Databases across multiple data centers or between on-premises SQL Server databases and Windows Azure SQL databases.

In my last tip, "Understanding SQL Data Sync" I talked in detail about this new capability, in this tip I am going to demonstrate how you get started using it.

In this demonstration, I am going to show how a Windows Azure SQL Database will be synched to a SQL Server database on-premises and vice versa.

Creating a Sync Agent on Azure

The first step in using SQL Data Sync is to create a New Sync Agent (this manages communication between Windows Azure SQL Database and the on-premises SQL Server database without compromising the firewall) on the Windows Azure SQL Database instance.

You need to go to the SQL DATABASES tab on the Windows Azure Management Portal, click on ADD SYNC and then click on New Sync Agent as shown below:

Creating a Sync Agent on Azure

On the New Sync Agent wizard, specify the name of the sync agent and then region where it should be created. For best practices on selecting the right region, please check my earlier tip, "Understanding SQL Data Sync". Also, please note, SQL Data Sync is in Preview mode as of this writing and might have some changes in the final release of the product.

SQL Data Sync is in Preview mode right now and might have some changes

Once the Sync Agent is created, you can see it here on the Sync tab on the top right side of the screen (please note this tab will be available only after you create your first sync agent). As you can see, the status of the created sync agent is offline as we have just created it and not configured it.

you can see it here under Sync tab on the top right side of the screen

 I am going to demonstrate the bi-directional data synchronization between Windows Azure SQL Database and on-premises SQL Server database, so I need to setup the Sync Agent for the on-premises machine as well. But before that, I need to get the Sync Agent access key (which I just created), so that it can be contacted by the Sync Agent of the on-premises machine. To get this key, click on the Sync Agent on Windows Azure Management Portal and then click on MANAGE KEY as shown below:

Windows Azure SQL Database and on-premises SQL Server database

Click on the Generate button on the Manage access key wizard, copy the Agent access key for use in the next section.

copy the Agent access key for use it later in the next section

Installing and Setting up a Microsoft SQL Data Sync Agent On-Premises

In order to synchronize an on-premises SQL Server Database to a Windows Azure SQL Database or vice versa, you need to install and configure SQL Data Sync Agent on your local machine. You can download it from here for installation and once installed you should see a new Windows service (Microsoft SQL Data Sync) as shown below.  Make sure this service is running.

Installing and Setting up a Microsoft SQL Data Sync Agent On-Premises

Now go to All Programs and launch the Microsoft SQL Data Sync Agent application which should look like this. Click on the Submit Agent Key as shown below:

launch Microsoft SQL Data Sync Agent application

Copy or enter the Agent access key, which we copied in the first section of this tip, and click on OK.

Please copy/put the Agent access key

Now you should see the Register icon has become enabled, click on this and specify the connection information to connect to the database from your on-premises SQL Server instance. You can click on the "Test Connection" to validate the information and save it as shown below:

Now you will notice, Register icon getting enabled

Once you have the on-premises database registered in SQL Data Sync Agent, you can see it here as shown below:

Once you have local or on-premises database registered in SQL Data Sync Agent, you can see it here

Creating and configuring a Sync Group on Azure

Now you need to create a sync group, which is actually a logical grouping of Windows Azure SQL Databases and databases from the on-premises SQL Server instance (in case you want to sync an on-premises SQL Server database). To do this, go to the SQL DATABASES tab on the Windows Azure Management Portal, click on ADD SYNC and then on New Sync Group as shown below:

Creating and configuring a Sync Group on Azure

On the first screen of the Create Sync Group wizard, specify the name of the sync group and region where you want this sync group to be created. For best practices on selecting the right region, please check my earlier tip, "Understanding SQL Data Sync".

specify the name of the sync group and region where you want this sync group to be created

On the second screen of the Create Sync Group wizard, specify one of the databases from the Windows Azure SQL database instance as a hub database for this configuration. You need to specify the right credentials to connect to the specified database and the setting to use when there is conflict. In my case I chose Hub Wins in case of conflicts as shown below:

specify one of the databases from the Windows Azure SQL database instance as hub database for this configuration.

On the last screen of the Create Sync Group wizard, you will notice the on-premises SQL Server database gets listed in the list of reference database (this is because we configured Sync Agent on the local machine using the agent access key of this Sync Agent and registered an on-premises database), choose the database you want to use as a reference database for synchronization; specify the credentials and sync direction as shown below:

notice on-premises SQL Server database gets listed in the list of reference database

So far we created a sync group which includes databases for synchronization, we still need to specify tables and columns for synchronization. To do that, click on the sync group you created, click on SYNC RULES then click on the DEFINE SYNC RULES link as shown below:

click on SYNC RULES then click on DEFINE SYNC RULES link

Now specify the tables and columns from each table to be synchronized as shown below and then save it:

Now specify the tables and columns from each table to be synchronized

In my case I have a Department table in Windows Azure SQL Database which has data and the same table in my on-premises SQL Server database which does not have data. So what I want to do, is to synchronize from Windows Azure SQL Database to the on-premises SQL Server database:

I have a Department table in windows Azure SQL Database which has data and the same table in on-premises SQL Server database does not contain any data

When you kick off data synchronization, you can see data is synchronized to the on-premises SQL Server database as shown below. Also, if you notice there are some objects that get created when we register the database in the sync agent to be used by the sync agent for synchronization.

you can see data gets synchronized in the on-premises SQL Server database

As you might have noticed in the last step, we manually kicked off data synchronization but what if I want to set it up to be automated. To configure automatic synchronization, go to the CONFIGURE tab of the sync group you have created, specify automatic synchronization along with the synchronization frequency. Please refer my last tip, "Understanding SQL Data Sync" on best practices for synchronization and frequency.

we manually kicked off data synchronization but what if I want to set it up to run in automated way

You can go to the LOGS tab of the sync group to analyze the logs generated by the sync service during data synchronization as shown below.

You can go to LOGS tab of the sync group to analyze the logs generated by sync service during data synchronization

Please note, SQL Data Sync is in Preview as of writing this tip and features might change in the final release of the SQL Data Sync product; for the latest updates on SQL Data Sync refer to this msdn documentation.

Next Steps


Last Updated: 2013-11-12


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 07, 2015 - 3:19:46 PM - Ameya Thakur Back To Top

Very neat and easy to follow. thanks for your valuable contribution , 

Thanks,

Ameya Thakur


Tuesday, April 28, 2015 - 5:37:53 AM - sree Back To Top

Hi,

Is it possible to sync the data from on premisses sql to Azure database? If possible what is the process.

 

Thanks,

sree


Wednesday, December 03, 2014 - 12:50:25 PM - Mubeen Back To Top

This is indeed a great article with all the steps.  How it differs from Replication? To me, it seems like a kind of Transaction or Merge Replication with a option of Bi-Directional sync and agent jobs.  Appreciate you sharing, great blog post.


Wednesday, November 13, 2013 - 12:49:34 AM - Marco Back To Top

Thank you for putting detail step-by-step Data Sync implementation together.   It's really helpful.   I have one question - I removed database and Sync group in Azure after I done , however, I can't delete the Agent in the cloud even I stopped the Agent on-premise.   It still says "This agent cannot be removed because one more more databases are still associated with it. Please remove the databases associated with the agent and then try again "
Any suggestions?


Tuesday, November 12, 2013 - 5:19:40 PM - Arshad Back To Top

Thanks Sri, good to know it was helpful for you!


Tuesday, November 12, 2013 - 4:07:07 PM - Sri Back To Top

Simple yet cleanly wrtten..Thanks Arshad!!


Learn more about SQL Server tools