[Step-by-Step Guide] Configure Azure Managed Instance SQL Replication to on-premises SQL Server

What is Azure SQL Managed Instance?

SQL Managed Instance is a cloud database service that preserves all PaaS capabilities.

SQL Managed Instance provides:

• Automatic patching
• Version updates
• Automated backups
• High availability

Having the features above reduces SQL management overhead and Total Cost of Ownership (TCO).

It provides a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for existing SQL Server customers.

Why choose Azure SQL Managed Instance?

The benefits of the cloud have become impossible to ignore.

Azure SQL Database Managed Instance is the easiest way to move your SQL Server on-premises database engine to the cloud (has near 100% compatibility with the latest SQL Server (Enterprise edition).

You can lift and shift on-premises SQL Servers and applications to Azure with minimal changes.

Key Features of Azure SQL Database Managed Instance:

Some key differences between SQL Server on-premises and SQL Managed Instance:

• SQL Managed Instance supports only automated backups and point-in-time restore.
• Specifying full physical paths is unsupported, so all corresponding scenarios have to be supported differently: RESTORE DB does not support WITH MOVE, CREATE DB doesn’t allow physical paths, BULK INSERT works with Azure blobs only, etc.
• You cannot use Windows Authentication; as a cloud alternative, SQL Managed Instance supports Azure AD authentication.
• SQL Managed Instance automatically manages XTP filegroups and files for databases containing In-Memory OLTP objects.
• SQL Managed Instance supports SQL Server Integration Services (SSIS) and can host an SSIS catalog (SSISDB) that stores SSIS packages, but they are executed on a managed Azure-SSIS integration runtime (IR) in Azure Data Factory (ADF).
  So, there are limitations like:
  • We administer SSIS via SSMS, while Azure Data Factory is administered via the Azure portal;
  • SSIS has a programming SDK and third-party components. Azure Data Factory has only automation via PowerShell.
  • Azure data factory uses JSON scripts for coding, while SSIS uses drag-and-drop tasks (no coding).

For more details, see the Azure SQL Managed Instance resource limits.

How to configure SQL transactional replication between Azure SQL Managed Instance & on-premises SQL Server

The SQL Server transactional replication feature allows you to replicate data from one database to another hosted on Azure SQL Managed Instance, Azure SQL Database, or on-premises SQL Server instance.

There are different ways to sync SQL Server data from Azure to on-premise SQL Server.

In this guide, you will learn how to configure a Managed Instance as a replication Publisher and Distributor and a SQL Server instance (on-premises) as a Subscriber (push mode).

Requirements

• An Azure subscription.
• SQL Server Management Studio (SSMS) 18.0 or greater.
• Use SQL Authentication for connectivity between replication participants.
• Use an Azure Storage Account share for the working directory used by replication.
• SQL Server Management Studio (SSMS) 18.0 or greater.
• Ports 445 and 1433 allow SQL traffic on both the Azure Firewall and the Windows Firewall.

1. Create a resource group

1. Sign in to the Azure portal.
2. Select Resource groups.

3. Select Create and fill out the Subscription, Resource group, and Region.

4. Select Review + Create. It takes a few seconds to create a resource group.

2. Create a SQL Managed Instance

1. Select the Resource group you just created, and click on Add.
2. Type and search for “Azure SQL Managed Instance”.

3. Fill out the mandatory information required on the Basics tab. This is the minimum set of information required to provision a managed instance.

4. Fill out optional information on the Networking tab. For a managed instance to be accessible through the Public endpoint (data), you need to enable this option. Also, select allow access from the Internet option.
   Used for test purposes, Microsoft does not recommend it for production environments.

5. Fill out optional information on the Additional settings tab. Select the Review + create tab to review your choices before you create a managed instance*.

*Deploying a managed instance is a long-running operation. On average it takes 4 hours, depending on several factors. Deployment of the first instance in the subnet typically takes much longer than deploying into a subnet with already existing managed instances.

3. Create an Azure storage account

1. Select the Resource group created, and click on Add. Type and search for “Storage account”. Fill out optional information on the Basics tab.

2. Fill out optional information on the Networking tab.
3. Make sure to select the Public endpoint (all networks).

4. Create a file share within the storage account.

5. Copy the file share path and storage access keys.

• Copy the file share path in the format of:
  \\storage-account-name.file.core.windows.net\file-share-name
  Example: \\azuresyncstoragetest.file.core.windows.net\replshare
• Copy the storage access keys in the format of: DefaultEndpointsProtocol=https;AccountName=;AccountKey=****;EndpointSuffix=core.windows.net
  Example: DefaultEndpointsProtocol=https;AccountName= azuresyncstoragetest;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net

4. Configure network security group

1. In the Resource Group, select the Network security group.
2. If you do not have it, create Inbound security rules to open the ports 1433 and 3342 as shown in the image below.

3. Make sure that there are Outbound rules for ports 445 and 1433.
4. If you don’t have them, create them.
5. Allow port 445 in the subnet security rules to access the Azure file share.

5. Configure distribution

Once connectivity is established and you have a sample database, you can configure distribution on your managed instance. To do so, follow these steps:

1. Launch SQL Server Management Studio on SQL Server.
2. Connect to the managed instance.
3. Open a New Query window and run the following T-SQL code to configure distribution on the distributor-managed instance:

USE [master] GO

EXEC sp_adddistributor @distributor = @@ServerName;

EXEC sp_adddistributiondb @database = N'distribution';GO

6. Configure the Publisher to use the Distributor

1. Change the query execution to SQLCMD Mode.

2. Run the following T-SQL code to register the new distributor with your Publisher.
   This script configures a local Publisher on the managed instance, adds a linked server, and creates a set of jobs for the SQL Server Agent.

:setvar username loginUsedToAccessSourceManagedInstance --Replace with your Credencials.
	:setvar password passwordUsedToAccessSourceManagedInstance --Replace with your Credencials.
	:setvar file_storage "\\azuresyncstoragetest.file.core.windows.net\replshare"
	-- Replace with your file share path.
	:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=;AccountKey=****;EndpointSuffix=core.windows.net"

-- example: file_storage_key ."DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net"
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)';

7. Create Publisher and Subscriber

Using SQLCMD mode, run the following T-SQL script to enable replication for your database, and configure replication between your Publisher, Distributor, and Subscriber.

8. Modify agent parameters

-- Set variables
:setvar username sourceLogin :setvar password sourcePassword :setvar source_db Aw -- Replace with source database name
	:setvar publication_name Test -- Replace with name of the publication to create. The name must be unique within the database.
	:setvar OBJECT Clients -- Replace with database object to be published (in this case the table name) 
	:setvar SCHEMA dbo -- Replace with source table schema
	:setvar target_server "73.100.00.00" -- Replace with target server IP Address (on-premise)
	:setvar target_username targetLogin -- Replace with target Server credential
	:setvar target_password targetPassword -- Replace with target Server credential
	:setvar target_db Aw_rep -- Replace with target Replication subscription database name 

-- Enable replication for your source database
USE [$(source_db)]

EXEC sp_replicationdboption @dbname = N'$(source_db)'
	,@optname = N'publish'
	,@value = N'true';

-- Create your publication
EXEC sp_addpublication @publication = N'$(publication_name)'
	,@status = N'active';

-- Configure your log reader agent
EXEC sp_changelogreader_agent @publisher_security_mode = 0
	,@publisher_login = N'$(username)'
	,@publisher_password = N'$(password)'
	,@job_login = N'$(username)'
	,@job_password = N'$(password)';

-- Add the publication snapshot
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)';

-- Add the ReplTest table to the publication
EXEC sp_addarticle @publication = N'$(publication_name)'
	,@type = N'logbased'
	,@article = N'$(object)'
	,@source_object = N'$(object)'
	,@source_owner = N'$(schema)';

-- Add the subscriber
EXEC sp_addsubscription @publication = N'$(publication_name)'
	,@subscriber = N'$(target_server)'
	,@destination_db = N'$(target_db)'
	,@subscription_type = N'Push';

-- Create the push subscription agent
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'$(username)'
	,@job_password = N'$(password)';

-- Initialize the snapshot
EXEC sp_startpublication_snapshot @publication = N'$(publication_name)';

Azure SQL Managed Instance is currently experiencing some backend issues with connectivity with the replication agents.

While this issue is being addressed, the workaround increases the login timeout value for the replication agents.

Run the following T-SQL command on the Publisher to increase the login timeout:

-- Increase login timeout to 150s
UPDATE msdb..sysjobsteps
SET command = command + N' -LoginTimeout 150'
WHERE subsystem IN (
		'Distribution'
		,'LogReader'
		,'Snapshot'
		)
	AND command NOT LIKE '%-LoginTimeout %'

Restart all three Agent Jobs to apply these changes.

9. Test the replication

Once replication has been configured, you can test it by inserting new items on the Publisher and watching the changes propagate to the Subscriber.

Also, you can check the synchronization status:

1. In the Object Explorer, expand Replication > Local Publications, and click on your publication.

2. Right-click on the Subscriber and select View Synchronization Status:

This is how you configure transactional replication between Azure SQL Managed Instance and an on-premise SQL Server!