Transactional Replication Using an Azure SQL Database Subscriber

By Graeme Scott – Data Platform Solution Architect.

For companies that have geographically dispersed operating models which may involve physical stores, outlets, venues or branches; data replication from individual stores to data centres is often a key technical function that enables companies to measure overall business performance.

It is not uncommon for geographically dispersed companies to capture a significant amount of transactional and operational data at individual stores or branches; in these scenarios, customers often use transactional replication to transport the data centrally for analysis.

With SQL Server 2016 it's now possible to replicate data from an on premise replication publisher to an Azure SQL Database subscriber.

This replication configuration provides customers with the following possibilities:

• A very easy route to transition to a hybrid – on premise cloud configuration with minimal disruption to business continuity.
• Off load reporting workload to separate cloud infrastructure.
• Provide fine grain control as to the database objects (tables, views) that are stored within an Azure SQL Database. With replication, individual tables (articles) are added to a publication that are to be replicated to a subscriber.
• With data stored within an Azure SQL Database subscriber, customers can enrich the data and gain increased insight by leveraging a range of Azure services, for example Azure Machine Learning and R.
• Data recoverability and high availability.

Setting up transactional replication from an on premise publisher to an Azure SQL Database subscriber is practically identical when compared to an on premise setup experience using SQL Server Management Studio. The configuration can also be achieved using TSQL scripts.

When I setup transactional replication to use an Azure SQL Database subscriber, the only change I had to make compared to a full on premise configuration was the subscriber connection to suit a Azure SQL Database (Azure SQL Database connection string):

SQL Server Replication Monitor also works seamlessly with an Azure SQL Database subscriber, all the replication information and diagnostics data are present.

So what are the limitations associated with using an Azure SQL Database as a subscriber to for transactional replication?

• Only transactional replication is supported.
• The transactional replication needs to be a push subscription.
• Any post snapshot scripts would need to be verified to determine suitability for an Azure SQL Database.
• A SQL Azure Database can only be a subscriber database for transactional replication.
• There will be limitations on publishing database objects that are CLR based.

It's possible to meet the majority of these goals with SQL Server Availability Groups. The main differences with these two options are that Availability Groups replicate an entire database as a secondary replica.

With transactional replication it's possible to replicate only a single table from within a publisher database. This may be less of a barrier for customers to consider moving data and workload to an Azure SQL Database.

The following article has more details about replicating to SQL Azure:

https://azure.microsoft.com/en-gb/blog/transactional-replication-to-azure-sql-db/

The following article has more details about SQL Server replication with SQL Server 2016:

https://msdn.microsoft.com/en-us/library/ms152559.aspx

It's outside the scope of this article however it is possible to use earlier versions of SQL Server (SQL Server 2012 upwards) to replicate from an on premise publisher to an Azure virtual machine. Some more details can be found here:

https://blogs.msdn.microsoft.com/repltalk/2014/07/28/microsoft-sql-replication-between-windows-azure-virtual-machines/

• Anonymous
  June 09, 2016
  Useful, especially the links to older versions of SQL able to do the same. How about going from Azure to my widely distributed estate, if i have data on a central on-site database that updates constantly that i wish to replicate outwards i can currently use Transactional Replication. If my central data is in Azure are my options limited to an ETL?