Azure Database for MySQL Without the Avoidable Pain

Provisioning a managed MySQL server in Azure takes a few minutes and a handful of dropdowns. The instance that results will work. Whether it is still working comfortably a year later is decided almost entirely by three of those dropdowns, and all three are expensive to revisit once data and applications depend on them.

1. Networking: public access or private access

The flexible server deployment model offers two connectivity methods, and the choice is effectively permanent.

• Public access gives the server a public endpoint protected by firewall rules that list permitted source addresses. Simple, works from anywhere you allow, and the endpoint exists on the internet.
• Private access injects the server into a virtual network subnet. There is no public endpoint. Access comes from inside the network, or through peering, a VPN or ExpressRoute.

The point most people discover too late: you choose this at creation and cannot switch afterwards — moving between them means creating a new server and migrating. If there is any chance the workload will need network isolation, choose private access from the start, even if it is less convenient during development.

Two related settings worth deciding at the same time: require encrypted connections, and set a minimum TLS version. Both are easier to turn on before clients exist than after.

2. Character set and collation

MySQL's historic utf8 character set stores at most three bytes per character, which excludes anything outside the basic multilingual plane — most notably emoji, and several scripts. It is a legacy alias, not a full UTF-8 implementation.

Use utf8mb4. For collation, utf8mb4_0900_ai_ci on MySQL 8 gives accent- and case-insensitive comparison over the modern Unicode rules.

Set it at the database level so new tables inherit it:

CREATE DATABASE appdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Converting later is possible but genuinely unpleasant: every table and every column must be altered, indexes on long varchar columns can exceed key length limits under the wider encoding, and comparisons between columns with different collations produce errors that surface as failing queries rather than as a clear migration problem.

Verify what you actually have rather than what you asked for:

SELECT table_schema, table_name, table_collation
  FROM information_schema.tables
 WHERE table_schema = 'appdb'
   AND table_collation NOT LIKE 'utf8mb4%';

3. Connections and pooling

A managed MySQL server's maximum connection count is tied to its compute size. Small tiers permit far fewer concurrent connections than people expect, and each connection consumes memory on the server whether or not it is doing anything.

Application frameworks default to a pool per process. Multiply that by the number of processes, containers or serverless instances and the arithmetic gets bad quickly: ten containers with a pool of ten each is a hundred connections before any traffic arrives.

Three practical measures:

• Size the pool to the server, not to the application. Decide the total connection budget first, then divide it among instances.
• Set an idle timeout below the server's wait_timeout. Otherwise the server closes a connection the pool still believes is alive, and the next query fails with a broken-pipe error that looks random.
• Consider a proxy for high instance counts. Serverless and heavily scaled workloads benefit from a connection proxy far more than from a bigger database tier.

-- What the server allows, and what is currently in use.
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

Backups and the restore you have not tested

Automated backups run with a configurable retention window and support point-in-time restore. That is genuinely useful and it is not a backup strategy on its own, for two reasons.

First, restore creates a new server. Your application does not follow it. Recovery therefore involves a connection-string change or a DNS change, and the time that takes is part of your recovery time whether or not you have measured it.

Second, an untested restore is a belief, not a capability. Restore to a scratch server once, time it, and write down the steps. Doing that for the first time during an incident is how a thirty-minute outage becomes a four-hour one.

For anything that must survive a regional problem, geo-redundant backup or a read replica in another region is a separate decision — and one that also has to be made before you need it.

Schema habits that pay off

• Explicit utf8mb4 on every table. Do not rely on a default someone might change.
• Timestamps in UTC. Store UTC, convert at the edge. Every alternative eventually produces an argument about an hour that happened twice.
• Index for the queries you run. Read the execution plan rather than guessing; EXPLAIN is cheap and specific.
• Beware the index key length limit under utf8mb4. A varchar(255) costs four bytes per character to index. Prefix indexes or shorter columns are usually the answer.
• Migrations are files, applied deliberately. Never as an automatic step on application start — that turns a deployment into a schema change nobody reviewed.

Troubleshooting

Symptom	Likely cause	Check
Connection refused from a new client	Firewall rule missing for that source address	Server firewall rules; remember that outbound addresses can change
Intermittent broken-pipe errors	Pool idle timeout longer than the server's wait_timeout	Compare the two values
Too many connections under load	Pool size multiplied across instances	Threads_connected against max_connections
Emoji or non-Latin text stored as question marks	utf8 rather than utf8mb4, on the column, table or client connection	Table collation, and the client's charset
Illegal mix of collations	Two columns compared across different collations	The collation of both columns in the join or comparison
Queries slow after data growth	A query that was fine on a small table is now a full scan	EXPLAIN on the actual query with production-shaped data

Frequently asked questions

Can I switch a server from public to private access later?

No. The networking method is fixed at creation for flexible server. Changing it means creating a new server with the desired configuration and migrating the data.

Should I use utf8 or utf8mb4?

utf8mb4, without exception for new work. MySQL's utf8 is a three-byte subset that cannot represent the whole of Unicode.

Does point-in-time restore overwrite the existing server?

No — it creates a new server from the backup at the chosen time. The original is untouched, which is helpful, and it means your application has to be pointed at the new one.

How do I connect without a password in the connection string?

Azure Database for MySQL supports Microsoft Entra authentication, which lets an application authenticate with a managed identity instead of a stored password. It takes more setup than a password and removes an entire class of credential-handling problem.

Why is my small tier slow under modest load?

Compute tier governs CPU and memory; storage size governs provisioned IOPS. A database that is CPU-comfortable can still be IO-starved because its disk is small. Check both before scaling either.

The ten minutes worth spending

Before creating the server, write down three answers: does this workload need network isolation, what character set and collation will every table use, and what is the total connection budget across all application instances. Those three answers cost ten minutes now and save a migration later — which is the only kind of trade in database work that is reliably worth making.