Memory hook: Compatibility, permission, plan, procedure, recovery.
Reviewed 10 October 2026. Read this once, then answer the last-pass checks without looking.
Must remember by domain
| Domain | Rapid revision |
|---|---|
| Platform | SQL Database is database-level PaaS; Managed Instance adds greater instance compatibility; SQL Server VMs allow OS/instance control. Choose tiers from measured CPU, IO, latency and limits. Elastic pools share suitable database capacity; partitioning aids management/elimination; sharding distributes across databases. Arc/Fabric capabilities require platform-specific checks. |
| Migration | Inventory unsupported features, jobs/logins, dependencies, collation, size and downtime. Offline migration stops writes; supported online methods synchronize before a final cutover. Reconcile data and server-level dependencies, switch clients, test permissions/performance and preserve fallback. |
| Security | Login/authentication is different from database user/role/object permission. TDE encrypts files at rest; TLS protects transport; Always Encrypted keeps selected column keys with the client; enclaves add supported computation. RLS filters rows; masking alters selected displayed values and is not strong protection from a powerful querying principal. Auditing records activity; ledger adds tamper evidence. |
| Performance diagnosis | Baseline waits/CPU/IO/duration → identify regression in Query Store → inspect actual plan/row estimates → check statistics, indexes and predicates → test a narrow fix. Sargable predicates enable suitable seeks. Blocking is waiting; deadlock is a cycle with a victim. Query Store history, DMVs current state and Extended Events observations are complementary. |
| Tuning and maintenance | Indexes trade read savings against writes/storage. Compression trades CPU for IO/storage. Check integrity, statistics and evidence-driven maintenance. Resource Governor and automatic-tuning capabilities vary by platform. Increasing compute does not fix every hot query, connection problem or blocking transaction. |
| Automation | SQL Server Agent suits supported SQL Server/MI jobs; SQL Database uses alternatives such as elastic jobs. Job step owner/proxy, dependencies and notifications matter. IaC deploys infrastructure; schema scripts/projects manage database changes. Review idempotency and destructive operations. |
| HA/DR | AGs replicate databases; FCIs provide instance availability using supported shared storage; log shipping sends log backups. Zone HA, active geo-replication and failover groups have different boundaries. Native restore order: full → optional latest differential → required logs → recovery at the intended point. PITR/LTR policies retain different histories. |
Traps and recovery order
SIMPLE recovery does not support a native transaction-log backup chain for arbitrary PITR. A differential depends on its base full backup. Replica health is not proof that logins/jobs/listeners and target capacity are ready. Validate backups, keys, permissions, endpoints and a useful application transaction. Never treat geo-replication as protection against every logical deletion.
Last-pass self-check
1. Prevent DB administrators from reading selected plaintext columns?
Assess Always Encrypted with client-controlled keys, not TDE alone.
2. One query regressed after a plan change: first evidence?
Query Store history and execution plans before broad resizing.
3. What distinguishes blocking from a deadlock?
Blocking is a wait; deadlock forms a dependency cycle requiring victim resolution.
4. Can SQL Database use SQL Server Agent directly?
No. Select supported alternatives such as elastic jobs or external automation.
5. Why test restore when backup jobs are green?
Only a restore validates usable data, key availability, dependencies and measured recovery time.
Sources
Every topic at a glance
Open any topic to revisit its essential facts, decisions and exam traps. Use the full topic for active recall and supporting references.
01 · Relational Data and Azure SQL
Memory hook: Keys define relationships; transactions preserve consistency; indexes trade reads against writes.
Must remember
- Tables contain rows/columns with data types. Primary keys identify rows; foreign keys express supported relationships. Normalisation reduces redundancy/update anomalies; deliberate denormalisation can improve selected reads with consistency costs.
- ACID means atomicity, consistency, isolation and durability. A transaction groups changes; isolation controls interaction with concurrent work. An index accelerates suitable lookups but consumes space and adds write/maintenance work.
- DDL defines structures (
CREATE,ALTER); DML changes data (INSERT,UPDATE,DELETE);SELECTqueries it. Views store query definitions; stored procedures package operations; indexes are access structures, not duplicate business tables to edit directly. - SQL Database is managed relational database service; SQL Managed Instance supports more instance-level compatibility; SQL Server on Azure VMs provides greater guest/instance control with more customer maintenance. Azure Database for PostgreSQL/MySQL provide managed open-source engines.
- Read query requirements carefully: joins combine related rows; aggregates summarise;
WHEREfilters rows; null represents missing/unknown rather than zero. Managed SQL does not eliminate customer schema, query, access or recovery-design responsibilities.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need SQL Server OS/instance control | SQL Server on a VM. |
| Need managed per-database relational service | SQL Database. |
| Need repeatable query projection | A view where its semantics fit. |
Traps
- Every added index has a write/storage cost.
- Null and zero mean different things.
- A managed database does not design its schema for you.
02 · SQL Platform Selection and Migration
Memory hook: Compatibility determines the destination; downtime and rollback determine the migration path.
Must remember
- Compare SQL Database, Managed Instance and SQL Server on VMs using required instance features, OS access, engine compatibility, networking, maintenance and cost. Arc extends supported hybrid management; Fabric SQL capabilities have their own scope and maturity.
- Select service tier, compute, storage and purchasing/scaling model from workload evidence. Elastic pools share resources across suitable databases; serverless/autoscale options have workload and tier constraints. Table partitioning manages data/access boundaries; sharding distributes data across databases with application/routing complexity.
- Compression can reduce I/O/storage at CPU cost. Partition elimination depends on query predicates and partition design; merely partitioning a table does not accelerate every query. Check index alignment and maintenance consequences.
- Inventory schema, features, jobs, logins, dependencies, collation and data size. Assess compatibility before migration. Offline migration accepts a write outage; online migration uses supported ongoing synchronisation and a final cutover.
- Reconcile data, test application queries and permissions, plan DNS/connection changes and preserve a rollback strategy. Managed Instance copy/move features have prerequisites; moving a database does not automatically transfer every server-level dependency.
- Use reviewed ARM/Bicep, CLI/PowerShell or database-deployment tooling. Patch IaaS/hybrid engines under a maintenance plan. Inspect migration/deployment logs for the original error, including quota, storage, network and unsupported features.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Application requires unsupported managed-instance features | Evaluate SQL Server on a VM. |
| Many databases have non-coincident demand | Evaluate an elastic pool. |
| Very small cutover window | Assess supported online migration and replication lag. |
Traps
- Database copy is not complete application migration.
- Partitioning is not a universal performance fix.
- A target accepting connections does not prove compatibility.
03 · SQL Security and Compliance Controls
Memory hook: Authentication identifies; permissions authorise; encryption and masking protect different exposures.
Must remember
- Configure Entra or SQL authentication as supported. Logins, database users, roles and object permissions have different scopes. Use least privilege through T-SQL or supported tools; distinguish failure to authenticate from successful login with insufficient database rights.
- TDE protects supported database files/backups at rest; TLS protects connections; object-level encryption and Always Encrypted address different threat models. Always Encrypted keeps selected data encryption under client control; secure enclaves enable supported confidential computations with additional requirements.
- Firewall rules, service endpoints and private links restrict access paths. A permitted network connection still needs database authentication and permissions. Key Vault/key rotation and recovery must preserve the ability to decrypt retained data.
- Dynamic data masking changes how selected users see results; it is not a robust boundary against a principal able to infer/query underlying data broadly. Row-level security filters accessible rows using defined policy logic; test administrative and application contexts.
- Classification labels identify sensitive data; audits record configured operations. Change tracking/CDC serve change-consumption purposes and are not identical to security audit. Ledger adds tamper-evidence capabilities; it does not replace backup or prove every business input was truthful.
- Review access, audit retention and export destinations, privileged identities and incident procedures. A data breach investigation needs identity, query and configuration context, not only a screenshot of enabled encryption.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Protect database files at rest | TDE with sound key management. |
| Keep selected plaintext from the database service boundary | Evaluate Always Encrypted and application compatibility. |
| Different users may access different rows | Row-level security with tested policy logic. |
Traps
- Masking is not encryption.
- TDE does not prevent an authorised query from returning plaintext.
- Network allow rules do not grant SQL permissions.
04 · Query Performance and Database Maintenance
Memory hook: Find the wait, inspect the plan and change the smallest proven cause.
Must remember
- Establish a baseline for CPU, I/O, memory, waits, duration and throughput. Database watcher, Azure Monitor, Extended Events, Query Store and DMVs expose different evidence. Capture a representative window; a quiet average can hide a peak problem.
- Query Store retains supported query/plan/runtime history and can reveal regressions. Actual/estimated execution plans show access methods, joins and estimates; compare estimated versus actual rows. Stale statistics, skew and parameter sensitivity can produce poor choices.
- Blocking means sessions wait for incompatible locks; a deadlock is a cycle requiring a victim. Shorten transactions, use appropriate indexes/access order and inspect isolation requirements. Killing a session without understanding rollback can prolong disruption.
- Indexes can reduce reads but add write, storage and maintenance cost. Statistics support estimates; index/statistics maintenance should follow evidence. Sargable predicates help use suitable indexes; excessive sorts, spills or scans suggest query/data-design issues.
- Integrity checks detect corruption; they do not repair all damage automatically. Automatic tuning and intelligent query processing help supported scenarios but need monitoring. Database-scoped settings and Resource Governor have platform-specific scope; not every SQL offering exposes the same instance controls.
- Scale compute/storage only after identifying saturation. Compare cost per useful transaction and tail latency after a change. Keep a rollback for forced plans or configuration changes and verify concurrent workload behaviour.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| A previously fast query suddenly regresses | Compare Query Store plans/runtime and recent changes. |
| High duration with lock waits | Investigate blocking and transaction scope. |
| Large estimate-versus-actual row difference | Inspect statistics, skew and query predicates. |
Traps
- An index recommendation is not a free improvement.
- Blocking and deadlocks are different.
- Scaling CPU cannot remove every lock or query-design problem.
05 · SQL Automation, Backup and High Availability
Memory hook: Automate a checked procedure and prove recovery against the required RPO and RTO.
Must remember
- SQL Server Agent jobs provide steps, schedules, owners/proxies and notifications on supported platforms. Azure SQL elastic jobs and Automation address other task scenarios. Inspect failed step output, execution identity, dependencies and retries rather than only the overall job status.
- ARM/Bicep/CLI/PowerShell automate infrastructure; database scripts and migration tools manage schema/data changes. Use idempotent controlled scripts, correct execution order, secure credentials and audited approvals. A maintenance job needs a failure notification and recovery path.
- Native full, differential and log backups have restore-chain requirements. Recovery model affects log-backup/PITR capabilities. A point-in-time restore applies the appropriate chain to the selected point; preserve encryption keys and verify the target state. Azure managed PITR/long-term retention have service-specific policies.
- For a native restore, start with the required full backup, optionally apply the appropriate differential based on it, then apply the necessary log sequence before final recovery. SIMPLE has no transaction-log backup chain; FULL supports log backups and suitable PITR; BULK_LOGGED reduces logging for eligible operations but can restrict point-in-time recovery within a log backup containing minimally logged changes. Switching models does not create missing historical backups.
- Always On availability groups, failover cluster instances, log shipping, active geo-replication and failover groups solve different HA/DR needs. Synchronous versus asynchronous replication affects commit latency and potential data loss; automatic failover requires the right supported configuration.
- Design listeners/connection strings, networking, quorum where applicable, login/job dependencies and failback. A secondary can be healthy but underprovisioned for production. Test planned/unplanned failover and backup restore in a safe environment.
- Monitor replication lag, backup age, job health and recovery results. Record actual data loss and time to usable service. Replication does not protect against every logical error; backups and HA complement each other.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Recover to before an accidental update | A valid point-in-time restore strategy. |
| Run scheduled tasks across SQL Databases | Evaluate elastic jobs and their target/identity model. |
| Regional database outage | A tested supported geo-replication/failover design. |
Traps
- A backup chain with a missing required log may block the desired restore.
- A successful failover can still leave jobs/logins or applications broken.
- HA is not historical recovery.