Does "Azure SQL Managed Instance" support publication distribution of transactio

  • Hi,

    I want to implement transactional replication on a database which should be on "Azure SQL Managed Instance", where entry will be done.

    And the subscriber which will be read only and will be "on premises" (that is with our site offices)

  • It's right in the Microsoft documentation. You can do that. I'm not sure you should (data movement out of Azure has some cost overhead), but you can.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I want to put my production database at azure MI , where people will do the entry , then want a copy of the  database "on premises "

    so that i can show offline or online reports.  but heard that i can not take full backup from azure MI. what are the other solution i need full backup from my production database from azure MI.

     

  • Backup and restore is out. It simply doesn't work. The Azure systems are constantly being upgraded and backups from there generally can't be restored down to on-premises. It's similar to how you can't restore to 2017 from a 2019 backup.

    So, you have a bunch of options within Azure to have readable secondaries and failover groups. I'd start there personally. Microsoft is working on a thing called Managed Instance Link, which will allow for an availability group between MI & on-premises, but it's currently one way only, from on-premises to MI. Further, it doesn't yet support going back the other way in the event of a failover. You'd be stuck up in MI. They also have replication set up, but I'd hate to rely on it. Replication is fragile on a good day, on-premises. Add in cloud connectivity and I'm guessing it's a ton of work to maintain. Better to use the cloud-based solutions I linked to at the start.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply