Home > Windows & SQL Server AZURE > SQL Database Service on Azure (Replication and Data Sync)

SQL Database Service on Azure (Replication and Data Sync)

This article focuses on replicating on-premises SQL Server Database data on Microsoft SQL Azure Database. We cannot use traditional  (Replication Topologies)way of replicating data to Azure SQL Server Database.

 

image

 

To set and configure we have to do the following.

 

  • Log on to Azure Portal or Manage Windows Azure
  • Browse to SQL Database from the panel image  (Create Azure Database)
  • On the right side of the panel your database will appear.Select the database by clicking the database record after the column [NAME].
  • At the bottom of the page click image  and select image
  • Register the new Sync Agent and install the [local client sync agent tool] from the link image
  • At the bottom of the page click image
  • Manage access key PREVIEW page will popup.Click generate to generate the sync agent key. This  key will be used to connect [Microsoft SQL Data Sync Agent Preview] client installed on step 5.

    image

  • Copy the key and pass it to the [Microsoft SQL Data Sync Agent Preview] client by clicking image
    Pass the copied key in Agent Key box and click ok.

    image

  • Register the On-premises database whose data has to be replicated by clicking image  on [Microsoft SQL Data Sync Agent Preview]

    image

    image 

    image 

  • You will be able to see the registered database under the sync Agent on the Azure Portal

    image 

  • Create the tables on the Azure SQL Databases whose data you want to replicate using Data Sync. Remember table name should be same and ever table should have a primary key.Azure portal will not show the table if it does not have primary key.

    Microsoft provide multiple method to transfer the database or tables to Azure .The one i used in this example is by using Migration Database to Azure Tool

  • You can see once the tables where created on Azure SQL Database
    image
  • Create Sync Group as we created Sync Agent.

    image 

    image image   image 

  • Sync Group  has been configured to sync the data from On-Premises database to Azure SQL Database

    image

    image 

  • Configure the Data Sync Rules
    Click on the Sync Group Name –> Select Sync Rules image –> image 

    DEFINE SYNC RULE will popup the select database window to select the database on which rule need to be defined. In this case i select HUB database to define the rules.

    image

  • Once you click the ok , you will be able to see all the tables in you Azure database which has primary key(Table with no primary key will not be display)

    image

    You can select the table and columns  or you can choose from options and select image
    image

    image 

  • Once the rule is provisioned and tables are in sync you can see the status of the Sync Group as below

    image

    image

 

Once Synchronization is  complete, Compare the azure SQL Database table and on-premises database table.You can also click on the LOGS on the Sync Group page

 

Message : Sync completed successfully in 35.61 seconds.     Upload:   17 changes applied    Download: 0 changes applied    For more information, provide tracing ID ‘f6876d05-63e9-4bef-b496-3ee05eb85b16’ to customer support.

 

image

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: