Architecture best practices for Azure Database for MySQL

Review known problems and service limitations that can affect reliability: Azure Database for MySQL flexible server has limitations that affect your reliability design. Review these constraints before you provision servers to avoid costly server re-creation. Confirm your zone-redundant HA requirements and network connectivity method at creation because you can't change these settings later.

Plan initial storage allocation carefully because you can only scale storage up and can't reduce it. Avoid using read replicas if you need stop/start functionality, and budget for dump and restore procedures when you plan major version upgrades. Select General Purpose or Memory Optimized tiers if your workload requires HA or read replicas.

Anticipate potential failure modes and plan mitigation strategies: Run failure mode analysis to identify potential database failure scenarios and pair each scenario with a targeted mitigation and recovery approach.

Implement redundancy through HA configurations: Use a HA configuration to distribute database components across fault domains and eliminate single points of failure. Deploy zone-redundant HA to place primary and standby servers in different availability zones that use zone-redundant storage (ZRS), which provides automatic failover within 60 to 120 seconds and zero RPO for committed transactions.

Consider same-zone HA when your workload accepts zone-level risk or prioritizes cost optimization by using locally redundant storage (LRS). This option protects against server-level failures but not zone outages.

Select zone-redundant HA mode when you provision the server because you can't change this setting later.

Design scaling strategies that maintain reliability during demand changes: Plan scaling operations to minimize availability impact and distribute load across database instances.

• Schedule compute tier changes during low-traffic periods to manage the brief restart as the workload migrates to a new instance. Use servers that have HA set up to reduce primary downtime because they perform scaling on the standby first. Rightsize initial storage allocations carefully. You can only increase storage capacity after provisioning, not reduce it.

• Use read replicas to distribute read workloads across up to 10 instances by using asynchronous binary log replication. Select General Purpose or Memory Optimized tiers for servers that need replicas, and monitor replication lag because it varies based on write volume on the primary.

Monitor reliability health indicators and set up failure detection alerts: Set up reliability-focused monitoring to detect service health degradation, replication problems, and resource saturation before they cause application failures. Track HA synchronization status and replication lag to check standby health.

Monitor resource saturation indicators for compute, memory, storage, and input/output (I/O) to reveal capacity pressure. Track active connections relative to connection limits to prevent exhaustion, and monitor aborted connections to detect connectivity problems.

Set alert thresholds on replication lag, storage utilization that's approaching capacity, and sustained compute or memory pressure so that you can scale before availability degrades.

Apply configuration and self-preservation techniques to protect against failures: Apply database configuration and platform protection measures to improve failover reliability and prevent configuration drift.

• Add explicit primary keys to all tables to improve replication performance and reduce failover duration.

• Set up connection retry logic in the data access layer to handle brief disruptions during failover, maintenance, and scaling events. Account for Domain Name System (DNS) propagation delay after failover by configuring applications to retry dropped connections.

• Apply Azure resource locks and Azure Policy to protect against configuration drift.

Design a disaster recovery (DR) strategy that includes backup and geo-redundancy capabilities: Define how backup, geo-redundancy, and cross-region recovery are used to meet RPO and recovery time objective (RTO) requirements.

• Set automated backup frequency and retention periods to achieve your workload's RPO. Adjust backup intervals for faster recovery of large databases.

• Use geo-redundant backup storage to replicate data to a paired region for regional outage protection. Use geo-restore to create a new server through universal geo-restore, or deploy cross-region read replicas as an alternative DR mechanism that requires manual promotion. Avoid custom port configurations if you need geo-restore or geo-replica capabilities.

• Select the appropriate redundancy level. Non-HA and same-zone HA deployments default to LRS, while zone-redundant HA deployments use ZRS. Turn on geo-redundant backup to replicate customer data outside the deployed region.

Review the security baseline for Azure Database for MySQL: The security baseline maps MySQL flexible server capabilities to Microsoft cloud security benchmark controls across network security, identity management, privileged access, data protection, logging, and backup. Review this baseline to understand available security controls and implementation approaches.

Use Azure Policy built-in definitions to enforce key controls including private endpoint usage, Secure Sockets Layer (SSL) enforcement, customer-managed key encryption, and Microsoft Entra authentication. Apply these policies to validate configuration compliance across server instances.

Segment access through role-based access control (RBAC): Set up layered access control across management and data planes to enforce least privilege access and restrict admin access.

• Use Azure RBAC to govern management operations and MySQL native privileges to control data plane operations. Flexible server doesn't support Azure RBAC for data plane access.

• Follow least privilege principles for database user accounts. Grant minimum necessary privileges for specific databases and tables, create separate accounts for each application component, and avoid broad privilege assignments.

• Limit Microsoft Entra admin configuration to one per server instance, and reserve the server admin account for emergency access only. Use Customer Lockbox to control Microsoft support access during support incidents.

Centralize identity management through Microsoft Entra ID: Microsoft Entra-only authentication eliminates password-based access entirely. Set up applications to authenticate by using short-lived tokens through managed identities or service principals. Assign a user-assigned managed identity and designate a Microsoft Entra admin for each server instance. Each server supports only one admin.

Use combined MySQL and Microsoft Entra authentication to support gradual migration from password-based to token-based access. Plan a transition timeline to Microsoft Entra-only mode after you migrate all connections because MySQL-only authentication lacks centralized identity governance.

Restrict network exposure by using connectivity controls: Use virtual network integration to embed the server into a customer-managed virtual network that has a private IP address in the delegated subnet, which eliminates public internet exposure. Set up the required subnet delegation before deployment and apply network security group (NSG) rules to control traffic flow.

Limit public access mode to development and migration scenarios only. Plan migration to private connectivity through private endpoints or server redeployment that uses virtual network integration. The connectivity mode can't change after server creation.

Encrypt data at rest and in transit: Protect stored data and network traffic through encryption mechanisms for both at-rest and in-transit scenarios.

• Data-at-rest encryption uses platform-managed keys by default for all stored data, backups, and transaction logs. Use customer-managed keys through Azure Key Vault or Azure Key Vault Managed HSM for extra control over the key life cycle.

• Enforce Transport Layer Security (TLS) 1.2 by default, and use TLS 1.3 on MySQL 8.0 and later for stronger security. Set the require_secure_transport parameter to control TLS enforcement. Federal Information Processing Standards (FIPS)-compliant cipher suites apply automatically.

• Trust only root certificate authorities (CAs) in client configurations to avoid connection failures. Azure manages server certificates through DigiCert Global Root G2 and Microsoft RSA Root CA 2017 root CAs. Intermediate CA rotations occur routinely without announcement.

Harden server configuration and reduce the attack surface: Beyond encryption controls, harden server configuration by adjusting parameter-level security settings to reduce attack surface.

Review and set security-related server parameters to strengthen your security posture. Assign custom ports during server creation only if needed. Geo-restore and geo-replica operations don't support custom port configurations.

Protect connection credentials and application secrets: Secure database credentials and secrets through identity-based authentication and centralized secret management to eliminate password exposure risks.

• Use identity-based authentication through Microsoft Entra ID managed identities to replace static passwords with short-lived tokens. Set connection strings by using Microsoft Entra authentication to eliminate embedded passwords.

• Store remaining MySQL native authentication passwords, including the server admin password from provisioning, in Key Vault and apply restricted access.

• Monitor Key Vault access closely when you use customer-managed key encryption because loss of access makes the server unavailable within 10 minutes. Create alerts for Key Vault access failures or key deletion events, and maintain key backups with documented recovery procedures.

Turn on security monitoring and audit logging: Use audit logs to track connection events, admin operations, data definition language (DDL) and data manipulation language (DML) statements, and table access. Turn on the audit_log_enabled server parameter and select event types based on your security requirements.

Route audit logs to Azure Monitor through diagnostic settings for Log Analytics, Azure Event Hubs, or Azure Storage destinations. Use the built-in Auditing workbook for security event visualization and create alerts for unusual connection patterns and failed authentication attempts.

Develop a cost model for database workload expenses: Azure Database for MySQL flexible server costs span multiple billing areas including compute, storage, I/O, and replication. Build a cost model that accounts for all these areas and their interdependencies.

• Compare pay-as-you-go pricing that charges hourly for compute and per gigabyte (GB) for storage against reserved instances that offer discounts for longer terms. Account for compute costs that vary significantly across tiers. Burstable is the lowest-cost option and Memory Optimized is the highest.

• Understand that storage is billed per GB per month and can only scale up after provisioning. Initial sizing decisions affect long-term costs.

• Expect HA to double compute cost because it deploys a standby replica that has dedicated storage and compute. Read replicas incur independent charges at the same rates as the primary server.

• Calculate backup storage costs. Backup storage is free up to 100% of provisioned server storage, but geo-redundant backup costs twice the locally redundant rate. Include extra feature costs that vary by tier and HA configuration.

Monitor database cost metrics and spending trends: Use Azure Monitor metrics to gain visibility into compute utilization, storage consumption, input/output operations per second (IOPS) usage, and backup storage, which directly correlate with service costs.

Monitor CPU percentage and memory usage to identify overprovisioned servers that consistently run below capacity. Review I/O utilization metrics to assess whether the current IOPS model aligns with actual consumption patterns. Analyze usage patterns over time to identify candidates for reserved instance purchases or tier changes.

Track storage usage trends because you can only increase storage after provisioning, not decrease it. Monitor the Backup Storage Used metric to understand backup costs relative to the free allowance.

Watch for storage autogrow events that increase costs as the server approaches capacity limits. Apply resource tags to servers for cost allocation across teams, projects, or environments.

Optimize server configuration to reduce ongoing costs: Rightsize compute tier, storage, and backup settings to match actual workload requirements and avoid overspending on unused capacity.

• Three compute tiers provide different cost-performance trade-offs. Use Burstable for intermittent workloads, General Purpose for balanced workloads, and Memory Optimized for memory-intensive workloads. Select the tier that matches your workload pattern to avoid overspending.

• Set backup retention to the minimum period that meets recovery requirements. This setting minimizes backup storage costs beyond the free allowance. Use locally redundant backup storage for workloads that don't require geo-disaster recovery. Geo-redundant storage doubles costs.

• Provision storage conservatively because storage can only scale up. Turn on autogrow as a safety net rather than overprovisioning initially.

• Compare hardware generation pricing across regions. The same tier can have different costs depending on which generations are available in each region.

Establish spending controls for database resources: Use Azure Policy, RBAC, and cost management to provide governance mechanisms that prevent costly database configurations and unauthorized resource changes.

Apply RBAC and use least privilege access to limit who can create, resize, or set up HA on database servers. Require approval workflows for changes that significantly affect costs like turning on HA, adding read replicas, or upgrading compute tiers.

Watch for storage autogrow events and IOPS autoscale consumption that can cause cost increases without explicit user action. Track read replica creation and configuration changes that add independent billing for compute and storage.

Optimize database configurations for each environment: Production and nonproduction environments have fundamentally different reliability, performance, and cost requirements that should drive database configuration choices. Set up each environment appropriately to optimize costs.

• Select the General Purpose or Memory Optimized tier for production based on workload performance requirements, and evaluate whether zone-redundant HA justifies double the compute cost. Size read replicas appropriately for their actual read workload rather than mirroring primary server specifications.

• Use the free tier for proof-of-concept and initial development. Reduce backup retention to 1-7 days and select locally redundant backup storage for nonproduction environments.

• Deploy development servers in cost-effective regions when data residency requirements permit.

Consolidate database resources to improve cost efficiency: Azure Database for MySQL flexible server supports hosting multiple databases on a single server instance, which distributes fixed infrastructure costs across workloads. Evaluate consolidation candidates based on compatible performance requirements, security isolation needs, and acceptable noisy-neighbor risk to improve resource utilization.

Consolidate workloads that have different peak usage patterns to maximize benefits from sharing server resources. Plan for cumulative storage growth because provisioned storage can only scale up.

Deploy read replicas for analytical and reporting workloads instead of provisioning separate servers. Size read replica compute independently from the primary server to match actual read workload requirements. The Burstable tier doesn't support read replicas, so use General Purpose or Memory Optimized tiers. Read replicas bill independently for both compute and storage.

Define server infrastructure by using code templates: Use infrastructure as code (IaC) through Azure Resource Manager templates (ARM templates), Bicep, Terraform, or the Azure CLI for declarative flexible server provisioning.

• Define HA mode, backup redundancy, network access mode, and MySQL version correctly in the initial deployment template. You can't change these settings after server creation unless you re-create the server.

• Parameterize the compute tier, SKU, storage size, IOPS, and backup retention for environment-specific variations.

• Deploy networking resources before server creation when you use virtual network integration, then set up diagnostic settings and RBAC as post-provisioning layers.

• Set up drift detection to identify parameters changed manually through the portal instead of through your IaC workflows.

Automate database schema deployments in pipelines: Integrate database schema changes into deployment pipelines by using migration scripts with validation gates and rollback capability. Use tools like Flyway, Liquibase, or custom scripts to version control schema changes and apply them through CI/CD pipeline stages.

Include premigration validation to check for GTID-incompatible operations when you target HA-enabled servers. HA-enabled servers don't support statements like CREATE TABLE ... SELECT. Test schema changes against nonproduction servers by using the Burstable tier to reduce costs and check compatibility.

Set up monitoring and logging: Set up Azure Monitor integration to collect metrics, resource logs, diagnostic settings, and workbook templates for operational visibility. Collect platform metrics for CPU, memory, storage, I/O, connections, and replication lag at one-minute frequency with 30-day retention.

Balance the detail level of logs with the performance impact because verbose logging affects server throughput. Set up the server logs feature for short-term file-based access alongside diagnostic settings for long-term analytics. Plan log routing to Event Hubs for security information and event management (SIEM) integration or Azure Storage for compliance archival.

Define operational procedures for routine and emergency response: Establish documented procedures that cover change management, maintenance coordination, and emergency recovery to ensure consistent operational practices across routine and critical scenarios.

• Require on-demand backups as a gate before schema migrations, parameter changes, and version upgrades to establish rollback points. Track backup consumption against the 50-backup-per-server limit and schedule cleanup of outdated backups.

• Reschedule maintenance events when they conflict with critical business periods. Only the General Purpose and Memory Optimized tiers support this capability. Set up Azure Service Health alerts to receive notifications about maintenance events and service incidents.

• Keep a runbook for recovering deleted servers within a five-day recovery window. Include escalation paths and validation steps. Document post-restore reconfiguration steps. Restored servers need HA reactivation, firewall rule updates, and parameter resets for time_zone and event_scheduler.

Establish safe deployment practices for server updates: Coordinate maintenance windows, patching strategies, and parameter changes to minimize disruption while maintaining security and functionality. Balance near-zero-downtime capabilities with proper testing and workload-aware scheduling.

• Set up a custom maintenance window for production workloads to control when maintenance occurs. To minimize disruption, the service manages maintenance by using near-zero-downtime patching. Use early-access maintenance policies for nonproduction servers to check patch compatibility before production rollout.

• Make sure all tables have primary keys for the near-zero-downtime patching mechanism. Set up retry logic in the data access layer to handle brief connection interruptions during maintenance, and schedule maintenance during low-workload periods.

• Distinguish dynamic parameters that apply immediately to new connections from static parameters that require server restart. Account for how static parameter changes restart both primary and standby servers when HA is turned on. Test parameter changes in nonproduction environments.

Automate management and monitoring tasks: Use built-in automation features and Azure Monitor integration to reduce manual operational overhead. Set up automated backups with adjustable intervals and retention periods to maintain RPOs. Review Azure Advisor recommendations periodically for optimization suggestions about performance, reliability, and cost.

Automate configuration management by using Azure CLI or REST API scripts to apply consistent server parameter configurations across server fleets. Set up automated compliance checks to compare current parameters against baseline configurations and detect drift. Schedule on-demand backups through automation before planned maintenance or change operations.

Run capacity planning: Your compute, storage, and IOPS decisions determine your maximum performance. Run capacity planning to evaluate vCore and memory needs based on query complexity, concurrent connections, and workload type. Account for InnoDB buffer pool memory allocation, which varies by compute size and determines cache efficiency. Also consider max_connections limits, which scale with available memory.

Estimate storage growth. You can only scale up allocation, not reduce it. Calculate IOPS requirements based on read/write ratios, transaction volume, and peak demands. Use Storage IO Percent and Storage IO Count metrics to define baseline usage patterns.

Plan for connection pooling when concurrent application connections approach max_connections limits. Identify read versus write workload ratios to determine whether read replicas provide the horizontal read scale that your workload requires. Include failover capacity in your planning.

Choose compute tiers and SKUs based on performance requirements: Azure Database for MySQL offers three compute tiers that have distinct performance characteristics and feature availability. Match tier selection to workload performance needs, scalability requirements, and feature dependencies.

• Select the General Purpose tier for production workloads that require sustained compute capacity without throttling. This tier provides balanced compute and memory with support for HA, read replicas, and accelerated logs. Use this tier for most production scenarios that require predictable performance and enterprise features.

• Choose the Memory Optimized tier when workloads require higher memory-to-vCore ratios for cache-dependent and high-concurrency scenarios. This tier delivers the lowest latency and highest queries per second (QPS). Use this tier for memory-intensive workloads that have large working sets or high-concurrency requirements.

• Limit the Burstable tier to development and testing environments because the CPU credit model throttles under sustained load. This tier doesn't support HA, read replicas, or accelerated logs, which aligns with nonproduction requirements.

• Account for IOPS limits that scale with compute size. Make sure InnoDB buffer pool allocation matches working set requirements because it varies by SKU. Select General Purpose or Memory Optimized tiers when you need HA, read replicas, or accelerated logs support.

Define a scaling strategy: Plan compute tier changes for low-traffic periods. These changes require server restarts that take 60-120 seconds, which causes brief workload disruption. Apply storage increases online, but remember that you can't reduce allocation. Use autoscale IOPS to provide automatic I/O scaling based on demand without manual intervention.

Account for read replicas by using asynchronous binary log replication, which introduces measurable lag between source and replica data. Select General Purpose or Memory Optimized tiers for replica support, and set up application-level routing because each replica has its own connection endpoint. Create replicas with specifications equal to or more than the source to prevent lag accumulation.

Monitor database performance metrics: Monitor CPU Percent, Memory Percent, and Storage IO Percent to identify resource saturation before it affects query performance. Monitor InnoDB buffer pool metrics like pages_data, pages_free, and read request ratios to measure cache efficiency and memory pressure.

The Slow_queries count identifies queries that exceed the long_query_time threshold for optimization. InnoDB Row Lock Time and Row Lock Waits identify lock contention that affects transaction throughput, and lock_deadlocks and lock_timeouts indicate concurrency conflicts that require query or schema changes.

Use Azure Monitor metrics, slow query logs, and the Query Performance Insight workbook for query-level visibility. Use Log Analytics for historical trend analysis through Kusto queries.

Run performance testing: Create load test workloads that reflect production query patterns, including read/write ratios, transaction complexity, and concurrency levels. Include peak period simulations to validate autoscale IOPS behavior and compute headroom. Use production-representative data volumes in tests because InnoDB buffer pool efficiency and query plans depend on data size.

Test compute tier scaling to measure restart duration and application reconnection behavior. Simulate read replica lag under load and verify storage autogrow activation. Capture baseline metrics for query latency, throughput, IOPS consumption, and connection count to compare across IOPS settings and detect cache sizing problems during growth.

Optimize database performance through configuration and features: Tune MySQL engine parameters and Azure-specific features based on workload characteristics to maximize query performance, reduce resource contention, and improve overall database efficiency.

• Set the InnoDB buffer pool size, which determines how much working data is cached in memory and directly affects the cache hit ratio. Set transaction log sizing to balance write throughput against crash recovery time. Select appropriate table storage strategy to affect performance and manageability for large databases.

• Set up connection pooling at the proxy or application layer to reduce connection creation and teardown overhead under high concurrency. To reduce capacity for active queries, monitor and close idle connections that consume server thread resources.

• Use accelerated logs to speed up transaction log operations by separating log I/O to high-performance storage. Check feature availability by compute tier and region, and benchmark performance gains to measure improvement for specific workloads.