How to Set Up a One-Way Real-Time Replicated SQL MI Instance (Read/Write for Custom Tables Only) in a Different Subscription?

Question

How to Set Up a One-Way Real-Time Replicated SQL MI Instance (Read/Write for Custom Tables Only) in a Different Subscription?

Niket Kumar Singh 740

Hello Team,

We are currently running an Azure SQL Managed Instance (MI) in the South India region, hosting a production database db1. This instance (sql-mi-1) already participates in a failover group with a secondary MI (sql-mi-1-dr) in Central India for high availability.

Our business requirement is to add a third Azure SQL Managed Instance in a different subscription (but same tenant), which should:

Have real-time replication of the db1 database from the primary MI.
Be able to read from the replicated data for an external application. (Supports read and write operations on this third instance)
Support custom writes (only to non-replicated, application-specific tables).
Not replicate any of its writes back to the primary instance (i.e., one-way sync only).
Remain loosely coupled, acting like a near-real-time reporting or read+custom-write node.

We understand from documentation and guidance that:

SQL MI supports only one auto-failover group per instance.
Bi-directional writes and multi-primary-like topology are not supported.
However, Azure Database Migration Service (DMS) with CDC-based continuous sync supports online replication from one SQL MI to another, which may suit our case.

Our questions:

Can we use Azure DMS (latest version) to set up a one-way real-time replication from sql-mi-1 to a third MI in a different subscription?

Will the replicated data remain readable without breaking the sync if we add write-only custom tables to the target?

Are there any limitations in syncing a large production database (approx. 11 TB) continuously using Azure DMS?

Is this design Microsoft-recommended for scenarios where you need "read from PROD + write to new tables" in a different subscription?

Please let me know if there are alternative patterns we should consider or if this approach is supported fully with documentation reference.

Thanks in advance for your help!

Accepted answer

0 additional answers

Your answer

Answer 1

hi Niket Kumar Singh,

thanks for posting this detailed question, it's a pretty interesting setup u're trying to achieve ))

for ur Microsoft solution, yes u can totally use azure database migration service (dms) for this one way replication! the latest version supports continuous sync from sql mi to another sql mi, even across subscriptions (same tenant tho). here's the official doc that confirms it works https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online. the cool part? ur custom tables on the target won't mess with replication since dms only syncs what u configure.

about ur 11 TB database - aha, that's chunky but dms can handle it. just make sure to pick premium tier for dms and monitor throughput. do a test run first with a smaller subset to catch any weirdness early.

now for stuff that works beyond just microsoft... u might wanna check if logical replication could work here too. some cross platform tools like debezium or even sql server's own change data capture (cdc) with custom etl might give u more control. worth looking into if u need to tweak the sync logic later.

also, this might help in other tools too - consider breaking ur big sync into smaller batches if u hit performance walls. sometimes doing it in chunks is smoother than one giant push.

while this setup is microsoft approved, remember that writes on ur third instance won't failover automatically. so if ur primary goes boom, u'll need manual steps to repoint stuff. not ideal but that's the tradeoff for custom writes ))

let me know if u hit any snags! microsoft's got ur back on this one, their docs are surprisingly clear for once https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-between-two-instances-configure-tutorial happy replicating! :)

Best regards,

Alex

and "yes" if you would follow me at Q&A - personaly thx.
P.S. If my answer help to you, please Accept my answer
PPS That is my Answer and not a Comment

https://ctrlaltdel.blog/

Share via

How to Set Up a One-Way Real-Time Replicated SQL MI Instance (Read/Write for Custom Tables Only) in a Different Subscription?

0 additional answers

Your answer