SQL Azure Wiki Staging Page

SQL Azure Wiki Staging Page

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

Introduction


With the release of SQL Azure Data Sync CTP2 we have extended the ability of SQL Azure Data Sync so that you can now synchronize on-premise SQL Server databases with SQL Azure. By extension, you are also able to synchronize multiple on-premise SQL Server databases that are geographically disburse, such as headquarters, regional, and retail offices with one or more SQL Azure databases.

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

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

Launch the Agent Configuration Tool

The Agent Configuration Tool guides you through the steps to configure this agent to work with the SQL Server databases assigned to it.

  1. Click the Start menu.
  2. Click All Programs,
  3. Navigate to the Microsoft SQL Azure Data Sync CTP folder.
  4. Click the SQL Azure Data Sync Agent CTP2 folder.
  5. When prompted by UAC whether or not to allow the program to make changes to yoru computer, click Yes.

Use the Agent Configuration 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 8)


    Figure 8 – Agent Access Key entry page
  4. 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)   
    7.  

    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. Create an Empty SQL Azure Database 
    2. 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)

    Create the 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.

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

    For more information

    Top 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  5.  

  6.  

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 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.

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • 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.

Page 1 of 1 (3 items)