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.
- The 2 servers that form your failover group cannot be in the same region, try to use the paired region where possible.
- 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.
- If you want to re-name your failover group, well you can’t – you need to start again.