Sync Azure SQL Database to other SQL Database

Sync Azure SQL Database to other SQL Database

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
Sync Azure SQL Database to other SQL Database

In oltp DB, I have a sample table looks like below.

Sync Azure SQL Database to other SQL Database

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

Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

Configure source DB and destination DB

Next, we need to configure the source and destination DB, click into the sync group

Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

Input the credential for SQL Data Sync to login the source DB and click ok.

Sync Azure SQL Database to other SQL Database

Next we configure the destination DB.

Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

Once all done, click OK at the bottom.

Sync Azure SQL Database to other SQL Database

Select table(s) to be synced to destination DB

Let’s configure table(s) to be synced.

Sync Azure SQL Database to other SQL Database

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!

Sync Azure SQL Database to other SQL Database

ATTENTION:In order for SQL Data Sync to work, you need to allow access on BOTH of your DBs firewall!

Sync Azure SQL Database to other SSQL Database
Sync Azure SQL Database to other SQL Database
Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

Once I setup the FW, I can click Sync or just wait for 5 minutes for auto sync to kick in.

Sync Azure SQL Database to other SQL Database

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.

Sync Azure SQL Database to other SQL Database

Once login, we can verify that data is synced from OLTP to OLAP!

Sync Azure SQL Database to other SQL Database

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

AWS Certified SA, SysOps & Developer Associate, Alibaba Cloud certified SA. Focusing on Azure, Prometheus w/ Grafana, ELK and K8S now.