0

I have two SQL Managed Instances 1 - Primary Production 2 - Reporting / Analytic workloads

Besides traditional SQL replication (transactional) what is the best way to get data in near real-time from the primary to the secondary server. The secondary is not used in HA or DR at all and only for reporting workloads that we don't want to run on the primary managed instance.

My problem with transactional replication is certain tables in the primary instance are truncated which complicates replication. I also don't want to have to drop publications and generate snapshots, impacting the production applications.

Log shipping would be an option but it disables the database on the secondary while restoring and isn't nearly real-time.   Really what I'm looking for is something like AG, which doesn't appear to be available in same region servers, unless I'm wrong.

Researching Managed Instance Link seems perfect, except I'm not seeing that the source can be a Managed Instance, unless I'm wrong.

The target database on the secondary can be read-only if needed.

1 Answer 1

0

The analog of Managed Instance Link for MI-to-MI replication is an Auto Failover Group:

enter image description here

2
  • From everything I'm reading (including the link) this is for across regions, in my case both SQL Managed Instances are in the same region. The auto-failover groups feature allows you to manage the replication and failover of all user databases in a managed instance to another Azure region
    – Cody
    Nov 9, 2023 at 21:25
  • Yep, that’s a limitation. Nov 9, 2023 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.