This chapter covers Azure Database for MySQL and Azure Database for PostgreSQL, two fully managed relational database services in Azure. These services are part of the Azure Databases family and are essential for migrating or building open-source database workloads on Azure. For the AZ-900 exam, this falls under Objective 2.4: Describe Azure database services, which carries approximately 10-15% of the total exam weight. Understanding the differences between managed and unmanaged databases, deployment options (Single Server vs. Flexible Server), and key features like high availability and scaling is critical for exam success.
Jump to a section
Imagine you want to grow tomatoes for your restaurant. Option 1: You buy a piece of land (your own server), prepare the soil, plant seeds (install MySQL/PostgreSQL), water daily (apply patches), fight pests (security updates), and weed (optimize queries). Option 2: You rent a fully managed garden plot from a professional farmer (Azure Database for MySQL/PostgreSQL). The farmer prepares the soil, provides high-quality seeds, installs an automatic irrigation system (automated backups), sets up a greenhouse (high availability), and sends a weekly report on soil nutrients (performance insights). You only need to decide how many tomato plants (compute size) and how much water (storage). If a storm comes, the farmer has backup plants ready (geo-replication). You don't worry about soil erosion (disk failures) or pest outbreaks (vulnerabilities). The farmer also has a standard maintenance schedule (weekly patching) and guarantees your tomatoes will be ready 99.99% of the time (SLA). This is the core difference: you focus on your recipes (your application), not on farming (database administration).
What It Is and the Business Problem It Solves
Azure Database for MySQL and Azure Database for PostgreSQL are fully managed database as a service (DBaaS) offerings. They allow you to run MySQL and PostgreSQL databases in the cloud without managing the underlying infrastructure. The core business problem they solve is the operational overhead of traditional database administration: provisioning hardware, installing software, applying patches, managing backups, handling failovers, and scaling resources. For many organizations, this overhead consumes significant IT budget and time better spent on application development.
How It Works — Step-by-Step Mechanism
Provisioning: You create a database server instance via Azure portal, CLI, or ARM template. You choose the engine (MySQL or PostgreSQL), version, compute tier (Basic, General Purpose, Memory Optimized), storage size, and region. Azure automatically provisions the virtual machine, installs the database engine, and configures networking.
Connectivity: Your application connects using standard connection strings (e.g., JDBC, ODBC, Python connectors). Azure enforces SSL/TLS by default for secure connections. You can configure firewall rules to allow specific IP addresses or Azure services.
Backup and Restore: Azure automatically performs full backups weekly, differential backups daily, and transaction log backups every 5 minutes. Backups are stored in geo-redundant storage (by default) for up to 35 days. You can restore to any point within the retention period.
High Availability: Built-in high availability is provided at no extra cost with a 99.99% SLA (when configured with zone-redundant high availability in Flexible Server). This uses synchronous replication to a standby server in the same or different availability zone.
Scaling: You can scale compute (vCores) and storage independently with minimal downtime (usually under 60 seconds for compute scaling in Flexible Server). Storage auto-grows up to 16 TB.
Patches and Updates: Azure manages the underlying OS and database engine patching. You can configure a maintenance window for when patches are applied.
Key Components, Tiers, and Pricing Models
- Deployment Modes: - Single Server: Older deployment mode with limited features (no zone-redundant HA, no stop/start). Being deprecated in favor of Flexible Server. - Flexible Server: Newer deployment mode with more control (zone-redundant HA, stop/start, burstable compute, private network access via VNet integration).
- Compute Tiers: - Basic: Low-cost for dev/test, single vCore, no high availability. - General Purpose: Balanced compute and memory, suitable for most production workloads. - Memory Optimized: High memory-to-core ratio for in-memory workloads.
Storage: Up to 16 TB, with auto-growth option. IOPS are determined by storage size (e.g., 3 IOPS per GB up to 20,000 IOPS).
Pricing: Charged per hour based on compute (vCores), storage (GB/month), and backup storage (GB/month). Reserved instances offer up to 40% savings.
How It Compares to On-Premises
On-premises, you must buy servers, install and configure the database, manage backups (often with third-party tools), handle patching, and plan for capacity. Azure Database for MySQL/PostgreSQL eliminates all that. You get automatic backups, built-in high availability, easy scaling, and pay-as-you-go pricing. The trade-off is less control over the underlying OS and configuration parameters (e.g., you cannot access the file system).
Azure Portal and CLI Touchpoints
In the Azure portal, you can create and manage database instances under 'Azure Database for MySQL servers' or 'Azure Database for PostgreSQL servers'. Key blades include: Overview, Connection security (firewall rules), Replication (read replicas), Pricing tier, and Backup + Restore. Using Azure CLI:
az mysql flexible-server create --resource-group myRG --name myServer --location eastus --admin-user myAdmin --admin-password myPassword --sku-name Standard_B1ms --tier Burstableaz postgres flexible-server create --resource-group myRG --name myServer --location eastus --admin-user myAdmin --admin-password myPassword --sku-name Standard_D2ds_v4 --tier GeneralPurposeThese commands provision the server with specified compute, storage, and networking settings.
Create a Flexible Server
Navigate to Azure portal, select 'Azure Database for MySQL flexible servers' (or PostgreSQL), click 'Create'. Choose subscription, resource group, server name, region, MySQL/PostgreSQL version (e.g., 8.0 for MySQL, 14 for PostgreSQL). Select compute tier (Burstable for dev/test, General Purpose for production), storage size (start at 32 GB, auto-growth enabled). Configure admin username and password. Under 'Networking', choose 'Public access' (with firewall rules) or 'Private access' (VNet integration). Click 'Review + create' then 'Create'. Azure provisions the server in 5-10 minutes. Behind the scenes, Azure allocates a VM, installs the database engine, configures storage, and sets up networking.
Configure Firewall Rules
After creation, go to the server blade, select 'Networking'. Under 'Firewall rules', add a rule to allow your client IP address. For production, restrict to specific IP ranges or use Service Endpoints/Private Link. You can also allow Azure services (e.g., Azure App Service) to connect. Azure applies these rules at the network layer; any connection from an IP not in the list is rejected. For private access (VNet), you don't need firewall rules but must configure VNet integration during creation.
Connect from Application
Use a connection string that includes server name (e.g., myServer.mysql.database.azure.com), port (3306 for MySQL, 5432 for PostgreSQL), database name (default 'postgres' for PostgreSQL, you can create others), admin login, and password. Ensure SSL mode is required (default). For example, in Python using psycopg2 for PostgreSQL: conn = psycopg2.connect(host='myServer.postgres.database.azure.com', dbname='postgres', user='myAdmin', password='myPassword', sslmode='require'). Azure validates SSL certificates. Behind the scenes, the connection is routed through Azure's load balancer to the primary server.
Configure High Availability
In Flexible Server, you can enable zone-redundant high availability during creation or later (with downtime). Go to 'High Availability' blade, select 'Zone-redundant' (requires two zones in the same region). Azure provisions a standby server in another zone with synchronous replication. If the primary fails, Azure automatically fails over to the standby (typically under 120 seconds). Applications must reconnect using the same connection string. This provides 99.99% SLA. For Single Server, high availability is built-in but not zone-redundant (99.99% SLA claimed but with local redundancy).
Scale Compute or Storage
To scale, go to 'Compute + storage' blade. Change the number of vCores (e.g., from 2 to 4) or storage size (e.g., from 64 GB to 128 GB). For compute scaling, Flexible Server supports online scaling with minimal downtime (under 60 seconds). Storage scaling requires a restart for some changes. Azure redistributes data to new storage. You can also scale down. Scaling costs are prorated hourly. For Single Server, scaling compute may cause a longer downtime (minutes).
Scenario 1: E-commerce Migration from On-Prem MySQL
A mid-sized e-commerce company runs its product catalog and order management on MySQL 5.7 on a physical server that is nearing end of life. They want to migrate to Azure to reduce hardware costs and improve disaster recovery. They choose Azure Database for MySQL Flexible Server (MySQL 8.0) with zone-redundant high availability. The migration uses Azure Database Migration Service for near-zero downtime. After migration, they set up read replicas for reporting workloads. Cost: ~$300/month for a General Purpose 2 vCore, 64 GB storage instance. Without managed service, they would need to hire a DBA for patching and backups, costing $80,000/year. The migration is successful, but they initially forget to configure firewall rules for their app servers, causing connection failures — a common mistake.
Scenario 2: SaaS Application Using PostgreSQL
A SaaS startup providing analytics uses PostgreSQL for multi-tenant data. They need to scale quickly as customers onboard. They use Azure Database for PostgreSQL Flexible Server with auto-grow storage and burstable compute tier for development. For production, they use General Purpose with 4 vCores and 256 GB storage. They enable geo-redundant backup (read-access geo-redundant storage) for compliance. They also set up a read replica in a different region for disaster recovery. Cost: ~$500/month. A mistake they made: they didn't enable SSL enforcement initially, exposing data in transit. They corrected it by setting 'ssl_enforcement' to 'Enabled' in the server parameters.
Scenario 3: Gaming Leaderboard with High Write Throughput
A gaming company uses Azure Database for MySQL Flexible Server to store player scores and leaderboards. They need high write throughput. They choose Memory Optimized tier with 8 vCores and 512 GB storage. They enable connection pooling via ProxySQL on a separate VM to handle thousands of concurrent connections. They also use read replicas for leaderboard reads. Cost: ~$1,200/month. They initially provisioned too small storage (64 GB) and faced IOPS throttling. After increasing to 256 GB, IOPS improved. They also learned that the 'innodb_buffer_pool_size' parameter can be tuned for better write performance.
What AZ-900 Tests
Objective 2.4: Describe Azure database services. Specifically, you need to understand the difference between Azure Database for MySQL/PostgreSQL (PaaS) and running MySQL/PostgreSQL on Azure VMs (IaaS). You must know the deployment options: Single Server vs. Flexible Server, and when to use each. Also, know key features: built-in high availability (99.99% SLA with zone-redundant HA), automated backups (up to 35 days retention), automatic patching, and scaling of compute/storage independently. Understand that these services are for relational (SQL) workloads, not NoSQL.
Common Wrong Answers
'Azure Database for MySQL supports NoSQL workloads.' Wrong — it's relational. Candidates confuse MySQL with MongoDB (NoSQL).
'Single Server supports zone-redundant high availability.' Wrong — only Flexible Server supports zone-redundant HA. Single Server has built-in HA within a single zone.
'You must manage patches manually.' Wrong — Azure manages patching automatically. Candidates think of IaaS.
'Backups are stored locally only.' Wrong — by default, backups are geo-redundant (RA-GRS).
Specific Terms and Values
Flexible Server: Newer, more features, zone-redundant HA, stop/start, VNet integration.
Single Server: Older, being deprecated, no zone-redundant HA, limited scaling.
SLA: 99.99% with zone-redundant HA; 99.99% without (but lower effective availability).
Backup retention: Up to 35 days (configurable).
Storage: Up to 16 TB with auto-grow.
Compute tiers: Burstable, General Purpose, Memory Optimized.
Edge Cases and Tricky Distinctions
Connection pooling: The exam may ask if these services include a built-in connection pooler. They do not; you must use an external one like ProxySQL or PgBouncer.
Private access: Flexible Server supports VNet integration (private access), Single Server does not (only public access with firewall rules).
Read replicas: Both support read replicas (up to 5 per server), but only Flexible Server supports cross-region replicas.
Migration: Azure Database Migration Service is used for online migrations, not covered in depth on AZ-900 but good to know.
Memory Trick
'Flexible is better' — Flexible Server has more features (HA, stop/start, VNet). If the question mentions 'zone-redundant' or 'private access', pick Flexible Server. If 'legacy' or 'deprecated', pick Single Server.
Azure Database for MySQL and PostgreSQL are fully managed PaaS relational database services.
Two deployment modes: Single Server (legacy) and Flexible Server (newer, more features).
Flexible Server supports zone-redundant high availability (99.99% SLA).
Automated backups with up to 35 days retention and geo-redundancy by default.
Compute and storage can be scaled independently with minimal downtime.
Supports read replicas (up to 5) for read-heavy workloads.
Connection pooling is not built-in; use external tools like ProxySQL or PgBouncer.
Use Azure Database Migration Service for online migrations from on-premises.
These come up on the exam all the time. Here's how to tell them apart.
Azure Database for MySQL/PostgreSQL (PaaS)
Fully managed: automatic patching, backups, HA
99.99% SLA with zone-redundant HA
Scaling with minimal downtime
Built-in security: SSL enforcement, firewall, VNet integration
Pay per hour for compute and storage
MySQL/PostgreSQL on Azure VM (IaaS)
Full control over OS and database configuration
You manage patching, backups, HA manually
SLA depends on VM availability (99.95% for single VM)
You configure security (firewall, SSL) yourself
Pay for VM, storage, and licensing (if applicable)
Mistake
Azure Database for MySQL and PostgreSQL are the same as running MySQL/PostgreSQL on a VM.
Correct
They are fully managed PaaS services. You do not manage the OS, patching, or backups. On a VM (IaaS), you have full control but must manage everything yourself.
Mistake
Single Server offers zone-redundant high availability.
Correct
Single Server does not support zone-redundant HA. Only Flexible Server offers zone-redundant HA. Single Server has built-in HA within a single datacenter.
Mistake
You can stop and start a Single Server to save costs.
Correct
Single Server does not support stop/start. Flexible Server does. Stopping a Flexible Server stops billing for compute, but storage and backups still incur charges.
Mistake
Backups are only kept for 7 days by default.
Correct
Default backup retention is 7 days, but you can configure it up to 35 days. Backups are geo-redundant by default (RA-GRS).
Mistake
Azure Database for MySQL can be used for NoSQL workloads like MongoDB.
Correct
It is a relational database service for SQL workloads. For MongoDB, use Azure Cosmos DB for MongoDB API.
Single Server is the older deployment mode with limited features: no zone-redundant HA, no stop/start, and public access only. Flexible Server is the newer mode that supports zone-redundant HA, stop/start, VNet integration, and more compute tiers. Microsoft recommends using Flexible Server for new deployments. For the exam, know that Flexible Server is the preferred option for production workloads requiring high availability and private access.
No. Azure Database for MySQL is a relational database service that uses SQL. For NoSQL workloads, use Azure Cosmos DB (which supports MongoDB API). The exam tests your ability to match workloads to the correct database service. A common trap is choosing MySQL for a document database scenario.
The SLA is 99.99% uptime guarantee. Without zone-redundant HA, the SLA is also 99.99% but with a lower effective availability due to single-zone failures. For the exam, remember that Flexible Server offers 99.99% SLA when configured with zone-redundant HA.
Azure automatically performs full backups weekly, differential backups daily, and transaction log backups every 5 minutes. Backups are stored in geo-redundant storage (RA-GRS) by default. You can configure retention from 7 to 35 days. Restores can be to any point within the retention period. This is a managed service feature; you don't need to write backup scripts.
Yes, you can scale compute (vCores) and storage independently. For Flexible Server, compute scaling is online with minimal downtime (under 60 seconds). Storage scaling may require a restart. Storage can be increased up to 16 TB with auto-growth. Scaling down is also possible. Charges are prorated hourly.
The default port is 5432. For MySQL, it is 3306. When connecting from an application, you must specify the port and require SSL. The exam may ask about default ports as a basic fact.
By default, SSL enforcement is enabled. You can disable it in the server parameters, but it is not recommended for security. Azure provides certificates that must be downloaded for client connections. For the exam, know that SSL is enforced by default for secure connections.
You've just covered Azure Database for MySQL and PostgreSQL — now see how well it sticks with free AZ-900 practice questions. Full explanations included, no account needed.
Done with this chapter?