Publish data changes from Azure SQL Managed Instance using transactional replication

In some cases, you might need to get all updates made in a table and publish the changes to another table in order to keep them in sync.

Transactional replication is a technology that can help you to do this. Transactional replication in Azure SQL Database Managed Instance enables you to define what table should be published to some remote destination, and then it will automatically identify all changes made in the table by reading the database transaction log, and then it will apply the same changes on a remote instance.

Azure SQL Managed Instance can be publisher/distributor of the changes made in some database table placed on the instance, and other Managed instances, Single databases, and SQL Server databases can be subscribers.

Setup

First, you need to have source and target database (in this case I’m using WideWorldImporters database on both sides). In addition, you need a table in the source database that will contain the data that should be published on the remote database. I’m using a simple table shown in the following sample:

drop table if exists dbo.data;
go
create table dbo.data (
id int primary key identity,
value nvarchar(max)
)
insert into data(value) values ('First');
insert into data(value) values ('Second');
insert into data(value) values ('Third');

The only prerequisite is that the source table must have a primary key column because Transactional Replication uses this column as an identifier of the changes. Do not add the same table on the target instance because it will be created when Transactional Replication starts and initiates the snapshot.

Initializing Replication

As a first step you need to initialize your source Managed Instance and configure all components necessary for replication:

  • A database where Managed Instance will use to distribute the changes. This database is usually called ‘distributor’.
  • Azure File Storage account that is used for replication.

The following script configures Transactional Replication on your Managed Instance (I’m using SQLCmd mode to run the script because it enables me to define parameter separately):

:setvar username loginUsedToAccessSourceManagedInstance
:setvar password passwordUsedToAccessSourceManagedInstance
:setvar file_storage "\\*****.file.core.windows.net\*****"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=*****;AccountKey=**;EndpointSuffix=core.windows.net"
USE [master]​
GO
EXEC sp_adddistributor @distributor = @@ServerName​;
EXEC sp_adddistributiondb @database = N'distribution'​;
GO
USE [master]​
EXEC sp_adddistpublisher
@publisher = @@ServerName,
@distribution_db = N'distribution',​
@security_mode = 0,
@login = N'$(username)',
@password = N'$(password)',
@working_directory = N'$(file_storage)',
@storage_connection_string = N'$(file_storage_key)';

This script will configure a local publisher on the Managed Instance. It will add a linked server and a set of jobs, as it is shown on the following figure:

Objects added by the script

Make sure that you don’t execute this script twice (skip it if you have already configured Transactional Replication on your instance).

Publishing changes

Once you have configured the Managed Instance, you can configure publication that will publish changes made in dbo.data table in the database WideWorldImporters and that will distribute the changes to target Managed Instance:

:setvar username sourceLogin
:setvar password sourcePassword
:setvar source_db WideWorldImporters
:setvar publication_name PublishData
:setvar object Data
:setvar schema dbo
:setvar target_server "***.wcus17662feb9ce98.database.windows.net"
:setvar target_username targetLogin
:setvar target_password targetPassword
:setvar target_db WideWorldImporters

USE [$(source_db)]​
EXEC sp_replicationdboption
@dbname = N'$(source_db)',
@optname = N'publish',
@value = N'true'​;
EXEC sp_addpublication
@publication = N'$(publication_name)',
@status = N'active';​
EXEC sp_changelogreader_agent
@publisher_security_mode = 0,
@publisher_login = N'$(username)',
@publisher_password = N'$(password)',
@job_login = N'$(username)',
@job_password = N'$(password)';
EXEC sp_addpublication_snapshot
@publication = N'$(publication_name)',
@frequency_type = 1,​
@publisher_security_mode = 0,​
@publisher_login = N'$(username)',
@publisher_password = N'$(password)',
@job_login = N'$(username)',
@job_password = N'$(password)';
EXEC sp_addarticle 
@publication = N'$(publication_name)',
@type = N'logbased',
@article = N'$(object)',
@source_object = N'$(object)',
@source_owner = N'$(schema)'​;
EXEC sp_addsubscription
@publication = N'$(publication_name)',
@subscriber = N'$(target_server)',
@destination_db = N'$(target_db)',
@subscription_type = N'Push'​;
EXEC sp_addpushsubscription_agent
@publication = N'$(publication_name)',
@subscriber = N'$(target_server)',​
@subscriber_db = N'$(target_db)',
@subscriber_security_mode = 0,
@subscriber_login = N'$(target_username)',
@subscriber_password = N'$(target_password)',
@job_login = N'$(target_username)',
@job_password = N'$(target_password)';
EXEC sp_startpublication_snapshot
@publication = N'$(publication_name)';

This script will create necessary publication, configure SQL Agent that will read the changes in the log file and publish them to target instance. Publication that will be created should look like the one shown on the following picture:

Testing replication

Once you configured this you can test the replication. First wait some time and read data from the WideWorldImporters table:

select * from dbo.Data

Then you can insert data into the table in source database:

insert into data(value) values ('New');

Inserted data should appear after some time in destination table.

Conclusion

Transaction replication can be good solution that enables you to keep in sync tables on Managed Instance and remote Managed Instance, Azure SQL Single database or SQL Server database. This article can help you to configure Transactional Replication using Transact-SQL scripts.