You've successfully subscribed to Florin Loghiade
Great! Next, complete checkout for full access to Florin Loghiade
Welcome back! You've successfully signed in
Success! Your account is fully activated, you now have access to all content.

Azure MySQL as a Service - What is it

in

Every time we wanted to deploy an application to Azure that needed to connect to a MySQL Database we had a small problem. The problem was that we either had to use a third party MySQL provider like ClearDB and pay extra fees for the service or option two, create and maintain our MySQL instance in a virtual machine. The problem with ClearDB was that performance was horrible even, prices were high, and the service was not neatly integrated into Azure. The problem with a Virtual Machine was that we had to install, configure, secure and maintain a MySQL instance. One instance of MySQL is almost OK to support, but if you need High-Availability with data replication and all the she-bang, then we would be dealing with a disaster waiting to happen. For example, configuring SQL Always-On is in some way simple to do because the mechanisms are already integrated into the product, but you don’t have that with MySQL Community Edition or MariaDB Community Edition. I tried once to configure HA on a MariaDB instance, and oh boy, there were some many problems I couldn’t wrap my head around.

Last year Microsoft announced the public preview of the MySQL and Postgre database as a service offering in Azure which provided us mainly with a SQL Database like experience. Well, recently Microsoft announced that the services as mentioned earlier are now Generally Available, so I took the time to write my experience with them from a management and performance standpoint.

What is Azure Database for MySQL?

The Azure Database for MySQL is a database offering based on MySQL Community Edition with built-in high availability, scalable, secure and point in time backups. From a management standpoint, we’re relieved of patching, security, HA, backup and so on; That duty is offloaded to Azure at no cost to you as a user. The best benefit that you get with the service is that you can dynamically scale it on demand.
Comparable to the Azure SQL Database offering, we have multiple tiers of performance with different price points depending on our needs. At this point we have the following tiers available:

Basic
This one should be only used in dev/test scenarios. This type of tier will not offer you predictable performance nor a high number of MySQL Connections
The number of Max Connections is 50 per core, and this tier will only allow you to have a maximum 2vcores and it runs on Standard storage (HDD)

General Purpose
This is the tier that should be used for production applications. The tier offers scalable and predictable I/Os. If we want to compare this to the Azure SQL Database offering, we can compare it to the Standard tier.
The number of Max Connections is dependant on the vCore count:
2 vCores – 300 Connections
4 vCores – 625 Connections
8 vCores – 1250 Connections
16 vCores – 2500 Connections
32 vCores – 5000 Connections

Memory Optimized
This is the highest tier available at the moment and the most expensive. This tier is great for high concurrency and fast transaction processing. As this tier is not cheap to start with, I suggest first testing if your application uses the added benefits of the tier.

The number of Max Connections is dependant on the vCore count:
2 vCores – 300 Connections
4 vCores – 625 Connections
8 vCores – 1250 Connections
16 vCores – 2500 Connections
32 vCores – 5000 Connections

*The Max Connection numbers presented above can change at any point so consult the Azure documentation before you size a database for an application

If your application is very chatty with the database server and you’re using too many connections you will receive the following error:
ERROR 1040 (08004): Too many connections

This is a standard MySQL error. If you’re hitting that wall, then your options are to either scale up the Database or modify your application so that it doesn’t initiate that many TCP connections.

Another limitation that you will hit if you’re porting a legacy application to this service is that the engine doesn’t support MyISAM databases. If you’re using MyISAM, then you will have to convert it to InnoDB. The reason as to why MyISAM is not supported in this scenarios is because it’s not scalable and cannot work in distributed environments.

Converting the database can be simple, or it can be hard. The simple way is just to run “ALTER TABLE table_name ENGINE=InnoDB;” but you don’t get that many free lunches in life.
So Microsoft is announcing that it will add to it’s DMA (Database Migration Assistant) the possibility of migrating your on-premises / IaaS MySQL/Postgre instances to Azure Database for MySQL.

As the “server admin,” you do not have any super admin or DBA role privileges which means that modifying specific settings is not permitted. This is intended so that you don’t cause any issues with the database server and cause a service disruption by mistake. You have the possibility of importing databases using mysqlimport and mysqldump on the database server.

What we should know before using the service

As any PaaS offering, we have to understand that we are provisioning a database server and deploying databases in a shared environment. So depending on the plan we will be using, we will be affected in some way by the other databases that will be sitting on the same servers as we are. With that in mind, we have to know the limits of each tier that’s available to us and test the application while applying load.

If you’re getting the “Too many connections” error, then you might have to scale-up the database or re-write some code. Another factor that will affect the performance of our application is that the database server is not in the same virtual network or on the same VM as the application, so you have to take into account the network latency factor. The latency will not be huge, but if your application is expecting super fast responses because it found the database in memory, then you will have a significant issue.

Another factor that will cause problems to your application is transient errors. These types of errors occur naturally in a cloud environment because the cloud provider is dealing with millions of servers and failure is something pretty frequent. So these transient failures usually occur when your database was moved to another server, and the load balancer that’s handling your requests didn’t switch, and you will get a timeout. That timeout is very short, but if your application doesn’t have a retry mechanism like a circuit breaker, then you will get an exception.

How do I start?

Creating an Azure Database for MySQL is pretty simple. You can do it from the Portal or from the CLI.

From the portal you will have to go to Create a resource -> Type in “Azure MySQL” -> Select Azure Database for MySQL -> press create.

After you press create, you will be presented with a new blade asking you to fill out some parameters. After you fill out all the parameters shown in the screenshot below, you can select the pricing and performance tier.

From the CLI, you have to run the following commands:

#Create the RG
az group create --name MYSQL-RG --location westeurope

#Add the RDBMS extension to the Cloud Shell
az extension add --name rdbms

#Create a General Purpose MySQL Server on a Gen5 server with 2 vCores, running MySQL version 5.7. The command --sku-name GP_Gen5_2 translates to General Purpose, Server Generation and number of cores.
az mysql server create --resource-group MYSQL-RG --name azsqlserver  --location westeurope --admin-user adminuser --admin-password <server_admin_password> --sku-name GP_Gen5_2 --version 5.7

After you press create, you wait a few minutes for the server to be provisioned and after that, you ready to connect to it. What you need to know is that the firewall and SSL settings are enforced by default so you will have to add your IP to the whitelist so you can connect to it with MySQL Workbench, allow Azure services if you need an VM or App Service to connect to it and when you’re connecting your application, you will have to change the connection string to use encrypted connections otherwise you have to disable SSL.

You can dynamically scale the CPU / Storage based on your needs but you cannot change the pricing / performance tier after the server has been provisioned. Storage can only be increased and not lowered and you cannot change to LRS from GRS or vice versa for the Backup Redundancy Option.

Hope this was useful. Have a good one!