[Step-by-Step Guide] Configure Azure Managed Instance Replication to On-Premises SQL Server

Did you know that over 92% of Fortune 500 companies rely on Microsoft Azure for their cloud infrastructure?

It’s no surprise – businesses need scalable, secure, and high-performance solutions to manage their data across on-prem and cloud environments.

Enter Azure SQL Managed Instance – a bridge between your on-prem SQL Server and the cloud. It offers smooth hybrid connectivity, built-in high availability, automated backups, and near full SQL Server compatibility.

Native backup and restore using physical file paths is not supported. Use automated backups or Azure Blob Storage integration for backup and restore operations.

Quite simply, it is a game-changer for enterprises modernizing their data strategy.

When you’re facing fragmented systems, slow data replication, or security worries, this guide lays out exactly how to configure Azure SQL Managed Instance replication – step by step.

What is Azure SQL Managed Instance?

SQL Managed Instance is a fully managed cloud database service that preserves all PaaS (Platform as a Service) capabilities while maintaining near 100% compatibility with SQL Server

SQL Managed Instance provides:

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

The features above reduce 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 cloud’s benefits of scalability, flexibility, and cost efficiency have become impossible to ignore.

Azure SQL Database Managed Instance offers the easiest way to move your SQL Server on-premises database engine to the cloud.

With nearly 100% compatibility with the latest SQL Server Enterprise Edition, it preserves your existing investments in skills, tools, and applications.

You can lift and shift on-premises SQL Servers and applications to Azure with minimal changes, avoiding the costly rewrites often required by other cloud platforms.

Key Features & Differences: Azure SQL Managed Instance vs. On-Prem SQL Server

Although Azure SQL Managed Instance supports many SQL Server features, there are significant differences in comparison to on-premises SQL Server.

1. SQL Managed Instance supports only automated backups and point-in-time restore.
2. Specifying full physical paths is unsupported, so all corresponding scenarios must 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.
3. You cannot use Windows Authentication; as a cloud alternative, SQL Managed Instance supports Azure AD authentication.
4. SQL Managed Instance automatically manages XTP filegroups and files for databases containing In-Memory OLTP objects.
5. 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). Therefore, 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.
• 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. To access a managed instance through the public endpoint (data), make sure to enable this option.
   • ○ This setup is intended for testing; Microsoft advises against using it in 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.
   
   The Azure File Share used for the replication working directory must be accessible from both the Azure SQL Managed Instance subnet and any on-premises servers involved in replication. Ensure that firewall rules and network security groups allow SMB (port 445) traffic as required.

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

Note: Ensure that the SQL Server Agent on Azure SQL Managed Instance has the necessary permissions to access the Azure File Share and that the credentials used for replication have appropriate rights on both source and target databases.

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!

Frequently Asked Questions