Start with the plain meaning of each word, then see where this product sits among Microsoft’s other database choices.

What a relational database is

A relational database stores information in tables, which look like spreadsheets. Each table has columns that describe the kind of data, such as a customer name or an order date, and rows that hold one record each. Tables are connected to one another by shared values. An order row carries a customer number, which points to a row in the customer table. You ask questions of the data using a language called SQL, short for Structured Query Language, for example “show me all orders from this customer in the last month, newest first”. The database finds the answer for you and promises that the data stays consistent even when many people change it at the same time.

What “Azure SQL Database” adds

Azure SQL Database is a platform as a service, often shortened to PaaS. It means the cloud provider runs the platform underneath, and you use only the top layer. It is built on the same database engine as Microsoft SQL Server, the long-established product used in offices and data centers worldwide, so the language is the same Transact-SQL, or T-SQL, and most tools and skills carry over. The difference is that Azure manages the machines, the operating system, the database software, the updates, the backups and the failover. The engine is kept on a current version, which means new features arrive without you running upgrade projects.

The Azure SQL family

Azure offers three main ways to run SQL Server technology, and choosing the right one is the first design decision.

What problems it solves

It removes routine administration: patching, backups, health monitoring and hardware replacement. It provides built-in high availability, so a failing server does not mean an outage. It lets you resize in minutes instead of ordering hardware. It offers intelligent features such as automatic plan correction and tuning advice. And it ties into the rest of Azure for identity, networking, monitoring and security.

Every feature of Azure SQL Database answers a problem that earlier systems struggled with. A brief look back explains the design.

Files, then relations

Early business programs kept data in plain files, each program with its own format. Changing a format broke every program that read it, and two programs could easily disagree about the same customer. In 1970 a researcher named Edgar Codd proposed the relational model, which separates what the data means from how it is stored, and makes questions about the data independent of storage details. Within a decade, products built on the idea appeared, and the language SQL became the common way to talk to them.

The promise of transactions

Relational databases became trusted because of transactions. A transaction is a group of changes that must all happen or none happen. Moving money from one bank account to another subtracts from one row and adds to another. If the system crashes between the two steps, the database must undo the first, otherwise money disappears. The usual guarantees are summarized as ACID: Atomicity, meaning all or nothing; Consistency, meaning rules stay true; Isolation, meaning simultaneous users do not see each other’s half-finished work; and Durability, meaning a confirmed change survives a crash.

SQL Server and the move to the cloud

Microsoft SQL Server grew from the late 1980s into a mainstream engine in offices and data centers. When cloud computing arrived, running SQL Server on rented virtual machines was the first step, but it kept all the administration work. Microsoft then built a database service where the engine itself is hosted and operated for you, which became Azure SQL Database. Over the years the service gained elastic pools for sharing resources between many databases, serverless compute that pauses when idle, and the Hyperscale architecture for very large databases.

A few names appear in every conversation about Azure SQL Database. Learn them once and the portal, the documentation and error messages become readable.

The logical server

The name is slightly misleading. A logical server does not run your queries. It is the address and the rulebook. Every logical server has a name that ends in .database.windows.net, and applications connect to that name. The server stores who the administrators are, which network addresses are allowed in, where audit logs go, and which databases belong together. Think of it as the building’s front desk, while the databases are the apartments inside.

Single database and elastic pool

A single database has its own dedicated amount of compute and storage, chosen by you. An elastic pool is useful when you have many databases with unpredictable, uneven activity, such as one per customer in a software-as-a-service product. At any moment only a few are busy. Pooling lets all of them share one budget of compute, with limits you set per database so none can take everything.

The two ways to buy compute

Azure SQL Database offers two purchasing models. The vCore model lets you pick the number of virtual cores, the memory size and the storage separately, with a clear view of hardware. The older DTU model bundles compute, memory and input and output into a single blended unit, the Database Transaction Unit, with fixed packages named Basic, Standard and Premium. The vCore model is more flexible and is the one most new designs use. The DTU model remains useful for simple, small workloads where a ready-made package is easier to reason about.

The tier you choose decides how data is stored, how copies are kept, how fast it feels and how much it costs. This is the most important sizing decision after the model itself.

General Purpose

The General Purpose tier is the balanced, budget-friendly choice for most business applications. Compute runs on a node, and the data files live on highly available remote storage that is separate from the compute. If the node fails, Azure starts another node, attaches the same storage and the database is back, usually within a short time. Because storage is remote, each storage operation takes a little longer than local disks would.

Business Critical

The Business Critical tier is built for heavy transaction loads and the lowest delay. Data lives on fast local solid-state disks attached to the compute node. Four copies of the database run on four nodes in a cluster: one primary that accepts writes and three secondaries that continuously replay every change. A write is confirmed only after enough secondaries have safely received it. If the primary fails, one of the secondaries takes over almost immediately. One secondary can also answer read-only queries, so reports do not slow down the main workload. This tier supports in-memory features designed for very fast transactions.

Hyperscale

The Hyperscale tier separates the pieces of a database into independent services so that each can grow on its own. Compute nodes run the engine. A log service receives and hardens every change. A set of page servers each hold a slice of the data files and serve pages to the compute nodes. The durable data lives in Azure storage. Because backups are snapshots of storage and restores copy references rather than every byte, a very large database can be backed up and restored in minutes, and growth to 128 terabytes does not slow these operations. Extra read replicas can be added to scale out reading.

flowchart TD
  subgraph GP["General Purpose"]
    GC["Compute node"] --> GS["Remote data and log files in Azure storage"]
  end
  subgraph BC["Business Critical"]
    BP["Primary with local SSD"] --> B1["Secondary 1"]
    BP --> B2["Secondary 2"]
    BP --> B3["Secondary 3 readable"]
  end
  subgraph HS["Hyperscale"]
    HC["Primary compute"] --> LS["Log service"]
    LS --> PS1["Page server 1"]
    LS --> PS2["Page server 2"]
    HC --> PS1
    HC --> PS2
    PS1 --> ST["Durable Azure storage"]
    PS2 --> ST
    HC --> HR["Read replicas"]
  end

Provisioned versus serverless compute

Inside the vCore model, compute can be provisioned, meaning a fixed number of cores running and billed all the time, or serverless, available in General Purpose and Hyperscale. Serverless lets you set a minimum and maximum number of cores. The database scales between them automatically as load changes, and compute is billed per second for the cores actually used. After a quiet period you choose, a General Purpose serverless database can pause, in which case you pay only for storage. The next connection wakes it, which takes a short time, so the first user after a pause waits a little.

Your application only knows a server name. Behind that name sits a small set of cooperating services that find your database and keep it healthy.

flowchart LR
  APP["Application with connection string"] -->|"1 Resolve server name and connect to port 1433"| GW["Regional gateway"]
  GW -->|"2 Authenticate and check firewall"| CTRL["Control plane: placement, health, failover"]
  GW -->|"3 Proxy or redirect to the node that hosts the database"| NODE["Database node running the SQL engine"]
  APP -.->|"3b Redirect mode connects straight to the node"| NODE
  NODE --> STOR["Data and log storage for the chosen tier"]
  CTRL -.-> NODE
  CTRL -.-> REP["Replicas on other nodes"]
  NODE --> REP
  NODE --> MON["Telemetry, backups and audit streams"]

The gateway

When your program connects to the server name, DNS points it to a gateway in the database’s region. The gateway is a fleet of stateless machines that accept connections on the standard SQL port, 1433. It checks the request against the server’s firewall, confirms which database you want, and finds the node that currently hosts that database. Because gateways keep no important state of their own, there can be many, and a failed gateway is simply replaced. The set of gateway addresses for each region is published, which matters when you write firewall rules.

Connection policy: proxy or redirect

After the gateway finds your database it can either keep sitting in the middle, passing every message back and forth, or it can step aside and let your application talk to the node directly. These are the proxy and redirect connection policies. Redirect gives lower delay and higher throughput because there is one less hop, but it needs a range of extra ports to be open between the application and Azure. Proxy needs only port 1433. By default, connections that come from inside Azure use redirect and connections from outside use proxy. You can set the policy on the server to force either behavior.

The database node and the engine

The database itself runs on a node, which is a virtual machine inside a managed cluster. On it runs the SQL engine, the same family of code as SQL Server. Azure uses a cluster management service to place databases on nodes, watch their health, and move a database elsewhere when a node fails or when you change its size. You never log in to a node. You see only the database.

The control plane and the data plane

Two worlds exist side by side. The control plane is the management side: creating a database, changing its tier, setting backup retention, configuring replication. You reach it through the Azure portal, command line tools, templates and the Azure Resource Manager. The data plane is the work side: connecting, running queries and changing rows. Permissions are separate for each. Someone who can resize a database does not automatically gain the right to read the tables inside it, and the other way around.

A short tour of what the database does with each query explains almost every performance tip you will ever read.

From text to answer

When you send a query, the engine goes through steps. It first parses the text to check the grammar. It then asks the query optimizer to build a plan, which is a recipe for finding the answer cheaply: which indexes to use, in which order to join tables, how to sort. The optimizer decides using statistics, small summaries of how values are spread in each column. The plan is cached so the same query can reuse it. Finally the executor follows the plan, reading data pages and producing rows. If statistics are out of date, the optimizer can pick a poor plan, which is why the engine refreshes statistics automatically.

Memory first, disk second

Data is stored in fixed-size pages of eight kilobytes. The engine keeps recently used pages in a memory area called the buffer pool. A read that finds its page in memory is far faster than one that must fetch it from storage. Therefore more memory and good indexes, which reduce the number of pages touched, are the two best friends of read speed.

Write-ahead logging

Changes do not go straight to the data files. First, the engine writes a description of the change to the transaction log and makes sure that log record is stored safely. Only then does it confirm the commit to you. The modified data page in memory is written to the data file later, in the background. This rule, called write-ahead logging, is why a crash cannot lose a confirmed transaction: on restart the engine replays the log to redo committed work and undo unfinished work. Azure SQL Database enables a feature called accelerated database recovery, which keeps older row versions so that rolling back and recovering finish quickly even after very long transactions.

Locks, versions and isolation

When many people work at once, the engine must stop them from trampling each other. The traditional method uses locks: a writer blocks other writers on the same row. A reader might also have to wait. Azure SQL Database turns on read committed snapshot isolation by default, which keeps old versions of changed rows so readers see the last committed version and do not wait for writers. This greatly reduces blocking in typical applications. Writers still block each other, so a long transaction that holds locks can pile up a queue behind it.

Query Store and automatic tuning

The engine records the plans and run-time statistics of every query in a built-in history called the Query Store, which is enabled by default in Azure SQL Database. This history allows the platform to notice a plan regression, a case where a query suddenly becomes slower because the optimizer picked a worse plan after a statistics change or an upgrade. The automatic tuning feature can force the previous good plan back, and it is on by default. Optional settings can also create indexes that would help and drop indexes that are never used, and they validate the effect, undoing a change that makes things worse.

Compatibility levels

Each database has a compatibility level, a setting that controls which behaviors of the optimizer and language it follows. New databases use the newest level, but a database restored from an older system may keep an older one. Raising the level gives access to newer optimizer improvements, called intelligent query processing, but should be tested because plans can change.

Follow a single order from the web page through the database and out to the replicas, and the rest of the design becomes concrete.

sequenceDiagram
  participant App as Application
  participant GW as Gateway
  participant P as Primary node
  participant S as Secondary replicas
  App->>GW: Connect, sign in, choose database
  GW->>P: Route session
  App->>P: Begin transaction, insert order, update stock
  P->>P: Write log records to local log
  P->>S: Send log records
  S-->>P: Acknowledge hardened log
  P-->>App: Commit confirmed
  P->>P: Write data pages to files in the background
  Note over P,S: Replicas replay the log to stay current

Step by step

Transient errors and retry logic

In a cloud service, brief interruptions are normal. A scale operation, a failover to another node, a platform update or a short overload can make a connection drop or a request fail for a few seconds. These are called transient faults. They come back with specific error numbers such as 40613, meaning the database is not currently available, or 40501, meaning the service is busy. A well-built application treats them as expected. It retries the operation after a short wait, doubling the wait each time up to a limit, and it opens a new connection when the old one is broken. Most official drivers and libraries include this behavior, and it must be turned on and tested.

Connection pooling

Opening a connection is relatively expensive: network handshakes, sign-in and setup. Application libraries keep a pool of open connections and hand them out to requests, taking them back when finished. Without pooling, a busy site can drown the database with sign-ins. With careless pooling, too many connections can pile up and hit the limit on workers and sessions, so size pools sensibly and always release connections quickly.

Lifecycle of the data itself

Rows are created, updated and eventually archived or deleted. Over time tables grow, indexes fragment and statistics age. A healthy lifecycle includes regular maintenance such as index and statistics upkeep, an archive plan that moves old rows to cheaper storage or a separate table, and size monitoring so growth never surprises you. Azure handles backups and engine updates on its own, but data design and housekeeping remain yours.

Two different questions are hiding in the word “reliable”. What happens when one machine breaks? And what happens when a whole region goes dark? Azure SQL Database answers them with different tools.

Built-in high availability

Every database has an availability design built in at no extra price. In General Purpose, the database files are stored redundantly on remote storage, and a failed node is replaced by a fresh node that mounts the same files. In Business Critical and Hyperscale, replicas of the engine already run and one takes over quickly. Failovers during maintenance and hardware faults are normal events that applications see only as a short reconnect. The service level agreement is 99.99 percent for most configurations.

Zone redundancy

In regions that have availability zones, which are separate buildings with independent power and cooling, you can enable zone redundancy. The replicas of the database are then spread across zones, so the loss of an entire zone does not cause an outage. The service level agreement improves, and the application changes nothing except the retry behavior it should already have.

Active geo-replication and failover groups

To survive the loss of a whole region, you keep a copy in another region. Active geo-replication creates up to four readable secondary databases in other regions, fed asynchronously from the primary. A failover group goes a step further. It groups many databases from a primary server and a secondary server, and gives your application two stable names: a read-write listener that always points to the current primary, and a read-only listener that points to a secondary. When failover happens, the names move, so connection strings need not change. Failover can be manual, or automatic after a grace period you choose.

flowchart LR
  subgraph R1["Primary region"]
    S1["Primary logical server"]
    D1["Primary databases with zone redundancy"]
  end
  subgraph R2["Secondary region"]
    S2["Secondary logical server"]
    D2["Readable secondary databases"]
  end
  APP["Application"] -->|"Read write listener name"| FG["Failover group endpoints"]
  APP2["Reporting tool"] -->|"Read only listener name"| FG
  FG --> D1
  FG -.-> D2
  D1 -.->|"Asynchronous replication"| D2

Understanding the gap

Because geo-replication is asynchronous, a small amount of the newest committed data may not have reached the secondary when the primary region fails. Documentation describes the typical gap in seconds, but it is not a promise, and it grows when the primary is under heavy write load. A forced failover accepts that possible data loss. A planned, manual failover first waits until the secondary is fully caught up, so no data is lost. Always practice the failover so your team knows which one it is performing.

Backups happen automatically and need no scheduling from you. What you control is how long they stay, where they are copied and how you restore from them.

What is backed up and how often

Azure takes full backups about once a week, differential backups every twelve hours or so that capture the changes since the last full, and transaction log backups every few minutes. Together they let the service rebuild the database at nearly any second inside the retention period. This ability is called point-in-time restore. For Hyperscale, backups use storage snapshots instead, which is why they do not slow with database size.

Retention

The short-term retention window defaults to seven days and can be set between one and thirty-five days. For longer needs, long-term retention keeps weekly, monthly and yearly full backups for up to ten years, which suits legal and audit rules. You choose a schedule, such as “keep one backup per month for twelve months and one per year for seven years”.

Where backups are stored

Backups live in storage that Azure manages for you, and you pick its redundancy: locally redundant, zone redundant, geo-redundant or geo-zone-redundant. Geo-redundant backups, the usual default, are copied to the paired region. They are what makes geo-restore possible, which lets you create the database in a different region from the latest replicated backup if the home region is gone. Backup storage up to the size of your database is generally included in the price, with extra billed beyond that.

Restoring is always to a new database

A restore does not overwrite the existing database. It creates a new database from a chosen point in time, from a deleted database, from a long-term backup or from a geo-redundant backup. You then compare, copy the needed rows back or swap names. This is safer, because a wrong choice can be repeated with another time, and it also means you must have room and budget for the new database during the work.

A database slows down for only a handful of reasons: too little compute, too much work per query, waiting on locks, or a slow path to the storage. Scaling treats the first; good design treats the rest.

Scaling up and down

You can change the number of cores, the tier or the storage size of a database while it stays online. Behind the scenes Azure prepares a new set of resources, copies state, and then switches over. The switch causes a short interruption where connections are dropped, so the application must reconnect, again showing why retry logic matters. For General Purpose and Business Critical, the time taken may depend on the database size. For Hyperscale, resizing compute is fast because the data lives in the shared page servers and need not be copied.

Scaling out reads

Many applications read far more than they write. Reports, dashboards and search pages can be sent to a readable replica so they do not compete with the main workload. In Business Critical, one built-in secondary accepts read-only connections when the connection string says the intent is read only. In Hyperscale you can add several replicas, including named replicas that have their own sizes, so one team’s analytics cannot disturb another’s. Geo-replicas in other regions also serve reads close to remote users, with a small delay in freshness.

Elastic pools

Pools make sense when many databases are mostly idle. Suppose a software product serves 300 customers, each with their own database. Giving every database enough power for its busiest moment would waste money, because the busy moments rarely coincide. A pool gives all of them a shared total, and settings for the minimum guaranteed and maximum allowed for each database, so a noisy neighbor cannot starve the rest. Watching the pool’s overall use tells you when the shared total must grow.

The worst performance problems are usually in the queries

Buying more cores for a badly written query is like buying a bigger truck to carry a load that should have been split in two. The common causes are missing or unused indexes, queries that fetch more columns and rows than needed, loops that run one query per row instead of one set-based query, and implicit data type conversions that stop an index from being used. Fixing a single top query often cuts total load by more than doubling the compute.

Limits you meet before you run out of cores

Each size has ceilings: the number of concurrent workers that run requests, the number of sessions, the data and log input and output rate, and the size of temporary storage. Log throughput is governed, so a bulk load of millions of rows can be held back by log rate before CPU is busy. The portal shows these as percentages, so you can see which one is the real wall. When a query waits, the reason is recorded as a wait type, and the top waits tell you whether the cause is locks, log writes, reading data from storage or CPU.

A small worked example

A web store sees 2,000 database requests per second at peak, and each takes on average 10 milliseconds. By Little’s law, the average number of requests in progress equals arrival rate times time in system, which is 2,000 times 0.01, or 20 at once. If each request keeps one worker busy while it runs, you need to be sure the chosen size supports comfortably more than 20 concurrent workers, with headroom for spikes and slow moments. If a spike pushes average time from 10 to 50 milliseconds, concurrency jumps to 100, which is why a small slowdown can suddenly exhaust workers and make everything stall.

A database holds the most valuable data in a company, so it deserves several independent locks. Each layer below assumes the previous one might be bypassed.

Layer 1: who can reach the server

By default a new server accepts no traffic until you allow it. The server firewall lists the public IP addresses that may connect, and a switch lets trusted Azure services in. Virtual network rules allow chosen subnets. The strongest option is a private endpoint, which gives the server a private IP address inside your virtual network, so applications and connected corporate networks reach it without crossing the public internet. When private endpoints are in place, you can disable public network access entirely.

flowchart LR
  subgraph VN["Your virtual network"]
    APP["Application with managed identity"]
    PE["Private endpoint with private IP"]
  end
  CORP["Corporate network via VPN or ExpressRoute"] --> PE
  APP --> PE
  PE --> SRV["Logical server with public access disabled"]
  SRV --> DB["Database with TDE, auditing and Defender"]
  NET["Public internet"] -.->|"Blocked"| SRV
  DB -.->|"Audit logs"| LA["Log Analytics and storage"]
  ENTRA["Microsoft Entra ID"] -.->|"Tokens and roles"| SRV

Layer 2: who are you

Authentication proves identity. SQL authentication uses a user name and password stored in the database. Microsoft Entra authentication uses the company’s central identity system, so access follows people joining or leaving, multi-factor sign-in applies, and no passwords live in config files. Applications running in Azure should use managed identities, which let the service prove who it is with no secret at all. A server can be set to Entra-only authentication, which switches SQL passwords off completely.

Layer 3: what may you do

Authorization decides actions. Database roles such as reader and writer give broad rights, and permissions on individual tables, views and procedures give narrow ones. Good practice is least privilege: the web application account gets only the rights it needs, not owner rights. Two features go further. Row-level security filters rows by who is asking, so a user from one tenant never sees another tenant’s rows even if the query forgets a filter. Dynamic data masking hides part of sensitive values, such as showing only the last four digits of a card number, for users without special permission.

Layer 4: protecting the data itself

Transparent data encryption, or TDE, encrypts data files, backups and logs at rest, and it is turned on by default for new databases. You can let Azure manage the key or use a customer-managed key held in Azure Key Vault. Connections use TLS, and you can set a minimum version. For especially sensitive columns there is Always Encrypted, where the application encrypts data before sending it and only holds the key itself, so even database administrators and Microsoft staff see only scrambled values. Ledger tables add tamper evidence, keeping a cryptographic record that proves rows have not been altered behind the scenes.

Layer 5: seeing and detecting

Auditing records events such as sign-ins, queries and permission changes to storage, a log analytics workspace or an event hub. Microsoft Defender for SQL adds vulnerability assessments, which scan for risky settings, and threat detection, which warns about patterns such as injection attempts or sign-ins from unusual places. Data discovery and classification labels columns that hold personal or financial information so reports and policies can find them.

Four views answer almost every question: what the platform measures, what the engine remembers, what the logs say, and what alerts tell you before users do.

Platform metrics in Azure Monitor

Without any setup you receive numbers for each database: CPU percentage, data input and output percentage, log write percentage, workers and sessions percentage, storage used, deadlocks, connection counts and failed connections. For DTU databases, a single DTU percentage blends the main resources. For serverless, you also see the cores used and whether the database is paused. These can be charted, put on a dashboard and used for alerts.

Query-level insight

Platform numbers tell you the database is busy. Query-level views tell you why. Query Performance Insight in the portal lists the queries that use the most CPU, time or reads. The Query Store keeps their history and plans so you can compare today with last week. Dynamic management views, called DMVs, are built-in system views that show current requests, waits, blocking chains and missing index suggestions. Intelligent Insights and automatic tuning recommendations point out patterns such as plan regressions or resource limits being hit.

Logs and diagnostics

Diagnostic settings can stream metrics, blocked-process reports, deadlock details, errors, timeouts and query statistics into a Log Analytics workspace, a storage account or an event hub. Streaming the audit log to a protected location gives a trail for security investigations. Extended Events let you capture detailed events for a short period to study a specific problem.

Alerts that matter

A calm way to investigate slowness

The monthly cost is built from a small number of meters. If you know them, you can predict, reduce and explain the bill.

Ways to pay less

Match the tier to the need instead of defaulting to the most powerful. Use serverless for databases that sit idle for hours. Use elastic pools for many small databases with uneven use. Apply the Azure Hybrid Benefit if you already own SQL Server licenses with Software Assurance, which removes the license part of the vCore price for provisioned compute. Buy reserved capacity for one or three years for steady production databases at a discount. Delete forgotten test databases. Reduce the backup retention of non-critical databases. Microsoft also offers a limited free monthly allowance for new databases, whose terms change, so check the current page before relying on it.

A serverless versus provisioned example

The prices below are invented round numbers for illustration. Imagine a provisioned database with 4 cores at 0.25 dollars per core-hour. It runs all month, 730 hours, so the compute bill is 4 times 0.25 times 730, or 730 dollars. Now take a reporting database that really needs up to 4 cores but is only busy about six hours a day. Serverless charges a higher rate per core-second, say 0.35 dollars per core-hour, but only for cores in use. Six hours at 4 cores is 24 core-hours a day, which over 30 days is 720 core-hours, and at 0.35 that is 252 dollars. That is about 65 percent less, plus the same storage cost as before. If the same database were busy sixteen hours a day, serverless would cost 4 times 16 times 30 times 0.35, or 672 dollars, much closer to the provisioned price. The saving depends entirely on idle time.

Because Azure SQL Database shares its engine with SQL Server, many applications move easily. A few differences, though, decide whether a migration is quick or painful.

What is different from a full SQL Server

Azure SQL Database provides the database, not the whole instance. Anything that belongs to the instance rather than the database is unavailable or handled differently. There is no SQL Server Agent for scheduling jobs, so scheduled work moves to elastic jobs, to Azure Functions, Data Factory or other schedulers. Queries that join tables across two databases on the same server are not generally allowed, and the limited elastic query feature or a redesign takes its place. Linked servers, some uses of the common language runtime, and some server-level settings are not offered. If your application depends heavily on these, Managed Instance may be the better home.

Planning a migration

Beyond tables and queries, the engine ships with several features that remove whole categories of custom code. Knowing they exist saves weeks of building and maintaining your own versions.

Temporal tables: a built-in history

A temporal table, also called a system-versioned table, automatically keeps every earlier version of each row in a paired history table, with start and end times. When a row is updated or deleted, the old version is moved into history by the engine itself. You can then ask “show me this customer’s address as it was on the first of March” or “what changed in the last hour”. This is excellent for audit trails, for undoing mistakes at row level and for analyzing trends, and it needs no triggers or custom code. The history grows over time, so set a retention period that deletes very old versions.

JSON, graph, spatial and text

Not all data is a neat grid. The engine can store, validate and query JSON text, so a flexible document can sit in a column next to ordinary ones, and parts of it can be read or indexed. Graph tables model many-to-many relationships such as “who follows whom” or “which parts belong to which assemblies”. Spatial types handle map coordinates and distance searches. Full-text search finds words and phrases inside long text columns faster and more flexibly than simple pattern matching. These let one database serve several shapes of data without extra services.

Tracking changes for other systems

Many systems need to know what changed since they last looked, for example to refresh a search index or feed a data warehouse. Change tracking records which rows changed, so a reader can ask for all rows altered since a given version. Change data capture goes further and records the actual before and after values for each change in separate tables. Both avoid the clumsy method of scanning whole tables and comparing them. Microsoft also offers ways to mirror data from Azure SQL Database into Microsoft Fabric for analytics, so reporting can run on a copy without loading the transactional database; check the current documentation for the exact status and limits.

Elastic jobs and elastic query

Because there is no SQL Agent, elastic jobs fill the gap for running a script on a schedule against one database, many databases or a whole pool. They are the usual tool for applying a schema update to hundreds of tenant databases, collecting maintenance results or rebuilding indexes on a timetable. Elastic query lets one database read tables from another, which covers limited cross-database reporting, though it is not a full replacement for same-instance queries.

Intelligent query processing

Newer compatibility levels switch on a family of optimizer improvements. For example, the engine can adjust the memory it grants to a query based on how much the last run really used, and can choose a better join method while the query is running if the first estimate proved wrong. These improvements require no code change, only the right compatibility level and some testing.

A fair look at strengths and limits helps you decide when Azure SQL Database is the right answer and when something next to it is better.

How it compares with nearby choices

The three main trade-offs

Control versus convenience. Every feature Azure manages for you is one you cannot tune yourself. Performance versus cost. A faster tier with more replicas gives lower delay and more safety at a higher price. Consolidation versus isolation. Packing many databases in a pool is cheap, but a noisy tenant can still affect others, while a database per tenant on its own compute is safe and expensive. Decide per workload, and revisit the decision as the numbers change.

Experienced teams reuse a short list of shapes. Knowing them saves months of trial and error.

Patterns that work

flowchart LR
  APP["Application tier"] --> CAT["Tenant catalog: tenant to database map"]
  APP -->|"Route by tenant"| POOL
  subgraph POOL["Elastic pool shared compute"]
    T1["Tenant A database"]
    T2["Tenant B database"]
    T3["Tenant C database"]
    T4["Large tenant D database"]
  end
  JOB["Elastic job: schema updates and maintenance"] --> T1
  JOB --> T2
  JOB --> T3
  JOB --> T4

Anti-patterns to avoid

These habits come from running databases in production for years. Most cost very little and prevent most of the painful stories.

Common mistakes

Failures come from hardware, from software, from people and from attackers. The table below pairs the usual ones with the first response.

Recovery objectives in plain words

Two numbers guide any plan. The recovery point objective says how much recent data you can afford to lose, measured in time. The recovery time objective says how long the system may be unavailable. Zone redundancy gives near-zero data loss and a fast switch. A failover group gives seconds of possible loss and minutes of switch time. Backups give a flexible point in time, but a longer restore. Write both numbers for each system, then choose the cheapest combination that meets them.

Failing over with confidence

Five common workloads show how the ideas combine. Figures are illustrative, not from a particular customer.

Short answers to the questions people ask most when they first meet Azure SQL Database.