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?

  • Do you actually need transactional replication? If you're using it purely as a read-only source during production downtime, then replication is overkill and will cause you far more admin headaches than it's worth (especially with this volume of databases). I'd suggest considering log shipping instead. However with 300 DBs, you will need to be very careful in how you configure your LS backup schedules. Staggering them so they aren't all simultaneously executing - or perhaps even rolling your own LSCopy job and just relying on a scheduled log backup job that hits all DBs. – Dan Mar 15 at 0:41
  • @Dan It definitely doesn't need to be transactional replication, but of the avenues that Microsoft lists for pushing to Azure SQL, it's the primary method listed. Is there a method via log shipping that might accomplish this at scale? – PicoDeGallo Mar 15 at 1:47
  • I'm going to suggest maybe taking a step back and considering whether a Managed Instance is the best solution to meet your goals. If your primary goals are to 1. Add HA to your environment and 2. Have your HA be resilient to primary site failures, then Manged Instances aren't the way to go. Instead I would suggest Azure's (or AWS) IaaS offerings instead. Then, instead of trying to wrangle replication or log shipping to provide HA (neither of which are true HA), you can build an availability group that spans your on prem servers and cloud servers. – Dan Mar 15 at 3:08
  • @Dan The appeal towards a Managed Instance is primarily the cost being significantly lower and dependent on occupied space. Our databases, while large in volume, are rather small in physical footprint (some being only a few hundred MB). Another issue is that we are running Standard Edition, which is limited to Basic Availability Groups. So while I would love for this to be an option, the unfortunate reality is that I must seek alternatives. – PicoDeGallo Mar 15 at 14:14
  • Well I would be sure to make it clear to the business what limitations you're facing by going with MIs (make sure to review this page closely). You may even need to span multiple MIs, as they note a max file limit per instance of 280 files (data + log). – Dan Mar 15 at 22:37

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following: "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.

  • Thank you for reaching to the Azure SQL team. I agree that transaction replication is likely the best way especially given their official documentation states this, but the biggest question to me is how given that Publish/Subscription profiles are set up database per database through GUI. It seems as though I may be looking at writing large amount of dynamic SQL, but I have a feeling there must be a more automated way whether it's via Azure Data Migration Service, Data Factory, or even SSIS. – PicoDeGallo Mar 16 at 23:38
  • The approach of implementing transaction replication for 300+ databases is the biggest question mark. I would like to find the most efficient manner in which to accomplish this that can be leveraged further down the line. – PicoDeGallo Mar 16 at 23:39
  • You can use Transact-SQL and PowerShell to automate setting up replication for each database. beanalytics.wordpress.com/2017/02/03/… – Alberto Morillo Mar 17 at 4:24

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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