DP-300 exam cram kit
Use this page as a compact DP-300 review book for the final week. The goal is not to reread everything. The goal is to recall the right Azure SQL feature, tool, or configuration when the exam gives you a symptom.
Read one section, close the page, then answer the recall prompt from memory. Reopen the page only to correct your miss. Keep a one-line DP-300 Misses list and review only that list before sleep.
DP-300 domains in one view
Five domains, five reflexes. Memorize the reflex; the rest of the page is recognition.
4-day sprint
One day per domain pair. For the per-platform implementation steps, pair this with the DP-300 implementation playbook.
| Day | Focus | Anchor pages |
|---|---|---|
| Day 1 | D1 — platform choices | implementation-playbook §1.1–1.3, coverage map |
| Day 2 | D2 — security | implementation-playbook §2.1–2.3 |
| Day 3 | D3 — performance | implementation-playbook §3.1–3.3 |
| Day 4 | D4 + D5 — automation and HA/DR | implementation-playbook §4.1–4.3 and §5.1–5.3 |
Recall loop
Use this loop for every page in DP-300 Coverage Map.
Symptom:
Best first tool:
Likely root cause:
Fix:
Azure or SQL feature:
What to choose under pressure
| Scenario | First answer to consider | Why the exam likes it | Avoid this mistake |
|---|---|---|---|
| Need the most PaaS-managed SQL option | Azure SQL Database | Lowest admin surface; built-in backup, patching, HA | Picking SQL VM only because it feels familiar |
| Need near SQL Server instance compatibility | Azure SQL Managed Instance | Instance-scoped features with PaaS management | Picking SQL DB when cross-database or instance features are required |
| Need OS-level control or unsupported SQL feature | SQL Server on Azure VM | You control OS, storage layout, SQL version, and agents | Forgetting you also own patching, backups, and HA design |
| Query was fast yesterday, slow today | Query Store | Best source for plan regression and runtime history | Starting with index rebuilds before proving the cause |
| Need private network access to Azure SQL | Private endpoint | Removes public data path to the logical server | Treating firewall rules as private networking |
| Need cross-region managed failover | Failover group | Provides listener endpoint and group-level failover | Confusing active geo-replication with full failover orchestration |
| Need scheduled T-SQL across many Azure SQL databases | Elastic Jobs | Designed for Azure SQL Database job execution at scale | Choosing SQL Agent for Azure SQL Database |
| Need SQL Server Agent jobs | SQL Managed Instance or SQL VM | SQL Agent is available in those platforms | Expecting SQL Agent in Azure SQL Database |
Pick the most managed answer if it satisfies the requirement. Pick SQL VM only if the question requires OS control, unsupported features, or full SQL Server instance ownership.
Exam traps
| Trap | Correct mental model |
|---|---|
| Firewall rule equals private access | Firewall allows public endpoint access. Private endpoint changes the network path. |
| TDE protects data in use | TDE protects data at rest. Always Encrypted protects sensitive columns from the database engine in supported scenarios. |
| Query Store is only for reports | Query Store is the primary plan-regression tool and supports plan forcing. |
| Automatic tuning replaces DBA judgment | It can recommend or apply tuning actions, but you still validate workload impact. |
| PITR and LTR solve the same problem | PITR is operational recovery. LTR is retention for compliance and long-term restore needs. |
| Geo-replication and failover groups are identical | Geo-replication creates readable secondaries. Failover groups add listener endpoints and grouped failover. |
| SQL Agent works everywhere | SQL Agent is for SQL Server, SQL VM, and Managed Instance. Azure SQL Database uses Elastic Jobs or external automation. |
| DTU and vCore are just names | DTU bundles compute, memory, and IO. vCore gives clearer CPU/memory and license-control choices. |
Oracle DBA anchors
Oracle parallel: DP-300 is mostly your Oracle DBA mental model translated to Azure SQL. Data Guard maps to failover groups or Always On AGs, AWR and ASH map to Query Store plus DMVs, Wallet-managed TDE maps to TDE with service-managed or customer-managed keys, VPD maps to Row-Level Security, and DBMS_SCHEDULER maps to SQL Agent, Elastic Jobs, or Azure Automation depending on platform.
| Oracle memory hook | DP-300 answer |
|---|---|
| RAC for scale/HA | Business Critical tier, read scale, elastic pools, or SQL VM AG depending on requirement |
| Data Guard | Failover groups, active geo-replication, or Always On AG |
| RMAN restore | PITR, LTR, geo-restore, backup to URL for SQL VM |
| AWR/ASH | Query Store, DMVs, wait stats, Azure Monitor |
| Wallet/TDE | TDE, Key Vault, customer-managed keys |
| VPD | Row-Level Security |
| DBMS_SCHEDULER | SQL Agent, Elastic Jobs, Automation runbooks |
Final 2-hour review
| Time box | Do this | Do not do this |
|---|---|---|
| 30 min | Review the five-domain mind map and say each domain from memory | Open every page again |
| 30 min | Review only your DP-300 Misses list | Rewrite notes |
| 30 min | Drill the symptom-to-tool table | Watch a new long video |
| 20 min | Recheck HA/DR and security traps | Chase rare edge cases |
| 10 min | Read the official skills measured page headings | Start a new lab |
References
- Microsoft Learn: DP-300 study guide
- Microsoft Learn: Azure Database Administrator Associate
- Microsoft Learn: Azure SQL documentation
- DP-300 coverage map
Flashcards
Retake focus - verified answers
This section is the second-attempt focus drill. It exists because the first-attempt score report flagged three areas as weak, and a debrief of the remembered questions revealed gaps the general topic pages do not call out by name. Every claim below is verified against current Microsoft Learn documentation, with the source URL pinned at the end of each scenario.
Work through it in two passes.
- Pass 1 (recall): read one numbered scenario, close the page, and write the answer plus the reason from memory.
- Pass 2 (citation): reopen, compare to the Microsoft Learn snippet, and only flag a real miss if your reason was wrong - not just the wording.
A miss on the reason is what costs marks on multi-select questions. Memorize why, not just the option letter.
The three priority areas
The nine scenarios below are the highest-leverage drills for those branches. If a scenario links out to a deeper page in the same wiki, follow the link only after the recall attempt.
Scenario 1 — Database Mail across SQL VM, SQL MI, and Azure SQL DB
Pattern: "Where can you send notifications using Database Mail and the SQL Server Agent?"
On Managed Instance, the profile name is mandatory and literal: AzureManagedInstance_dbmail_profile. Any other name and SQL Agent silently refuses to send. This single string is the most popular MI Database Mail exam point.
Source: Database Mail (Azure SQL Managed Instance).
Scenario 2 — Adding a database to an existing Always On availability group
Pattern: "An AG already exists across two replicas. You add a new database on the primary. What sequence brings it into the AG on the secondary?"
On SQL Server 2016+ you can skip the manual backup/restore by enabling automatic seeding:
-- On the primary, grant the AG permission to create databases on secondaries:
ALTER AVAILABILITY GROUP [ag] GRANT CREATE ANY DATABASE;
-- Add the database with SEEDING_MODE = AUTOMATIC on the replica.
ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON 'NODE2'
WITH (SEEDING_MODE = AUTOMATIC);
ALTER AVAILABILITY GROUP [ag] ADD DATABASE [db];
The exam mostly asks about the manual path, because RESTORE WITH NORECOVERY is the only answer where one option is unambiguously correct.
RESTORE … WITH RECOVERY brings the database online standalone — which disqualifies it from being joined to an AG. The required option is always WITH NORECOVERY. Same for the log restore.
Sources:
Scenario 3 — Detect both physical and logical corruption
Pattern: "You suspect both physical and logical corruption in a user database. Which DBCC command do you run?"
| Command / option | Physical checks | Logical checks | Notes |
|---|---|---|---|
DBCC CHECKDB (no flags) | ✅ | ✅ | Default: runs CHECKALLOC, CHECKTABLE, CHECKCATALOG, indexed-view consistency, FILESTREAM link checks, Service Broker checks. |
DBCC CHECKDB ... WITH PHYSICAL_ONLY | ✅ | ❌ | Skips all logical checks and FILESTREAM. Lighter weight for production. |
DBCC CHECKDB ... WITH DATA_PURITY | adds | ✅ | Column-value range checks. Enabled by default on databases created on SQL 2005+. |
DBCC CHECKALLOC | ✅ | ❌ | Allocation only. Subset of CHECKDB. |
DBCC CHECKTABLE | ✅ | ✅ | Single table only. |
DBCC CHECKCATALOG | ❌ | ✅ | System-catalog consistency only. |
Both physical and logical → plain DBCC CHECKDB. If you see PHYSICAL_ONLY in any answer option, reject it for "both" questions — that flag explicitly disables logical checks. The Microsoft Learn doc opens with: "Checks the logical and physical integrity of all the objects in the specified database".
Source: DBCC CHECKDB (Transact-SQL).
Scenario 4 — Availability group VM placement for low-latency sync commit
Pattern: "Two SQL Server VMs in the same region must run an AG in synchronous-commit mode with minimum network latency between replicas. How do you place them?"
Microsoft Learn says it explicitly: "When creating Azure VMs, you must choose between configuring Availability Sets vs Availability Zones. An Azure VM can't participate in both." Any answer combining both for the same VM is wrong.
Synchronous-commit replicas wait for the secondary to harden the log before acknowledging the transaction on the primary. Microsoft Learn notes: "VMs located in different Availability Zones have greater network latency between them, which can increase the time it takes to synchronize data between the primary and secondary replicas." For latency-sensitive sync commit pick PPG + AS; for tolerating a datacenter loss pick AZ and accept the latency.
Sources:
Scenario 5 — Power BI Service connecting to Azure SQL Database
Pattern: "Power BI Service in the cloud must read from an Azure SQL Database that is locked down with a private endpoint and public network access disabled. What's the most secure connection method?"
- VNet service endpoint on the SQL subnet — Power BI Service is not in your VNet, so a service endpoint does not help; service endpoints are subnet-side.
- Network Security Group rule — NSGs apply to VNet NICs, not to the SQL logical server's public listener.
- Add the Power BI public IP range to the SQL firewall — works for Pro but is not the most secure option and re-exposes the public listener.
If you do not have Premium / Fabric capacity, an On-premises data gateway on a VM inside the same VNet can reach the private endpoint and acts as a Power BI proxy. Same security posture, more infrastructure to run.
Source: VNet data gateway overview.
Scenario 6 — Change the password protecting a certificate's private key
Pattern: "A certificate's private key is currently encrypted with one password. Rotate it to a new password without recreating the certificate."
ALTER CERTIFICATE TDECert
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = '<old-password>',
ENCRYPTION BY PASSWORD = '<new-password>'
);
The verb is ALTER CERTIFICATE. CREATE CERTIFICATE would produce a new certificate object with a new key pair and break anything currently encrypted with the old certificate (TDE DEKs, signed modules, Service Broker dialogs).
Microsoft Learn states: "To change the password used for encrypting the private key, don't specify either the FILE or BINARY clauses." Adding FILE = '...' switches the command into "import a private key from disk" mode and the password rotation will fail or produce confusing results.
Source: ALTER CERTIFICATE (Transact-SQL) (see Example B).
Scenario 7 — Mask a phone number, hide first digits, keep last four
Pattern: "Column stores phone numbers like 2223-3412-4121. Hide the leading digits, keep the trailing 4121 visible, with the dashes preserved. What's the feature and the exact mask?"
ALTER TABLE dbo.Customers
ALTER COLUMN Phone ADD MASKED WITH (
FUNCTION = 'partial(0,"XXXX-XXXX-",4)'
);
-- Result for 2223-3412-4121: XXXX-XXXX-4121
Anatomy of partial(prefix, padding, suffix)
| Argument | Meaning | This example |
|---|---|---|
prefix | Integer — number of original characters left visible at the start | 0 (hide everything at the start) |
padding | Literal string injected in the middle | "XXXX-XXXX-" (literal mask incl. dashes) |
suffix | Integer — number of original characters left visible at the end | 4 (keep the last four characters) |
Always Encrypted is column-level encryption that the engine never decrypts. There is no "show only the last 4 characters" mode — the DB engine cannot generate a partial reveal because it never sees the plaintext.
DDM is a display-time mask computed by the engine for users without UNMASK permission. The original value still lives in the table; db_owner and members of UNMASK see it in the clear.
- Writing
partial(6,"...",4)when you mean hide the first 6: the first argument is the count kept, not the count hidden. To hide 6 starting chars, the prefix must be0(or6to keep 6 and mask the rest). - Forgetting that the middle argument is a literal.
partial(0,XXXX,4)without quotes is a syntax error. - Using DDM for compliance ("we're PCI compliant because the credit card is masked") — DDM is not a compliance control. It's an obfuscation layer; the data is still plain in the file.
Source: Dynamic data masking.
Scenario 8 — Microsoft Entra ID authentication on SQL Server on an Azure VM
Pattern: "Enable Microsoft Entra (Azure AD) authentication for SQL Server running on an Azure VM. What is required?"
Microsoft Learn: "Using Microsoft Entra authentication with failover cluster instances isn't supported." If the scenario is "AG on Azure VM" it's supported — but if it's "FCI on Azure VM" the answer is not Entra auth.
- Configuration lives on the SQL virtual machine resource, not the VM resource — these are two different resources in the same resource group.
- Once Entra authentication is enabled on a SQL VM, there is no way to disable it. Plan before flipping the switch.
Source: Enable Microsoft Entra authentication for SQL Server on Azure VMs.
Scenario 9 — Isolate 40 customer databases on a shared logical server
Pattern: "A SaaS app puts 40 customer databases on the same Azure SQL logical server. Each customer must be able to access only their own database. Pick two actions."
| Option | Action | Pick? | Why |
|---|---|---|---|
| A | Server-level firewall rule per customer IP | ❌ | A server-level rule still authenticates against the logical server's master and authorizes against every DB the login is mapped to. Wrong granularity for per-tenant isolation. |
| B | Create a database user in each customer's database | ✅ | The user is contained to that database. Without a user in OtherCustomerDB, the principal cannot read it even if it can reach the server. |
| C | Configure database-level firewall rules | ✅ | Microsoft Learn explicitly pairs contained users with database-level firewall rules as the SaaS isolation pattern. The rule narrows the network reach to the specific DB the customer needs. |
| D | Apply Row-Level Security to the customer table | ❌ | RLS filters rows in a single table, not access across databases. Wrong scope. |
| E | Create a SQL login for each customer in master | ➖ | Works as an alternative pattern (traditional login + mapped user), but it forces every connection to authenticate against master and breaks geo-replication cleanly. Microsoft documents this pattern for users who must reach multiple databases — the opposite of this scenario. |
| F | Always Encrypted on the tenant-id column | ❌ | Encrypts a column value at the client; does nothing about which DB a credentialed user can reach. |
| G | Enable TDE on each database | ❌ | TDE protects data files at rest. It does not gate any user from connecting. |
What Microsoft Learn says — verbatim
"When you're using SQL Database, combine contained database users with database-level firewall rules. Database-level firewall rules, combined with contained database users, can eliminate the need to access the master database of the server during the connection."
— Make your database portable by using contained database users
SaaS, many tenants, one DB each → contained users + database-level firewall rules (B + C). The login-in-master + user-in-DB pattern (E) is documented as the choice when a single user must reach multiple databases — the inverse of the question.
Some practice tests and dumps mark B + E correct because both technically isolate access. If your real exam graded B + E, the question wording almost certainly leaned on login-based authentication explicitly. Without that wording, trust the Microsoft Learn pattern — B + C — and pick contained users + DB firewall.
Wait-stats decoder — bonus drill for D3
sys.dm_exec_requests reports a wait_resource such as 2:3:905856. Decode it as database_id : file_id : page_id.
database_id | Database | Common waits | Real fix |
|---|---|---|---|
| 1 | master | extremely rare | usually not the root cause |
| 2 | tempdb | PAGELATCH_UP, PAGELATCH_SH on PFS / GAM / SGAM pages | Add multiple equally-sized tempdb data files (1 per core up to 8; equal fixed autogrowth, not percent). On 2016+, TF1117/1118 are default for tempdb. |
| 3 | model | almost never on a live system | safe to ignore |
| 4 | msdb | Agent / Mail under load | move Mail relay off the hot instance |
| ≥5 | user DBs | PAGEIOLATCH_* (storage), LCK_M_* (locking), WRITELOG (log writes) | match the wait to the resource type, not the DB id |
If the exam shows PAGELATCH_UP and a wait_resource starting with 2:, the answer is never "change autogrowth on master" and never "add an index". The right fix is the tempdb files answer — multiple equally-sized data files with equal fixed autogrowth. Percentage autogrowth on tempdb makes contention worse by breaking the round-robin allocation.
Source: Recommendations to reduce allocation contention in tempdb.
Scenario 10 — Migration strategy: minimal downtime for a large database
Pattern: "You must migrate a large on-premises SQL Server database to Azure with minimal downtime. Which migration approach do you choose?"
"Minimal downtime" + MI or SQL VM → Azure DMS Online (Premium tier). "Minimal downtime" + Azure SQL Database → DMS cannot do it; look at transactional replication or BACPAC + cutover. Backup/restore = full downtime; never the right pick when the question says "minimal".
Microsoft Learn explicitly states: "Using Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier." If the answer mentions Standard tier + online, it's wrong.
Sources:
- What is Azure Database Migration Service?
- DMS FAQ — offline vs online
- Supported migration scenarios — online matrix
- Managed Instance link feature
Scenario 11 — Reduce Azure Monitor metric alert noise
Pattern: "An Azure Monitor metric alert on an Azure SQL DB or VM fires too often, but you don't want to set a fixed threshold. How do you tune it down?"
Noisy alert + dynamic → Low sensitivity. Missing alert + dynamic → High sensitivity (or change the metric). High doesn't mean "more important", it means "tighter bounds".
Source: Dynamic thresholds overview — known issues with sensitivity.
Scenario 12 — Resource Governor on Managed Instance (or SQL Server)
Pattern: "An app must be able to use up to 100% of CPU when no one else needs it, but always be guaranteed at least 50% under contention. Configure Resource Governor."
-- 1) Resource pool: floor 50%, ceiling 100% (soft).
CREATE RESOURCE POOL app_pool
WITH (
MIN_CPU_PERCENT = 50,
MAX_CPU_PERCENT = 100
);
-- 2) Workload group bound to the pool.
CREATE WORKLOAD GROUP app_group
USING app_pool;
-- 3) Classifier function (runs at login).
CREATE FUNCTION dbo.fn_classifier()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp sysname = N'default';
IF SUSER_SNAME() = N'app_login'
SET @grp = N'app_group';
RETURN @grp;
END;
GO
-- 4) Wire the classifier and apply.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
MIN_CPU_PERCENT= reservation. Other pools cannot consume this slice under contention. Set this to your "always guaranteed" floor.MAX_CPU_PERCENT= soft ceiling. Only enforced when there is contention. With no other workload running, the pool can use up to 100% even if MAX is lower.CAP_CPU_PERCENT= hard ceiling. Never exceeded, even when CPU is idle. Use this for chargeback / "thou shalt not exceed" scenarios.
- Configure on
masterof the primary replica. Resource Governor changes on a secondary replica do not stick until you reconfigure there. - Maximum 40 resource pools on Managed Instance. SQL Server itself has the same hard cap (per Learn).
- Azure SQL Database does not allow user-defined Resource Governor. It uses RG internally but the surface isn't exposed.
Sources:
- Resource Governor — overview and limits
- Resource pool — MIN/MAX/CAP semantics
- T-SQL differences — Resource Governor on MI
Scenario 13 — Correlate Extended Events with ETW
Pattern: "You need to correlate SQL Server events with Windows OS or application traces in one timeline. Add what to your XE session?"
ALTER EVENT SESSION my_session ON SERVER
ADD TARGET package0.etw_classic_sync_target;
GO
- Target = where the event data goes (file, ring buffer, ETW, histogram).
- Action = extra columns captured when the event fires (sql_text, session_id, plan_handle). Costs CPU.
- Predicate = filter — drops events before they're written. Cheapest noise control.
For OS + SQL correlation, the only target that matters is package0.etw_classic_sync_target.
The ETW target is available on SQL Server only. It is not available on Azure SQL Database or Azure SQL Managed Instance — those instances expose only event_file, ring_buffer, event_stream, histogram, and event_counter. If a question puts ETW correlation on Azure SQL DB / MI, the answer is "not supported — use the event_file target and external correlation".
Two more setup gotchas the exam likes:
- The SQL Server service account must be a member of the Performance Log Users group for ETW to start.
- The ETW output file lives at
%TEMP%\XEEtw.etland its path can't be changed after the first session starts.
Sources:
- Event Tracing for Windows target
- Extended Events targets — platform availability matrix
- Monitor system activity using Extended Events
Power BI — public vs private connection patterns
Pattern: There are two Power BI → Azure SQL DB exam questions, and they have different right answers depending on how the question is worded.
| Question wording | Right answer |
|---|---|
| "Power BI Service must read from Azure SQL DB — what is the simplest way?" | Allow the Power BI Service IP ranges on the server-level firewall (or check "Allow Azure services and resources"). Public endpoint, no gateway needed. Pro / shared capacity OK. |
| "What is the most secure way to connect Power BI Service to Azure SQL DB when public network access is disabled / private endpoint only?" | VNet data gateway (Premium / Fabric) + private endpoint on the SQL server. See Scenario 5. |
| On-prem alternative (no Premium / Fabric) | On-premises data gateway on a VM inside the same VNet as the private endpoint. |
If the question says "simplest", "easiest", "default" → public + firewall. If it says "most secure", "private endpoint", "no public access", "compliance" → VNet data gateway. Picking VNet gateway on a "simplest" question or public+firewall on a "most secure" question is the most common 1-point loss.
SQL Server Agent and email — quick recap across platforms
The table below is the same content as Scenario 1 (Database Mail) cross-walked with Agent behavior, because the exam often combines them in one question.
| Capability | SQL Server on Azure VM | SQL Managed Instance | Azure SQL Database |
|---|---|---|---|
| SQL Server Agent service | Windows service. Set Automatic startup. Stop/start manually. | Built in, always running. You cannot stop or start it. | Does not exist. Use Elastic Jobs or Azure Automation. |
| Database Mail | Full. Configure profile in msdb, allow outbound SMTP from the subnet. | Supported. Profile name must be literally AzureManagedInstance_dbmail_profile. Outbound SMTP relay on TLS port 587. | Not supported. Use Logic Apps, Power Automate, or Azure Automation. |
| Operators + Alerts | Full Agent Operators + Alerts. | Supported (with the literal profile name above). | N/A. |
sp_send_dbmail from job logic | Works. | Works. | Not available. |
Some study guides claim "MI only supports sp_send_dbmail inside job logic" — that's incomplete. MI supports the full Operator + Alert configuration on SQL Agent as long as you use the literal profile name AzureManagedInstance_dbmail_profile. The exam pattern "configure Operator + Alert + Notification on MI" is a valid answer; you do not have to fall back to inline T-SQL.
Sources: Database Mail (Azure SQL Managed Instance) — same as Scenario 1.
Dynamic Data Masking — partial() general formula
The exam reuses this question with different "kept" widths and different middle masks. Memorize the formula, not one example.
ALTER TABLE T ALTER COLUMN C ADD MASKED WITH (
FUNCTION = 'partial( <kept_start_chars> , "<literal_middle>" , <kept_end_chars> )'
);
| Source value | Mask string | Result |
|---|---|---|
2223-3412-4121 | partial(0,"XXXX-XXXX-",4) | XXXX-XXXX-4121 |
2223-3412-4121 | partial(0,"XXXXXX-XXXX-",4) | XXXXXX-XXXX-4121 |
2223-3412-4121 | partial(4,"-XXXX-",4) | 2223-XXXX-4121 |
John.Smith@contoso.com | partial(1,"XXX@",4) | JXXX@.com (last 4 of full string) |
- The first argument is the number of chars kept at the start — not the number hidden.
- The middle is a literal string in double quotes — including any dashes you want to preserve.
- Always Encrypted cannot do partial reveal. If the question asks for "show last 4 only" the only acceptable answer is DDM.
Source: Dynamic data masking — same as Scenario 7.
Failover topology — 30-second recall
| Symptom in the question | Pick |
|---|---|
| "Automatic failover, single endpoint, multiple DBs together" | Auto-failover group |
| "Up to 4 readable secondaries, manual failover, per-DB endpoints" | Active geo-replication |
| "Region-loss survival but no synchronous-cost replicas" | Geo-restore from GRS / RA-GRS backups |
| "Lowest latency sync commit in one region" | AG on VMs in same AS + PPG |
| "Survive a whole datacenter, still in one region" | AG across Availability Zones |
| "Failover Cluster Instance with Entra auth" | Not supported — trick option |
| "MI: protect ALL user DBs together cross-region" | Auto-failover group on the MI (all-or-nothing scope) |
- Auto-failover group ≈ Data Guard with Fast-Start Failover + a virtual broker name. The application sees one endpoint and never reconfigures.
- Active geo-replication ≈ multiple physical standbys in Active Data Guard, but you flip them manually and the connection string per-replica is distinct.
- AG in PPG + AS ≈ Data Guard with sync
LogXptMode=SYNCbetween two RAC sites in the same building — minimum latency, no datacenter-loss protection.
Flashcards — high-yield recall set
Reflex checks before exam day
Run this set in your head the morning of the exam. Whisper the reason, not just the option letter.
RESTORE … WITH NORECOVERYwhenever you add a DB to an AG.DBCC CHECKDBwith no flags whenever you see "physical AND logical".2:x:y→ tempdb → multiple files, equal size, fixed autogrowth.- Sync-commit AG → AS + PPG. Cross-DC survival → AZ.
- SaaS multi-tenant → contained users + database firewall.
- SQL VM Entra auth → SQL 2022 + IaaS extension + MI + Directory Readers.
ALTER CERTIFICATE … WITH PRIVATE KEY (DECRYPTION BY '…', ENCRYPTION BY '…').- DDM
partial(prefix_count, "middle_literal", suffix_count). - Power BI Service to private Azure SQL → VNet data gateway.
- Database Mail: VM yes, MI yes (literal profile name), Azure SQL DB no.
Source index
Every claim on this page was checked against the URLs below on the last_reviewed date in the frontmatter. If a section conflicts with current Microsoft Learn, the docs win — update this page before the next retake.
- Contained database users — pair with DB firewall
- Manually prepare a secondary database for AG
- Automatic seeding for AG secondary replicas
- DBCC CHECKDB (Transact-SQL)
- Always On availability groups on Azure VMs
- Proximity placement groups
- Database Mail (Azure SQL Managed Instance)
- Enable Microsoft Entra auth for SQL Server on Azure VMs
- ALTER CERTIFICATE (Transact-SQL)
- Dynamic data masking
- VNet data gateway overview
- Reduce allocation contention in tempdb
- What is Azure Database Migration Service?
- DMS supported migration scenarios (online matrix)
- DMS FAQ — offline vs online
- Managed Instance link feature overview
- Azure Monitor dynamic thresholds overview
- Troubleshoot Azure Monitor metric alerts
- Resource Governor overview
- Resource Governor — resource pool concepts
- T-SQL differences SQL Server vs MI — Resource Governor
- Event Tracing for Windows target
- Extended Events targets — platform availability matrix
- Monitor system activity using Extended Events