---
title: "DP-300 Study Guide — Microsoft Azure Database Administrator Associate"
description: "An interactive study guide built on 7 memory techniques to help you pass the Microsoft Azure Database Administrator Associate exam."
url: "https://lucidlabs.com.au/insights/dp-300"
---

# Master the DP-300

An interactive study guide built on 7 memory techniques to help you pass the Microsoft Azure Database Administrator Associate exam.

Plan & Implement 20-25%Secure Environment 15-20%Monitor & Optimise 25-30%Automation 15-20%HA/DR 15-20%

What it covers

Azure SQL Database/MI/VM provisioning, security (TDE, Always Encrypted, auditing), performance monitoring (DMVs, Query Store, Intelligent Insights), automation (elastic jobs, maintenance plans), high availability (Always On, failover groups, geo-replication), and migration.

Ideal for

Database administrators, SQL Server DBAs transitioning to cloud, and infrastructure engineers managing Azure SQL workloads.

Aspire to this if

You manage SQL Server databases and want to validate your Azure database administration skills, or you're moving from on-premises SQL Server to Azure SQL services.

Section 1 / Spatial Memory

## The Map

Tap any component to see what you need to know for the exam.

**🗄️ Provisioning**

SQL DB · Managed Instance · VM

**🚚 Migration**

DMS · BACPAC · Replication

**🌐 Networking**

VNet · Private Endpoint · Firewall

**🛡️ Security**

TDE · Always Encrypted · Auditing

**🔑 Access Control**

RBAC · Row-Level · Ledger

**📊 Monitoring**

DMVs · Query Store · Insights

**⚡ Performance**

Indexes · Tuning · Waits

**⚙️ Automation**

Elastic Jobs · Agent · Runbooks

**🛡️ HA/DR**

Always On · Failover Groups · Geo-Rep

**💾 Backup & Restore**

PITR · LTR · Geo-Redundant

**📈 Scaling**

vCore · DTU · Elastic Pool

**🔍 Governance**

Purview · Compliance · Policy

Section 2 / Narrative Memory

## The Story

Follow the narrative to build a mental model of how everything connects.

🏗️

### Laying the Foundation

Every Azure database journey begins with provisioning the right service. Azure SQL Database offers a fully managed PaaS with vCore or DTU pricing. Managed Instance provides near-complete SQL Server compatibility for lift-and-shift. SQL Server on VMs gives you full control when you need specific versions or OS access.

**Exam Intel**Know when to choose each: SQL DB for new cloud-native apps, MI for on-prem migrations needing cross-database queries or CLR, VM for features MI doesn’t support (like FILESTREAM). Understand vCore vs DTU, service tiers (GP/BC/Hyperscale), and elastic pool sizing.

🚚

### The Great Migration

Migrating databases to Azure requires careful planning. The Data Migration Assistant assesses compatibility, Azure Database Migration Service handles the heavy lifting, and BACPAC exports provide a portable format. Transactional replication enables near-zero downtime cutover for mission-critical workloads.

**Exam Intel**DMA = assessment and compatibility checking. DMS = actual migration (online = minimal downtime, offline = simpler). BACPAC = schema + data export/import. Know the migration path for each target: SQL DB (DMS/BACPAC), MI (DMS/native backup restore), VM (backup/restore, log shipping).

🛡️

### Locking the Vault

Security in Azure SQL is defence-in-depth. TDE encrypts data at rest by default. Always Encrypted keeps sensitive data encrypted even from the DBA. Dynamic Data Masking hides data from non-privileged users. Microsoft Entra ID centralises identity, while auditing tracks every action for compliance.

**Exam Intel**TDE = at-rest encryption (automatic). Always Encrypted = client-side, column-level (DBA can’t see plaintext). Dynamic Data Masking = query-time obfuscation (not true encryption). Row-Level Security = filter predicate on SELECT. Auditing targets: Azure Storage, Log Analytics, Event Hubs.

🔍

### The Watchful Eye

Monitoring is the DBA’s superpower. Dynamic Management Views expose real-time engine stats. Query Store captures execution plans and performance history. Intelligent Insights uses machine learning to detect anomalies like plan regression or resource limits. Azure Monitor ties it all together.

**Exam Intel**Key DMVs: sys.dm\_exec\_requests (active queries), sys.dm\_os\_wait\_stats (waits), sys.dm\_db\_index\_usage\_stats (index usage), sys.dm\_db\_missing\_index\_details (missing indexes). Query Store = flight recorder for queries. Intelligent Insights = AI-detected patterns in Log Analytics.

⚡

### The Performance Engine

Performance tuning in Azure SQL combines art and science. Wait statistics reveal what the engine is waiting on. Missing index recommendations guide index creation. Automatic tuning can create indexes and force plans without human intervention. For analytical queries, columnstore indexes provide massive speedups.

**Exam Intel**Wait types to know: PAGEIOLATCH\_SH (I/O), LCK\_M\_S/X (locking), CXPACKET (parallelism), RESOURCE\_SEMAPHORE (memory grants). Automatic tuning: CREATE INDEX, DROP INDEX, FORCE PLAN. Columnstore = batch-mode processing, 10x compression. Understand MAXDOP, cost threshold for parallelism.

⚙️

### The Automation Factory

Repetitive tasks deserve automation. Elastic jobs run T-SQL across multiple Azure SQL databases on a schedule. SQL Server Agent handles jobs on MI and VMs. Azure Automation runbooks manage maintenance via PowerShell. For complex multi-step workflows, Logic Apps orchestrate actions triggered by database events.

**Exam Intel**Elastic jobs = Azure SQL DB multi-database scheduling (replaces Agent). SQL Agent available on MI and VM only. Azure Automation = PowerShell/Python runbooks for resource management. Maintenance plans = SQL VM backup, integrity, index rebuild schedules. Know which tool works with which deployment.

🏠

### Always Available

High availability and disaster recovery are non-negotiable. Azure SQL Database has built-in HA via replicas. Auto-failover groups provide geo-failover with a single connection endpoint. Active geo-replication creates readable secondaries. Always On Availability Groups protect SQL VMs with synchronous commit within a region.

**Exam Intel**Built-in HA: GP uses remote storage, BC uses local SSD replicas. Failover groups: automatic geo-failover, read-write listener endpoint. Geo-replication: up to 4 readable secondaries, manual failover. Always On AG: synchronous (same region HA) vs asynchronous (DR). PITR: 7-35 days. LTR: up to 10 years.

💾

### The Safety Net

Backups are the last line of defence. Azure SQL automates full, differential, and log backups. Point-in-time restore recovers to any second within the retention window. Long-term retention stores weekly, monthly, or yearly backups for up to a decade. Geo-redundant storage ensures backups survive regional outages.

**Exam Intel**Automated backup cadence: full (weekly), diff (12-24hr), log (5-10min). PITR retention: 7 days default, up to 35 days. LTR policies: weekly (W), monthly (M), yearly (Y). Backup storage redundancy: LRS, ZRS, GRS, RA-GRS. SQL VM: Azure Backup service or BACKUP TO URL for blob storage.

📈

### Scaling the Summit

Azure SQL scales to meet demand. Scale up by changing tiers or adding vCores. Scale out with read replicas on Business Critical and Hyperscale. Serverless auto-pauses during inactivity to save costs. Hyperscale supports databases up to 100 TB with near-instant backups and rapid scale-out reads.

**Exam Intel**vCore = flexible (compute + storage separate, Azure Hybrid Benefit). DTU = bundled (simpler but less control). Serverless = auto-scale + auto-pause (dev/test, bursty workloads). Hyperscale = up to 100 TB, page servers, near-instant PITR. Read Scale-Out offloads reporting to replicas (ApplicationIntent=ReadOnly).

🔒

### Governance & Compliance

Governing a database estate requires visibility and control. Azure Policy enforces configuration standards. Microsoft Purview discovers and classifies sensitive data. Vulnerability Assessment scans for security gaps. Data classification labels columns containing personal, financial, or health information — ensuring regulatory compliance.

**Exam Intel**Azure Policy: deny or audit non-compliant SQL configurations (e.g., enforce TDE, minimum TLS). Vulnerability Assessment: baseline + periodic scans for misconfigurations. Data Classification: sensitivity labels (Confidential, Highly Confidential) in SSMS or portal. Purview integration for estate-wide governance.

Section 3 / Acronym Memory

## Mnemonic Wall

Memorable acronyms and phrases to anchor key exam concepts in your memory.

🗄️

DMV

**D**atabase, **M**anaged Instance, **V**M

Azure SQL deployment options from most managed to least. PaaS → PaaS → IaaS. Choose based on compatibility needs.

🛡️

TADAM

**T**DE, **A**lways Encrypted, **D**ynamic Masking, **A**uditing, **M**icrosoft Entra

Five pillars of Azure SQL security. TDE = at rest, AE = client-side columns, DDM = query-time obfuscation, Auditing = tracking, Entra = identity.

📊

QSI

**Q**uery Store, **S**tat DMVs, **I**ntelligent Insights

Three monitoring pillars. Query Store = historical plans. DMVs = real-time stats. Intelligent Insights = AI anomaly detection.

⚡

PLCR

**P**AGEIOLATCH, **L**CK, **C**XPACKET, **R**ESOURCE\_SEMAPHORE

Critical wait types. I/O waits, lock contention, parallelism waits, memory grant waits. Diagnose bottlenecks by wait category.

⚙️

EALM

**E**lastic Jobs, **A**gent, **L**ogic Apps, Main**t**enance Plans

Automation tools by deployment. Elastic Jobs = SQL DB. Agent = MI/VM. Logic Apps = event-driven workflows. Maintenance Plans = SQL VM.

🔄

FAGR

**F**ailover Groups, **A**lways On AG, **G**eo-Replication, **R**eplica Read Scale-Out

HA/DR mechanisms. Failover groups = geo-failover with listener. AG = VM/MI HA. Geo-rep = readable secondaries. Read Scale-Out = offload reads.

💾

FDL

**F**ull, **D**ifferential, **L**og

Automated backup types. Full = weekly, Differential = 12-24hr, Log = 5-10min. All three enable point-in-time restore.

📈

GBH

**G**eneral Purpose, **B**usiness Critical, **H**yperscale

Azure SQL service tiers. GP = remote storage, cost-effective. BC = local SSD, built-in HA replicas. Hyperscale = up to 100 TB, page servers.

🚚

DDB

**D**MA, **D**MS, **B**ACPAC

Migration toolchain. DMA = assess compatibility. DMS = execute migration (online/offline). BACPAC = portable schema + data export.

🔑

RLS

**R**ow-**L**evel **S**ecurity

Filter predicate on tables that restricts which rows users can see. Uses security policy with inline table-valued function. Transparent to the application.

🌐

PSE

**P**rivate Endpoint, **S**ervice Endpoint, Fire**w**all

Three network security layers. Private Endpoint = VNet integration via Private Link. Service Endpoint = Azure backbone routing. Firewall = IP-based rules.

📊

ATC

**A**uto-Create Index, **T**une (Force Plan), **C**orrect (Drop Index)

Automatic tuning actions in Azure SQL Database. Creates missing indexes, forces last known good plan, drops unused indexes.

🔒

CMK-CEK

**C**olumn **M**aster **K**ey + **C**olumn **E**ncryption **K**ey

Always Encrypted key hierarchy. CMK stored in Azure Key Vault (client-side). CEK encrypts column data, itself encrypted by CMK.

🏠

PITR-LTR

**P**oint-**I**n-**T**ime **R**estore + **L**ong-**T**erm **R**etention

Two recovery strategies. PITR = any second in 7-35 days. LTR = weekly/monthly/yearly backups up to 10 years. Both use automated backups.

💰

vDSH

**v**Core, **D**TU, **S**erverless, **H**yperscale

Purchasing and compute models. vCore = flexible. DTU = bundled. Serverless = auto-pause. Hyperscale = 100 TB scale-out. Know when to use each.

Section 4 / Contrast Memory

## Versus Arena

Side-by-side comparisons to sharpen your understanding of similar concepts.

vs

SQL DBvsManaged InstancevsVM

Click to compare

#### Azure SQL Deployment Options

| Aspect | SQL DB | Managed Instance | VM |
| --- | --- | --- | --- |
| Model | PaaS (SQL DB) | PaaS (MI) | IaaS (VM) |
| Compatibility | Most T-SQL features | ~99% SQL Server | 100% |
| SQL Agent | No (use Elastic Jobs) | Yes | Yes |
| Cross-DB queries | No (elastic query) | Yes | Yes |
| CLR/.NET | No | Yes | Yes |
| OS access | No | No | Yes |

Click to flip back

vs

vCorevsDTU

Click to compare

#### Purchasing Models

| Aspect | vCore | DTU |
| --- | --- | --- |
| Pricing | Compute + storage separate | Bundled package |
| Scaling | Independent scaling | Scale as unit |
| Hybrid Benefit | Yes (save up to 55%) | No |
| Tiers | GP, BC, Hyperscale | Basic, Standard, Premium |
| Flexibility | More control | Simpler to size |
| Best for | Production workloads | Predictable, simple apps |

Click to flip back

vs

General PurposevsBusiness Critical

Click to compare

#### Service Tiers

| Aspect | General Purpose | Business Critical |
| --- | --- | --- |
| Storage | Remote (Azure Premium Storage) | Local SSD |
| HA replicas | 0 readable | 1 readable (Read Scale-Out) |
| Latency | 5-10ms | 1-2ms |
| In-Memory OLTP | No | Yes |
| Zone redundancy | Optional | Optional |
| Cost | Lower | Higher |

Click to flip back

vs

TDEvsAlways Encrypted

Click to compare

#### Encryption at Rest vs Client-Side

| Aspect | TDE | Always Encrypted |
| --- | --- | --- |
| Scope | Entire database | Specific columns |
| Encryption location | Server-side (storage engine) | Client-side (driver) |
| DBA sees plaintext? | Yes | No |
| Performance impact | Minimal | Query limitations |
| Key management | Service or customer-managed | CMK in Key Vault |
| Default | Enabled by default | Opt-in per column |

Click to flip back

vs

Failover GroupsvsGeo-Replication

Click to compare

#### HA/DR Options

| Aspect | Failover Groups | Geo-Replication |
| --- | --- | --- |
| Scope | Group of databases | Single database |
| Failover | Automatic (with listener) | Manual only |
| Endpoint | Read-write + read-only listener | Direct connection string |
| Managed Instance | Supported | Not supported |
| Max secondaries | 1 region | Up to 4 regions |
| Best for | Production DR with auto-failover | Read scale-out, custom DR |

Click to flip back

vs

Elastic JobsvsSQL Agent

Click to compare

#### Job Scheduling

| Aspect | Elastic Jobs | SQL Agent |
| --- | --- | --- |
| Available on | Azure SQL Database | MI and SQL VM |
| Multi-database | Yes (target groups) | Single instance |
| Language | T-SQL | T-SQL, SSIS, PowerShell, cmdexec |
| Monitoring | Portal + T-SQL views | SSMS Job Activity Monitor |
| Alerts | Azure Monitor integration | SQL Server alerts/operators |
| Cost | Dedicated job database | Included with instance |

Click to flip back

vs

Query StorevsDMVs

Click to compare

#### Performance Monitoring

| Aspect | Query Store | DMVs |
| --- | --- | --- |
| Persistence | Survives restart (persisted) | Reset on restart (in-memory) |
| History | Days/weeks of history | Current snapshot only |
| Plan tracking | Captures all plan changes | Current plan only |
| Regression analysis | Built-in (regressed queries) | Manual comparison needed |
| Force plan | Yes (pin good plan) | No |
| Best for | Historical tuning | Real-time troubleshooting |

Click to flip back

vs

OnlinevsOffline Migration

Click to compare

#### Migration Approaches

| Aspect | Online | Offline Migration |
| --- | --- | --- |
| Downtime | Minutes (cutover only) | Hours (full migration) |
| Complexity | Higher (continuous sync) | Lower (one-time copy) |
| Tool | DMS online mode | DMS offline, BACPAC, backup/restore |
| Source impact | Change data capture needed | Minimal during copy |
| Best for | Mission-critical, 24/7 apps | Maintenance windows available |
| Validation | Continuous sync verification | Post-migration validation |

Click to flip back

Section 5 / Grouping Memory

## Cheat Sheet

Organised reference grouped by exam domain — everything you need on one page.

### Plan & Implement Data Platform

20-25%

#### Deployment Options

-   Azure SQL Database: PaaS, vCore or DTU, serverless auto-pause
-   SQL Managed Instance: PaaS, near-full SQL Server compat, VNet-native
-   SQL Server on VM: IaaS, full OS access, any SQL version
-   Elastic pools: shared resources across multiple databases

#### Service Tiers

-   General Purpose: remote storage, cost-effective, 5-10ms latency
-   Business Critical: local SSD, readable replica, In-Memory OLTP
-   Hyperscale: up to 100 TB, page servers, near-instant backup/restore

#### Migration Tools

-   DMA: assess compat, identify blockers, recommend target
-   DMS: online (minimal downtime) or offline migration
-   BACPAC: schema + data export/import for SQL Database
-   Transactional replication: near-zero downtime for MI

#### Networking

-   Private endpoints: VNet via Private Link (most secure)
-   Service endpoints: Azure backbone routing
-   Firewall rules: server-level IP allow-lists
-   MI: dedicated subnet, NSG rules, UDR for management traffic

### Secure Environment

15-20%

#### Encryption

-   TDE: transparent at-rest encryption (default on)
-   Always Encrypted: client-side, column-level (CMK + CEK)
-   TLS: in-transit encryption, enforce minimum TLS 1.2
-   Customer-managed keys (BYOK) via Azure Key Vault

#### Identity & Access

-   Microsoft Entra ID (Azure AD): centralised authentication
-   Contained database users: no server-level logins needed
-   RBAC: management plane (Contributor, Reader, SQL Security Manager)
-   Database roles: db\_owner, db\_datareader, db\_datawriter

#### Data Protection

-   Dynamic Data Masking: obfuscate data for non-privileged users
-   Row-Level Security: filter rows via security predicates
-   Azure SQL Ledger: tamper-evident, blockchain-verified tables
-   Data Classification: label PII, financial, health data columns

#### Threat Detection

-   Microsoft Defender for SQL: SQL injection, anomalous access alerts
-   SQL Auditing: track events to Storage, Log Analytics, Event Hubs
-   Vulnerability Assessment: scan for misconfigurations, track baseline
-   Advanced Threat Protection: brute force, suspicious activity alerts

### Monitor, Configure & Optimise

25-30%

#### Monitoring Tools

-   DMVs: real-time engine stats (requests, waits, indexes)
-   Query Store: historical plans, runtime stats, regression detection
-   Intelligent Insights: AI-detected issues in Log Analytics
-   Azure Monitor: metrics, alerts, diagnostic settings

#### Performance Tuning

-   Wait stats: PAGEIOLATCH (I/O), LCK (locks), CXPACKET (parallelism)
-   Missing index DMVs: suggest beneficial indexes
-   Automatic tuning: create/drop index, force plan
-   Columnstore indexes: batch processing for analytics

#### Configuration

-   MAXDOP: max degree of parallelism (recommended: 8 or fewer)
-   Cost threshold for parallelism: default 5, often raised to 25-50
-   tempdb: multiple files, auto-growth, instant file initialisation
-   Memory configuration: max server memory (VM), In-Memory OLTP (BC)

#### Scaling

-   vCore: independent compute + storage, Hybrid Benefit
-   DTU: bundled, simpler, no Hybrid Benefit
-   Serverless: auto-scale + auto-pause for bursty workloads
-   Read Scale-Out: offload reads via ApplicationIntent=ReadOnly

### Automation of Tasks

15-20%

#### Job Scheduling

-   Elastic jobs: T-SQL across multiple SQL DBs, cron schedule
-   SQL Server Agent: MI/VM, multi-step jobs, SSIS packages
-   Azure Automation: PowerShell/Python runbooks, hybrid workers
-   Logic Apps: event-driven workflows with 400+ connectors

#### Maintenance Automation

-   Index maintenance: rebuild (offline/online) or reorganise
-   Statistics updates: auto-update or manual UPDATE STATISTICS
-   Integrity checks: DBCC CHECKDB on schedule
-   Maintenance plans: SQL VM wizard for backup + index + integrity

#### Deployment Automation

-   ARM templates / Bicep: infrastructure as code for SQL resources
-   Azure CLI / PowerShell: scripted provisioning and config
-   DACPAC: schema-only deployment with SqlPackage.exe
-   Azure DevOps pipelines: CI/CD for database changes

#### Alert Automation

-   Azure Monitor alerts: metric and log-based rules
-   Action groups: email, SMS, webhook, Logic App, Automation runbook
-   Smart detection: Intelligent Insights auto-generated alerts
-   SQL Agent alerts: WMI events, performance conditions (MI/VM)

### HA/DR Environment

15-20%

#### High Availability

-   SQL DB GP: remote storage HA, automatic failover
-   SQL DB BC: local SSD replicas, zone-redundant option
-   MI: zone-redundant BC tier, automatic HA
-   VM: Always On AG (sync within region, async cross-region)

#### Disaster Recovery

-   Auto-failover groups: geo-failover with listener endpoints
-   Active geo-replication: up to 4 readable secondaries (SQL DB)
-   Always On AG async replica: cross-region DR for VM/MI
-   Geo-restore: restore from geo-redundant backup (RPO ~1 hour)

#### Backup Strategy

-   Automated: full (weekly), diff (12-24hr), log (5-10min)
-   PITR: restore to any second, 7-35 day retention
-   LTR: weekly/monthly/yearly, up to 10 years
-   Backup redundancy: LRS, ZRS, GRS, RA-GRS

#### Recovery Metrics

-   RPO (Recovery Point Objective): max acceptable data loss
-   RTO (Recovery Time Objective): max acceptable downtime
-   Failover group: RPO ~5s, RTO ~30s (automatic)
-   Geo-restore: RPO ~1hr, RTO hours (manual)

Section 6 / Method of Loci

## The Memory Palace

Walk through themed rooms — each object anchors a concept in spatial memory.

### The Provisioning Workshop

Plan & Implement — Where databases are born

🗄️

Azure SQL Database

PaaS. vCore or DTU. Serverless auto-pause. Elastic pools for multi-tenant

🔄

Managed Instance

Near-full SQL Server compat. VNet-native. Cross-DB queries. SQL Agent

🖥️

SQL Server on VM

Full IaaS. Any SQL version. OS access. FILESTREAM, SSRS, SSIS

🚚

Migration Tools

DMA = assess. DMS = migrate (online/offline). BACPAC = export/import

🌐

Networking

Private endpoints, service endpoints, firewall rules, VNet integration

📈

Service Tiers

GP = cost-effective. BC = low latency + readable replica. Hyperscale = 100 TB

### The Security Vault

Secure Environment — Where data is protected

🔒

TDE

Transparent Data Encryption. At rest. Enabled by default. Service or customer key

🔐

Always Encrypted

Client-side column encryption. CMK in Key Vault. DBA cannot see plaintext

🎭

Dynamic Data Masking

Obfuscate in query results. Default, email, random, custom masks. Not true encryption

🔑

Entra ID Auth

Centralised identity. MFA support. Managed identities. Replace SQL logins

📋

Auditing

Track all events. Write to Storage, Log Analytics, Event Hubs. Compliance ready

🛡️

Defender for SQL

Threat detection: SQL injection, brute force, anomalous access patterns

### The Monitoring Tower

Monitor & Optimise — Where performance is mastered

📊

DMVs

Real-time engine stats. Requests, waits, indexes, memory. Reset on restart

💾

Query Store

Flight recorder. Captures plans + stats. Force plan. Survives restart

🧠

Intelligent Insights

AI-detected issues: plan regression, resource limits, blocking. Log Analytics

⚡

Wait Statistics

PAGEIOLATCH = I/O. LCK = locks. CXPACKET = parallelism. Root cause analysis

🔍

Missing Indexes

DMV recommendations. Impact score. Validate before creating. Monitor usage

⚙️

Automatic Tuning

Auto-create index, drop unused index, force last known good plan

### The Automation Engine

Automation — Where tasks run themselves

⚙️

Elastic Jobs

T-SQL across multiple SQL DBs. Schedule-based. Replaces Agent for PaaS

👤

SQL Server Agent

MI and VM only. Multi-step jobs. SSIS. PowerShell. Operators and alerts

🔧

Azure Automation

PowerShell/Python runbooks. Hybrid workers. Schedule or webhook trigger

📅

Maintenance Plans

SQL VM wizard: backup, integrity check, index rebuild, statistics update

📦

DACPAC Deployment

Schema-only via SqlPackage. CI/CD pipelines. Compare and publish

💡

Logic Apps

Event-driven workflows. 400+ connectors. Trigger on DB changes

### The Recovery Fortress

HA/DR — Where uptime is guaranteed

🔄

Failover Groups

Automatic geo-failover. Read-write + read-only listener. SQL DB and MI

🌐

Geo-Replication

Up to 4 readable secondaries. Manual failover. SQL DB only

🏠

Always On AG

VM/MI HA. Sync commit = HA (same region). Async = DR (cross-region)

⏰

Point-in-Time Restore

Any second in 7-35 days. Full + diff + log backups. RPO near zero

💾

Long-Term Retention

Weekly, monthly, yearly backups. Up to 10 years. Compliance and archival

🛡️

Backup Redundancy

LRS, ZRS, GRS, RA-GRS. Geo-redundant = cross-region restore. RPO ~1hr

Section 7 / Pattern Recognition

## Pattern Spotter

Decision trees and trigger-answer pairs — see the pattern, know the answer.

Which Azure SQL Deployment?

Which Azure SQL Deployment?  
  ├── New cloud-native app, simplest management → Azure SQL Database  ├── Migrating SQL Server, need cross-DB queries or CLR → SQL Managed Instance  ├── Need OS access, FILESTREAM, SSRS, or specific SQL version → SQL Server on Azure VM  ├── Multi-tenant SaaS with shared resources → SQL Database elastic pool  ├── Bursty, intermittent workload (dev/test) → SQL Database serverless  └── Database > 4 TB or need instant scale-out reads → SQL Database Hyperscale

Which Encryption Approach?

Which Encryption Approach?  
  ├── Protect entire database at rest (transparent) → TDE (default, automatic)  ├── Hide specific columns from DBA and server → Always Encrypted (client-side)  ├── Obfuscate data for non-privileged users in queries → Dynamic Data Masking  ├── Restrict rows visible to different users → Row-Level Security  └── Tamper-evident audit trail for critical tables → Azure SQL Ledger

Which HA/DR Strategy?

Which HA/DR Strategy?  
  ├── Automatic geo-failover with listener endpoint → Auto-failover groups  ├── Readable secondaries, manual failover (SQL DB) → Active geo-replication  ├── HA for SQL VMs within a region → Always On AG (synchronous)  ├── DR for SQL VMs across regions → Always On AG (asynchronous)  ├── Recover from accidental data deletion → Point-in-time restore (PITR)  └── Long-term compliance backup retention → Long-term retention (LTR)

Which Migration Tool?

Which Migration Tool?  
  ├── Assess compatibility before migration → Data Migration Assistant (DMA)  ├── Online migration with minimal downtime → Azure DMS (online mode)  ├── Simple one-time export/import to SQL DB → BACPAC (SqlPackage)  ├── Migrate to MI with near-zero downtime → Log replay service or transactional replication  ├── Migrate to SQL VM with maximum compatibility → Backup and restore  └── Discover and assess SQL Server estate → Azure Migrate

Which Automation Tool?

Which Automation Tool?  
  ├── Run T-SQL across multiple Azure SQL databases → Elastic jobs  ├── Multi-step jobs on MI or VM → SQL Server Agent  ├── Azure resource management via script → Azure Automation (PowerShell runbooks)  ├── Event-driven workflow with external integrations → Logic Apps  ├── Backup, integrity, index maintenance on VM → SQL Server Maintenance Plans  └── Schema deployment in CI/CD pipeline → DACPAC with SqlPackage

## Decision Cards

“vCore” or “purchasing model” for flexible compute→vCore model (compute + storage separate, Azure Hybrid Benefit)

“DTUs” or “bundled performance”→DTU model (bundled CPU/memory/IO, simpler sizing)

“elastic pool” or “shared resources”→Azure SQL Database elastic pool

“serverless” or “auto-pause”→Azure SQL Database serverless tier

“Hyperscale” or “100 TB”→Hyperscale service tier (page servers, near-instant backup)

“TDE” or “at-rest encryption”→Transparent Data Encryption (enabled by default)

“Always Encrypted” or “client-side encryption”→Always Encrypted (CMK + CEK, DBA cannot see plaintext)

“Dynamic Data Masking” or “obfuscate”→Dynamic Data Masking (query-time, not true encryption)

“Row-Level Security” or “filter predicate”→RLS with security policy and inline table-valued function

“Query Store” or “regressed queries”→Query Store (flight recorder, force plan, regression detection)

“PAGEIOLATCH” or “wait statistics”→Wait stats analysis (I/O, locking, parallelism, memory)

“Intelligent Insights” or “AI-detected”→Intelligent Insights in Azure Monitor Log Analytics

“automatic tuning” or “auto-create index”→Automatic tuning (create/drop index, force plan)

“elastic jobs” or “multi-database scheduling”→Elastic jobs (Azure SQL DB, replaces SQL Agent)

“SQL Agent” on Azure→SQL Server Agent (MI and VM only, not SQL DB)

“failover group” or “auto-failover”→Auto-failover groups (listener endpoint, geo-failover)

“geo-replication” or “readable secondary”→Active geo-replication (up to 4 secondaries, manual failover)

“Always On” or “availability group”→Always On AG (sync = HA, async = DR, VM and MI)

“point-in-time restore” or “PITR”→PITR (any second, 7-35 day retention)

“long-term retention” or “LTR”→LTR (weekly/monthly/yearly, up to 10 years)

Ready to certify?

## Train with practitioners, not presenters

Lucid Labs delivers Microsoft certification training led by Microsoft Certified Trainers (MCTs) and grounded in real-world project experience. We adapt every session to your team's environment, data stack, and business objectives — because the best exam prep comes from engineers who build these solutions every day.

🎯

Tailored Content

Training built around your actual SQL workloads, your Azure environment, and your operational challenges — not generic slides.

🛠️

Hands-On Labs

Configure HA/DR, tune queries with DMVs and Query Store, implement security controls, and automate maintenance in guided lab environments.

📈

Exam + Capability

Pass the DP-300 exam and build lasting Azure SQL administration skills your team can apply from day one.

[Talk to us about Azure Database Administrator training](https://lucidlabs.com.au/#contact?service=training-consulting&message=I'm%20interested%20in%20Azure%20Database%20Administrator%20\(DP-300\)%20training%20for%20my%20team%20-%20covering%20Azure%20SQL%2C%20performance%20tuning%2C%20security%2C%20and%20HA%2FDR.)

Custom training for teams & individuals — remote or on-site across Australia

![Keith Oak](https://lucidlabs.com.au/team/koak-400.jpg)

Keith Oak

Chief Technology Officer — Lucid Labs

Microsoft Solutions Partner architect specialising in Fabric, Azure Data & AI, and GitHub Enterprise. 18+ years delivering data platforms for Australian businesses — building the systems these exams test every day.

[LinkedIn ↗](https://www.linkedin.com/in/keithoak/)[lucidlabs.com.au ↗](https://lucidlabs.com.au/)Published 29-03-2026