Lets summarise some important learnings about SQL MI and failover groups.
- when you add a database to a failover group the secondary database has the same edition / compute tier as the primary.
- From the last blog post, storage sizes should be the same too!
- Your secondary must be empty else setup will fail.
- You will have read / write and read only listener. Use the R /W endpoint for the main app and read only endpoint for reads – you will get a better experience.
- When setting up failover groups, a process called seeding takes place. There are limits to how fast this can happen, Microsoft states roughly for Managed Instance a seeding process rate of 360 GB / hour, however this does depend on the networking infrastructure you have such as the Express Route link you have.
- By default, this is automatic failover mode, that is once the GRACE period has expired. So if the robots cannot heal the machines within the grace period then expect the failover to take place.
- Minimum grace period is 1 hour!
- The failover of the read-only listener is disabled by default. you can enable failover for the read-only listener by configuring the
AllowReadOnlyFailoverToPrimaryproperty. In that case, the read-only traffic will be automatically redirected to the primary if the secondary is not available.
- Always use the Paired Data Centre for best performance – where possible, for example UK South and UK West for your primary and secondary servers.
- If you delete a database on the primary server, it will be dropped from the secondary.
- The VNETs for the servers involved in replication ,must be able to talk to each other – Global VNET peering is now supported https://azure.microsoft.com/en-gb/updates/global-virtual-network-peering-support-for-azure-sql-managed-instance-now-available/
If not, you will get the following
"InstanceFailoverGroupIncorrectNetworkingConfiguration", "message": "Failover group creation failed because the primary server's replication traffic cannot reach the secondary server. Please verify that connectivity between the VNets of the primary and secondary managed servers has been established."
11. The VNETs should not have overlapping addresses. If you do not follow this you will get the following error when trying to setup connectivity.
12. Network Security Groups (NSG) such that ports 5022 and the range 11000~12000 are open inbound and outbound for connections from the subnet of the other managed instance. This is to allow replication traffic between the instances.
13.Important workflow must be followed when upgrading / downgrading the database!
When upgrading start with all of the secondary databases first, and then upgrade the primary. When downgrading, do the opposite, WHY? to avoid the problem where the secondary at a lower SKU gets overloaded and must be re-seeded which will impact your experience.
I will try and keep this list updated as I go. Anything I have missed that is important let me know.
Pingback: Dew Drop – December 10, 2021 (#3576) – Morning Dew by Alvin Ashcraft
Pingback: Dew Drop – December 10, 2021 (#3576) - Online Code Generator