How to configure transactional replication between Azure SQL Managed Instance and an on-premise SQL Server

What is Azure SQL Managed instance?

SQL Managed Instance is a cloud database service that preserves all PaaS capabilities (automatic patching and version updates, automated backupshigh availability) that reduce management overhead and 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 can not use Windows authentication, as a cloud alternative SQL Managed Instance supports Azure AD authentication.
  • SQL MI 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. 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 transactional replication between Azure SQL Managed Instance and an on-premise 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).

Figure 1- Publisher with local distributor on SQL Managed Instance (common configuration).

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.
Figure 2 – Portal Azure – Create new resource group
  1. Select Create and fill out the Subscription, Resource group, and Region.
Figure 3 – Fill out Subscription, Resource group, and Region.
  1. Select Review + Create. It takes a few seconds to create a resource group.

2. Create a managed instance

  1. Select the Resource group you just created, and click on “Add”.
  2. Type and search for “Azure SQL Management Instance”.
Figure 4 – Add “Azure SQL Managed Instance”.
  1. Fill out mandatory information required on the Basics tab. This is a minimum set of information required to provision a managed instance.
Figure 5 – Create “Azure Database Managed Instance – Networking”.
  1. Fill out optional information on the Networking tab. For a managed instance to be accessible through the public data endpoint, 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.
Figure 6 – Fill out Networking tab optional information.
  1. Fill out optional information on the Additional settings tab. Select the “Review + create” tab to review your choices before you create a managed instance*.
Figure 7 – Create “Azure SQL Managed Instance – Fill out additional settings”.

*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.
Figure 8 – Create a “Storage account”.
  1. Fill out optional information on the Networking tab.
  2. Make sure to selectthe “Public endpoint (all networks)”.
Figure 9 – Storage Account Creation and Networking details.
  1. Create a file share within the storage account.
Figure 10 – Create file share within the Azure storage account.
  1. Copy the file share path and storage access keys.
Figure 11 – Copy URL and 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=<Storage-Account-Name>;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 rules to open the ports 1433 and 3342 as shown in the image below.
Figure 12 – Configure the “Inbound security rules” for Network Security Group.
  1. Make sure that there are Outbound rules for the ports 445 and 1433.
  2. If you don’t have, create them.
  3. Allow the port 445 in the subnet security rules to access the Azure file share.
Figure 13 – Configure the outbound rules.

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 Transact-SQL code to configure distribution on the distributor managed instance:
  1. USE [master]
  2. GO
  3. EXEC sp_adddistributor @distributor = @@ServerName;
  4. EXEC sp_adddistributiondb @database = N'distribution';
  5. GO

6. Configure the publisher to use the distributor

  1. Change the query execution to SQLCMD mode.
Figure 14 – Choose the SQLCMD mode.
  1. Run the following Transact-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.
  1. :setvar username loginUsedToAccessSourceManagedInstance --Replace with your Credencials.
  2. :setvar password passwordUsedToAccessSourceManagedInstance --Replace with your Credencials.
  3. :setvar file_storage "\\azuresyncstoragetest.file.core.windows.net\replshare"
  4. -- Replace with your file share path.
  5. :setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=;AccountKey=****;EndpointSuffix=core.windows.net"
  6. -- example: file_storage_key ."DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net"
  7.  
  8. USE [master]
  9. EXEC sp_adddistpublisher
  10.   @publisher = @@ServerName,
  11.   @distribution_db = N'distribution',
  12.   @security_mode = 0,
  13.   @login = N'$(username)',
  14.   @password = N'$(password)',
  15.   @working_directory = N'$(file_storage)',
  16.   @storage_connection_string = N'$(file_storage_key)';

7. Create Publication 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

  1. -- Set variables
  2. :setvar username sourceLogin
  3. :setvar password sourcePassword
  4. :setvar source_db Aw -- Replace with source database name
  5. :setvar publication_name Test -- Replace with name of the publication to create. The name must be unique within the database.
  6. :setvar object Clients -- Replace with database object to be published (in this case the table name) 
  7. :setvar schema dbo -- Replace with source table schema
  8. :setvar target_server "73.100.00.00" -- Replace with target server IP Address (on-premise)
  9. :setvar target_username targetLogin -- Replace with target Server credential
  10. :setvar target_password targetPassword -- Replace with target Server credential
  11. :setvar target_db Aw_rep -- Replace with target Replication subscription database name 
  12.  
  13. -- Enable replication for your source database
  14. USE [$(source_db)]
  15. EXEC sp_replicationdboption
  16.   @dbname = N'$(source_db)',
  17.   @optname = N'publish',
  18.   @value = N'true';
  19.  
  20. -- Create your publication
  21. EXEC sp_addpublication
  22.   @publication = N'$(publication_name)',
  23.   @status = N'active';
  24.  
  25. -- Configure your log reader agent
  26. EXEC sp_changelogreader_agent
  27.   @publisher_security_mode = 0,
  28.   @publisher_login = N'$(username)',
  29.   @publisher_password = N'$(password)',
  30.   @job_login = N'$(username)',
  31.   @job_password = N'$(password)';
  32.  
  33. -- Add the publication snapshot
  34. EXEC sp_addpublication_snapshot
  35.   @publication = N'$(publication_name)',
  36.   @frequency_type = 1,
  37.   @publisher_security_mode = 0,
  38.   @publisher_login = N'$(username)',
  39.   @publisher_password = N'$(password)',
  40.   @job_login = N'$(username)',
  41.   @job_password = N'$(password)';
  42.  
  43. -- Add the ReplTest table to the publication
  44. EXEC sp_addarticle
  45.   @publication = N'$(publication_name)',
  46.   @type = N'logbased',
  47.   @article = N'$(object)',
  48.   @source_object = N'$(object)',
  49.   @source_owner = N'$(schema)';
  50.  
  51. -- Add the subscriber
  52. EXEC sp_addsubscription
  53.   @publication = N'$(publication_name)',
  54.   @subscriber = N'$(target_server)',
  55.   @destination_db = N'$(target_db)',
  56.   @subscription_type = N'Push';
  57.  
  58. -- Create the push subscription agent
  59. EXEC sp_addpushsubscription_agent
  60.   @publication = N'$(publication_name)',
  61.   @subscriber = N'$(target_server)',
  62.   @subscriber_db = N'$(target_db)',
  63.   @subscriber_security_mode = 0,
  64.   @subscriber_login = N'$(target_username)',
  65.   @subscriber_password = N'$(target_password)',
  66.   @job_login = N'$(username)',
  67.   @job_password = N'$(password)';
  68.  
  69. -- Initialize the snapshot
  70. EXEC sp_startpublication_snapshot
  71.   @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:

  1.   -- Increase login timeout to 150s
  2. update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
  3. where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Restart all three agents 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.
Figure 15 – Replication and “View Synchronization Status”.
  1. Right-click on the Subscriber and select “View Synchronization Status”:
Figure 16 – This shows Subscriber Synchronization Status.

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

In case you don’t have time for this… Let our expert SQL Server Migration team handle the technical complexities, so you can focus on driving your business forward with confidence.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

One Response

  1. Hi Mark,

    Thank you for the post. It really helps. I have doubt.

    Under the “– Create the push subscription agent”, you are using Windows Authentication of On-Premise environment. Is it Domain Account or Local Account. Can we use SQL Authentication instead of windows Authentication. If planning to use SQL authentication, any additional steps need to be performed.

    Thanks,
    Rajes

Leave a Reply

Your email address will not be published. Required fields are marked *