Topic 5 of 5
Open-source databases on Azure
Not every organization uses SQL Server. For those working with MySQL or PostgreSQL, Azure offers managed versions of those engines. The exam asks you to identify these services, recognize what sets each engine apart, and know what the PaaS version gives you.
Contents
Why there are open-source services
Besides Azure SQL, Azure offers data services for other popular relational systems, such as MySQL and PostgreSQL. The main reason is to let organizations that already use them in on-premises apps move to Azure quickly, without significant changes to their applications.
As with Azure SQL, administrators manage them for transactional applications, and they serve as sources for data engineers' pipelines and analysts' reports.
What is MySQL?
MySQL started life as a simple-to-use open-source database management system. It's very popular for LAMP stack apps (Linux, Apache, MySQL, and PHP).
Community
Free; historically very popular for web apps on Linux. Versions for Windows also exist.
Standard
A commercial, paid edition.
Enterprise
Commercial, with more security, availability, and scalability tools.
What is PostgreSQL?
PostgreSQL is an object-relational database: it stores data in relational tables, but it also allows custom data types with their own non-relational properties.
- Extensible: you can add code modules that queries can run.
- It can store and manipulate geometric data: lines, circles, and polygons.
- It has its own procedural language, PL/pgSQL, which extends SQL to write stored procedures that run inside the database.
Azure Database for MySQL
A PaaS implementation of MySQL on Azure, based on MySQL Community Edition (versions 5.7, 8.0, and 8.4 on flexible server). It scales to large databases without managing hardware, network, virtual servers, or patches.
High availability
Built in, with automatic failover.
Backups and restore
Automatic backups and point-in-time restore for up to 35 days (7 by default).
Connection security
Firewall rules and, optionally, required TLS connections. Data protected at rest and in motion.
Configuration and pricing
Server parameters (lock modes, maximum connections, timeouts) and pay-as-you-go pricing. Metrics, logs, and alerts.
Some operations, mainly server security and administration, aren't available because Azure manages them.
Azure Database for PostgreSQL
A PaaS implementation of PostgreSQL with the same availability, performance, scaling, security, and administrative benefits as the MySQL service.
- High availability with built-in failure detection and failover, within a single availability zone or across multiple zones.
- It separates compute and storage; storage keeps three synchronous, locally redundant copies of the database files.
- It supports a set of extensions, but not all of those in on-premises PostgreSQL.
- You can still use pgAdmin to manage and monitor, though without server functions like backup and restore, which Microsoft handles.
- Query store records the queries run in a database named azure_sys; you query them through the query_store.qs_view view to tune performance.
Flexible server
Both services are deployed as a flexible server: a fully managed service that gives more granular control over management and configuration, with cost optimization options.
To try it at no cost
With an Azure free account, MySQL and PostgreSQL flexible server can be used free for 12 months.
Comparison with Azure SQL
| Azure SQL | Azure Database for MySQL | Azure Database for PostgreSQL | |
|---|---|---|---|
| Engine | SQL Server | MySQL Community Edition | PostgreSQL |
| Dialect | T-SQL | MySQL's SQL | PL/pgSQL |
| Distinctive trait | IaaS and PaaS options | LAMP stack, web apps | Object-relational, extensible, geometric data |
| Typical reason | Organizations using SQL Server | Migrate MySQL apps without changes | Migrate PostgreSQL apps without changes |
Exam scenarios
| Situation | Answer | Why |
|---|---|---|
| Migrate a LAMP web application to a managed service | Azure Database for MySQL | MySQL is the typical LAMP stack database. |
| Store geometric shapes (lines, polygons) and custom data types | Azure Database for PostgreSQL | An object-relational, extensible database. |
| Write stored procedures in PL/pgSQL | Azure Database for PostgreSQL | It's PostgreSQL's procedural language. |
| Restore a MySQL database to how it was 20 days ago | Point-in-time restore | Up to 35 days (7 by default). |
| Review which queries users run in PostgreSQL | Query store | It saves information in the azure_sys database. |
| Move a SQL Server app with minimal changes | Azure SQL, not MySQL or PostgreSQL | Each service matches its engine. |
| More control over server configuration and cost | Flexible server | Granular control and cost optimization options. |
Common exam traps
❓ Which MySQL edition is Azure Database for MySQL based on?
MySQL Community Edition.
❓ Is PostgreSQL a purely relational database?
It's object-relational: besides tables, it supports custom data types with non-relational properties.
❓ How many days of point-in-time restore does Azure Database for MySQL offer?
Up to 35 days, with 7 by default.
❓ Does Azure Database for PostgreSQL support every on-premises PostgreSQL extension?
No. It supports a set of extensions and modules, not all of them.
❓ Can I run server backups and restores with pgAdmin on Azure?
No. pgAdmin is for managing and monitoring, but those server functions are handled by Microsoft.
❓ What is the main reason for these services?
To let organizations already using MySQL or PostgreSQL move to Azure quickly, without significant app changes.
Did you get this topic?
Test what you just learned
A team has an application that stores geographic data as polygons and uses custom data types. They want a managed service on Azure without changing engines. What do they choose?
Ready to test "Open-source databases on Azure"?
Original questions, each with a full explanation.
Start a mock exam