Learning: Azure SQL Database advanced - private networking, security, failover groups, performance tuning and CI/CD

#About this session

Warning

This lab creates resources in two regions, including a geo-secondary database. Delete the resource group at the end (Module 10). Check current pricing before you start.

#Module 1: the target architecture

            Region A (primary)                        Region B (secondary)
  ┌──────────────────────────────────┐      ┌──────────────────────────────────┐
  │ VNet 10.10.0.0/16                │      │ VNet 10.20.0.0/16                │
  │  snet-app  ── App (managed id) ──┼──┐   │  snet-pe ── private endpoint ──┐ │
  │  snet-pe   ── private endpoint ─┐│  │   └────────────────────────────────┼─┘
  └─────────────────────────────────┼┘  │                                    │
                                    ▼   │ failover group listener            ▼
                       sql-adv-pri (Entra-only) ◀── geo-replication ──▶ sql-adv-sec
                          └ sqldb-orders                                └ sqldb-orders (secondary)
  Private DNS zone privatelink.database.windows.net linked to both VNets
  Auditing → Log Analytics · Defender for SQL · TDE key in Key Vault

Set variables once (Cloud Shell Bash):

RG=rg-sqladv
LOCA=centralindia
LOCB=southindia
PRI=sql-adv-pri-$RANDOM
SEC=sql-adv-sec-$RANDOM
DB=sqldb-orders
FG=fg-orders-$RANDOM
ME_UPN=$(az ad signed-in-user show --query userPrincipalName -o tsv)
ME_OID=$(az ad signed-in-user show --query id -o tsv)

az group create -n $RG -l $LOCA

#Module 2: servers with Entra-only authentication

for S in "$PRI:$LOCA" "$SEC:$LOCB"; do
  NAME=${S%%:*}; LOC=${S##*:}
  az sql server create -g $RG -n $NAME -l $LOC \
    --enable-ad-only-auth \
    --external-admin-principal-type User \
    --external-admin-name "$ME_UPN" --external-admin-sid "$ME_OID" \
    --assign-identity
done

az sql db create -g $RG -s $PRI -n $DB \
  --edition GeneralPurpose --family Gen5 --capacity 2 \
  --backup-storage-redundancy Zone --zone-redundant false

In production, make the Entra admin a group (for example sg-sql-admins), not a person. That avoids the "nobody can administer it" problem when people leave.

Check Entra-only is enforced:

az sql server ad-only-auth get -g $RG -n $PRI -o table

#Module 3: private networking

#VNets, subnets and the private endpoint

az network vnet create -g $RG -n vnet-a -l $LOCA --address-prefixes 10.10.0.0/16 \
  --subnet-name snet-pe --subnet-prefixes 10.10.1.0/24
az network vnet subnet create -g $RG --vnet-name vnet-a -n snet-app --address-prefixes 10.10.2.0/24

PRI_ID=$(az sql server show -g $RG -n $PRI --query id -o tsv)

az network private-endpoint create -g $RG -n pe-$PRI -l $LOCA \
  --vnet-name vnet-a --subnet snet-pe \
  --private-connection-resource-id $PRI_ID \
  --group-id sqlServer --connection-name pe-$PRI-conn

#Private DNS: the step people miss

Clients must resolve <server>.database.windows.net to the private IP. Azure does this through the privatelink.database.windows.net zone:

az network private-dns zone create -g $RG -n privatelink.database.windows.net
az network private-dns link vnet create -g $RG -n link-vnet-a \
  --zone-name privatelink.database.windows.net --virtual-network vnet-a --registration-enabled false

az network private-endpoint dns-zone-group create -g $RG \
  --endpoint-name pe-$PRI -n default \
  --private-dns-zone privatelink.database.windows.net --zone-name sql

From a VM in vnet-a:

nslookup $PRI.database.windows.net
# expect: an alias to $PRI.privatelink.database.windows.net with a 10.10.1.x address

On-premises clients need DNS forwarding to Azure (Azure DNS Private Resolver or your DNS servers forwarding the zone). See the private endpoint DNS Sev1 post for troubleshooting.

#Turn off public access

az sql server update -g $RG -n $PRI --enable-public-network false

#Module 4: application access with a managed identity

Give your app (App Service, Container App, Function or VM) a system-assigned identity, then create a contained user for it. Run this in sqldb-orders as the Entra admin:

CREATE USER [app-orders-api] FROM EXTERNAL PROVIDER;      -- name of the managed identity
CREATE ROLE app_orders;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_orders;
GRANT EXECUTE ON SCHEMA::dbo TO app_orders;
ALTER ROLE app_orders ADD MEMBER [app-orders-api];

Connection string: no password, plus retry logic.

Server=tcp:<failover-group-name>.database.windows.net,1433;Database=sqldb-orders;
Authentication=Active Directory Managed Identity;Encrypt=True;Connect Timeout=30;

# Python with ODBC Driver 18 and a system-assigned managed identity
import pyodbc, time
conn_str = ("Driver={ODBC Driver 18 for SQL Server};"
            "Server=tcp:fg-orders.database.windows.net,1433;Database=sqldb-orders;"
            "Authentication=ActiveDirectoryMsi;Encrypt=yes;")
for attempt in range(5):
    try:
        conn = pyodbc.connect(conn_str, timeout=30)
        break
    except pyodbc.Error:
        time.sleep(2 ** attempt)        # transient faults: failover, scaling, reconfiguration

#Module 5: audit, detect, encrypt

#Auditing to Log Analytics

az monitor log-analytics workspace create -g $RG -n law-sqladv -l $LOCA
LAW_ID=$(az monitor log-analytics workspace show -g $RG -n law-sqladv --query id -o tsv)

az sql server audit-policy update -g $RG -n $PRI --state Enabled \
  --log-analytics-target-state Enabled --log-analytics-workspace-resource-id $LAW_ID

Query the audit log (KQL, in the workspace):

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where action_name_s in ("DATABASE AUTHENTICATION FAILED", "SCHEMA OBJECT ACCESS GROUP")
| project TimeGenerated, server_principal_name_s, client_ip_s, action_name_s, statement_s
| order by TimeGenerated desc

Table and column names depend on the diagnostic settings mode; check your workspace.

#Microsoft Defender for SQL

Enable Defender for Azure SQL (Defender for Cloud plan, or per server). It adds vulnerability assessment and alerts for SQL injection patterns, unusual locations and brute-force attempts. Route alerts to the security team's channel.

#TDE with a customer-managed key

TDE is on by default with a service-managed key. Some policies require your own key in Key Vault:

az keyvault create -g $RG -n kv-sqladv-$RANDOM -l $LOCA --enable-purge-protection true --enable-rbac-authorization true
KV=$(az keyvault list -g $RG --query "[0].name" -o tsv)
az keyvault key create --vault-name $KV -n tde-key --kty RSA --size 3072
KEY_ID=$(az keyvault key show --vault-name $KV -n tde-key --query key.kid -o tsv)

# Let the server's identity use the key
PRI_MI=$(az sql server show -g $RG -n $PRI --query identity.principalId -o tsv)
az role assignment create --assignee-object-id $PRI_MI --assignee-principal-type ServicePrincipal \
  --role "Key Vault Crypto Service Encryption User" --scope $(az keyvault show -n $KV --query id -o tsv)

az sql server key create -g $RG -s $PRI --kid $KEY_ID
az sql server tde-key set -g $RG -s $PRI --server-key-type AzureKeyVault --kid $KEY_ID

Caution

If the key is deleted or access is removed, databases become inaccessible. Keep purge protection on, back up the key, and give the geo-secondary server access to the same key (or a copy in its region's vault) before creating geo-replication.

#Data-level protection, briefly

#Module 6: high availability and disaster recovery

#What you get without doing anything

That covers node and zone failures. Regional failure needs geo-replication.

#Failover group

A failover group gives you a listener that always points at the current primary: <fg-name>.database.windows.net (read-write) and <fg-name>.secondary.database.windows.net (read-only).

az sql failover-group create -g $RG -s $PRI -n $FG \
  --partner-server $SEC --add-db $DB \
  --failover-policy Manual

Watch the secondary seed:

az sql db replica list-links -g $RG -s $PRI -n $DB -o table

Repeat Module 3 (VNet, private endpoint, DNS link) for region B, so clients there can resolve and reach the secondary privately.

#Test a planned failover

-- Before: on the listener
SELECT @@SERVERNAME AS server_now, DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS mode;

az sql failover-group set-primary -g $RG -s $SEC -n $FG       # run against the new primary

Run the query again through the listener: the server name changes, the app reconnects (retry logic), no data lost (planned failover is synchronised). Fail back the same way with -s $PRI.

Write the runbook: who decides, how DNS/connection strings behave, how to check data loss, how to fail back (see the Azure DR strategy post).

#Module 7: performance tuning

Create a workload to tune:

CREATE TABLE dbo.Orders (
    OrderId     BIGINT IDENTITY CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerId  INT NOT NULL,
    Status      VARCHAR(20) NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL
);

INSERT INTO dbo.Orders (CustomerId, Status, OrderDate, Amount)
SELECT TOP (2000000)
       ABS(CHECKSUM(NEWID())) % 50000,
       CHOOSE(1 + ABS(CHECKSUM(NEWID())) % 4, 'New','Paid','Shipped','Cancelled'),
       DATEADD(minute, -(ABS(CHECKSUM(NEWID())) % 525600), SYSUTCDATETIME()),
       (ABS(CHECKSUM(NEWID())) % 100000) / 100.0
FROM sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c;

-- The "slow" query: run it 50+ times
DECLARE @c INT = ABS(CHECKSUM(NEWID())) % 50000;
SELECT OrderId, OrderDate, Amount FROM dbo.Orders WHERE CustomerId = @c AND Status = 'Paid';

#Find it with Query Store

SELECT TOP (10) q.query_id, qt.query_sql_text,
       SUM(rs.count_executions) AS execs,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
       AVG(rs.avg_logical_io_reads) AS avg_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

#What is it waiting on?

SELECT TOP (10) wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_db_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%' AND wait_type NOT IN ('WAITFOR','XE_TIMER_EVENT','BROKER_TASK_STOP')
ORDER BY wait_time_ms DESC;

SELECT TOP (20) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent
FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

#Fix: index and verify

CREATE INDEX IX_Orders_Customer_Status ON dbo.Orders (CustomerId, Status) INCLUDE (OrderDate, Amount)
WITH (ONLINE = ON);

Run the query again and compare logical reads (SET STATISTICS IO ON) and Query Store numbers.

#Automatic tuning

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF);

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

SELECT name, type, reason, state, JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations;

FORCE_LAST_GOOD_PLAN reverts plan regressions automatically. Index creation is useful for many workloads; review its choices regularly.

#Blocking check

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Azure SQL Database uses Read Committed Snapshot Isolation by default, so readers rarely block writers. Blocking usually comes from long write transactions.

#Module 8: scale-out options

# Move to Hyperscale (online; plan for the copy time on large DBs)
az sql db update -g $RG -s $PRI -n $DB --edition Hyperscale --family Gen5 --capacity 2

# Elastic pool for many small databases
az sql elastic-pool create -g $RG -s $PRI -n pool-tenants --edition GeneralPurpose --family Gen5 --capacity 4 \
  --db-min-capacity 0 --db-max-capacity 2

A database in a failover group must be scaled on the secondary first when scaling up (and primary first when scaling down), so the secondary is never smaller than the primary.

#Module 9: protect data and ship changes safely

#Ledger for tamper evidence

CREATE TABLE dbo.PaymentAudit (
    PaymentId  BIGINT NOT NULL,
    Action     VARCHAR(20) NOT NULL,
    Amount     DECIMAL(12,2) NOT NULL,
    ActionAt   DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
) WITH (LEDGER = ON (APPEND_ONLY = ON));

EXEC sys.sp_verify_database_ledger_from_digest_storage;   -- after configuring automatic digest storage

#Long-term retention

az sql db ltr-policy set -g $RG -s $PRI -n $DB \
  --weekly-retention P8W --monthly-retention P12M --yearly-retention P7Y --week-of-year 1

LTR backups survive database deletion and can be restored to any server in the subscription.

#Schema changes as code (dacpac)

Keep the schema in a SQL database project (SDK-style .sqlproj), build a dacpac in CI, and publish with an identity that has rights only for deployments:

dotnet build OrdersDb.sqlproj -c Release
SqlPackage /Action:Script  /SourceFile:bin/Release/OrdersDb.dacpac \
  /TargetConnectionString:"Server=tcp:$FG.database.windows.net;Database=$DB;Authentication=Active Directory Default;Encrypt=True" \
  /OutputPath:deploy-preview.sql /p:BlockOnPossibleDataLoss=true
SqlPackage /Action:Publish /SourceFile:bin/Release/OrdersDb.dacpac \
  /TargetConnectionString:"Server=tcp:$FG.database.windows.net;Database=$DB;Authentication=Active Directory Default;Encrypt=True" \
  /p:BlockOnPossibleDataLoss=true

#Module 10: clean up

az sql failover-group delete -g $RG -s $PRI -n $FG
az group delete -n $RG --yes --no-wait

Key Vault with purge protection stays soft-deleted for its retention period; that's expected.

#Exercises

1. Add a second app identity that may only EXECUTE one stored procedure. Prove it can't SELECT from dbo.Orders.
2. From a machine outside the VNet, try to connect after disabling public access. Note the exact error, then explain it in one sentence.
3. Run a forced failover in the lab (--allow-data-loss) while a write loop is running. How many writes were lost? What does your runbook need to say about it?
4. Force a plan regression (for example with a hint in a stored procedure), and watch FORCE_LAST_GOOD_PLAN correct it in sys.dm_db_tuning_recommendations.
5. Add row-level security so a sales_south group sees only orders for a set of customer IDs.
6. Build a pipeline (GitHub Actions or Azure DevOps) that publishes the dacpac to a test database on every merge and to production after approval.

#Knowledge check

1. Why does a private endpoint fail for clients even when it shows "Approved", and how do you check?
2. What is the difference between built-in HA and a failover group?
3. Why scale the geo-secondary before the primary when scaling up?
4. Which DMV metric tells you the log rate limit is the bottleneck, and what are your options?
5. What happens to databases if the customer-managed TDE key becomes unavailable?
6. Why script a dacpac deployment before publishing it?

#Summary

• Production Azure SQL is private (private endpoint + DNS), Entra-only, and accessed by managed identities.
• Auditing, Defender and (where required) customer-managed TDE keys give detection and control. Plan key access for every replica.
• Built-in HA handles node and zone failures; failover groups handle regions, and need a tested runbook.
• Tune from evidence: Query Store, waits and resource stats, then let automatic tuning handle plan regressions.
• Ship schema changes as reviewed dacpac deployments from a pipeline.

#Further reading

• Microsoft Learn: Azure Private Link for Azure SQL Database, Failover groups overview, Monitor performance with DMVs, Automatic tuning, Transparent data encryption with customer-managed keys, Ledger overview, SqlPackage Publish.
• On this site: Azure SQL Query Store and automatic tuning, Azure SQL elastic pools and serverless sizing, Azure Private Link vs service endpoints.