Post reply

Migration to Azure SQL Managed Instance Business Critical Tier from VM's

  • August 18, 2021 at 1:51 pm

    #3921278

    Hello All,

    Currently we are planning to migrate from two production Azure SQL Server VM's to Azure SQL Managed Instance and I was doing some reading on SQL Managed instance Business Critical tier and I had few questions which I would like to ask the experts here. As always appreciate all the inputs.

    1. I have provisioned one SQL MI Business Critical server, does it automatically come with readable secondary replica or do I have to configure the secondary replica?
    2. The point I highlighted in yellow in the screenshot below mentions that in case of system failure, the databases on secondary replica will become primary read-write, do I have configure anything in order for this to happen or now that I have already provisioned a Business Critical server, this feature is automatically available to me?
    3. What is the difference between readable secondary replica and setting up another auto-failover group?
    4. In general, for production servers, would you recommend creating automatic failover group or readable secondary replica which can become read-write in case of failure should suffice?

    SQLMI_BC

    Thank you,

     

  • August 18, 2021 at 2:18 pm

    #3921388

    Hi MillionQueries,

    I'll have a go at answering:

    1. Business Critical tier in Managed Instance comes with multiple secondaries, one of which is readable at a time (via ApplicationIntent=READONLY in the connect string). You don't need to do anything, it is set up behind the scenes for you and you can't really see the configuration either.  General Purpose also has built-in HA but is not readable.

    2. You don't need to do anything if the primary server goes down, a secondary will take over. You might have to reconnect though.  It's fairly quick as the secondaries are already in place and ready.

    3. The Business Critical tier gives resilience in one region in Azure so if that goes down you won't have access.  You can set up a failover group to another region to protect against this and it will also provide read-only data in another region.

    4. We've set up a failover group to another region for safety although hope never to use it!

    • This reply was modified 3 years, 3 months ago by  DNA_DBA.

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

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