Azure SQL

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

See the section bibliography for the reference material consulted while preparing these pages.

Examples on this page target Azure CLI 2.90.0 — re-verify exact flag names and behavior against the Azure SQL Database documentation and the az sql command reference before relying on them, since the Entra-only-authentication flags, the free offer’s terms, and Hyperscale/serverless behavior have all changed within the last few release cycles.

This page goes deep on Azure SQL Database itself: the three SQL-on-Azure deployment options and when to pick each, the logical server, DTU vs. vCore purchasing models, service tiers including Hyperscale, the serverless compute tier and elastic pools, the free offer, provisioning through the portal and the CLI with Entra-only authentication, firewall rules and private endpoints, connecting with Entra ID from a Spring Boot JDBC application and from sqlcmd, backups (point-in-time and long-term retention), geo-replication and failover groups, and Microsoft Defender for SQL, auditing and transparent data encryption. Where a connection string or secret would otherwise need to be stored, see Key Vault and Secrets — every example below prefers Entra ID authentication instead, so no secret exists in the first place. Broader resource-group and networking concerns that sit above a single database follow the same Bookshelf scenario introduced in Managed Databases on Azure, which this page assumes as a prerequisite.

Azure SQL Database vs. Managed Instance vs. SQL Server on Azure VMs

Three ways to run SQL Server-compatible workloads on Azure, trading off management effort against compatibility and control:

Option What it’s for

Azure SQL Database

A fully managed platform-as-a-service (PaaS) database (or elastic pool of databases) — Microsoft owns the OS, the SQL Server instance, patching, and high availability. Provisioned under a logical server, which is a purely administrative/connectivity construct (see below), not a real VM or instance. Best fit for new cloud-native applications like Bookshelf’s catalog database, where instance-level features (cross-database transactions, SQL Server Agent, CLR, linked servers) aren’t required.

Azure SQL Managed Instance

A fully managed PaaS instance with near-complete SQL Server instance-level compatibility — cross-database queries and transactions, SQL Server Agent, Service Broker, CLR, and a shared collation are all its own, covered in Managed Databases on Azure. Deployed into a delegated subnet of a virtual network rather than reachable via public firewall rules. The right fit for a lift-and-shift of an existing SQL Server application that depends on instance-scoped features but doesn’t want to manage patching or backups.

SQL Server on Azure VMs

Infrastructure-as-a-service (IaaS): an ordinary Azure VM running a licensed SQL Server image, with the OS and SQL Server version, patch cadence, and configuration fully under the operator’s control. The right fit only when a workload needs OS-level access, an unsupported SQL Server version, or third-party agents/drivers that assume a real Windows/Linux host — everything Azure SQL Database and Managed Instance can do, they do with less operational burden.
flowchart TD A[Need SQL Server-compatible database?] --> B{Need OS-level access or third-party agents on the host?} B -->|Yes| C[SQL Server on Azure VMs] B -->|No| D{Need instance-scoped features:
cross-DB transactions, Agent, CLR, linked servers?} D -->|Yes| E[Azure SQL Managed Instance] D -->|No| F[Azure SQL Database]

References: Azure SQL Database vs. Azure SQL Managed Instance vs. SQL Server on Azure VMs, What is Azure SQL Database?.

The logical server

Every Azure SQL Database (and elastic pool) is created under a logical server — a purely administrative grouping that provides a single fully-qualified DNS name (<server>.database.windows.net), a shared Entra admin and Entra-only-authentication setting, shared firewall rules, and shared auditing/Defender configuration for every database created under it. It is not a VM, is not billed on its own, and does not run a persistent SQL Server process a customer can see — each database underneath it is otherwise fully isolated (its own compute, storage, and backup schedule). Bookshelf’s catalog-api database lives under a logical server named sql-bookshelf in rg-bookshelf.

References: What is a logical SQL server in Azure SQL Database?.

Purchasing models: DTU vs. vCore

Model How it works

DTU (Database Transaction Unit)

A blended, pre-packaged measure of compute, memory, and I/O bundled into Basic/Standard/Premium tiers with fixed size steps. Simple to reason about but opaque — there’s no way to scale compute and storage independently, and there’s no serverless or Hyperscale tier under DTU. Microsoft’s own guidance is that new deployments should default to the vCore model; DTU remains supported for existing databases and is documented mainly for migration/comparison purposes.

vCore

Compute is expressed directly as a number of virtual cores against a hardware generation, storage is provisioned (or, in Hyperscale, effectively unbounded) independently of compute, and licensing can either be included in the rate or supplied via Azure Hybrid Benefit against an existing SQL Server license. vCore is the only purchasing model that exposes Hyperscale, serverless compute, and zone redundancy, and is the model every example on this page uses.

References: Purchasing models and resources available for Azure SQL Database and SQL Managed Instance, vCore purchasing model.

Service tiers

Under the vCore model, three service tiers trade off availability architecture, storage ceiling, and cost:

Tier What it’s for

General Purpose

Compute and storage layers are separated — data files sit on remote, Azure-managed premium storage, with a single primary replica and standard availability via storage-layer redundancy. The default choice for most new workloads, including Bookshelf’s catalog database, and the only tier whose serverless compute option (below) can auto-pause.

Business Critical

Multiple synchronous replicas (built on the SQL Server Always On availability-group technology) with local SSD storage on each replica, giving faster failover and lower I/O latency than General Purpose at higher cost. One replica can be configured as a free readable secondary for reporting/read-scale-out workloads. The right fit for latency- or availability-sensitive workloads that outgrow General Purpose.

Hyperscale

A cloud-native storage architecture that decouples compute from a multi-tier, log-structured storage engine, scaling data size up to 100 TB with near-instant backups (snapshot-based, not full-copy) and fast storage auto-growth. Read scale-out replicas, zone redundancy, and the serverless compute tier (without auto-pause) are supported. The right fit for a database that may grow well beyond the few-TB ceiling of General Purpose/Business Critical, or that needs very fast restores regardless of size.

Serverless compute and elastic pools are additional configurations layered on top of General Purpose or Hyperscale (serverless) or any tier (pools), covered next.

References: Overview of the vCore-based purchasing model service tiers, Hyperscale service tier.

Serverless compute (auto-pause)

The serverless compute tier — available under General Purpose and Hyperscale (not Business Critical), on standard-series (Gen5) hardware — auto-scales compute between a configured minimum and maximum vCore count based on load. Under General Purpose only, it can also auto-pause the database entirely (billing only for storage) after a configurable period of inactivity, auto-resuming transparently on the next connection. It is billed per second of vCore actually used rather than per-hour of provisioned compute, which makes it a good fit for intermittent workloads such as a dev/test catalog database or Bookshelf’s low-traffic staging environment — not for a database that must never incur the auto-resume latency (typically low seconds, but occasionally longer) on a cold connection. Auto-pause and auto-resume are supported only on General Purpose serverless; Hyperscale serverless scales compute automatically but never pauses, and Business Critical has no serverless option at all.

References: Serverless compute tier.

Elastic pools

An elastic pool groups multiple databases under one logical server onto a shared pool of vCores/DTUs with a configured min/max per database, billed once for the pool rather than per database. It suits Bookshelf-style multi-tenant scenarios with many databases whose individual peak-usage windows don’t overlap — pooling lets idle tenants' unused capacity cover an active tenant’s spike instead of every database provisioning its own worst-case compute. A database can be moved in and out of a pool without downtime beyond a brief reconfiguration.

References: Elastic pools help you manage and scale multiple databases.

The free offer

Azure SQL Database’s free offer provides, per database and for the lifetime of the subscription, 100,000 vCore-seconds of General-Purpose serverless compute, 32 GB of data storage, and 32 GB of backup storage per calendar month at no charge, for up to 10 databases per subscription (unlike the older, now-superseded free offer tied specifically to an Azure Free Account subscription). When a database’s monthly free allowance is exhausted, it can either auto-pause until the next calendar month (the default, with PITR limited to 7 days, backup storage forced to locally redundant, and long-term retention unavailable) or continue running with usage beyond the free limits billed at standard General Purpose serverless rates — once that second option is chosen for a database it cannot be switched back. A free-offer database also cannot join an elastic pool or a failover group, can’t use Elastic Jobs or a DNS alias, and every free database in a subscription is pinned to whichever Azure region the first one was created in. This makes it a reasonable way to try Bookshelf’s catalog schema out at zero cost before sizing a production General Purpose or Hyperscale database, but not a substitute for the production tier’s backup/HA options.

References: Try Azure SQL Database for free, Azure SQL Database free offer FAQ.

Creating the logical server and database

The examples below provision Bookshelf’s logical server sql-bookshelf and its catalog database (backing the catalog-api service) with Microsoft Entra-only authentication enabled from the start — SQL authentication (a server-level admin username/password) is never created, matching the "no embedded secrets" approach used throughout this section.

In the Azure portal

  1. Search for SQL databases → + Create.

  2. On the Basics tab, choose the subscription and rg-bookshelf, then next to Server select Create new: name it sql-bookshelf, pick a region (eastus2 here — pick the region closest to your own users), and under Authentication choose Microsoft Entra-only authentication, then select an Entra user or group (for example your own signed-in account) as the Microsoft Entra admin. Confirm back on the Create SQL Database wizard.

  3. Under Database details, name the database catalog. Under Compute + storage select Configure database, choose the General Purpose tier, the Serverless compute tier, a Gen5 hardware generation, a min/max vCore range (for example 0.5—​2), and an auto-pause delay (for example 60 minutes), then Apply.

  4. On the Networking tab, set Connectivity method to Public endpoint, and under Firewall rules toggle Allow Azure services and resources to access this server to No for now (a scoped rule is added below) — private endpoint is covered separately in Private endpoint.

  5. On the Security tab, decide whether to enable Microsoft Defender for SQL now — it’s a paid plan, see Microsoft Defender for SQL, auditing, and TDE.

  6. Select Review + create, then Create.

With the Azure CLI

export RG=rg-bookshelf
export LOCATION=eastus2
export SQL_SERVER=sql-bookshelf
export SQL_DB=catalog

# The Microsoft Entra principal (user, group, or service principal) that becomes this server's Entra admin --
# resolve your own signed-in user's object id instead of hardcoding one
export ENTRA_ADMIN_OID=$(az ad signed-in-user show --query id -o tsv)
export ENTRA_ADMIN_UPN=$(az ad signed-in-user show --query userPrincipalName -o tsv)

# No --admin-user/--admin-password: Entra-only authentication means no SQL login is ever created
az sql server create \
  --resource-group "$RG" \
  --name "$SQL_SERVER" \
  --location "$LOCATION" \
  --enable-ad-only-auth \
  --external-admin-principal-type User \
  --external-admin-name "$ENTRA_ADMIN_UPN" \
  --external-admin-sid "$ENTRA_ADMIN_OID"

# General Purpose, Gen5, serverless compute between 0.5 and 2 vCores, auto-pause after 60 minutes idle
az sql db create \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$SQL_DB" \
  --edition GeneralPurpose \
  --family Gen5 \
  --capacity 2 \
  --compute-model Serverless \
  --min-capacity 0.5 \
  --auto-pause-delay 60

A Hyperscale database instead uses --edition Hyperscale (with --read-replicas for read scale-out replicas). Hyperscale also supports --compute-model Serverless with --min-capacity, but not --auto-pause-delay: auto-pause is available only on General Purpose serverless.

References: az sql server command reference, az sql server ad-only-auth command reference, Tutorial: Enable Microsoft Entra-only authentication, az sql db command reference.

Networking: firewall rules and private endpoint

By default a newly created logical server has no firewall rules at all — every connection, including from other Azure services, is rejected until a rule admits it.

In the Azure portal

  1. Open sql-bookshelf → Security → Networking.

  2. Under Firewall rules, select + Add your client IPv4 address to admit the machine currently browsing the portal, and Add a firewall rule for any other known client IP range (for example an office network or a CI runner’s static egress IP).

  3. Toggle Allow Azure services and resources to access this server to Yes only if an Azure-hosted client (App Service, Container Apps, an Azure VM without a private endpoint) needs to reach the server over its public endpoint — this opens the server to all Azure tenants' outbound traffic, not just this subscription’s, so prefer the private endpoint below for any production workload.

  4. Select Save.

With the Azure CLI

# Admit a specific known client range (replace with the real caller's IP or CIDR)
az sql server firewall-rule create \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name AllowOfficeNetwork \
  --start-ip-address 203.0.113.10 \
  --end-ip-address 203.0.113.10

# Special-cased range 0.0.0.0-0.0.0.0 admits traffic from any Azure-internal IP (any tenant) -- use sparingly
az sql server firewall-rule create \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name AllowAllWindowsAzureIps \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

References: az sql server firewall-rule command reference, Azure SQL Database and Azure Synapse IP firewall rules.

Private endpoint

A private endpoint attaches a private IP address, from a virtual network the caller already controls, to the logical server, so traffic never traverses the public internet at all — the recommended networking model for any production workload, and the one Bookshelf’s catalog-api container app should use once it moves past a dev/test public-endpoint setup. Once at least one private endpoint exists, public network access to the server can be disabled entirely.

In the Azure portal

  1. Open sql-bookshelf → Security → Networking → Private access tab → + Private endpoint.

  2. Choose the subscription, resource group, a name (for example pe-sql-bookshelf), the region, and the virtual network/subnet the catalog-api container app’s environment already uses.

  3. Under Private DNS integration, let the wizard create or reuse a privatelink.database.windows.net private DNS zone so the server’s existing FQDN resolves to the private IP with no application-side connection-string change.

  4. Select Review + create, then Create.

With the Azure CLI

export VNET=vnet-bookshelf
export SUBNET=snet-data

az network private-endpoint create \
  --resource-group "$RG" \
  --name pe-sql-bookshelf \
  --vnet-name "$VNET" \
  --subnet "$SUBNET" \
  --private-connection-resource-id $(az sql server show --resource-group "$RG" --name "$SQL_SERVER" --query id -o tsv) \
  --group-id sqlServer \
  --connection-name conn-sql-bookshelf

# Once every consumer reaches the server through a private endpoint, disable the public endpoint entirely
az sql server update \
  --resource-group "$RG" \
  --name "$SQL_SERVER" \
  --set publicNetworkAccess=Disabled

References: Private endpoints for Azure SQL Database and Azure Synapse Analytics.

Connecting with Entra ID

Every connection example below relies on the caller’s own Microsoft Entra identity (a signed-in developer, a managed identity, or a service principal already granted a role inside the catalog database via CREATE USER …​ FROM EXTERNAL PROVIDER) — no password or connection string with an embedded secret is ever created.

From Spring Boot (JDBC)

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
</dependency>
spring:
  datasource:
    url: jdbc:sqlserver://sql-bookshelf.database.windows.net:1433;database=catalog;encrypt=true;trustServerCertificate=false;authentication=ActiveDirectoryDefault

authentication=ActiveDirectoryDefault on the JDBC URL delegates to the same credential chain as DefaultAzureCredential in the Azure SDKs — a managed identity when the catalog-api container app has one assigned, or the developer’s own az login session locally — so com.example.bookshelf.catalog never constructs or stores a DataSource username/password pair. The managed identity (or developer principal) must first exist as a database user:

CREATE USER [catalog-api-identity] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [catalog-api-identity];
ALTER ROLE db_datawriter ADD MEMBER [catalog-api-identity];

References: Connecting using Azure Active Directory authentication with the JDBC driver, Authenticate and connect to SQL database using Microsoft Entra authentication.

From sqlcmd

sqlcmd -S sql-bookshelf.database.windows.net -d catalog -G --authentication-method ActiveDirectoryDefault

-G requests Entra authentication and --authentication-method ActiveDirectoryDefault mirrors the JDBC setting above, falling through the same credential chain (environment, managed identity, Azure CLI/PowerShell session, interactive browser) without prompting for anything the caller hasn’t already established elsewhere.

References: sqlcmd utility, Microsoft Entra authentication with sqlcmd and other connection tools.

Backups: point-in-time restore and long-term retention

Every Azure SQL Database is backed up automatically with no configuration required to enable the base protection:

Point-in-time restore (PITR)

Automatic full, differential, and transaction-log backups feed a continuous restore window — any point within the configured retention period (7 days by default, configurable from 1 up to 35 days) can be restored to a new database on the same logical server (an existing database can’t be overwritten). Restoring creates a new logical database, which is then typically swapped in for the original (rename both with ALTER DATABASE …​ MODIFY NAME) or mined for the specific rows that regressed.

Long-term retention (LTR)

An opt-in policy that copies specific weekly, monthly, or yearly full backups into separate, independently redundant storage for up to 10 years — useful for compliance retention that outlives PITR’s rolling window. LTR backups are restored the same way as PITR ones, to a new database, and are billed separately from the database’s own backup storage allowance. The first LTR backup can take up to 7 days to appear after a policy is set.

In the Azure portal

  1. Short-term (PITR) retention: open sql-bookshelf → Data management → Backups → Retention policies tab, tick the catalog database, select Configure policies, set Point-in-time restore retention (for example 14 days) and the differential backup frequency (12 or 24 hours), then Apply.

  2. Long-term retention: in the same Configure policies pane, set the weekly, monthly, and yearly LTR retention (for example 12 weeks, 12 months, and 5 years, with the yearly backup taken in week 16), then Apply. A value of 0 means no LTR backup of that kind is kept.

  3. Point-in-time restore: open the catalog database → Overview → Restore on the toolbar. Under Source, choose Point-in-time, pick the restore point (UTC), name the new database catalog-restored, optionally adjust Compute + storage, then Review + create → Create.

  4. Restore from an LTR backup: open sql-bookshelf → Data management → Backups, select Manage in the Available LTR backups column for catalog, select a backup → Restore, name the new database, then Review + create → Create.

With the Azure CLI

# Short-term (PITR) retention: 14 days, differential backups every 12 hours
az sql db str-policy set \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$SQL_DB" \
  --retention-days 14 \
  --diffbackup-hours 12

# Long-term retention: weekly backups kept 12 weeks, monthly 12 months, yearly (week 16) 5 years
az sql db ltr-policy set \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$SQL_DB" \
  --weekly-retention "P12W" \
  --monthly-retention "P12M" \
  --yearly-retention "P5Y" \
  --week-of-year 16

# Point-in-time restore into a NEW database on the same server (time is UTC, ISO 8601)
az sql db restore \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$SQL_DB" \
  --dest-name catalog-restored \
  --time "2026-09-20T08:30:00"

# Restore the most recent LTR backup of catalog into a new database
LTR_BACKUP_ID=$(az sql db ltr-backup list \
  --location "$LOCATION" \
  --server "$SQL_SERVER" \
  --database "$SQL_DB" \
  --only-latest-per-database True \
  --query "[0].id" -o tsv)

az sql db ltr-backup restore \
  --dest-resource-group "$RG" \
  --dest-server "$SQL_SERVER" \
  --dest-database catalog-ltr-restored \
  --backup-id "$LTR_BACKUP_ID"

References: Automated backups in Azure SQL Database and SQL Managed Instance, Restore a database from a backup, Long-term retention, Manage long-term backup retention, az sql db str-policy command reference, az sql db ltr-policy command reference, az sql db ltr-backup command reference, az sql db restore command reference.

Geo-replication and failover groups

Active geo-replication creates up to four readable secondary replicas of a database in any Azure region, kept in sync asynchronously, each independently promotable to primary on demand. A failover group wraps one or more of these primary/secondary pairs under a single pair of read-write and read-only listener endpoints (<group>.database.windows.net and <group>.secondary.database.windows.net), so an application only ever points at the group’s endpoint and never needs to know which region is currently primary — a manual or (optionally) automatic failover repoints the listener to the secondary transparently. This is the recommended building block for Bookshelf’s own disaster-recovery story once the catalog database needs a cross-region secondary, in preference to hand-rolling geo-replication without a failover group. Creating the group with catalog in it seeds the secondary database on the partner server automatically.

In the Azure portal

  1. Create the partner logical server first, for example sql-bookshelf-dr in centralus, with the same Entra-only authentication and Entra admin as sql-bookshelf (see the creation steps above).

  2. Open sql-bookshelf → Data management → Failover groups → + Add group.

  3. Name the group fog-bookshelf, select sql-bookshelf-dr as the secondary server, choose the Read/Write failover policy (Customer managed keeps failover manual; Microsoft managed with a grace period enables automatic failover), then under Databases within the group select Configure database, tick catalog, and select Create.

  4. To fail over, open Failover groups → fog-bookshelf → Failover on the command bar and confirm; select Failover again later to fail back.

  5. The read-write and read-only listener endpoints are shown on the same fog-bookshelf page — point the Spring Boot JDBC URL at fog-bookshelf.database.windows.net instead of the server name.

With the Azure CLI

export DR_LOCATION=centralus
export SQL_SERVER_DR=sql-bookshelf-dr
export FAILOVER_GROUP=fog-bookshelf

# Partner server in a second region, with the same Entra-only admin as the primary
az sql server create \
  --resource-group "$RG" \
  --name "$SQL_SERVER_DR" \
  --location "$DR_LOCATION" \
  --enable-ad-only-auth \
  --external-admin-principal-type User \
  --external-admin-name "$ENTRA_ADMIN_UPN" \
  --external-admin-sid "$ENTRA_ADMIN_OID"

# Failover group containing catalog; the geo-secondary on the partner server is created automatically
az sql failover-group create \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$FAILOVER_GROUP" \
  --partner-server "$SQL_SERVER_DR" \
  --failover-policy Manual \
  --add-db "$SQL_DB"

# Planned failover: run against the server that should BECOME primary
az sql failover-group set-primary \
  --resource-group "$RG" \
  --server "$SQL_SERVER_DR" \
  --name "$FAILOVER_GROUP"

# Confirm the current roles
az sql failover-group show \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --name "$FAILOVER_GROUP" \
  --query "{role:replicationRole, state:replicationState}"

--failover-policy Automatic --grace-period 1 (hours) instead lets Microsoft initiate the failover after an outage lasting longer than the grace period.

References: Active geo-replication, Failover groups overview, Configure a failover group for Azure SQL Database, az sql failover-group command reference.

Microsoft Defender for SQL, auditing, and TDE

Microsoft Defender for SQL (surfaced today under the Defender for Databases plan in Microsoft Defender for Cloud) adds vulnerability assessment and advanced threat protection — anomalous-access and SQL-injection detection, among other alerts — on top of a logical server, billed per protected server (covering every database under it) once enabled; it is a deliberate, priced opt-in, recommended at subscription level so new servers are covered automatically, rather than something enabled implicitly by any of the commands above.

Auditing writes every configured category of database event (by default successful and failed logins plus every completed batch; schema and security-principal changes as configured) to a chosen destination — a Log Analytics workspace, an Event Hub, or Blob Storage — independently of Defender for SQL, and is the source most incident investigations actually query first. A server-level audit policy applies to every existing and future database on the server.

Transparent Data Encryption (TDE) encrypts the database’s data and log files at rest and is turned on by default for every newly created Azure SQL Database, using a Microsoft-managed service key; switching to a customer-managed key in Key Vault (bring-your-own-key) is optional and does not change TDE’s behavior otherwise — there’s no scenario where TDE is off for a new database.

In the Azure portal

  1. Defender for SQL (subscription level, recommended): open Microsoft Defender for Cloud → Environment settings, select the subscription, set the Databases plan (or its Azure SQL Databases resource type) to On, then Save.

  2. Defender for SQL (single server): open sql-bookshelf → Security → Defender for Cloud → Enable Microsoft Defender for SQL.

  3. Auditing: open sql-bookshelf → Security → Auditing, switch Enable Azure SQL Auditing on, tick Log Analytics, pick the law-bookshelf workspace, then Save.

  4. TDE: open the catalog database → Security → Transparent data encryption to confirm data encryption is on; the server-level sql-bookshelf → Security → Transparent data encryption blade is where a customer-managed Key Vault key (the TDE protector) would be selected.

With the Azure CLI

# Defender for SQL on every Azure SQL server in the subscription (the SqlServers plan)
az security pricing create --name SqlServers --tier standard

# Log Analytics workspace to receive the audit records
export LAW=law-bookshelf
az monitor log-analytics workspace create \
  --resource-group "$RG" \
  --workspace-name "$LAW" \
  --location "$LOCATION"
export LAW_ID=$(az monitor log-analytics workspace show \
  --resource-group "$RG" --workspace-name "$LAW" --query id -o tsv)

# Server-level auditing to Log Analytics (applies to catalog and every future database on the server)
az sql server audit-policy update \
  --resource-group "$RG" \
  --name "$SQL_SERVER" \
  --state Enabled \
  --log-analytics-target-state Enabled \
  --log-analytics-workspace-resource-id "$LAW_ID"

# TDE is already on -- confirm it
az sql db tde show \
  --resource-group "$RG" \
  --server "$SQL_SERVER" \
  --database "$SQL_DB" \
  --query state

References: Microsoft Defender for Azure SQL Database, Enable Microsoft Defender for SQL, az security pricing command reference, Auditing for Azure SQL Database and Azure Synapse Analytics, Set up auditing, az sql server audit-policy command reference, az monitor log-analytics workspace command reference, Transparent data encryption for SQL Database, SQL Managed Instance, and Azure Synapse Analytics, az sql db tde command reference.

Pricing

Both purchasing models bill compute and storage separately; the pricing calculator is the authoritative source for a given region/tier/hardware-generation combination, since rates and available generations both change between release cycles. Serverless compute is billed per vCore-second actually consumed (dropping toward zero while auto-paused, aside from storage), Hyperscale bills storage growth-based rather than a fixed provisioned size, and the free offer described above covers a modest General Purpose serverless workload at zero cost. Reserved capacity (1- or 3-year vCore reservations) and Azure Hybrid Benefit (bringing an existing SQL Server license) both reduce the compute rate further for steady-state production workloads, at the cost of the up-front commitment.

References: Azure SQL Database pricing, Save costs with reserved capacity, Azure Hybrid Benefit.

Troubleshooting

Cannot open server '…​' requested by the login. Client with IP address '…​' is not allowed to access the server. The calling client’s egress IP isn’t covered by any firewall rule (or the connection is arriving through a path that requires a private endpoint instead). Confirm the actual outbound IP with the error message itself (SQL Server reports it verbatim) and either add a scoped firewall rule or move the caller behind the private endpoint in Private endpoint.

Login failed for user '<token-identified principal>'. after switching a server to Entra-only authentication. The connecting principal (managed identity, service principal, or user) has never been created as a database user with CREATE USER …​ FROM EXTERNAL PROVIDER, or was created before being granted the roles it now needs. Entra-only authentication removes SQL logins entirely, so there is no SQL-auth fallback to fall back to — the fix is always granting/creating the Entra-mapped database user, never re-enabling SQL authentication.

A serverless database’s first query after being idle is unexpectedly slow. This is the auto-resume path: a paused database takes on the order of low seconds (occasionally longer under load) to resume before it can serve the first connection. An application with a hard-real-time SLA on cold connections should either disable auto-pause (set the auto-pause delay to -1) or move off serverless for that specific database.

ActiveDirectoryDefault authentication fails locally but works in Azure. DefaultAzureCredential’s underlying chain differs by environment — locally it typically falls through to the developer’s `az login session or Visual Studio/IDE credential, while in Azure it uses the resource’s managed identity. Confirm which credential the chain is actually resolving to (its debug logging names the credential type it succeeded with) before assuming the target database user/role assignment is wrong.

Restoring a PITR backup "loses" recent writes. PITR always restores to a new database as of the chosen timestamp; if the timestamp chosen is earlier than intended, or the restore is mistaken for an in-place rollback of the original database, transactions after that point are absent from the restored copy but remain untouched in the original — reconcile by comparing the two rather than assuming data was destroyed.

References: Troubleshoot common connectivity issues in Azure SQL Database, Logins, users, and roles in SQL Database and SQL Managed Instance.

Clean up

az group delete --name "$RG" --yes --no-wait

This removes sql-bookshelf, its catalog database, every firewall rule and private endpoint created above, and any failover group or geo-replicated secondary created against it. A long-term retention backup is stored independently of the logical server and database and is not removed by deleting the resource group — delete LTR backups explicitly (az sql db ltr-backup delete) if they should not be retained.

Related pages

References