Select Page

Azure MySQL as a Service – What is it

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:

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!

Post-Event Conferinta de Cloud – Bucharest

Conferinta de Cloud

On the 23rd of November, we started the first premium cloud conference in Bucharest, and we’ve had a blast. The event lasted for one full day with two tracks (Business and Technical) with subjects about Cloud, GDPR, Security and other great stuff. Over 170 cloud hungry people participated at the event.

You can find the event photos here:
https://www.facebook.com/search/str/conferinta+de+cloud/photos-keyword

My session was on Azure Site Recovery & Backup in Microsoft Azure

Description:

The need for protecting your data against disaster is not a new concept. It doesn’t matter if we’re talking about SMBs or large enterprises because data is something that they all have in common and they all have to protect it so that their business can run smoothly. Traditional backup/disaster recovery solutions have the disadvantage that they require an upfront investment for the initial implementation and on-going allocated resources (people, money, time) to maintain and test them. When you want to implement a backup solution in a company, you have to buy the server, install the solution and after that maintain it so that it works all the time. When it comes to disaster recovery, we’re talking about a different scale, you have to build a separate standby data center that has to be synchronised with the main one and hope you never get to test your failover solution.
In this session we will talk about the Recovery Services in Azure and how they can help us implement a backup and disaster recovery plan with minimal upfront costs and pay-as-you-go model. By using Azure as our backup and disaster recovery solution, we do not need to buy new servers to do backups or build data centers for disaster recovery.

I hope you were at the event and had as much fun as I did. 🙂

Running Linux web apps in Azure App Service

If we look at the statistics of Azure, we will see that most of the Virtual Machines that are deployed are running Linux. There’s a good reason as to why to run Linux applications, and I’m not going to cover that in this blog article. Today I will be talking about running Linux Web Applications in Azure’s App Services offering.

You may or may not know that Azure App Services run on IIS so in a nutshell, when you spin up an App Service and deploy a Web Application, you’re deploying that code using the same worker process with one application pool per website. So you’re not provisioning a single virtual machine to host your Web App instead you’re receiving space on an existing VM to host your application.

The main problem with App Services was that you could only host applications on IIS thus limiting your options. You have the possibility of running PHP or Java applications on IIS, but they wouldn’t be as performant as you would expect. Microsoft solved the problem by introducing Containers for Web Apps. You spin up a Linux App Service, and from there you deploy your application on an Apache solution (prebuilt) or a container built by you.

Why containers you might say?

Containers have been around for a long time, and they allow you to consistently run your application in any environment you want without having to say “It works on my machine”. API that was chosen to create, deploy, run containers is Docker. Most people call those containers as Docker Containers, but in reality, Docker is just the API that allows you to create/manage those containers. The main idea is that if you create a container that runs your web application without problems, then you can just take that container and deploy it anywhere because the code and all your dependencies are held in that image.

So how publish/pushh / push my container in a Linux App Service?

Taking your image and pushing it in a Linux App Service is very simple. You first have to have your container image pushed in a public/private repository like Azure Container Registry or Docker Hub then you just create a Web App for Containers and reference your image.

How do I deploy my code in a consistent manner?

Creating images in a centralized consistent manner is quite different than working alone on your laptop. Web App for Container has integration with most of the thing that you would use to deploy your code in a regular Windows App Service.

There are a couple of ways of pushing your code to the Linux App Service:

1. You can create your container image and push it to a repository like Azure Container Registry or Docker Hub.
2. You can use a CI/CD engine like VSTS to create your image and push it to the registry.
3. You just upload your files via FTP and be done with it 🙂

Down below is a demo flow of how you would push an image to the Web App for Containers service

Now if you’re used to App Services running IIS, there are some limitations that you should be aware of.

1. Containers are stateless by nature – If you need persistence you need to leverage blob storage or use another service like Redis Cache or you can leverage a feature to mount the /home directory to an Azure Files share. The latter will downgrade your performance a lot so tread carefully.
2. You only get ports 80/443 so if you need a custom port for your web application then App Services will not allow it.
3. You don’t have Web Jobs
4. You cannot do VNET integration
5. You cannot do Authorization with Azure AD

This is just a number of limitations that you should be aware of. Some features that you get from a regular App Service will eventually pop up in the Linux ones but until then, you need to work with what you have 🙂

That being said, take a look at Web Apps in Containers, play around with them and see what you can come up with.

Pin It on Pinterest