Transactional replication with single, pooled, and instance databases in Azure SQL Database

Transactional replication is a feature of Azure SQL Database and SQL Server that enables you to replicate data from a table in Azure SQL Database or a SQL Server to the tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.

When to use Transactional replication

Transactional replication is useful in the following scenarios:

  • Publish changes made in one or more tables in a database and distribute them to one or many SQL Server or Azure SQL databases that subscribed for the changes.
  • Keep several distributed databases in synchronized state.
  • Migrate databases from one SQL Server or managed instance to another database by continuously publishing the changes.

Overview

The key components in transactional replication are shown in the following picture:

replication with SQL Database

The Publisher is an instance or server that publishes changes made on some tables (articles) by sending the updates to the Distributor. Publishing to any Azure SQL database from an on-premises SQL Server is supported by the following versions of SQL Server:

  • SQL Server 2019 (preview)
  • SQL Server 2016 to SQL 2017
  • SQL Server 2014 SP1 CU3 or greater (12.00.4427)
  • SQL Server 2014 RTM CU10 (12.00.2556)
  • SQL Server 2012 SP3 or greater (11.0.6020)
  • SQL Server 2012 SP2 CU8 (11.0.5634.0)
  • For other versions of SQL Server that do not support publishing to objects in Azure, it is possible to utilize the republishing data method to move data to newer versions of SQL Server.

The Distributor is an instance or server that collects changes in the articles from a Publisher and distributes them to the Subscribers. The Distributor can be either Azure SQL Database managed instance or SQL Server (any version as long it is equal to or higher than the Publisher version).

The Subscriber is an instance or server that is receiving the changes made on the Publisher. Subscribers can be either single, pooled, and instance databases in Azure SQL Database or SQL Server databases. A Subscriber on a single or pooled database must be configured as push-subscriber.

Role Single and pooled databases Instance databases
Publisher No Yes
Distributor No Yes
Pull subscriber No Yes
Push Subscriber Yes Yes
     

Note

A pull subscription is not supported when the distributor is an Instance database and the subscriber is not.

There are different types of replication:

Replication Single and pooled databases Instance databases
Standard Transactional Yes (only as subscriber) Yes
Snapshot Yes (only as subscriber) Yes
Merge replication No No
Peer-to-peer No No
Bidirectional No Yes
Updatable subscriptions No No
     

Note

  • Attempting to configure replication using an older version can result in error number MSSQL_REPL20084 (The process could not connect to Subscriber.) and MSSQ_REPL40532 (Cannot open server <name> requested by the login. The login failed.)
  • To use all the features of Azure SQL Database, you must be using the latest versions of SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT).

Supportability matrix for Instance Databases and On-premises systems

The replication supportability matrix for instance databases is the same as the one for SQL Server on-premises.

Publisher Distributor Subscriber
SQL Server 2017 SQL Server 2017 SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2016 SQL Server 2017
SQL Server 2016
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2014 SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2012 SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2008 R2
SQL Server 2008
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
     

Requirements

  • Connectivity uses SQL Authentication between replication participants.
  • An Azure Storage Account share for the working directory used by replication.
  • Port 445 (TCP outbound) needs to be open in the security rules of the managed instance subnet to access the Azure file share.
  • Port 1433 (TCP outbound) needs to be opened if the Publisher/Distributor are on a managed instance and the subscriber is on-premises.

Note

Compare Data Sync with Transactional Replication

Data Sync Transactional Replication
Advantages - Active-active support
- Bi-directional between on-premises and Azure SQL Database
- Lower latency
- Transactional consistency
- Reuse existing topology after migration
Disadvantages - 5 min or more latency
- No transactional consistency
- Higher performance impact
- Can’t publish from Azure SQL Database single database or pooled database
- High maintenance cost

Common configurations

In general, the publisher and the distributor must be either in the cloud or on-premises. The following configurations are supported:

Publisher with local Distributor on a managed instance

Single instance as Publisher and Distributor

Publisher and distributor are configured within a single managed instance and distributing changes to other managed instance, single database, pooled database, or SQL Server on-premises.

Publisher with remote distributor on a managed instance

In this configuration, one managed instance publishes changes to distributor placed on another managed instance that can serve many source managed instances and distribute changes to one or many targets on managed instance, single database, pooled database, or SQL Server.

Separate instances for Publisher and Distributor

Publisher and distributor are configured on two managed instances. There are some constraints with this configuration:

  • Both managed instances are on the same vNet.
  • Both managed instances are in the same location.

Publisher and distributor on-premises with a subscriber on a single, pooled, and instance database

Azure SQL DB as subscriber

In this configuration, an Azure SQL Database (single, pooled, and instance database) is a subscriber. This configuration supports migration from on-premises to Azure. If a subscriber is on a single or pooled database, it must be in push mode.

Next steps

  1. Configure replication between two managed instances.
  2. Create a publication.
  3. Create a push subscription by using the Azure SQL Database server name as the subscriber (for example N'azuresqldbdns.database.windows.net and the Azure SQL Database name as the destination database (for example Adventureworks. )
  4. Learn about the limitations of Transactional replication for a managed instance

See Also