Image：https://azure.microsoft.com/en-us/blog/announcing-the-general-availability-of-azure-sql-data-sync/
SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.
What happend to me is that my customer uses their DB as OLTP in the beginning but later on, without acknowledge me, use the DB as OLAP as well. It causes the DB to run at 100% capacity 95% of the time which causing long response time to their ERP and POS system.
The solution is to split the DB by its purpose, SQL Data Sync happens to fit my need, which I can use it to sync data from OLTP DB to OLAP DB.
In my lab environment, I have two Azure SQL Databases provision in the same Vnet, which are
- ystatit-oltp, the source DB
- ystatit-olap, the destination DB
In oltp DB, I have a sample table looks like below.
Now, let’s start setting up the sync process. The steps are
- Create sync group, configure source DB, destination DB and sync frequency
- Select table(s) to be synced to destination DB
- Verify result
Create sync group
On source DB, choose Sync to other databases
You can configure if DB is sync automatically and at what frequency and most importantly, you can configure who ( source or destination DB ) wins where there is data conflict. For my case, oltp DB is the source DB, so it always win, so I choose hub.
Configure source DB and destination DB
Next, we need to configure the source and destination DB, click into the sync group
This is where you setup your DBs and tables configuration, as well as start / stop sync job and view sync job list. Let’s configure our DBs first.
First we configure the source DB so that SQL Data Sync can access source DB and read out tables we want to sync to destination DB.
Input the credential for SQL Data Sync to login the source DB and click ok.
Next we configure the destination DB.
As shown below, we input destination DB’s information. Pay attention that since we are syncing data TO destination DB, so sync direction will be From the Hub, which is the source DB. Username and password is the credential setup in destination DB.
Once all done, click OK at the bottom.
Select table(s) to be synced to destination DB
Let’s configure table(s) to be synced.
You will see the two DBs we have setup previously in the drop down list, select the source DB and refresh the schema if no tables are shown. Once shown, choose the table(s) you like to sync and click save at the bottom.
Pay attention that only tables with primary key can be synced!
ATTENTION：In order for SQL Data Sync to work, you need to allow access on BOTH of your DBs firewall!
Verify result
SQL Data Sync will kick into work for the first time once saved in the previous step. In my case, I did not allow FW access in the destination DB, so I had error for the auto first run.
Once I setup the FW, I can click Sync or just wait for 5 minutes for auto sync to kick in.
Lastly, let login OLAP DB to verify the data. Since my portal’s ip is not allowed on OLAP DB’s FW, so error msg is shown when loggin in. Just click the link provided and portal will whitelist the IP automatically, then click OK again.
Once login, we can verify that data is synced from OLTP to OLAP!
I have also tested to insert new data into OLTP DB and the new data will sync to OLAP after 5 minutes by auto sync.
That’s it for today, hope you like it ;)
REFERENCE：
Tutorial：Set up SQL Data Sync between databases in Azure SQL Database and SQL Server
