Adding Managed Instances to SQL Server Distributed Availability Groups

SQL Server Always On Availability Groups help you build a more highly available database server by spanning your database across two or more SQL Server instances. When the primary goes down, the secondary can take over. You can also scale out reads to the secondary servers.

Distributed Availability Groups take this a step further and let you build an Availability Group of Availability Groups, hahaha, spanning a database across different clusters. In theory, this makes version upgrades possible with lower downtime: the individual AGs can be different SQL Server versions, and you can do rolling version upgrades. (That’s the theory: in practice, it’s a hell of a lot of work.)

That’s where Azure SQL Managed Instances come in. They’re basically an Availability Group that’s hosted and managed by Microsoft up in Azure, but the next version of SQL Server rather than the one you’re currently running on premises. In theory, you could have an on-premises Availability Group running SQL Server 2019, and run a Distributed Availability Group up to Azure SQL Managed Instances.

In practice, this isn’t supported today.

However, SQL Server 2019 Cumulative Update 11 added a few new rows in sys.messages that indicate Microsoft’s working on this, though:

  • 47500: Manual seeding is not supported option for secondary AG ‘%.*ls’ configuration when secondary participant in distributed availability group is Azure SQL Managed Instance.
  • 47501: Synchronous commit is not supported option for the initial secondary AG ‘%.*ls’ configuration when secondary participant in distributed availability group is Azure SQL Managed Instance.
  • 47502: Cannot create distributed availability group ‘%.*ls’ when local AG ‘%.*ls’ contains more than one database in cases when secondary participant is Azure SQL Managed Instance.
  • 47503: AG ‘%.*ls’ already contains one database and adding more is not supported because AG participates in distributed availability group ‘%.*ls’ with secondary on Azure SQL Managed Instance.
  • 47504: Error related to distributed availability group ‘%.*ls’ with secondary participant on Azure SQL Managed Instance.

If this feature is ever released publicly – sometimes Microsoft builds things but doesn’t actually release them publicly, like snapshot materialized views – then these limitations point to the use case scenario for the feature. The above-listed limitations make the feature only useful for one thing: one-time, one-way, single-database migrations from conventional SQL Server to Azure SQL Managed Instances. You wouldn’t really wanna have an Availability Group where you couldn’t have synchronous commits or multiple databases.

Previous Post
Learn Fundamentals of Index Tuning for $1.
Next Post
Free Video: 500-Level Guide to Career Internals: Building a Brand

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu