Sjoukje Zaal is head of the Microsoft Cloud Center of Excellence at Capgemini, Microsoft Regional Director and Microsoft Azure MVP with over 20 years of experience providing architecture, development, consultancy, and design expertise. She works at Capgemini, a global leader in consulting, technology services and digital transformation. She mainly focuses on Cloud, Security, Productivity, and IoT.
She loves to share her knowledge and is active in the Microsoft community as a co-founder of the user groups Tech Daily Chronicle, Global XR Community and the Mixed Reality User Group. She is director of the Global AI Community and board member of Azure Thursdays. Sjoukje is an international speaker and involved in organizing many events. She wrote several books and writes blogs.
MariaDB started as a fork of MySQL, after it was acquired by Oracle, to create a new fully open source database. Over time, MariaDB has replaced MySQL in many places, because—besides the fact that MariaDB is being developed fully in the open—it offers more cutting-edge features then MySQL does.
With Azure Database for MariaDB, Microsoft further extends its commitment to the open source community. By hosting your MariaDB in Azure, you can use the high availability and scaling features that are part of the underlying foundation of Azure for your data. It is designed to deliver high availability with a service-level agreement (SLA) of 99.99 percent. You can also replicate your data from an on-premises MariaDB instance or virtual machine to an Azure Database for MariaDB in Azure.
In this article, we are going to create a MariaDB on Azure and look at how to scale with built-in high availability. To get started with Azure Database for MariaDB, you’ll need an Azure subscription. If you don’t already have one, you can create a free Azure account before you begin.
Create a new server
The first step is to create a new server. We are going to use the Azure Cloud Shell and Azure CLI for this, which can be executed directly in the Azure portal. Common Azure tools are preinstalled and configured in Cloud Shell for you to use.
Make sure that Bash is selected in Cloud Shell and add the following line of code to create a new resource group. A resource group is the container in which we are going to create the database:
az group create --name demoresourcegroup --location westus
The following example creates a server named demoserver in the West US region. The server is also created in the resource group demoresourcegroup and has the server admin login myadmin. The server is a Gen 5 server in the General Purpose pricing tier, and it has two vCores. (For more information about pricing tiers, you can refer to the useful links section at the end of this article.) Replace <server_admin_password> with your own server admin password. The name for the server must be unique, or you will get an error creating the database.
az mariadb server create --resource-group demoresourcegroup --name demoserver459 --location eastus --admin-user myadmin --admin-password <server_admin_password> --sku-name GP_Gen5_2 --version 10.2
Now that we have created the database, we are going to configure the firewall. A server-level firewall rule allows an external application like MySQL Shell or MySQL Workbench to connect to your server through the Azure Database for MariaDB service firewall.
Add the following line of code to create a rule called AllowIP that allows connections from a specific IP address, 192.168.0.1. Replace the IP address or range of IP addresses with an address corresponding to the location you connect from.
az mariadb server firewall-rule create --resource-group demoresourcegroup --server demoserver459 --name AllowMyIP --start-ip-address 192.168.0.1 --end-ip-address 192.168.0.1
Configure SSL settings
By default, SSL connections between your server and client applications are enforced. This default setting ensures security of “in motion” data by encrypting the data stream over the Internet.
For this demo, disable SSL (this is not recommended for production servers) by adding the following line of code:
az mariadb server update --resource-group demoresourcegroup --name demoserver459 --ssl-enforcement Disabled
Get connection information
We need to retrieve the host information and access credentials to connect to the database using MySQL Shell. The result will be in JSON. Make a note of the values for fullyQualifiedDomainName and administratorLogin.
az mariadb server show --resource-group demoresourcegroup --name demoserver459
Connect to the server using mysql
You can connect to the server using MySQL Shell or MySQL Workbench. You can also connect to it from the Azure Cloud Shell. I’ve downloaded the MySQL Shell and connected from my local system. I’ve added my IP address coming from my Internet provider as the allowed IP address in the previous step. Install and open MySQL and add the following line of code to connect to your database:
mysql -h demoserver459.mariadb.database.azure.com -u myadmin@demoserver459 -p
Create a database, tables and add some data
Once you’re connected to the server, create a blank database by adding the following line of code:
mysql> CREATE DATABASE sampledb;
Add the following to change the connection to the newly created database:
mysql> USE sampledb;
To create a table, add the following:
CREATE TABLE inventory (
id serial PRIMARY KEY,
To add some data to the table:
INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150);
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);
Configure vCores and storage
Now that we have a database, table, and some data added to it, we can look into the different scaling options for your database. Therefore, we need to go to the Azure portal and select the database that we created using CLI. In the database properties blade, under Settings, select Pricing tiers. Here, you can adjust the scaling of your database:
You can switch the pricing tier, scale the amount of vCores, increase the storage capacity, and adjust the backup retention period.
Configure and manage security
To configure and manage the security of your MariaDB on Azure, in the Azure Portal again, under Settings, select Connection security. Here you can add additional IP addresses that can connect to the database; you can enable or disable Enforce SSL Connection; and you can add VNet rules (see the useful links for more information about VNets).
As an addition to the above security features, the Azure Database for MariaDB service uses storage encryption for data at rest and is FIPS 140-2–compliant. Data, including backup data, is encrypted on disk. (Temporary files that are created by the engine when it runs queries are not encrypted on disk.) The service uses AES 256-bit cipher, which is included in Azure Storage encryption. The keys are system-managed. Storage encryption is always on and can’t be disabled.
My new book, Microsoft Azure Administrator – Exam Guide AZ-103, is now published! See below, the book description:
Microsoft Azure Administrator – Exam Guide AZ-103 will cover all the exam objectives that will help you earn Microsoft Azure Administrator certification. Whether you want to clear AZ-103 exam or want hands-on experience in administering Azure, this study guide will help you achieve your objective. It covers the latest features and capabilities around configuring, managing, and securing Azure resources.
Following Microsoft’s AZ-103 exam syllabus, this guide is divided into five modules. The first module talks about how to manage Azure subscriptions and resources. You will be able to configure Azure subscription policies at Azure subscription level and learn how to use Azure policies for resource groups. Later, the book covers techniques related to implementing and managing storage in Azure. You will be able to create and configure backup policies and perform restore operations. The next module will guide you to create, configure, and deploy virtual machines for Windows and Linux. In the last two modules, you will learn about configuring and managing virtual networks and managing identities. The book concludes with effective mock tests along with answers so that you can confidently crack this exam.
By the end of this book, you will acquire the skills needed to pass Exam AZ-103.
What You Will Learn
Configure Azure subscription policies and manage resource groups
Monitor activity log by using Log Analytics
Modify and deploy Azure Resource Manager (ARM) templates
Protect your data with Azure Site Recovery
Learn how to manage identities in Azure
Monitor and troubleshoot virtual network connectivity
Manage Azure Active Directory Connect, password sync, and password writeback