Microsoft Azure – Database Availability and Consistency For Azure SQL

 When you or your company decides to move your database from your on-premise servers/computers to a cloud environment, consistency and availability are two important factors to consider.

Database consistency is much more intricate. It means that no matter how or where you access your data, the same query would give you the same data output.  It also means that your data stays intact in case of an application failure, natural disaster, and so on.  

In this article, we aim to simplify how Azure Database for MySQL achieves the objectives of data consistency and high availability, across different deployment options. We will get an overview of the following concepts:

• Understanding the 2 deployment modes Azure offers
• Availability and consistency for single-server deployment.
• Availability and consistency for flexible deployment.

 

Key Terminologies

Before getting started get yourself familiar with the following keywords:

• PaaS:  Platform as a Service
• Instance: Virtual servers running on physical hardware (In this case, Azure)
• SLA: Service Level Agreement

 

Deployment Modes in Azure

Azure Database for MySQL is a PaaS (Platform as a service). This means that Azure provides you with an instance (or multiple instances) of the MYSQL server. Everything from patching to upgrades is handled by Azure. For this mode, there are 2 available deployment options-

1. Single Server Deployment: This option is only recommended if you currently have an application/workload that is using a single server, and you wish to migrate it to the Azure cloud. A single server requires minimal user customization. It provided an SLA of 99.99% availability within a single region.  
2. Flexible Server Deployment: This option is highly recommended for production workloads. It’s flexible, allowing you to choose availability zones, pricing tiers, cost optimizations, and more.  

So now that you have a basic understanding of the deployment nodes, let’s deep dive into availability and consistency for these 2 modes.  

 

Availability and Consistency

For Single-Server Deployment

Planned downtime: As an example, suppose you start out with 4 virtual cores on your Azure Database instance. As you add more and more applications, your database load increases. To handle this, you might need to increase your virtual cores. This is an instance of planned downtime. As you can see in the architecture, Azure Database for MySQL is built out of separate components like

• Gateway/proxy
• Database server
• Azure storage

 

• • If you need to scale up/down your compute instances, a new database server is provided. This happens with minimal downtime.
  • Scaling up storage requires no downtime.
  • Upgrades, patch fixes, and bug fixes require minimal downtime, up to 60-120 seconds at maximum. However, users are advised to eliminate heavy transactions running on their database instances at planned downtimes.
  • Unplanned downtime: Whenever your server faces unexpected downtime, Azure automatically deploys another server within a minute.  For Azure storage failures, there is no downtime. This is because data is replicated 3 times, and if one copy fails, the azure servers switch to read data from the other 2 copies.
  • You can also configure Azure read replicas (backup database copies) in multiple Azure regions to ensure Disaster Recovery (DR) in case of failures in an entire region.

   

  For Flexible Server Deployment:

   There are 2 primary modes of design:

  • Zone Redundant Architecture: This design will create a primary database server in the availability zone (like east us). Another server with the exact same configurations will be placed in a different availability zone within the same region (like east us 2). This model provides higher fault tolerance, but higher latency in transmitting traffic.
  • Same Zone Architecture: In this design, both your primary and secondary servers are placed within the same availability zone. Data transmission is faster, latency is lower due to the servers being physically close. However, fault tolerance is lower.
  • With both architectures, users can provision read replicas for backup and database consistency. Read replicas is a read-only copy of your Azure database, designed to reduce the load on your primary server. 

  Azure Database for MYSQL has a comprehensive set of services that ensure data availability and consistency. There are various options for you to choose between different servers, architectures and pay only for the services you use.