I had a need to setup transactional replication from my SQL Server to SQL Database (Azure) where I only needed a selection of tables that I wanted to off-load to Azure.  For this post I will be going through the setup, post checks with T-SQL / replication monitor, checking the incoming commands with Azure’s query performance insight tool and finally hooking it up to Power BI.

If this does sound like something that you want to do then all I can say is that it is possible and seems to work fine (minus latency fluctuations and security setup configuration).

A picture speaks a thousand words (just throw Power BI tool on top of it).

Diagram

Pre-Reqs

• Make sure your locally installed SQL Server (Publisher/ distributor) IP address is allowed to connect to Azure SQL Database.
• SQL Database must be a push subscriber of a SQL Server publisher.
• The distribution database and the replication agents cannot be placed on SQL Database.
• Snapshot and one-way transactional replication are supported only.
• The publisher and distributor must be at least at one of the following versions SQL Server 2012 SP3 – SQL Server 2016.
• I did an initial “data sync” between SQL Server and SQL Database via a bacpac see here for more details: https://blobeater.blog/category/azure/migration/

Setting up the Publication

Select your database.

Remember only snapshot and transactional replication is possible to Azure.

SELECT your tables.

Another key thing here – where you have a Primary Key column it must be published. Primary key columns are required in transactional publications.

I then create a snapshot.

For the snapshot security setup, I wouldn’t use SQL Server Agent accounts I would use dedicated accounts.

There is a folder within your MSSQL directory: \repldata\unc\ you need to make sure the accounts used in replication have access to it.

Create Subscription

Select the publisher that we created in the previous section.

Here opting for push-mode.

Here now we make the connection to Azure via the Add Subscriber option

Now you must make the connection to SQL Database, I do not have Azure Active Directory setup so I used SQL authentication.

Once you hit connect and you are in that is a very good sign.

Distribution security, again you should be using dedicated accounts.

Connecting to the subscriber I used a login that is a server admin – could you/should you be more granular? I would so say, so please try to be.

The Initialisation settings are as shown below.

You can clearly see from the below that it shows the synchronization status for a server.

Post Checks

Replication monitor is your friend.

I ran some updates on a table called dbo.people, from the below screen shot you can see the movement of data taking place.

Let’s insert a tracer token to check latency, you know it’s probably a safe option to be selective on what tables you want to replicate to Azure, it does have some distance to go. The below screen shot does not really surprise me.

Let’s just connect to SQL Database and check a table, the most recent INSERT was ID 6.

Note: This is just dummy data for this post.