Tuesday, January 24, 2012

In this article, I will show you how to synchronize the databases between on-premise SQL server and SQL Azure. FYI, no code involved in this demonstration.

Register for SQL Azure Data Sync Service

  1. Navigate to https://datasync.azure.com/
  2. Read the Terms Of Use.
  3. Check the "I have read and I accept the Terms Of Use" checkbox, if you accept the Terms Of Use.
  4. Click Next.
    Terms of Use
  5. Fill out the "User Registration" form.
    "Registration Code" can be applied from https://datasync.azure.com/
  6. Click Submit.
    User Registration

Create an Agent for your SQL Server Database

  1. Once your SQL Azure Data Sync account is ready, return to the SQL Azure Data Sync UI.
  2. Navigate to Agents tab.
  3. Click Generate Agent Key.
  4. Enter the Agent’s name and click Generate.
  5. Then, highlight the Agent key and copy it to the clipboard.
    Generate Agent Key
  6. Install the AgentServiceSetup.msi by clicking the download link.
  7. Install the service accordingly to the instruction.
  8. Once the installation completed, launch the Agent Configuration Tool from Start | All Programs | Microsoft SQL Azure Data Sync CTP | SQL Azure Data Sync Agent CTP2.

      Configure AgentConfigData.xml

  9. Check the "Encrypt Password" checkbox.
    Ecrypt Password
  10. Click "Edit Agent Key" and paste the agent key from your clipboard into the Agent Key space.
    Agent Key
  11. Click "Ping Sync Service" to confirm that the Sync Service is running and your proxies can successfully connect to the service.
  12. "Successfully pinged service" alert will pop up if the connection successfully established.

    Add SQL Server Databases to the Agent

  13. Click "Add Member" from the toolbar.
  14. Add the SQL Server details.
  15. Click "Test Connection".
  16. If the connection succeeds, click Save and close the dialog.
    SQL Server Configuration

      Run SQL Azure Data Sync Agent Service

  17. Open run.exe.
  18. Search for services.msc.
  19. Click on the program called "Services".
  20. Look for SQL Azure Data Sync Agent CTP2.
  21. Right click on it and Start the service.
  22. Click Apply then click OK.
  23. Close the Services window and return to the SQL Azure Data Sync web UI.

    Register the SQL Server Databases

  1. Navigate back to https://datasync.azure.com/
  2. Go to "Databases" tab.
  3. Click "Add".
  4. Register your database accordingly for your on-premise SQL Server and SQL Azure server.
    Add Databases
    * For the first time synchronization, please create a SQL Azure database.

    Create a Sync Group

  1. Navigate to https://datasync.azure.com/
  2. Go to "Sync Groups" tab.
  3. Click "New Sync Group".
  4. Click on the "Registered Databases" dropdown list.
  5. Add both on-premise SQL Server and SQL Azure server databases into the "Member List".
  6. Type your preferable "Sync Group Name" and click Next.
    New Sync Group
  7. Select the tables from each server that you want to synchronize.
  8. Check the "Enabled" checkbox and set your preferable schedule time for the synchronization.
  9. Click "Create Sync Group".
    Add tables
  10. Well, that all for the demonstration. You may view the Synchronization logs from the "Sync Logs" tab.

No comments:

Post a Comment