Whether AAG can use managed instance as the replication distributor?

Question

I know, it's recommended to use one isolate instance as the distributor with the on premise AAG. And from the Books Online, I know it supports to use MI as the distributor.

I want to know whether below approaches are workable?

On premise AAG as Replication Publisher, Azure SQL Managed Instance as Distributor, SQL Azure Database as Subscriber.

On premise AAG as Replication Publisher, Azure SQL Managed Instance as Distributor, AAG(IaaS+SQL Server) as Subscriber.

Thanks a lot

Answer 1

Hi WisonHii •,

Thanks for posting question on Microsoft Q&A forum.

As per the question, you want to know whether Always On Availability Groups (on-premise) can use Azure SQL Managed Instance as Replication Distributor and about the two approaches.

Could you please let us know the SQL Server version in use On-premises?

Please let us know the configuration details of the same.

Regarding transactional replication, 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 a 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 is not supported when the distributor is an Azure SQL Managed Instance and the subscriber is not. 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

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)

Note

• For other versions of SQL Server that do not support publishing to objects in Azure, it is possible to utilize the republishing data method to move data to newer versions of SQL Server.
• Attempting to configure replication using an older version can result in error number MSSQL_REPL20084 (The process could not connect to Subscriber.) and MSSQ_REPL40532 (Cannot open server <name> requested by the login. The login failed.)

Common Configurations link is here.

Please let us know if I am missing something in question to comment on. Anticipating your reply, thanks

Answer 2

Hi WisonHii •,

Thanks for your patience.

I have got the reply from the internal team which is as below:

When it comes to Transactional Replication and Azure SQL Managed Instance, there are two distinct topologies that are supported:

1. Published and distributor configured on one or two separate Azure SQL Managed Instances + Subscriber on Azure SQL Managed Instance or Azure SQL Database or SQL Server hosted anywhere, including on-prem or SQL Server on Azure VMs
2. Publisher and distributor configured on one or two separate SQL Servers hosted anywhere, including on-prem or SQL Server on Azure VMs + Subscriber on Azure SQL Managed Instance.

 

So, configurations that you’ve asked about with Pub on SQL Server and Distributor on Azure SQL Managed Instance are NOT supported.

Team is interested to understand your intention/reason for having only Distributor on Azure SQL Managed Instance?

Could you please let us know so that we can further assist you.

Thanks.