With Azure Database for MySQL - Flexible Server, you can configure high availability with automatic failover within a region. The high availability solution is designed to ensure that committed data is never lost because of failures and that the database won't be a single point of failure in your software architecture.
Note: For more information, see Azure Database for MySQL - Flexible Server - High Availability Concepts.
Within a region, there are three potential options to consider, as shown in the following table:
|
Option (Mode) |
Committed SLA |
|
Non-HA |
99.9% |
|
Same Zone HA |
99.95% |
|
Zone Redundant HA (ZHRA)* |
99.99% |
*ZRHA is only available in regions that support availability zones. For the latest list of Azure regions, in the Azure Database for MySQL documentation, see Azure regions.
In addition to the ‘in-region’ modes listed above, there’s also an option to design for protection of database services across Azure regions. One common pattern we’ve seen with several customers is the need for maximum in-region availability along with a cross region disaster recovery capability. This manifests itself as ZRHA in the primary region and a Read Replica in another region, preferably the paired region, as illustrated in the following diagram:
With ZRHA, failover between the Primary and Standby servers is automatically managed by the Azure platform, and importantly, the service endpoint name does not change. On the other hand, the manual process associated with a regional failover does introduce a change to the service endpoint name. Some customers have expressed an interest in being able to perform a regional failover without later having to update the associated application connection strings.
In this post, I’ll explain how to address this requirement and provide a regional failover that requires no application connection string changes.
For our purposes, we’ll use the following simplified architecture diagram as a starting point:
In this illustration, there’s a single Primary server located in Australia East and a Replica is hosted in Australia Southeast. With this setup, it’s important to understand some implementation details, especially around networking and guidance:
- Each server is deployed using the Private Access option.
- Each server is registered to the same Azure Private DNS Zone, in this case, myflex.private.mysql.database.azure.com.
- Each server is on separate a VNet, and the two VNets are peered with each other.
- Each VNet is linked to the Private DNS zone.
The server name, IP address, server type, and region for the two servers I created are shown in the following table:
|
Server / Service name |
IP address |
Role |
Region |
|
primary01.mysql.database.azure.com |
10.0.2.4 |
Primary |
Australia East |
|
replica01.mysql.database.azure.com |
192.168.100.4 |
Replica |
Australia Southeast |
Note: For more information about Azure Database for MySQL connectivity and networking, see the article Connectivity and networking concepts for Azure Database for MySQL - Flexible Server.
When configured properly, the Private DNS Zone (should appear as shown in the following image:
It’s possible to resolve these DNS names from within either VNet. For example, the Linux shell shows the following detail for a Linux VM, which happens to be on the Australia East VNet, and it can resolve the both the service name and the private DNS zone name of each of the servers.
Note: This Linux VM is being used simply to host the ‘nslookup’ and ‘mysql’ binaries that we are using in this article:
In addition to name resolution and courtesy of our VNet peering, I can also connect to both databases using either the service name or the private DNS name. Running the command-line application ‘mysql’, I’ll connect to the primary server using both DNS names as shown in the following image:
And next, I’ll use ‘mysql’ again to connect to both DNS names for the replica server:
To recap, we have set up a primary server in one region and replica service in another region using the Private Access networking, standard VNET peering, and Private DNS Zone features. I then verified that I could connect to both databases using the service name, or the name allocated by the Private DNS zone. The remaining question, however, is how to failover to the replica database, for example in a DR drill, and allow my application to connect to the promoted replica without making any changes to the application configuration? The answer, it turns out, is pretty simple…
In addition to typical DNS record types of ‘A’ Address and ‘PTR’ Pointer, ‘CNAME’ is another useful record type that I can use as an “alias” to effectively point to another DNS entry. Next, I’ll demonstrate how to configure a ‘CNAME’ record to point to either of the databases in our set up.
For this example, I’ll create a CNAME record with value ‘prod’ that points at the ‘A’ record for the Primary server. Inside the Private DNS Zone you can add a new record by choosing ‘+ Record Set’. Then you can add a CNAME record like so:
While the default TTL is 1 hour, I’ve reduced this to 30 seconds to limit DNS clients and applications from caching an answer for too long, which can have a significant impart during or after a failover. After I’ve added the CNAME record, the DNS zone looks like this:
Notice that the new ‘prod’ name points to the ‘A’ record for the primary server.
Now, I’ll verify that I can use the CNAME record to connect to the primary database:
Cool! That’s just DNS doing its thing with the CNAME record type.
It is also possible to edit the CNAME DNS record to point it to the replica:
After saving the updated CNAME, when I connect to ‘prod’, it is now connecting to the replica, which is in READ-ONLY mode. I can verify this by trying a write operation, such as creating a table:
Sure enough, the CNAME ‘prod’ now points to the replica, as expected.
Given what I’ve shown so far, it’s clear the using the flexibility of Azure Private DNS and CNAME records is ideal for this use case.
The last step in this process is to perform the failover and complete the testing.
In the Azure portal, navigate to the Replication blade of either the Replica server or the Standby server, and then ‘Promote’ the Replica:
After selecting Promote, the following window appears:
When the newly promoted Replica server is available, I want to verify two things, that the:
- CNAME record points to the Replica (now Primary)
- Database is writeable
From an application perspective (the application is the mysql client in this article), we haven’t had to make any changes to connect to our database regardless of which region is hosting the workload. This method can be easily integrated within DR procedures or failover testing. Making use of the Azure CLI to semi-automate these changes is also possible and could possibly reduce the likelihood of human errors associated with changing DNS records. However, DNS changes are, in general, less risky than making application configuration changes.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!