Azure SQL Always on Availability groups replicate one database from secondary to primary

Question

Are we able to use Azure SQL Always on Availability groups to manage the replication between two Azure SQL Managed Instances (one and two) so that one of our databases is "primary" or read/writable on Azure SQL Managed Instance two with a read-only replica on Azure SQL Managed Instance one? With all other databases as "primary" or read/writable on Azure SQL Managed Instance one with their read-only replica on Azure SQL Managed Instance two.

We have a legacy setup with a reporting database on a transactional server. This transactional server is hosted as an Azure SQL Managed Instance replicated to a secondary instance. There are stored procedure ties between our transactional databases and reporting database which makes moving the reporting database to another server difficult. We'd like to use the two instances we have to move the processing load of the reporting database but still leave a read-only replica of the reporting database on the primary transactional instance.

Answer 1

Hi Mark, Thanks for the great question! I have recently been discussing a similar solution with some of my colleagues, except using Azure SQL DB instead of SQL MI. I have a couple of clarifying questions:

1. Are the two SQL Managed Instances in separate regions?
2. What is the business driver for keeping the primary Reporting DB on a different replica to the primary replica for the OLTP databases? e.g. Cost, Query latency/performance, wanting to take advantage of underutilised resources, etc

There's a great article written by one of the SQL PG PM's about Creating a Linked Server to a SQL Managed Instance replica when using the Business Critical tier. You could use this linked server solution to keep all your databases on a single Primary Replica while offloading the read-heavy reporting workload/queries to a secondary replica. For the link between your databases with Stored Procedures, you would need to update them to use the four part name ([LinkedServerName].[DatabaseName].[SchemaName].[TableName]), and there are potential performance impacts you need to be aware of. However we do have some guidance on addressing these.

Would this approach potentially work for your situation?

Answer 2

Hi Mark, Thanks for the updates. As discussed during the FastTrack QnA session, an SQL Managed instance can only have 1 Auto-Failover group per instance, meaning you will not be able to replicate the AlwaysOn topology you created on-prem in Azure using SQL MI.

When considering Auto-failover groups, it is probably best to think of them as a similar technology to Distributed Availability groups, providing cross-region data replication. Within a region, we use a technology similar to Availability Groups to replicate data between replicas. During your 12-18 month run-off period, hopefully the linked server solution can provide a way for you to update your reporting database through the day while alleviating some of the blocking/locking issues which can occur when reloading the reporting database from your primary OLTP databases by using the secondary replica.

Regarding the updates to Stored Procedures, unfortunately there is no super easy way to make these changes. If your database is stored in source control, you may be able to perform the updates and then test the changes with a representative production workload in a test environment. Otherwise if your DB is not in source control, you may be able to use sys.sql_modules to assist with discovery of procedures using these cross-database queries in your reporting DB.