NOTE: This page exists for staging and testing purposes only. The final content will be published in other pages.
 

Introduction

This article walks you through the steps to set up synchronization from an on-premise SQL Server to a SQL Azure database. 

Article Sections

The article contains the following sections. Go directly to any section by clicking on it in the list below.

• Create a SQL Server-to-SQL Azure Synchronization
  • Prerequisites
  • Register for the SQL Azure Data Sync Service
  • Create an Agent for your SQL Server Database 
  • Create a Sync Group
  • Monitor Your Synchronizations
  • Edit a Sync Group 
• Feedback
• For More Information

Create a SQL Server-to-SQL Azure Synchronization

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 

Prerequisites

1. You must have a Windows Live ID.
   If you do not have a Windows Live ID go to http://windows.live.com.
2. You must have a SQL Azure account.
   If you do not have a SQL Azure account go to http://sql.azure.com to start the process.
3. SQL Server 2008 or later is installed and is not running.
   For best results, SQL Server 2008 R2 or later. SQL Server 2008 R2 ads native support for SQL Azure to SQL Server Management Studio. A SQL Server 2008 R2 trial version can be downloaded from: http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

Register for the SQL Azure Data Sync Service

1. Go to https://datasync.azure.com.
2. Read the Terms of Use. (Figure 2 #1)
3. If you accept the Terms of Use check the “I have read and I accept the Terms of Use” checkbox. (Figure 2 #2)
4. Click Next. (Figure 2 #3)
   
   
   Figure 2 – SQL Azure Data Sync Service Registration page
   
5. Fill out the User Registration information
   For the Registration Code use your SQL Azure Data Sync CTP2 token.
   If you do not have a token you can apply for one. Go to SQL Azure Data Sync CTP2 and click the “Sign Up Now!” link.  For CTP2 tokens are restricted.
   
   
   Figure 3 – SQL Azure Data Sync Service User Registration page
   
6. Click Submit

Top 

Create an Agent for Your SQL Server Database

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.

Five steps to set up an agent for your SQL Server Database

1. Create a Key for the Agent
2. Install the Agent with AgentServiceSetup.msi
3. Configure the AgentConfigData.xml file
4. Add SQL Server Databases to the Agent
5. Register the SQL Server Databases

 Create a Key for the Agent

1. Return to the SQL Azure Data Sync UI. (https://datasync.azure.com)
2. Click the Agents tab.
3. Click Generate Agent Key. (Figure 4 #1)
   
   Figure 4 – Agents Tab
   
4. In the dialog, enter a name for this agent.
5. Click Generate.
6. On the Agents tab, Click on the Agent’s name. (Figure 4 #2)
7. Highlight the Agent key and copy it to the Clipboard for later use. (Figure 4 #3)

Top 

Install the Agent with AgentServiceSetup.msi

1. On the Agents tab, Click the Download AgentServiceSetup.msi link at the bottom of the page. (Figure 4 #4)
2. When asked if you want to Open or Save this file click Open.
3. At the installer Welcome screen click Next.
4. Read the License Agreement.
5. If you accept the License Agreement click the I Agree radio button then click Next, otherwise click Cancel.
6. When prompted, enter your local credentials and click Next.
7. On the Select Installation Folder screen (Figure 5)
   1. Click Disk Cost to determine if you have sufficient disk space. (Figure 5 #1)
   2. If you do not want to install AgentConfigData.xml at the default location (c:\Program Files (x86)\Microsoft SQL Azure Data Sync\CTP2) click “Browse” and browse to the folder where you want the agent installed. (Figure 5 #2)
   3. Select whether you want the agent installed for just you or anyone who uses this computer. (Figure 5 #3)
   4. Click Next. (Figure 5 #4)
      
      
      Figure 5 – Select Agent Installation Folder
      
8. On the Confirm Installation page click Next.
9. Wait until the installation completes.
   This can take several minutes.
10. On the Installation Complete page make sure the Launch AgentConfig to start adding databases checkbox is checked.
    
    
    Figure 6 – Installation Complete
    
11. Click Close.

Top

Configure the AgentConfigData.xml file

Two steps to configure the AgentConfigData.xml file

1. Launch the configuration tool
2. Use the tool to configure AgentConfigData.xml

1. Check the Encrypt Password checkbox. (Figure 7 #1).
   For more information on security and password encryption see SQL Azure Data Sync - Data Security.
2. Click Edit Agent Key. (Figure 7 #2)
   
   
   Figure 7 – UI for Configuring AgentConfigData.xml
   
3. Paste the agent key from your clipboard (See Figure 4 #3) into the Agent Key space (Figure 8). Remove any leading and trailing spaces. (Figure&nbstents/articles/sql-azure-data-sync-data-security.aspx">SQL Azure Data Sync - Data Security.
4. Click Edit Agent Key. (Figure 7 #2)
   
   
   Figure 7 – UI for Configuring AgentConfigData.xml
   
   
   Figure 8 – Agent Access Key entry page
   
5. Click OK.

   Top 

   Add SQL Server Databases to the Agent

   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.)

   1. Click Add Member. (Figure 7 #3)
   2. Click the SQL tab if the database you are adding uses SQL Server authentication, otherwise click the Windows tab. (Figure 9 and 10)
   3. 
      Figure 9 – SQL Server Authentication            Figure 10 – Windows Authentication
   4. Click Test Connection.
   5. If the connection succeeds click Save.
      Repeat steps 1 through 5 for each SQL Server database you want to add for this agent.
      Note: The agent is only for SQL Server databases. Add SQL Azure databases to a Sync Group using the SQL Azure Data Sync UI at https://datasync.azure.com/SADataSync.aspx.
      
   6. When finished close the dialog. (Figure 7)   
   18.  

   Top  

   Register Your SQL Server Databases

   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.

   1. Start the SQL Azure Data Sync Agent CTP2 service
      Windows 7 / Vista
      
      1. Click Start.
      2. Type “services.msc” in the text field labeled Search programs and files.
      3. In the search results click on the program called “Services.”
      Windows XP
      
      1. Click Start.
      2. Type “services” in the text field labeled Open.
      3. Click OK. 
   2. In the Services window, scroll to the entry for SQL Azure Data Sync Agent CTP2. Right-click the entry and then click Properties.
   3. In the SQL Azure Data Sync Agent CTP2 Properties window, click the drop-down menu labeled Startup type and select Automatic, then click the Start button located under the label Service Status.
   4. Click Apply then OK.
   5. Close the Services window
   6. Return to the SQL Azure Data Sync web UI. (Figure 11)
   7. Click on the Databases tab.
      You should see the name of your SQL Server databases listed.  If you don’t wait a few moments and re-click the Databases tab.
      
      
      Figure 11 – Databases tab with database name listed

    Top

   Create a Sync Group

   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.

   Two steps to create a Sync Group

   1. 
      Figure 11 – Databases tab with database name listed

    Top

   Create a Sync Group

   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 datpage/edit.aspx#CreateAnEmptySQLAzureDatabase">Create an Empty SQL Azure Database 

6. Create and Schedule the Sync Group

Create an Empty SQL Azure Database 

1. Start SQL Server Management Studio.
2. Connect to your SQL Azure server. (Figure 12)
   
   Figure 12 – Connect SQL Server Management Studio to your SQL Azure database
   
3. After connecting to your SQL Azure server, in SQL Server Management Studio click on the “New Query” button.
4. Create an empty database.
   Run the following query against the master database.
   CREATE DATABASE <yourAzureDatabaseName>
   GO
   After you create the database you should see it when you refresh the database list.
5. Return to the SQL Azure Data Sync web UI.
6. Click the Databases tab.
7. Click Add. (Figure 11 #1)
8. Enter the credentials for your SQL Azure database. (Figure 13)
   
   
   Figure 13 – Register your SQL Azure Database for SQL Azure Data Sync
   
9. Click Save.
   This new database should now be listed under the Databases tab.

Repeat the above steps for each SQL Azure database you want included in this sync group.

Top 

Create and Schedule a Sync Group

Four steps to creating a functioning Sync Group.

1. Start a New Sync Group and Name it
2. Add Databases to the Sync Group
3. Select Tables to Synchronize
4. Set the Synchronization Schedule

Start a New Sync Group and Name it

1. Return to the web SQL Azure Data Sync web UI.
2. Click on the Sync Groups tab.
3. Click on New Sync Group.
4. Give your new Sync Group a meaningful name. (Figure 14 #1)

Add Databases to the Sync Group

1. From the Registered Databases dropdown select a database to add to the Sync Group. (Figure 14 #2)
2. Click Add. (Figure 14 #3)
   
   Repeat for each database you want included in this Sync Group. (Figure 14 #4)
   
3. Click Next. (Figure 14 #5)
   
   
   Figure 14 – Add Databases to a Sync Group

Top

Select the Tables to Synchronize

1. Select the tables to include in this Sync Group
   1. In the Available Tables box highlight the table or tables you want included in this Sync Group. (Figure 15 #1)
   2. To add a table to the Sync Group highlight it in the Available Tables box, click the right arrow (). (Figure 15 #2)
      To remove a table from the Sync Group highlight it in the Selected Tables box and click the left arrow ().
2. Set the synchronization order of the tables in the Sync Group. 
   Tables are synchronized top to bottom as listed in the Selected Tables box. (Figure 15 #2) The order may be important to ensure that data is applied in the correct order to manage Foreign Key constraints.
   1. In the Selected Tables box highlight the table you want to move up or down.
   2. Click the up () or down () arrow to change the table’s position in the list. 
      

Set the Synchronization Schedule

1. Check the “Enabled” checkbox. (Figure 15 #3)
   If “Enabled” is not checked the sync schedule is not applied to the Sync Group.
2. Set the schedule for this Sync Group
   The minimum value that can be set is 5 minutes. (Figure 15 #4)

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

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  for each sync group you want to create.

Top 

Monitor Your Synchronizations

Once the Sync Group has been created and the synchronization schedule set you are able to monitor the synchronizations of the Sync Group.

1. If you just created a Sync Group, under the Sync Groups tab select the Sync Group and click Sync Now.
   This will initiate your first synchronization which will give you something to monitor in the following steps.
2. Click on the Sync Logs tab.
   
   
   Figure 16 – Sync Logs tab
   
   The Task State column notifies you of the state of each attempted operation. (Figure 16 #1).
   Pending                   
   In progress               
   Success                   
   Warning                  
   Failure                     
3. Click on the View Log link to see the log messages of any attempted operation. (Figure 16 #2)
4. The Task Log box identifies the type of operation attempted, the result, and related messages. (Figure 16 #3).

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. 

Top 

Edit a Sync Group

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

1. Set or Change the Synchronization Schedule
2. Set or Change the Synchronization Direction
3. Add Databases to the Sync Group

Set or Change the Synchronization Schedule

1. Click the Sync Groups tab. (Figure 17 #1)
2. Select the Sync Group for which you want to set a new schedule. (Figure 17 #2)
3. Click Edit. (Figure 17 #3)
   
   
   Figure 17 – Edit a Sync Group

Set or Change the Synchronization Direction

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 tablimg alt=" " src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/CommunityServer-Wikis-Components-Files/00-00-00-00-05/2061.SADS_5F00_SyncGroupTab.jpg" style="border:0px solid;" />
Figure 17 – Edit a Sync Group

Set or Change the Synchronization Direction

By default SQL Azure Data Sync synchronizations are bi-directional (See Figure 1). Data changes are collected and consolidated by es 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 Synchronization Direction dropdown select one of the following (Figure 18 #1)
  • Bi-directional – aggregates all changes from member databases to the hub, then distributes the aggregation out to the member databases.
  • Sync to Hub – aggregates from member databases to the hub database.
  • Sync from Hub – distributes from the hub database to the member databases.

Add Databases to the Sync Group

From the Registered Database dropdown select any database you want added to this sync group. (Figure 18 #2)

Set or Change the Synchronization Schedule

1. Set the synchronization frequency. (Figure 18 #3)
2. Make sure the Enabled checkbox is checked. (Figure 18 #4)
   If the Enabled checkbox is not checked the Sync Group will sync only when you click the Sync Now button.
   
   
   Figure 18 – Change a Sync Group’s Direction, Databases, and Schedule. 

 

 

 

 

 

 

 

 

 

 

 

1.  

When finished, click Save

Top.

Feedback

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.

• Email - syncfdbk@microsoft.com
• Send us a note on Twitter - @syncfx
• Post a comment on our blog - http://blogs.msdn.com/sync
• Microsoft Sync Framework Dev Center - http://social.microsoft.com/Forums/en-US/category/sync

For more information

• SQL Azure Data Sync Overview
• SQL Azure Data Sync Glossary of Terms
• Microsoft SQL Azure: http://www.microsoft.com/windowsazure/sqlazure/
• Microsoft Sync Framework Dev Center: http://msdn.microsoft.com/sync

Top