Transactional replication with Azure SQL Managed Instance

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:

• A SQL Server database (on-premises or on an Azure Virtual Machine)
• A database in Azure SQL Database
• An instance database in Azure SQL Managed Instance

Components

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

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 an 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 isn't supported when the distributor is an Azure SQL managed instance and the subscriber isn't. 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 versions
• 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)

Types of replication

There are different types of replication:

Supportability matrix

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

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

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

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 SQL 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.

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

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 isn't. 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.

Limitations

Transactional replication has some limitations that are specific to Azure SQL Managed Instance. Learn more about these limitations in this section.

Snapshot files aren't deleted from Azure Storage Account

Azure SQL Managed Instance is using user configured Azure Storage Account for snapshot files used for transactional replication. Unlike SQL Server in the on-premises environment, Azure SQL Managed Instance isn't deleting snapshot files from Azure Storage Account. Once files are no longer needed, you should delete them. This can be done via Azure Storage interface on Azure portal, Microsoft Azure Storage Explorer, or via command line clients (Azure PowerShell or CLI) or Azure Storage Management REST API.

Here is an example of how you can delete file and how you can delete an empty folder.

az storage file delete-batch --source <file_path> --account-key <account_key> --account-name <account_name>
az storage directory delete --name <directory_name> --share-name <share_name> --account-key <account_key> --account-name <account_name>

Number of distribution agents running continuously

Number of distribution agents configured to run continuously is limited to 30 on Azure SQL Managed Instance. To have more distribution agents they need to be running either on demand or with a defined schedule. Schedule can be defined with daily frequency and occurrence on every 10 seconds (or more), so even though it's not continuous, you still can have distributor that's introducing latency that's only several seconds. When large number of distributors is needed, it's recommended to use scheduled and not continuous configuration.

With failover groups

Using transactional replication with instances that are in a failover group is supported. However, if you configure replication before adding your SQL 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. Replace the <name of publication> and <name of subscriber> values:

   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 the subscriber SQL managed instance. Replace the <full DNS of publisher> value. For example, example.ac2d23028af5.database.windows.net:

   EXEC sp_subscription_cleanup
      @publisher = N'<full DNS of publisher>',
      @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 the 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 the 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 SQL managed instance. In the event of a failover, subsequent action by the SQL 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 works as well. It replicates 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 needs to reinitialize the subscription database.

Next steps

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

• Configure replication between a SQL Managed Instance publisher and subscriber
• Configure replication between a SQL Managed Instance publisher, SQL Managed Instance distributor, and SQL Server subscriber
• Create a publication.
• Create a push subscription by using the server name as the subscriber (for example N'azuresqldbdns.database.windows.net), and the database in Azure SQL Database name as the destination database (for example, Adventureworks).

See also

• Replication with a SQL Managed Instance and a failover group
• Replication to SQL Database
• Replication to managed instance
• Create a Publication
• Create a Push Subscription
• Types of Replication
• Monitoring (Replication)
• Initialize a Subscription