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.
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 (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 and select
- Register the new Sync Agent and install the [local client sync agent tool] from the link
- At the bottom of the page click
- 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.
- Copy the key and pass it to the [Microsoft SQL Data Sync Agent Preview] client by clicking
Pass the copied key in Agent Key box and click ok.
- Register the On-premises database whose data has to be replicated by clicking on [Microsoft SQL Data Sync Agent Preview]
- You will be able to see the registered database under the sync Agent on the Azure Portal
- 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
- Create Sync Group as we created Sync Agent.
- Sync Group has been configured to sync the data from On-Premises database to Azure SQL Database
- Configure the Data Sync Rules
Click on the Sync Group Name –> Select Sync Rules –>
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.
- 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)
- Once the rule is provisioned and tables are in sync you can see the status of the Sync Group as below
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.