Setting up SQL Server to SQL Database (Azure) Replication

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

repl

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

repl1

Select your database.

repl2.JPG

Remember only snapshot and transactional replication is possible to Azure.

repl3

SELECT your tables.

repl4

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.

repl5

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

repl7

repl8

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

localdir

Create Subscription

sub

Select the publisher that we created in the previous section.

sub1

Here opting for push-mode.

push

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

sub2

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

ssmsmain1

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

net

Distribution security, again you should be using dedicated accounts.

dist

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.

repl10

The Initialisation settings are as shown below.

sub3

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

PLAYdb.JPG

Post Checks

Replication monitor is your friend.

monitor

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

datamoves

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.

lat

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

ssmsazure

Note: This is just dummy data for this post.

 

Advertisements

4 thoughts on “Setting up SQL Server to SQL Database (Azure) Replication

  1. Pingback: Setting up SQL Server to SQL Database (Azure) Replication - SQL Server Blog - SQL Server - Toad World

  2. Pingback: Azure Cloud “Fear” Busting #4 – Losing Control? | All About SQL

  3. Pingback: Setting up SQL Server to SQL Database (Azure) Replication – Toad World Blog

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s