Tier, Terraform, HA: turnkey Azure SQL Database setup

Configuring Azure SQL Database: where infrastructure breaks

Configuring Azure SQL Database starts not with choosing a service tier, but with answers about the workload profile: who reads, who writes, and what will happen if a node fails.

Migrating a database to the cloud goes smoothly, while bottlenecks under load appear later — when reporting and background jobs begin competing with transactions for the same resources.

The first failure is growth in read load. Heavy analytical queries go to the same instance that serves writes, and checkout operations suffer first.

This is solved not only by scaling up the tier: some reads are moved to read-only replicas, reports to a separate copy or elastic pool, and hot queries receive covering indexes.

The second source is blocking. A long transaction holds rows, the application waits, timeouts grow in a cascade.

We check snapshot-based isolation, transaction lifetime, query plans, and wait statistics: more often the cause is implicit type conversions and a missing index for a specific filter, not insufficient capacity.

The third is database high availability without a failover plan. A geo-replica or failover group is configured, but no one has checked how long it will take the application to come up after an incident or whether the driver can reconnect.

A separate risk is manual changes: the Terraform description diverges from the actual state, and the next deployment rolls back the settings.

The fourth is untested backups: retention period, point of recovery, and restore to a separate server have not been rehearsed by anyone. A backup from which a database has never been restored is not a guarantee. We break down how these four areas are closed by configuration, procedures, and drills.

What changes after implementation: downtime, recovery, failures

After configuring Azure SQL Database, the environment is described as code and behaves identically across all environments: replicas in different availability zones, automatic failover, and an application that survives it without manual intervention.

The downtime window during planned maintenance and node failure shrinks from hours to minutes, and recovery time becomes a predictable value rather than the result of troubleshooting.

What goes away permanently

A whole class of failures is closed at the configuration level: data loss due to missing backups, downtime waiting for an administrator, and settings divergence between environments due to manual changes.

Point-in-time restore is a standard procedure: the point is selected from the transaction log, the backup retention policy is documented and tested in a test environment, so the time to return to a working state is known in advance.

Predictable load and volume

Load becomes predictable when parallelism, connection pool size, and the service profile for peak queries are bounded. The wait log and query statistics show degradation before users notice it.

At the same time, data volume does not drag costs along with it: hot data remains in the operational part, historical data goes to an archive with compression and partitioning, and old snapshots are deleted according to the retention policy. Storage costs stop growing, and the backup registry shrinks several times over.

Comparison of deployment models and service tiers

In Azure SQL Database, the choice is made along two axes: the deployment model — single database, elastic pool, managed instance — and the service tier within it.

A single database provides isolation and predictable application behavior, a pool aggregates shared reserve across dozens of databases with time-shifted peaks, and a managed instance opens up server-level capabilities.

The service tier determines what moves into the platform: in General Purpose, storage is moved outside the compute node, so response time is sensitive to I/O latency; in Business Critical, a local layer and a second readable replica are added; Hyperscale quickly expands storage and restores from snapshots.

In-memory logic, cross-database queries, and SQL Agent jobs are not available everywhere — these are the compatibility limitations that are checked before migration.

Changing the deployment model is almost always one-way, so we fix the configuration before migration: where a pool is needed, where a tier with a local replica is needed, and where a managed instance is indispensable. The application code remains the same — only platform parameters change.

Configuration and implementation: stages from audit to handover

The work follows the same sequence for both a new database and migrating an existing one: first we record facts about the workload, then we design, configure, and only after validation hand it over to operations.

This rules out the typical scenario where high availability is assembled from a template and falls apart under the real query profile of Azure SQL Database.

1. Workload audit. We capture statistics on waits, blocking, data growth, and hourly peaks, and analyze heavy query plans — the service tier is selected based on the profile, not by eye.
2. High availability design. We determine exactly what must survive a failure: a zone, a region, loss of a replica. We choose the replica topology, backup model, and target recovery time.
3. Configuration and infrastructure as code. The server, database, access rules, and private connections are described in Terraform: deployment is reproducible from scratch, changes go through review, and no manual edits remain in the console.
4. Failure testing. Before moving to production, we run forced failover, zone failure, and degradation under load, recording actual recovery time and application behavior.
5. Switchover plan. We document the procedure with roles and decision points: what automation handles, what the duty engineer confirms according to an individual calculationPost-implementation check and handover. We capture metrics on production traffic, compare them with targets, and hand over the architecture, configuration, switchover procedure, and operations checklist.

Configurations, high availability designs, and procedures

After project delivery, your team is left not with a set of verbal agreements, but with documentation by which the infrastructure can be operated, expanded, and handed over to another engineer.

We record the state of the Azure SQL Database as code and attach designs, policies, and procedures to it — all in your repository, with change history and review.

• Infrastructure description in Terraform: servers, databases, pools, network rules, and service tier parameters — the configuration is reproducible, changes go through review rather than manual edits in the console.
• Infrastructure designs: placement topology, replication, failover to a backup region, application entry points, connections to private networks and monitoring.
• Backup policy: retention depth for full and differential backups, schedule, regular restore testing in a test environment with time measurement.
• Access procedure: role matrix, separation of administrative and read permissions, granting and revocation procedure, connection only through a secure gateway.
• Operations documentation: planned maintenance, changing the service tier and scaling, troubleshooting typical failures, and actions for degradation.
• Disaster recovery scenario: rollback to a restore point, failover to a replica, and the criteria by which the duty engineer makes a decision.
• Schema change procedure: rules for applying migrations, testing in a test environment, and the procedure for rolling back an unsuccessful release.

Case study: recovery time and peak load before and after

A service with short peaks: the load was concentrated in several windows per day, and the database on the general-purpose tier went into a wait queue during those hours.

Response time dropped, some queries were terminated due to timeout, and a node failure was handled manually — recovery stretched over hours.

We migrated the database to the managed service Azure SQL Database and described the topology as code: the service tier, read replicas, backup rules, and failure handling procedure are now reproduced from the description rather than assembled manually.

The staging and production environments stopped diverging — configuration drift caused half of the nighttime troubleshooting.

What changed in numbers

What this gave the team

The duty engineer stopped being a bottleneck: a typical failure is resolved according to the instructions rather than by looking for the guilty component. The freed-up time went to product development, not to firefighting peaks and manual configuration edits.

How does a managed instance differ from a single database?

A managed instance is almost a full-fledged SQL Server in a private subnet: cross-database queries using three-part names, SQL Agent jobs, linked servers, and standard backup and restore all work.

A single database is a service for one schema: it cannot see neighboring databases, and administration comes down to the settings of the database itself and its service tier.

The first selection criterion is application compatibility.

If the code grew out of a monolith and accesses a neighboring database using a three-part name, and nightly processing depends on SQL Agent jobs, migrating to a single database will require rewriting those accesses and moving the jobs outside.

The reverse migration has no such limitations: a database from a managed instance is moved to a separate service without code changes, so starting with an instance is safer.

The second criterion is networking. A managed instance lives in your virtual network, has no public address, and traffic comes only from private addresses.

If the security procedure requires a closed environment, this immediately rules out scenarios with external connections to a single database.

The third criterion is the service model. An instance reserves resources for itself, and neighboring databases do not affect your workload; a single database is more flexible in service tier and pauses when idle, but shares capacity with others. Terraform describes both models declaratively — service tier, redundancy zone, failover group.

Hence the working rule: we migrate a system with cross-connections to a managed instance, and we stand up a new service with one schema on a single database. We make the decision based on application requirements and security procedures, not habit.

Common technical doubts and applicability boundaries

We establish the applicability boundaries before starting: whether a dedicated database fits the workload profile or an elastic pool is more cost-effective, what level of redundancy is needed, and how the application will behave on read-only replicas. Below are answers to the questions that most often come up during assessment.

How does switchover work, and is rollback possible?

Switchover is managed: we stop writes, drain the replication queue, verify checksums, change the connection string, and return traffic. Rollback remains available until the source environment is decommissioned — we define the point of no return in the procedure in advance.

What about access, updates, and high availability testing?

We distribute access by roles through a unified directory, service accounts only for specific tasks, and secrets are stored in a secure vault rather than in configuration files.

Maintenance follows an agreed window; the update procedure describes the sequence and notification timing.

We conduct high availability testing before handover: we simulate node and region failure, measure recovery time and data completeness, and document the result in a report. If the scenario does not meet target metrics, we change the configuration before acceptance, not after.

Let's discuss the task: what will be included in the first-stage plan

To make the timeline estimate based on facts, we start the first stage with an analysis of the current state: which tier and purchasing model are used, how the read and write load is distributed, where the I/O and blocking bottlenecks are, and how the infrastructure is currently deployed.

Then we evaluate target configuration options and only after that give timelines — otherwise the number is pulled out of thin air and falls apart at the first change in load.

With the estimate and work plan, we return with a specific set of artifacts:

• target configuration design: tier for the workload profile, purchasing model, scaling parameters, and the ceilings the database will start hitting;
• Terraform modules for deploying the database, access rules, and related resources — with versioning and a clear way to move the configuration between environments;
• backup and recovery policy: backup retention periods, point-in-time restore, verification that the backup actually restores;
• high availability design: replication, failover procedure for a region failure, expected downtime, and data loss point;
• monitoring and alerts: consumption metrics, waits, deadlocks, transaction log growth — with thresholds that trigger a signal, not after-the-fact troubleshooting;
• stage-by-stage work plan with timeline estimates, roles, and acceptance points at each step;
• operations procedure for your team: routine operations, rollback, what to do during degradation.

Describe the task — the current configuration, constraints, and what hurts most. We will return with a timeline estimate and a work plan for the first stage.