A relational database that lives in Microsoft’s cloud, looked after by Microsoft, and used by you.

Starting with the word “database”

A database is an organized place to keep information so you can find it, change it, and trust it later. A relational database keeps information in tables, which look like spreadsheets. A “Customers” table might have one row per customer and one column each for name, email, and city. A separate “Orders” table might have one row per order. The two tables are linked by a shared value, such as a customer number. That link is the “relation” in “relational”.

To talk to a relational database you use a language called SQL, short for Structured Query Language. SQL lets you ask questions like “show me all orders from last week over one hundred dollars” without telling the computer exactly how to go and find them. The database engine works out the best way on its own.

What makes it “Azure” and what makes it “managed”

Azure is Microsoft’s cloud platform, a huge collection of data centers around the world that you can rent by the minute. Azure SQL Database is a database service on that platform. It uses the same core engine as Microsoft SQL Server, the database software that businesses have trusted for decades, but it is delivered as a Platform as a Service, usually shortened to PaaS.

PaaS means the provider runs the machines, the operating system, the database software, the patches, the backups, and the failover plans. You run only your own data and your own queries. This is very different from renting an empty virtual machine and installing SQL Server yourself, where you are still responsible for almost everything.

A short history

Microsoft SQL Server first appeared in the late 1980s and became one of the most widely used databases in business. As the cloud grew, Microsoft launched a cloud version around 2010, first called SQL Azure and later renamed SQL Database and then Azure SQL Database. Early versions were small and had many limits. Over the years the service gained larger sizes, more compatibility with the full SQL Server product, stronger security, better availability, serverless billing, and the very large Hyperscale tier.

A very important design idea is that Azure SQL Database is evergreen. Microsoft ships new engine improvements to the cloud service first and keeps it updated all the time. You never run an “upgrade weekend”. New features simply appear, and the service keeps your database working while it changes underneath.

What problem does it solve?

Running databases yourself brings the same pains again and again. Disks fail. Software needs urgent security patches. Backups are forgotten, or worse, never tested. A traffic spike arrives and the server is too small. A second data center for disasters is expensive and rarely rehearsed. Azure SQL Database turns these jobs into settings and built-in behaviors. You choose a size, and the platform handles the rest.

Where it is used

Teams use Azure SQL Database for web and mobile app back ends, software-as-a-service products where every customer needs a safe corner of the data, reporting databases, line-of-business systems, and modernized versions of older applications that once ran on a company server. If your data fits neatly into tables with strong rules about accuracy, this service is a natural fit.

“Azure SQL” is a family name, not a single product. Knowing the relatives saves you from picking the wrong one.

When people say “Azure SQL”, they might mean one of several related services. All of them speak the SQL Server language, but they differ in how much control you get and how much work you take on.

flowchart TD
    A["Azure SQL family"] --> B["Azure SQL Database"]
    A --> C["Azure SQL Managed Instance"]
    A --> D["SQL Server on Azure Virtual Machines"]
    B --> B1["Single database"]
    B --> B2["Elastic pool"]
    C --> C1["Near full SQL Server compatibility"]
    D --> D1["Full control and full responsibility"]
    

How to choose

Start with Azure SQL Database. If you are moving an older application that depends on instance-wide features, such as cross-database queries, a built-in job scheduler, or restoring a native backup file, look at Managed Instance. If you need to install special software on the same machine, or need an exact old version of the engine, choose a virtual machine. Because Azure SQL Database is the focus of this guide, the rest of the chapters go deep on it.

Six building blocks appear in almost every conversation about Azure SQL Database. Learn them once and the rest becomes easy.

Subscription and resource group

A subscription is your billing account in Azure. Everything you create is charged to a subscription. A resource group is a folder that holds related items together, such as the database, its server, and its monitoring settings. You can delete the folder and everything inside goes with it, which makes cleaning up easy.

Logical server

Every Azure SQL Database lives under a logical server. The word “logical” matters. It is not a physical computer you can log into. It is a management point, like the front desk of a building. It holds the server name that applications connect to, the administrator account, the firewall rules, the auditing settings, and the list of databases that belong to it. The databases themselves can actually run on different machines inside the data center.

Database

The database is where your tables, indexes, and stored data live. A single database has its own resources, its own backups, and its own performance settings. This is the unit you scale, back up, restore, and copy.

Elastic pool

An elastic pool is a shared bucket of compute and storage that many databases draw from. It is perfect when you have lots of databases whose busy times do not line up. Chapter 12 explains it fully.

DTU and vCore

These are the two ways to describe database power. A DTU (Database Transaction Unit) is a blended bundle of processor, memory, and disk speed sold as a single number. A vCore is a virtual processor core, and you choose cores and memory in a more transparent way. Chapter 4 compares them.

Firewall and endpoints

A firewall is a gate that decides who may even knock on the door. An endpoint is the network address through which clients reach the server. You can expose it publicly with strict rules, or hide it inside your private network using a private endpoint.

flowchart TD
    S["Azure subscription"] --> RG["Resource group"]
    RG --> LS["Logical server with admin, firewall and auditing"]
    LS --> DB1["Single database A"]
    LS --> DB2["Single database B"]
    LS --> EP["Elastic pool"]
    EP --> DB3["Database C"]
    EP --> DB4["Database D"]
    EP --> DB5["Database E"]
    

Two ways to buy, three main tiers to choose from, and one decision that shapes cost, speed, and resilience.

The DTU model: simple bundles

In the DTU model you pick a tier, and each tier offers a ladder of sizes. Basic is for tiny workloads with very small storage. Standard suits most small and medium apps. Premium gives faster storage and more headroom. The price is easy to predict, and you do not need to understand processors or memory. The trade-off is that you cannot tune compute and storage separately, and the number is somewhat of a black box.

The vCore model: transparent choices

In the vCore model you choose the number of virtual cores, the memory that comes with them, and the storage size. It also lets you use Azure Hybrid Benefit (bring licenses you already own to lower the price) and buy reserved capacity (promise to use a size for one or three years in return for a discount). The vCore model offers three service tiers that matter greatly for architecture.

Which model should a beginner choose?

For a new project, the vCore model is usually the better long-term choice because it is transparent, supports every new tier, and gives more ways to save money. DTU is fine for small, steady workloads where simplicity matters more than control.

A way to run a database that sleeps when nobody is using it.

In a normal (provisioned) database, you choose a size, and that compute is reserved and billed every hour, whether busy or idle. In the serverless option you choose a minimum and a maximum number of virtual cores. The platform moves the database up and down inside that range based on demand and bills per second for the compute actually used. Storage is always billed, because your data is always kept.

Auto-pause and auto-resume

If nobody uses the database for a period you choose, serverless can pause it. While paused, you pay only for storage. When a new connection arrives, the database resumes. Resuming takes a short time, so the first request after a long quiet period can be slow, or can even fail once, which is why applications must be written to retry. You can also turn auto-pause off if you need steady responsiveness while still enjoying automatic scaling.

What actually sits between your application and your data inside Azure.

You never see the inner machinery, but understanding it explains why the service behaves as it does. Azure SQL Database is built from several cooperating layers, and every connection and every change passes through them.

flowchart TD
    APP["Client application"] --> GW["Regional gateway"]
    GW --> CTRL["Control plane and health management"]
    GW --> DBN["Database node running the SQL engine"]
    CTRL --> DBN
    DBN --> ST["Durable storage layer"]
    DBN --> LOG["Transaction log"]
    CTRL --> MON["Monitoring and automatic repair"]
    

Resource governance

Many customers share the same physical hardware, so the platform needs rules to keep neighbors from starving one another. Resource governance caps how much processor, memory, disk activity, and log writing each database may use, based on the size you bought. If you hit a cap, queries slow down or wait. This is why choosing the right size matters, and why monitoring “percent of limit” values is so valuable.

How the database stores data, keeps promises, and recovers quickly when something goes wrong.

Pages and extents

Inside the engine, data is saved in small fixed-size blocks called pages, each around eight kilobytes. Rows live inside pages, and pages are grouped for efficient reading. When you ask for a customer, the engine finds the right page, reads it into memory, and returns the row.

The buffer pool: a fast desk

Memory is much faster than disk. The engine keeps recently used pages in an area of memory called the buffer pool. If the page you need is already there, the answer comes back almost instantly. If not, the engine reads it from storage first. A larger memory size generally means fewer slow reads.

Indexes

An index is a sorted shortcut, like the index at the back of a book. Without one, the engine may have to read every page of a table to find a single customer. With a good index, it jumps straight to the right place. Indexes make reading faster but add a little cost to every write, so they should be chosen with care.

The transaction log and the promise of durability

A transaction is a group of changes that must succeed or fail as one unit. Moving money from one account to another is the classic example: subtracting from one account and adding to the other must both happen or neither. Databases promise four things, often remembered as ACID: Atomicity (all or nothing), Consistency (rules are respected), Isolation (transactions do not trip over each other), and Durability (once confirmed, it stays saved).

To keep the durability promise, the engine uses write-ahead logging. Before a change is confirmed, a short description of it is written to the transaction log and made safe. The actual data pages can be updated in memory and saved to storage a little later. If a crash happens, the engine replays the log to redo finished work and undo unfinished work.

Accelerated Database Recovery

Older versions of the engine could take a long time to roll back a huge failed transaction or to start up after a crash. Azure SQL Database uses a design called Accelerated Database Recovery, which keeps old row versions so that rollbacks and restarts finish in seconds rather than minutes or hours. This is one reason failovers in the cloud feel quick.

Isolation and row versioning

When two people work at once, the engine must decide who sees what. Azure SQL Database commonly uses read committed snapshot isolation, where readers see a steady, committed version of rows without blocking writers. This reduces waiting but uses some extra space for row versions, which is another reason to keep transactions short.

The Query Optimizer and plans

When you submit a query, the query optimizer considers many ways to run it and picks one that it estimates to be cheapest. The chosen recipe is a query plan. Plans depend on statistics, small summaries of how data is spread out. Stale statistics lead to poor plans, so the platform keeps them fresh automatically in most cases.

Follow one request from the moment your app opens a connection to the moment it receives an answer.

Understanding the path of a request helps you debug slow connections, choose network settings, and explain the service clearly in an interview. Here is the journey in plain steps.

sequenceDiagram
    participant App as Client application
    participant DNS as Name lookup
    participant GW as Regional gateway
    participant Node as Database node
    participant Store as Storage and log
    App->>DNS: Find the server name
    DNS-->>App: Address of the regional gateway
    App->>GW: Open secure connection and sign in
    GW->>GW: Check firewall and identity
    GW-->>App: Redirect to the database node
    App->>Node: Send the query
    Node->>Store: Read pages or write log records
    Store-->>Node: Data or confirmation
    Node-->>App: Rows or commit result
    

Redirect versus proxy

There are two connection policies. With Redirect, the gateway only helps at the start and then points the client straight to the database node, which gives lower delay and higher speed. This needs extra network ports to be open. With Proxy, all traffic keeps flowing through the gateway on the standard database port, which is simpler to firewall but adds a little delay. By default, connections coming from inside Azure use Redirect, and connections from outside use Proxy. Teams with strict network rules often set the policy deliberately.

Connections are precious

Opening a connection costs time, and every database size has a maximum number of simultaneous sessions and workers. A busy application should use connection pooling, which keeps a small set of open connections and lends them out repeatedly. Opening a new connection for every click is like hiring a new taxi driver for every block of a trip.

Transient faults: the normal hiccups of the cloud

In any large system, short interruptions happen: a failover, a scaling event, a brief overload. These are called transient faults. Azure SQL Database may reply with errors such as “database is not currently available” or “service is busy”. The right response is to wait a moment and try again, increasing the wait each time, a pattern called retry with exponential backoff. Modern database drivers often include helpers for this. An app without retry logic will look unreliable even though the service is healthy.

Splitting the database into specialist teams so it can grow far beyond a single machine’s limits.

A traditional database engine does everything on one machine: it runs queries, keeps pages in memory, writes the log, and manages data files. That works well until the data gets so large that backups take hours and copying a database becomes painful. Hyperscale solves this by splitting the jobs across separate layers.

flowchart TD
    APP["Application"] --> GW["Gateway"]
    GW --> PC["Primary compute node"]
    GW --> RR["Secondary compute replicas for reads"]
    PC --> LS["Log service"]
    LS --> PS1["Page server 1"]
    LS --> PS2["Page server 2"]
    LS --> PS3["Page server N"]
    PS1 --> AS["Durable Azure storage with snapshots"]
    PS2 --> AS
    PS3 --> AS
    PC -.-> PS1
    PC -.-> PS2
    RR -.-> PS3
    

What this design gives you

• Very large databases: sizes far beyond other tiers, because storage is spread over many page servers.
• Fast backups and restores: backups use storage snapshots, so their speed does not depend on how big the database is.
• Quick scaling: compute can be resized without copying the data.
• Read scale-out: several secondary replicas, including named replicas that you can dedicate to a particular reporting job, share the same storage.

How the service keeps answering even when machines, racks, or entire buildings fail.

High availability means the system keeps working when parts of it break. In Azure SQL Database it is built into every tier, using two main designs.

Design A: remote storage (General Purpose)

In this design the compute is stateless in the sense that your data files live on separate, durable storage. If the node running your database fails, the control plane starts your database on another healthy node and attaches the same storage. Because the data never moved, nothing is lost, but the new node begins with an empty memory cache, so the first few queries may be slower while the cache warms up. This takes longer than the other design but is still usually quick.

Design B: replica set (Business Critical)

Here one primary node and several secondary nodes each hold their own full copy of the data on local solid-state storage. The primary sends every log record to the secondaries and waits for enough of them to confirm before telling your app the commit succeeded. If the primary fails, a secondary that is already up to date becomes the new primary within seconds. One secondary can also be used for read-only queries at no extra charge.

flowchart TD
    APP["Application"] --> GW["Gateway"]
    GW --> P["Primary replica with local SSD"]
    P -->|"log records"| S1["Secondary replica 1"]
    P -->|"log records"| S2["Secondary replica 2"]
    P -->|"log records"| S3["Secondary replica 3 readable"]
    CTRL["Control plane"] -.-> P
    CTRL -.-> S1
    CTRL -.-> S2
    CTRL -.-> S3
    APP2["Reporting application"] --> S3
    

Zone redundancy

An Azure region is divided into several availability zones, which are separate buildings with their own power, cooling, and networking. By enabling zone redundancy, the replicas of your database are spread across these zones. If a whole building goes dark, another zone takes over without you doing anything. This raises the availability target and is usually worth turning on for important production databases in regions that support it.

Planned maintenance

Patching and platform upgrades happen without scheduling a downtime window, because the platform moves your database to an updated node, which looks to your app like a very short failover. This is why retry logic is not optional. You can also choose a maintenance window preference so that these moves tend to happen at quieter hours.

Two words to remember: RPO (how much data you can afford to lose) and RTO (how long you can afford to be down).

Automatic backups

You do not need to schedule backups. The service takes them for you: regular full backups about once a week, differential backups (changes since the last full one) every twelve or twenty-four hours, and transaction log backups every few minutes. Together they let you rewind to almost any second inside your retention window.

Point-in-time restore

Point-in-time restore (often called PITR) creates a new database as it looked at a chosen moment. The default window is seven days, and you can set it from a single day up to thirty-five days. It is the tool you reach for after a bad deployment or an accidental delete. Notice that restore creates a new database. You then check it and swap it in, or copy the needed rows back.

Long-term retention

Some rules require keeping yearly or monthly copies for a long time. Long-term retention (LTR) keeps selected full backups for up to ten years in low-cost storage, with weekly, monthly, and yearly rules you define.

Backup storage redundancy

You can choose how safely the backup files themselves are stored: kept in one data center, spread across zones, or copied to a paired region. Geo-redundant storage lets you do a geo-restore into another region if the original region is lost, but the data may be somewhat older than the live database.

Active geo-replication and failover groups

For fast recovery from a regional disaster, you can keep a live, readable copy of your database in another region. Active geo-replication sends changes continuously and asynchronously to up to a few secondary databases. A failover group goes one step further: it bundles several databases together and offers stable listener names for read-write and read-only traffic. When you fail over, the names point to the new primary, so your applications keep the same connection strings.

flowchart LR
    APP["Application using listener name"] --> PRI["Primary region database"]
    PRI -->|"asynchronous replication"| SEC["Secondary region database"]
    PRI --> BK["Automated backups"]
    BK --> GEO["Geo-redundant backup copy"]
    FG["Failover group"] -.-> PRI
    FG -.-> SEC
    APP -.->|"after failover"| SEC
    

Make it fast first by being smart, then by being bigger, and only then by being clever.

Scaling up and down

Vertical scaling means changing the size of one database, for example from four to eight virtual cores. In Azure SQL Database this is an online operation. Your database stays available, with a brief reconnection at the end when the switch happens. Because the platform copies the data to the new size in the background for some tiers, large databases may take longer, while Hyperscale is typically quick.

Reading from replicas

Read scale-out lets reporting or search queries run on a secondary replica instead of the primary. Applications opt in through a setting in the connection that declares read-only intent. This protects your main workload from heavy reports, a pattern sometimes called CQRS-lite: writes go one way, reads go another.

Elastic pools

An elastic pool shares one budget of compute among many databases. Suppose you run a software product with five hundred small customer databases. Each is busy at different hours. Sizing every one for its own peak would waste money. Putting them in a pool lets each borrow from the shared bucket when busy and give back when quiet.

Sharding

When one database cannot hold or serve all your data, you can split it across several databases, a technique called sharding. For example, customers A to M live in one shard and N to Z in another. A small lookup map tells the application which shard to talk to. Microsoft provides elastic database tools to help manage this, but sharding adds real complexity, so most teams try every other option first.

Query tuning beats hardware

Most slow databases are slow because of how they are used, not because they are small. The top causes are missing indexes, queries that return far more data than needed, many tiny queries where one would do (the “N plus one” problem), long-running transactions that block others, and out-of-date statistics. Fixing these is usually cheaper and gives a bigger gain than moving to a larger tier.

Automatic tuning

Azure SQL Database can watch your workload and help itself. Automatic tuning can force a previously good query plan when a new plan turns out slower, and can suggest or even apply index creation and removal, then check that performance really improved and roll back if it did not. Teams often start with recommendations only, and enable automatic application after they trust the results.

Understanding limits

Each size has ceilings: processor share, memory, data and log input/output rates, maximum worker threads, and maximum sessions. When a query waits because it hit one of these ceilings, it looks “slow” but the real cause is resource governance. Reading the “percent of limit” metrics tells you which wall you hit.

Good security is like an onion or a castle: if one layer fails, the next one still protects you.

flowchart TD
    U["User or application"] --> N["Network layer: firewall, private endpoint"]
    N --> I["Identity layer: Entra ID, managed identity, least privilege"]
    I --> T["Transport layer: TLS encryption"]
    T --> D["Data layer: row level security, masking, Always Encrypted"]
    D --> R["At rest: transparent data encryption"]
    R --> M["Audit and threat detection across all layers"]
    

Layer 1: network

By default, the server has a public address protected by a firewall that blocks everyone until you allow specific addresses. Better still, use a private endpoint, which gives the server a private address inside your own virtual network, so traffic never crosses the open internet. You can then switch off public access altogether.

Layer 2: identity and permissions

Sign-in can use a SQL username and password, but the stronger option is Microsoft Entra ID (the new name for Azure Active Directory). With it, people sign in using the same company identity, with multi-factor checks and central control. Applications running in Azure can use a managed identity, an automatic identity for the app, so no password has to be stored anywhere. Follow least privilege: give each person or app only the exact permissions needed. You may also choose Entra-only authentication to turn off password logins completely.

Layer 3: encryption in motion

All connections can be forced to use TLS, the same technology that shows a padlock in a web browser. You can also set a minimum version so old, weak versions are refused.

Layer 4: encryption at rest

Transparent Data Encryption (TDE) scrambles the database files and backups on disk. It is on by default for new databases. Keys are normally managed by the service, but you can bring your own key stored in Azure Key Vault if rules require you to control it.

Layer 5: protecting data from administrators too

Sometimes even database administrators should not see raw values, such as card numbers. Always Encrypted encrypts specific columns on the client side, so the server only ever sees scrambled values. Dynamic data masking shows partly hidden values, such as the last four digits of a phone number, to people without special rights. Row-level security makes sure each user sees only the rows they own, which is very handy in multi-customer systems.

Layer 6: watching and proving

• Auditing records who did what and sends logs to storage, Log Analytics, or Event Hubs.
• Microsoft Defender for SQL looks for suspicious behavior such as a login from an odd place or signs of injection attacks, and scans for weak settings.
• Data discovery and classification helps find and label sensitive columns.
• Ledger features can make changes tamper-evident, useful when you must prove records were not secretly altered.

You cannot fix what you cannot see. Set up your eyes before the trouble starts.

Platform metrics

Azure Monitor collects numbers about your database every minute. The most useful ones are the ones that show how close you are to your limits.

Query Store

Query Store is a flight recorder inside the database. It saves each query, its plans, and its performance over time. It is on by default. With it you can see which query got slower after yesterday’s release, compare plans, and force the old good plan. The portal feature called Query Performance Insight shows the same data in friendly charts.

Dynamic management views

The engine also exposes built-in views, called dynamic management views (DMVs), which show live information: current sessions, waiting queries, missing index hints, and resource use. They are the tool of choice for investigating a problem happening right now.

Logs and alerts

Through diagnostic settings you can stream logs and metrics to a Log Analytics workspace, a storage account, or an event stream. Then you can search them, build dashboards, and create alerts that notify the team by email, chat, or on-call tools when a rule is broken. Good alerts are about symptoms users feel, like errors and slow responses, and about early warnings such as storage close to full.

Creating the database is the easy part. Doing it repeatably, moving data in safely, and keeping the bill sensible is the craft.

Ways to create and manage it

You can create a database by clicking through the Azure portal, which is fine for learning. In real projects, teams describe their setup in text files using infrastructure as code tools such as ARM templates, Bicep, or Terraform. The file says what you want (a server, a database, a firewall rule, a size), and the tool makes the cloud match it. This makes every environment identical, reviewable like any other change, and easy to rebuild.

Keeping the database shape under version control

The structure of a database, meaning its tables, columns, and indexes, is called the schema. Treat schema changes like application code: store them in version control, review them, and apply them through an automated release pipeline. Two common approaches are migration scripts applied in order, and a state-based approach that compares the desired schema with the live one and generates the difference. Whichever you choose, test changes on a copy of production-like data first.

Environments

Keep separate databases for development, testing, and production. Use smaller or serverless sizes for non-production and turn them off or pause them when not in use. Never copy real customer data into lower environments without masking it first.

Migrating from somewhere else

Controlling cost

Cloud bills grow quietly, so make cost a regular habit rather than a yearly shock. Here are the levers, from easiest to most strategic.

Also watch the quiet costs: long backup retention, extra geo-replicas that nobody uses, oversized log storage, and test databases that were never deleted. Microsoft has also offered a free usage allowance for new experiments from time to time, so check the current offer before paying for a learning project.

A pattern is a recipe that worked many times. An anti-pattern is a trap that looks tempting and hurts later.

Helpful patterns

flowchart LR
    W["Web and mobile apps"] --> API["Application services"]
    API --> CA["Cache"]
    API --> Q["Message queue"]
    Q --> WK["Background workers"]
    WK --> DBP["Primary database"]
    API --> DBP
    DBP --> DBR["Readable replica"]
    API -->|"reports"| DBR
    

Anti-patterns to avoid

A practical checklist you can carry into any project.

Mistakes beginners make, and how to dodge them

• Confusing vertical scaling with a performance fix. A larger size hides a bad query for a while, then the problem returns. Tune first.
• Treating backups as a disaster plan. Backups protect against mistakes and corruption. Regional disasters need replication and a rehearsed failover.
• Over-trusting defaults. Defaults are safe starting points, but retention, redundancy, and auditing deserve a conscious choice.
• Ignoring the weakest link. A perfect database behind an application with a leaked password is not secure.
• Mixing environments. Pointing a test application at the production database has deleted real data in many companies. Use separate servers and separate credentials.

Four typical situations, described as realistic example setups, showing how the pieces combine.

How to talk about it in an interview

A strong short answer might sound like this: “Azure SQL Database is a managed relational service based on the SQL Server engine. I choose a tier by workload: General Purpose for typical apps, Business Critical for low latency and fast failover, and Hyperscale for very large or fast-growing data. I add zone redundancy for resilience, failover groups for regional disaster recovery, private endpoints and Entra authentication for security, and I rely on Query Store and Azure Monitor to keep an eye on performance and cost.” Then be ready to explain the reasoning behind each choice using the ideas in this guide.

You have now walked from the basic idea of a table all the way through gateways, replicas, page servers, encryption layers, and cost levers. The thread running through everything is simple: Azure SQL Database lets you hand the heavy machinery to the platform, while you keep responsibility for good design, safe access, honest monitoring, and sensible spending.