Edit

Transactional replication with Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

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

Overview

You can use transactional replication to push changes made in an Azure SQL Managed Instance to:

Components

The key components in transactional replication are the Publisher, Distributor, and Subscriber, as shown in the following picture:

replication with SQL Database

Role Azure SQL Database Azure SQL Managed Instance
Publisher No Yes
Distributor No Yes
Pull subscriber No Yes
Push Subscriber Yes Yes

The Publisher publishes changes made on some tables (articles) by sending the updates to the Distributor. The publisher can be an Azure SQL Managed Instance or a SQL Server instance.

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

The Subscriber receives changes made on the Publisher. A SQL Server instance and Azure SQL Managed Instance can both be push and pull subscribers, though a pull subscription is not supported when the distributor is an Azure SQL Managed Instance and the subscriber is not. A database in Azure SQL Database can only be a push subscriber.

Azure SQL Managed Instance can support being a Subscriber from the following versions of SQL Server:

  • SQL Server 2016 and later

  • SQL Server 2014 RTM CU10 (12.0.4427.24) or SP1 CU3 (12.0.2556.4)

  • SQL Server 2012 SP2 CU8 (11.0.5634.1) or SP3 (11.0.6020.0) or SP4 (11.0.7001.0)

    Note

    • 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.
    • 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.)

Types of replication

There are different types of replication:

Replication Azure SQL Database Azure SQL Managed Instance
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

Supportability Matrix

The transactional replication supportability matrix for Azure SQL Managed Instance is the same as the one for SQL Server.

Publisher Distributor Subscriber
SQL Server 2019 SQL Server 2019 SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2017 SQL Server 2019
SQL Server 2017		 SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2016 SQL Server 2019
SQL Server 2017
SQL Server 2016		 SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2014 SQL Server 2019
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 2019
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 2019
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

When to use

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 databases in a SQL Server instance or Azure SQL Database that subscribed for the changes.
  • Keep several distributed databases in synchronized state.
  • Migrate databases from one SQL Server instance or Azure SQL Managed Instance to another database by continuously publishing the changes.

Compare Data Sync with Transactional Replication

Category 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 - No transactional consistency
- Higher performance impact		 - Can't publish from Azure SQL 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 SQL Managed Instance

Single instance as Publisher and Distributor

Publisher and distributor are configured within a single SQL Managed Instance and distributing changes to another SQL Managed Instance, SQL Database, or SQL Server instance.

Publisher with remote distributor on SQL Managed Instance

In this configuration, one managed instance publishes changes to a distributor placed on another SQL Managed Instance that can serve many source SQL Managed Instances and distribute changes to one or many targets on Azure SQL Database, Azure SQL Managed Instance, 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.

On-premises Publisher/Distributor with remote subscriber

Azure SQL Database as subscriber

In this configuration, a database in Azure SQL Database or Azure SQL Managed Instance is a subscriber. This configuration supports migration from on-premises to Azure. If a subscriber is a database in Azure SQL Database, it must be in push mode.

Requirements

  • Use SQL Authentication for connectivity between replication participants.
  • Use an Azure Storage Account share for the working directory used by replication.
  • Open TCP outbound port 445 in the subnet security rules to access the Azure file share.
  • Open TCP outbound port 1433 when the SQL Managed Instance is the Publisher/Distributor, and the Subscriber is not. You may also need to change the SQL Managed Instance NSG outbound security rule for allow_linkedserver_outbound for the port 1433 Destination Service tag from virtualnetwork to internet.
  • Place both the publisher and distributor in the cloud, or both on-premises.
  • Configure VPN peering between the virtual networks of replication participants if the virtual networks are different.

Note

You may encounter error 53 when connecting to an Azure Storage File if the outbound network security group (NSG) port 445 is blocked when the distributor is an Azure SQL Managed Instance database and the subscriber is on-premises. Update the vNet NSG to resolve this issue.

With failover groups

Using transactional replication with instances that are in a failover group is supported. However, if you configure replication before adding your managed instance into a failover group, replication pauses when you start to create your failover group, and replication monitor shows a status of Replicated transactions are waiting for the next log backup or for mirroring partner to catch up. Replication resumes once the failover group is created successfully.

If a publisher or distributor SQL Managed Instance is in a failover group, the SQL Managed Instance administrator must clean up all publications on the old primary and reconfigure them on the new primary after a failover occurs. The following activities are needed in this scenario:

  1. Stop all replication jobs running on the database, if there are any.

  2. Drop subscription metadata from publisher by running the following script on publisher database:

    EXEC sp_dropsubscription @publication='<name of publication>', @article='all',@subscriber='<name of subscriber>'

  3. Drop subscription metadata from the subscriber. Run the following script on the subscription database on subscriber SQL Managed Instance:

    EXEC sp_subscription_cleanup
   @publisher = N'<full DNS of publisher, e.g. example.ac2d23028af5.database.windows.net>',
   @publisher_db = N'<publisher database>',
   @publication = N'<name of publication>';

  4. Forcefully drop all replication objects from publisher by running the following script in the published database:

    EXEC sp_removedbreplication

  5. Forcefully drop old distributor from original primary SQL Managed Instance (if failing back over to an old primary that used to have a distributor). Run the following script on the master database in old distributor SQL Managed Instance:

    EXEC sp_dropdistributor 1,1

If a subscriber SQL Managed Instance is in a failover group, the publication should be configured to connect to the failover group listener endpoint for the subscriber managed instance. In the event of a failover, subsequent action by the managed instance administrator depends on the type of failover that occurred:

  • For a failover with no data loss, replication will continue working after failover.
  • For a failover with data loss, replication will work as well. It will replicate the lost changes again.
  • For a failover with data loss, but the data loss is outside of the distribution database retention period, the SQL Managed Instance administrator will need to reinitialize the subscription database.

Next steps

For more information about configuring transactional replication, see the following tutorials:

See also