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 called XXX.DATABASE.WINDOWS.NET.

PLAYdb.JPG

Post Checks

Replication monitor is your friend but later on in this section we will issue some T-SQL.

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.

Checking Replication Tables in Azure

Connecting to SQL Database you will see the replication tables within system tables.

SELECT @@VERSION

sqlazu

The below table contains one row of replication information for each distribution agent servicing the local subscriber database, subscription type 0 means push subscription.

 SELECT * FROM [dbo].[MSreplication_subscriptions]

replazure

The below table obviously will show what objects you are replicating.


SELECT * FROM [dbo].[MSreplication_objects]

objects

Below table will show you the synchronization status.

 SELECT * FROM [dbo].[MSsubscription_agents]
 

synctime

Going back to your local SQL Server you can run the following:

exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = @@ServerName, @publication_type=0

lats

Here you can see my status of 3 and warning 2 – status 3 means replication was in progress and warning 2 meant latency issues where the time taken to replicate data from my local transactional publisher to the subscriber (Azure based) exceeded the threshold, in seconds.

You could see this via replication monitor that eventually the command went through.

lat2

Then re-issuing the system stored procedure everything looked normal.

 exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = @@ServerName, @publication_type=0

lat3

Observations: I will note 1 thing, when replication is idle and you issue a command on the publisher the log reader agent queues transactions to the distribution database then from the distribution database the command(s) get sent to the subscriber and it is here that sometimes you may see TCP error/ communication messages.

Below you can see TCP errors.

select * from MSdistribution_history WHERE ERROR_ID <> 0

error

Now if you dig into the  MSrepl_errors table you may see further errors:Communication link failure(s) that relates to the above screen shot. To me  it seems that the initial connection to Azure fails, maybe due to timeout? Or maybe it’s down to the fact that Azure terminates the connection if idle past a certain time limit? BUT it is self healing, it retries the command and works fine.

 

Query Performance Insight

I know the data is being replicated and you can even use Azure’s Query Performance Insight tool to confirm it.

Query ID 113 has a pink bar below.

pinkbar

This was the replicated code being inserted into the database, remember it was the insert where ID = 6.

insert

So we have now moved a subset of tables that will be “reporting” based to a dedicated SQL database in Azure, how you want to query it is up to you, Power BI does make sense.

Enter Power BI

Connecting to Azure SQL Database (shown below as a data source) is easy.

powerbi

sqlauth

Again I use a login that has the relevant permissions to read data. There you have it; the data is there ready for analysis.

data101

3 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

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s