question

MarkHansen-9510 avatar image
0 Votes"
MarkHansen-9510 asked NWiddup-MSFT commented

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

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.

fasttrack-azure
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NWiddup-MSFT avatar image
0 Votes"
NWiddup-MSFT answered NWiddup-MSFT commented

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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

  1. Yes they are in separate regions the primary is AU-South-East (close to the majority of our users) and secondary in AU-East. We run all reporting from the secondary. We update the data in our reporting database overnight on the primary server. As the reporting database updates are run they replicate to secondary to serve our reporting users.

  2. The business driver to move the reporting database to a different replica is to enable us to run a reporting database update during the day without impacting transactional system performance. To add to the complexity our transactional systems have many references to the reporting database. This is why we have not decoupled the two. Instead, we are working to move reporting to a new stack. The current reporting database is in run-off but will likely be in run-off for 12-18 months.

We investigated using linked server, but have been limited by hard-coded database references in our transactional, and reporting systems. There may have been something we missed. So thanks for the article. I will read it to see if we can implement a linked server solution. As you mention we will need to update our stored procedures - there are many of these - and feel that their number will still present a blocker.

0 Votes 0 ·

Hi Mark, please see my comment below for response. Unfortunately in-line replies are limited to 1600 characters and the response wouldn't fit on this thread.

0 Votes 0 ·
NWiddup-MSFT avatar image
0 Votes"
NWiddup-MSFT answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.