NOTE: This page exists for staging and testing purposes only. The final content will be published to other pages.
This article walks you through the steps to set up synchronization from an on-premise SQL Server to a SQL Azure database.
The article contains the following sections. Go directly to any section by clicking on it in the list below.
This Walkthrough takes you through the steps to set up synchronization from an on-premise SQL Server database to a SQL Azure database. These steps can be applied to synchronize any number of SQL Server databases with one or more SQL Azure databases. Even though there are no physical restrictions, for CTP2 we have only tested a maximum of ten databases in a single Sync Group.
Figure 1 – SQL Server to SQL Azure Synchronization
Top
The SQL Azure Data Sync Agent resides with the SQL Server database and SQL Azure. As part of the SQL Azure Data Sync service the agent enables bi-directional HTTPS based communication between the on-premise SQL Server databases and SQL Azure databases. You should have one agent for each geographic location, such as headquarters, regional offices, and retail outlets.
Two steps to configure the AgentConfigData.xml file
The Agent Configuration Tool guides you through the steps to configure this agent to work with the SQL Server databases assigned to it.
The SQL Azure Data Sync service the Agent enables bi-directional HTTPS based communication between the on-premise SQL Server database and SQL Azure databases. A single Agent can support multiple SQL Server databases, however, you should have a separate agent for each geographical location where you want to use the SQL Azure Data Sync service to synchronize a SQL Server database. For example, if headquarters is in New York, the European regional office in London, and the Asian regional office in Bejing, you would have three agents, one at each location even if they were all synchronizing with the same SQL Azure database. (See Figure 1.)
After the AgentConfigData.xml file is configured we need to start the SQL Azure Data Sync Agent service. If at any time you alter the AgentConfigData.xml file, such as adding or changing an endpoint, you need to stop and re-start the SQL Azure Data Sync Agent so that changes in the configuration are applied.
A Sync Group is a collection of SQL Azure and SQL Server databases that are configured for mutual synchronization by the SQL Azure Data Sync service. A Sync Group is comprised of a "hub" database and one or more "member" databases. The "hub" database must be a SQL Azure databaseStart the SQL Azure Data Sync Agent Service.
Repeat the above steps for each SQL Azure database you want included in this sync group.
Four steps to creating a functioning Sync Group.
When finished click “Create Sync Group.” (Figure 15 #5) Figure 15 – Add Tables to a Sync Group
Note: Once you have created the Sync Group you can go back and edit it. You cannot go back and add or remove tables from the Sync Group. Repeat the steps in Create a Sync Group for each sync group you want to create.
Once the Sync Group has been created and the synchronization schedule set you are able to monitor the synchronizations of the Sync Group.
Click the Refresh Tasks button (Figure 16 #4) to refresh the lists of tasks. If additional tasks have run since the last refresh they are added to the list. If you deleted a Sync Group since the last refresh all tasks related to that Sync Group are removed from the list.
After you create a Sync Group you may want to make changes to it. Some changes, such as adding or removing a table or database to or from the Sync Group can only be done by creating a new Sync Group. Other changes, such as changing the synchronization schedule or the synchronization direction can be done by clicking Edit from the Sync Groups tab. Three types of Edits you can do to an existing Sync Group
By default SQL Azure Data Sync synchronizations are bi-directional (See Figure 1). Data changes are collected and consolidated by the SQL Azure “hub” database from each “member” database. The consolidated changes are then sent to each “member” database, thereby maintaining consistent data across all tables in the Sync Group. Some scenarios require single direction synchronization, such as backup, a reporting-only environment, or publishing data. SQL Azure Data Sync supports single direction syncs.
From the Registered Database dropdown select any database you want added to this sync group. (Figure 18 #2)
When finished, click Save
Top.
This release was provided to gather feedback and suggestions from our customers. Now that you have previewed what we are doing please let us know what you think of our direction, suggestions you might have, and what your experience with SQL Azure Data Sync has been.
You can contact us in any of a number of ways.
Maheshkumar S Tiwari edited Revision 12. Comment: Added tags
Ed Price MSFT edited Revision 5. Comment: Also added a clarifying note at the top.
Ed Price MSFT edited Revision 4. Comment: I talked to Russ. This is his staging page. So let's go with a title that makes a little more sense.