SQL Managed Instances – Failing Over

This is our current setup shown below.

There is not much to failing over with Managed Instances, from experience it is similar to that of Azure SQL Database.

Let’s have a look around. Starting from the top the name, primary and secondary servers are obvious what they are. The read / write failover policy is set to automatic rather than manual and should observe the grace period (which you can change from 1 hr to 24 hrs)
For example, if the grace period is set to 24 hrs, it means that if an outage at the data centre occurs which would trigger a failover get mitigated by Azure in <24 hrs, the failover will not be triggered at all and therefore no data will be lost. But the flip side of it is that the application will be down for 24 hrs for it be resolved. If you set this to 1 hr and the issue is not resolved after 1 hour the failover occurs.

When you setup failover groups there is a process called seeding that needs to happen, this is the most time-consuming piece, based from Microsoft, for SQL MI up seeding speed can be up to 360 GB an hour. Seeding is performed for all databases in parallel.
Always make use of the endpoints exposed to you. The RW endpoint is great and should be the entry points for applications because we don’t know what server underneath is actually serving as primary. For example, if you are in SSMS and issuing the same query pre and post a failover. Nothing changes from the query writing perspective.

Some important things to remember that I have encountered so far.

  1. The 2 servers that form your failover group cannot be in the same region, try to use the paired region where possible.
  2. Be careful if you have built your own code that lives within the master database ( other other system databases) because these objects are not replicated.
  3. If you want to re-name your failover group, well you can’t – you need to start again.

4 thoughts on “SQL Managed Instances – Failing Over

  1. Pingback: Dew Drop – December 7, 2021 (#3573) – Morning Dew by Alvin Ashcraft

  2. Pingback: Dew Drop – December 7, 2021 (#3573) - Online Code Generator

  3. Pingback: ➧Dew Drop – December 7, 2021 (#3573) • Softbranchdevelopers

  4. You mention seeding being the most time consuming piece. I guess that’s why I don’t use it for initial set up. I already have tools for restoring multiple databases to a new server – and for applying transaction logs. Using these to prime a new replica has the advantage of not over burdening your Primary replica, and knowing exactly where you are in the process- no guessing when the replica dbs will be done synchronizing. Once primed, databases ont the new replica can be added to the Availability Group. Once that’s done however, Seeding is wonderfully convenient for adding a new db to an existing Availablity Group, or re synchronizing a database that has ‘lost its way’ for whatever reason.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s