Databases Azure SQL

Database compatibility level

A SQL database setting that controls engine behaviors and query-processing compatibility so applications can move versions without every feature changing at once.

Source: Microsoft Learn - View or Change the Compatibility Level of a Database Reviewed 2026-05-13

Exam trap: Changing Database compatibility level in production without checking the parent resource, identity path, monitoring evidence, or rollback procedure.
Production check: Can you name the owner, scope, and downstream dependency for Database compatibility level without guessing?

Practice this topic Microsoft Learn

Article details and learning context

Aliases: compatibility level, SQL compatibility level, Azure SQL compatibility level
Difficulty: fundamentals
CLI mappings: 4
Last verified: 2026-05-13

Related context No curated learning path mapped yet; use the category trail.

Compare related concepts Search related resources Browse the glossary

Understand the concept

In plain English

Database compatibility level is a SQL database setting that controls which database-engine behaviors, optimizer features, and Transact-SQL semantics are active for that database. Think of it as a compatibility switchboard for application behavior after an engine upgrade. In Azure, teams check database behavior, query plan risk, application regression testing, and release timing before they build, secure, automate, or troubleshoot the workload. It matters because it lets teams upgrade database platforms while keeping application behavior predictable until testing proves a higher level is safe. The entry should name the owner, scope, safe change path, and signals operators should trust.

Why it matters

Database compatibility level matters because application teams can separate platform upgrades from query behavior changes, reducing surprise regressions during modernization. Without a clear model, teams misread symptoms, troubleshoot the wrong layer, or make changes that appear local but affect security, reliability, cost, and performance together. In enterprise Azure environments, the term also gives architects, operators, developers, data owners, and auditors a shared language for ownership and evidence. That shared language helps teams write better runbooks, ask sharper questions, and avoid risky shortcuts during incidents, migrations, or modernization work. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.

Official wording and source

A SQL database setting that controls engine behaviors and query-processing compatibility so applications can move versions without every feature changing at once. Microsoft Learn places it in View or Change the Compatibility Level of a Database; operators confirm scope, configuration, dependencies, and production impact.

Open Microsoft Learn

Technical context

Technically, Database compatibility level sits at an Azure SQL database, SQL Managed Instance database, or SQL Server database rather than the entire Azure subscription. It is configured through T-SQL, SQL tooling, deployment scripts, release runbooks, and database inventory checks. Operators validate it by checking current compatibility value, Query Store baselines, application test results, failed query patterns, and approved rollback scripts. In design reviews, scope matters more than the name: changing this object can affect access, automation, telemetry, cost, and runtime behavior. Treat it as an architecture control with documented owners and safe rollback steps.

Exam context

Compare with

Database server / account Query Store SQL database

Where it is used

Where you see it

database properties in SSMS, Azure Data Studio, or T-SQL queries show the compatibility level alongside collation, containment, and other database-scoped settings before a release changes behavior
migration runbooks and upgrade test plans reference the compatibility level when teams move from older SQL Server versions to Azure SQL Database or SQL Managed Instance
Query Store reports, failed performance tests, and regression tickets mention compatibility level when a query plan changes after a database engine upgrade or feature rollout

Common situations

Decide how application data is stored, indexed, scaled, cached, and protected.
Troubleshoot connection failures, throughput pressure, indexing, backup, or regional availability.
Explain why one database capability changes cost, latency, consistency, or recovery behavior.
Prepare production changes with source, identity, network, and command context visible.

Illustrative Azure scenarios

These examples show how the concept can affect design and operations. They are illustrative scenarios, not customer claims.

Scenario 01 Database compatibility level in action for insurance Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Pioneer Mutual, a insurance organization, needed to solve a specific Azure platform challenge: its claims application moved to Azure SQL Database, but several vendor reports used older query patterns that failed performance tests at the newer level. The architecture team used Database compatibility level as the practical control point for a measurable production improvement.

Goals

Move the claims database without blocking the cloud migration
Keep month-end reporting within the two-hour SLA
Document a rollback path for query regressions
Raise compatibility only after vendor testing

Approach using Database compatibility level

The solution started with a current-state inventory, ownership review, and read-only evidence collection. Engineers then designed Database compatibility level into the operating model by connecting it with the relevant Azure resources, identity controls, monitoring signals, deployment artifacts, and support runbooks. architects inventoried every database level, restored a production copy, enabled Query Store, and tested reports at the proposed target level before the Azure SQL cutover. They kept the production database at the proven compatibility level during migration, recorded the approved T-SQL change, and scheduled a second release to raise the level after vendor fixes passed. Azure Monitor and Query Store baselines were attached to the change record so operators could prove whether the setting or application code caused any issue. The team tested the design in a lower environment, recorded the commands or configuration used, and promoted it through a controlled change window with rollback steps and stakeholder approval.

Potential outcomes

Migration finished before the data center exit deadline
Month-end reports stayed under one hour and forty minutes
No emergency database scale-up was needed during cutover
Vendor query fixes were validated before the final compatibility increase

What to learn

Compatibility level lets teams modernize the platform without forcing every application behavior change into the same release.

Scenario 02 Database compatibility level in action for retail Scenario, objectives, solution, measured impact, and takeaway.

Scenario

Trailstone Retail, a retail organization, needed to solve a specific Azure platform challenge: checkout analytics slowed after a SQL engine upgrade because a handful of revenue queries chose different plans under a higher compatibility level. The architecture team used Database compatibility level as the practical control point for a measurable production improvement.

Goals

Restore dashboard latency below five seconds
Avoid reverting the entire database migration
Identify exactly which queries regressed
Create a repeatable post-upgrade checklist

Approach using Database compatibility level

The solution started with a current-state inventory, ownership review, and read-only evidence collection. Engineers then designed Database compatibility level into the operating model by connecting it with the relevant Azure resources, identity controls, monitoring signals, deployment artifacts, and support runbooks. the database team compared Query Store data before and after the compatibility change, isolated three regressed statements, and used compatibility testing in staging to confirm the behavior. They temporarily returned the production database to the earlier level, tuned the affected indexes, and then raised the level again during a monitored window. Diagnostic settings streamed Query Store runtime data to Log Analytics so support could watch the result without changing application code. The team tested the design in a lower environment, recorded the commands or configuration used, and promoted it through a controlled change window with rollback steps and stakeholder approval.

Potential outcomes

Dashboard P95 latency dropped from twelve seconds to four seconds
Only three queries required tuning instead of a full rollback
The second compatibility change completed with no Sev1 incidents
The upgrade checklist became mandatory for all SQL releases

What to learn

Treat compatibility level as a controlled change because it can affect query behavior even when schema and application code stay unchanged.

Scenario 03 Database compatibility level in action for public sector Scenario, objectives, solution, measured impact, and takeaway.

Scenario

CivicLab Records, a public sector organization, needed to solve a specific Azure platform challenge: a records management system had to remain certified while its SQL databases moved from legacy virtual machines to managed Azure SQL. The architecture team used Database compatibility level as the practical control point for a measurable production improvement.

Goals

Preserve certification evidence during migration
Keep existing application behavior stable for six months
Prepare tests for a higher compatibility target
Give auditors a clear database setting inventory

Approach using Database compatibility level

The solution started with a current-state inventory, ownership review, and read-only evidence collection. Engineers then designed Database compatibility level into the operating model by connecting it with the relevant Azure resources, identity controls, monitoring signals, deployment artifacts, and support runbooks. engineers created an Azure SQL inventory showing server, database, compatibility level, Query Store state, and diagnostic settings for each workload. Production cutover used the current certified level, while a parallel test database ran the higher level with representative case searches and document imports. The team connected results to release gates, so auditors could see why compatibility was held, when it would change, and which metrics would prove safety. The team tested the design in a lower environment, recorded the commands or configuration used, and promoted it through a controlled change window with rollback steps and stakeholder approval.

Potential outcomes

Certification evidence was accepted on the first review
Application behavior stayed stable through the migration window
Test coverage reached ninety percent of critical database paths
Compatibility exceptions were reduced from nine databases to two

What to learn

A visible compatibility-level inventory turns a risky hidden database setting into an auditable modernization plan.

Azure CLI

Use CLI checks for Database compatibility level when you need repeatable evidence instead of a one-off portal view. Start with read-only commands, confirm the resource scope, and only run mutating commands after reviewing identity, cost, and rollback impact.

Useful for

Inventory Database compatibility level across subscriptions, resource groups, or workspaces before a migration, audit, or production change.
Capture current Database compatibility level configuration as evidence during incidents, access reviews, or release planning.
Compare dev, test, and production settings so automation drift is visible before users experience failures.

Before you run a command

Run az account show, confirm the tenant and subscription, and verify the operator identity has the intended scope.
Collect the exact resource group, workspace, server, account, database, or resource ID before running commands.
Prefer read-only commands first; review any command that changes security, cost, networking, or production state.

What the output tells you

Whether Database compatibility level exists at the expected Azure or Databricks scope and is owned by the right team.
Which identity, region, SKU, policy, network, monitoring, or dependency fields are currently configured.
Whether the issue is a missing resource, permission problem, naming mistake, policy drift, or unsupported dependency.

Mapped commands

Database compatibility level operational checks

direct

az sql server show --name <sql-server> --resource-group <resource-group>

az sql serverdiscoverDatabases

az sql db show --name <database> --server <sql-server> --resource-group <resource-group>

az sql dbdiscoverDatabases

az monitor diagnostic-settings list --resource <resource-id>

az monitor diagnostic-settingsdiscoverAI and Machine Learning

az sql db list --server <sql-server> --resource-group <resource-group> --output table

az sql dbdiscoverDatabases

Architecture context

Scope: an Azure SQL database, SQL Managed Instance database, or SQL Server database rather than the entire Azure subscription Configured through: T-SQL, SQL tooling, deployment scripts, release runbooks, and database inventory checks Connected services: Azure SQL Database, SQL Managed Instance, Query Store, automatic tuning, application releases, and Azure Monitor Validation signals: current compatibility value, Query Store baselines, application test results, failed query patterns, and approved rollback scripts

Security: Security for Database compatibility level starts with knowing the exact owner, scope, and access path. Review permission to change database settings, production approval, audit trails, least-privilege SQL administration, and controlled access to Query Store evidence before approving production changes. The main risk is treating the term as harmless configuration when it can expose data, widen administrative access, bypass governance, or hide privileged actions. Use least privilege, approved identity paths, private networking where required, diagnostic evidence, and change records. For sensitive workloads, confirm the setting aligns with data classification, compliance requirements, and the team responsible for emergency rollback. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.
Cost: Cost impact for Database compatibility level usually appears through indirect usage rather than the label itself. Watch extra testing environments, longer query runtimes after plan changes, emergency scale-ups during regressions, and duplicated tuning work when teams skip compatibility inventory. Poorly governed settings can create idle resources, noisy telemetry, duplicated storage, unnecessary retries, or emergency scale-ups that hide behind another team's budget. Tag resources consistently, review usage after releases, and separate production requirements from experiments. When cost rises, inspect the related compute, storage, monitoring, network, and support effort before assuming the term is only a configuration detail. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.
Reliability: Reliability for Database compatibility level depends on repeatable configuration and tested recovery behavior. Pay attention to known-good query plans, compatibility testing, rollback to the prior level, failover group behavior, and release windows that avoid mixing unrelated database changes. A small undocumented change can break jobs, applications, dashboards, or access paths long after the change window closes. Keep known-good settings in source control where possible, validate changes in lower environments, and capture before-and-after evidence. Operators should know which dependencies fail first, which alerts prove the issue, and which rollback step is safe when production behavior changes unexpectedly. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.
Performance: Performance for Database compatibility level is tied to workload shape, not just service limits. Review optimizer behavior, cardinality estimation, Query Store forced plans, plan regression alerts, and whether higher compatibility unlocks features without hurting critical queries before adding capacity or changing architecture. The right fix might be a policy change, better path design, query tuning, identity cleanup, or a different compute pattern rather than more resources. Measure before and after every important change, keep representative tests, and compare live telemetry with expected design. Good performance practice makes the term explainable under real production pressure. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.
Operations: Operations for Database compatibility level should focus on ownership, evidence, and safe repeatability. Standardize inventorying levels across fleets, tracking exceptions, scheduling test cycles, pairing changes with Query Store review, and recording exact T-SQL used during releases. Avoid relying on portal memory or individual notebooks as the only record of production behavior. Use read-only commands first, document resource identifiers, and connect runbooks to monitoring queries and source-controlled definitions. During incidents, operators should quickly answer who owns it, what changed, which dependency is affected, and what evidence proves the current state. That discipline reduces guesswork across platform, data, and application teams. Confirm the owning subscription, resource group, identity, network path, monitoring destination, and rollback procedure before treating the setting as production ready.

Common mistakes

Changing Database compatibility level in production without checking the parent resource, identity path, monitoring evidence, or rollback procedure.
Using portal screenshots as the only record when a repeatable CLI, template, or source-controlled definition is available.
Assuming a Databricks workspace setting, Azure resource property, and data-plane permission all have the same owner.