Deploying a .NET 6 Web API and a MySQL Database with Azure Pipelines

In this article, I explain how to deploy a .NET 6 Web API to an App Services in Azure Portal and a MySQL database using MySQL Flexible Server, by using Azure Pipelines. In this example, I’m using a repository in Azure DevOps environment.

In order to deploy an application, it’s necessary to have an Azure Subscription, and a Resource Group. If you want to know more about it, please check the article on this link.

Creating a MySQL Server

In Azure Portal, search for “Azure Database for MySQL servers”:

Then click on “Create”:

On the next page, you can choose between “Flexible server” and “Single server”. If you want to know which one you should choose for your app, check this doc from Microsoft: “Choose the right MySQL Server option in Azure”. And if you want more information about them, check these two docs:

• Azure Database for MySQL — Flexible Server — Microsoft Docs
• Azure database for MySQL Single Server — Microsoft Docs

As Microsoft advises in the official documentation, “Flexible servers are best suited for all new developments and migration of production workloads to Azure Database for MySQL service.”, so in this example let’s go for it. In the “Flexible server”, so click on “Create”:

On the next page, inform the Resource group, the server name, region, MySQL version, the administrator account (make sure to remember the user name and the password, we will need them later), and click on “Next: Networking”:

Add your client IP or the IP’s that you want to allow to connect in the MySQL Server and also make sure to select the option “Allow public access from any Azure service within Azure to this server’, then click in “Review + create”:

Once the MySQL Server is deployed, you should see a similar message:

Creating a MySQL Database

Now we have the MySQL Server in Azure, and we need to create the database. To create it, go to the MySQL Server in Azure Portal and search for “Databases”:

Click on “Add”:

Inform the database name (and the collation and character set if necessary), and click on “Save”:

Character Set and Collation Tip

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. (MySQL)

In case you already have a MySQL Server in your on-premise environment, and you want to make sure you are using the same collation and character set, you can run the following commands in your local database:

SELECT @@character_set_database, @@collation_database;

This Select query will return the character set and the collation of the database:

In this example, you can notice that in my local database, the collation is “utf8mb4_unicode_ci”, but in Azure is “UTF8MB4_0900_AI_CI”, and that’s ok. The one that is being used in Azure is the new version and the one you should use in Azure.

“A MySQL collation is a well-defined set of rules which are used to compare characters of a particular character-set by using their corresponding encoding. Each character set in MySQL might have more than one collation, and has, at least, one default collation.” (GeeksForGeeks)

Sometimes MySQL Server could be created with different character set and collation. To check it (and change it, if necessary), go to the MySQL Server in Azure Portal, select the “Server parameters” option and search for “character_set_server” and for “collation”, and change them if necessary:

Creating the App Service

We need to have an “App Services” to deploy the Web API. With this service we don’t need to worry about the infrastructure where the application will run, Azure will take care of that for us. You can click on the “App Services” option or search for it in the search bar in the Azure portal:

“Azure App Service lets you create apps faster with a one-of-a kind cloud service to quickly and easily create enterprise-ready web and mobile apps for any platform or device and deploy them on a scalable and reliable cloud infrastructure.” (Azure Microsoft)

Then click on “Create”:

You can now configure your app service, by selecting your subscription, the resource group, informing a name for the service, the tech stack, operating system, region and the plan:

Extra — Configuring Always On

“Always On” — Keeps the app loaded even when there’s no traffic. When Always On is not turned on (default), the app is unloaded after 20 minutes without any incoming requests. The unloaded app can cause high latency for new requests because of its warm-up time. When Always On is turned on, the front-end load balancer sends a GET request to the application root every five minutes. The continuous ping prevents the app from being unloaded. (Microsoft)

When an App Service is created, by default the “Always On” option is off, but in some scenarios, it’s better to turn it on. If you want to enable it, go to the App Service in Azure Portal > Configuration > click on “General settings”, and enable the “Always on” option (please note that if you want to enable it, you must have a plan that allows it):

Remember to Save the configuration and restart the App Service.

Configuring the Connection String

When we work with a MySQL database in Azure, there is a small detail that we need to pay extra attention to when configuring the connection string in the App Service. In the MySql server in Azure Portal, go to the “Connection String” and copy the connection string for ANO.NET:

This is the value that comes from this page:

Server="{mysql-server-name}";UserID = "{your-user}";Password="{your_password}";Database="{your_database}";SslMode=MySqlSslMode.Required;SslCa="{path_to_CA_cert}";

But if you try to use this configuration in your App Service, it will not work. You will get an Error 500 (Internal Server Error) when trying to execute some action:

So this is the correct way how you must add the connection string:

Server="{mysql-server-name}";UserID = "{your-user}";Password="{your_password}";Database="{your_database}";SslMode=Required;

Go to the App Service in Azure Portal > Configuration > click on “New connection string”:

Then add the “DefaultConnection” and add the connection string in the “Value” field, select “MySQL” in the Type field and click on “Ok”:

After that just remember to save the changes in the App Service:

Creating Variables using Pipelines Library

A library is a collection of build and release assets for an Azure DevOps project. Assets defined in a library can be used in multiple build and release pipelines of the project. The Library tab can be accessed directly in Azure Pipelines. (Microsoft Docs)

In Azure DevOps, it’s possible to make use of the Library to store a group of variables that we can use in the pipeline. In the Library we can configure variables that we want to use in our pipelines, and it’s also possible to add sensitive data and hide the value. In order to use the variables in the Library in Azure DevOps, go to Pipelines > Library > click in “+ Variable group”:

Add the variable group name, add the variables you want to use in the pipeline (you can also click on the lock icon to “lock” the value of sensitive data for example), and click on “Save”:

All assets defined in the Library share a common security model. You can control who can define new items in a library, and who can use an existing item. (Microsoft Docs)

Then in the pipeline, you can set the variables group and make use of them by using the syntax $(variable-name):

After creating the variables and running the pipeline for the first time, it’s necessary to give the permission:

Implementing the Pipeline

In a previous article, I explained how to create a Repository in Azure DevOps, and how to create Pipelines and Environments. If you want to know more about it, please check the article on this link.

In case you saw the previous article about Deploying a .NET 6 Web API with MSSQL Database, you will notice that the steps are basically the same, there is only one difference in the last task, which is the one we use to run the SQL on the database.

In the “azure-pipelines.yml” file we have the following main steps in this exact order:

• 1- The configuration to use .NET 6 will be added
• 2- The application will be build (unit tests task is comment since there are no unit tests in this project, but I left it there in case you need to use it in your project, then you can just uncomment this task)
• 3- The application will be zipped and published to the pipeline artifacts
• 4- The application will be deployed to an App Service
• 5- EF Core will be installed
• 6- The SQL Script will be created, based on the EF Core migrations we have in the project
• 7- The SQL Script will be added to the pipeline artifacts
• 8- The SQL Script will be executed in the database

To have an overview, you can see the complete yml file below. I will explain step by step:

In the first part, in the “trigger”, means that every time something changes in the “master” branch, it will run the pipeline. Next, we are setting the configuration to be executed in a Linux environment. After that we are setting all the variables that we are going to use in the yml file:

In the build stage, we are setting the configuration to use .NET 6, and then we build the solution (the task to run unit tests are commented because there are no unit tests on this project, but I left that in case you need to use in your project, then you can just uncomment this task):

In the next stage, we are deploying to Development. The “dependsOn” and “condition” configuration means that this stage will only be executed if the Build stage it’s running without failure. And in the variables group, we are setting the configuration to make use of the “minimalapi” variables group, which was created in the Pipeline Library. And we created a job to deploy to development, and we add a new variable named “sqlFile”, which is the path for the SQL Script that will be created and executed by the pipeline:

In the next step, we set the configuration to use .NET 6, and we publish the project to an artifact:

In the next task, in AzureWebApp task, we publish the app to an App Service:

Then we add a task to for the manifest, which is necessary in order to install local tools and after that, we install EF Core, and then we create the SQL Script (which will be generated based on the migrations of your project) to be executed and we publish it to a pipeline’s artifact:

And in the last task we execute the SQL Script in the database:

Now we can save the file, and the pipeline will run. If everything goes well, you must see your pipeline green:

Now the API will be available in the App Service (you can check the URL in the App Service in Azure Portal). Let’s test our API by making one POST request:

And let’s execute now a GET operation:

We can also use our local MySQL workbench to see the database and query the data:

Extra

There is one bug with the temporary engine on MySQL 8, which is related to the storage engine for in-memory internal temporary tables. This happened to me when I was trying to execute some complex queries with some relation tables, and I got an error such as “/app/work/temp/#sql40_13e_1' is full” (if you want to read more about this issue, take a look on the topic at this link). In order to avoid issues with that, go to the MySQL Server in Azure > Server parameters > and search for internal_tmp_mem_storage_engine, the default value will be “TEMPTABLE”, change it to “MEMORY” and click on “Save”:

Conclusion

In this example, we saw how to deploy a .NET 6 Web API to an App Services in Azure Portal and a MySQL database using MySQL Flexible Server, by using Azure Pipelines and a Repository in Azure DevOps. With MySQL Database there are some different steps that must be done when compared with the MSSQL Database (you can read about Microsoft SQL Server in the article at this link), but as we saw, once we learn how to configure, it’s also simple to use. You can check the code of this project here:

https://github.com/henriquesd/DotNetMySqlAzurePipelines

If you like this solution, I kindly ask you to give a ⭐️ in the repository.

Thanks for reading!

Choose the right MySQL Server option in Azure — Microsoft Docs

Azure Database for MySQL — Flexible Server — Microsoft Docs

Azure database for MySQL Single Server — Microsoft Docs

Tutorial: Install and use a .NET local tool using the .NET CLI — Microsoft Docs

Character Sets and Collations in General — MySQL

What is Collation and Character Set in MySQL? — GeeksForGeeks

Deploying a .NET 6 Web API and a SQL Database with Azure Pipelines

Developing a Minimal API — Desenvolvedor.io