#StackBounty: #sql-server #sql #replication #azure-sql-database Replicating on-premise SQL Server instance with hundreds of databases t…

Bounty: 50

I am wanting to replicate an entire SQL Server 2016 on-premise instance of databases (300+) to an Azure SQL Managed Instance for redundancy of a read-replica in case of on-premise downtime. It appears as though I can use Azure Database Migration Service in order to deploy multiple databases, but this seems to imply that this is a one-time migration.

This will not be a one-time migration, and I am wanting to perform transaction replication with a Publish (on-premise SQL) and Subscriber (Azure SQL). Microsoft has outlined this method, but Publications only allow for a single database to be selected. I would like to explore publishing the full instance of databases, and shipping the logs at a particular interval.

Each of these databases share the same schema, so merging the data into a single database poses problems surrounding application configurations in the event we need to fail-over for read purposes. This is also a temporary solution until we are able to replicate our VMware cluster for Disaster Recovery, and our transactional throughput is on the lower side and is constrained to set working hours.

Is my only option to create publications for each and every database, or can the Azure Data Migration Service be extended for replicating at regular intervals?

Edit: Another option I’ve been toying with is creating a single database with each individual tenant as a security schema and file group, and developing an ETL pipeline to pass the tenants to these matching security schemas. In essence I would mimic the database-per-tenant through tenant-per-schema, allowing me to only need to push a single database to the Azure SQL. This might cause more headaches than it would solve, though. Does anyone have experience with this at scale?


Get this bounty!!!

Leave a Reply