Study Guide · DP-300

DP-300 Cheat Sheet

478 entries · 15 chapters · 5 domains

Plan and implement data platform resources

Plan and deploy Azure SQL solutions

Read full chapter

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Instance-level dependencies favor SQL Managed Instance over SQL Database

When an application needs native cross-database three-part-name queries or transactions within one instance, Service Broker, SQL Server Agent, or SQL CLR calculations without operating-system access, use Azure SQL Managed Instance to retain those instance capabilities in a managed service. Azure SQL Database suits independently managed application databases, but does not provide SQL Server Agent, user-defined CLR integration, Service Broker, or native cross-database transactions. For access to a remote server rather than another database within the instance, Managed Instance supports linked servers to supported SQL Server and Azure SQL Database targets; SQL Database has no native linked servers, so evaluate an alternative such as elastic query against the required remote-query behavior. Both services delegate infrastructure patching and built-in availability to Azure; compatibility requirements, not simply database count, determine the choice.

Trap Using an Azure SQL Database elastic pool to preserve SQL Server Agent and native cross-database instance behavior, although pooling shares database resources rather than supplying a SQL Server instance.

4 questions test this
Host-level integration requires SQL Server on Azure virtual machines

When a workload requires operating-system administration, local file-share integration from SQL CLR, or FILESTREAM and FileTable, deploy SQL Server on Azure Virtual Machines; the latter CLR and file-storage requirements need Windows. IaaS preserves SQL Server and host-level control on the selected supported platform, whereas the managed SQL services restrict access to their underlying hosts. That control also leaves the customer responsible for operating-system and SQL Server servicing rather than transferring those duties to a PaaS service.

Trap Choosing SQL Managed Instance solely because it supports CLR, although CLR support does not grant access to the managed service's local file system.

4 questions test this
The vCore model exposes resource and licensing choices that DTUs bundle

When sizing must map to processor capacity, hardware characteristics, and separately selected storage, use the vCore purchasing model. Azure SQL Database also offers the DTU model, which packages CPU, memory, and I/O into Basic, Standard, and Premium performance levels rather than exposing independent hardware choices. Azure Hybrid Benefit and reserved-capacity discounts are vCore options for eligible SQL Database deployments, and SQL Managed Instance uses the vCore model rather than DTUs. Azure Hybrid Benefit reuses qualifying SQL Server licenses with active Software Assurance: select LicenseType BasePrice instead of LicenseIncluded to exclude the Azure SQL license charge, while compute and storage remain billable. For SQL Database, DTU and serverless deployments are ineligible, and newly created Hyperscale databases use pricing without a separate SQL software license fee rather than receiving an additional Hybrid Benefit discount merely because they use vCores.

Trap Selecting a Premium DTU database to apply Azure Hybrid Benefit, although that licensing benefit requires an eligible vCore deployment.

4 questions test this
SQL database in Fabric combines an operational database with automatic analytics integration

When a new transactional application needs a SQL database inside a Fabric workspace and near-real-time analytical access without a separately assembled replication pipeline, use SQL database in Microsoft Fabric. It uses the Azure SQL Database engine and automatically replicates supported operational data into analytics-ready OneLake storage. If an Azure SQL Database must remain an external operational system, use Fabric mirroring to bring its data into OneLake rather than replacing it with a newly created Fabric SQL database.

Trap Creating a Fabric SQL database merely to expose an existing external Azure SQL Database to analytics, although creation provisions a new operational database rather than configuring mirroring for the existing one.

4 questions test this
Fabric's operational SQL endpoint and SQL analytics endpoint serve different workloads

SQL database in Fabric provides a transactional database endpoint for application reads and writes and a separate SQL analytics endpoint over the replicated OneLake data. Send operational inserts and updates to the database endpoint; use the analytics endpoint for read-only T-SQL analysis, views, and functions over the replicated tables. The two endpoints have different connection strings and independently applicable SQL access controls, so an analytics connection is not a substitute for the operational connection.

Trap Pointing an application's write connection at the SQL analytics endpoint because it supports T-SQL, although that endpoint is read-only over replicated data.

4 questions test this
Declarative templates describe resource state while scripts orchestrate operations

When repeatable environments must declare SQL resources and their dependencies as a versioned desired state, use ARM JSON templates or Bicep. Bicep compiles to ARM templates and expresses a database's dependency on its logical server through the parent resource relationship; repeated deployment of unchanged resource definitions is idempotent. Azure CLI and Azure PowerShell are appropriate when the workflow needs imperative branching or sequenced administrative actions, and they can also submit templates through az deployment group create or New-AzResourceGroupDeployment.

Trap Choosing Azure Cloud Shell as the deployment definition, although it is an execution environment rather than a versioned declaration of the required resources.

3 questions test this
DACPAC publishing manages database schema rather than Azure infrastructure

When an existing database needs its tables, views, and procedures reconciled with a SQL project, use SqlPackage /Action:Publish with a DACPAC. Publishing computes the schema changes required to match the package, so the deployment identity needs the documented database permissions, including db_owner for an existing target. Provision the Azure logical server, networking, and service tier separately through infrastructure deployment; a database-schema package is not an ARM resource template.

Trap Deploying an ARM template alone to add application stored procedures, although resource provisioning does not reconcile the database's SQL object definitions.

4 questions test this
Provision a logical server before its Azure SQL databases

An Azure SQL Database logical server is the administrative parent for databases, not a virtual machine or a SQL Managed Instance. Use az sql server create to provision the parent, then az sql db create with --resource-group, --server, --name, and the required performance selection such as --service-objective S0 to create a database under it. The PowerShell equivalents are New-AzSqlServer and New-AzSqlDatabase; creating the server alone does not create the application's user database, and client connectivity still needs its network access path configured.

Trap Using az sql mi create to provision the parent for Azure SQL Database, although that command creates a different managed-instance offering.

4 questions test this
Managed Instance deployment requires both subnet headroom and regional vCore quota

Deploy Azure SQL Managed Instance into its dedicated delegated subnet only after verifying both subnet-address headroom and regional vCore quota; use New-AzSqlInstance or az sql mi create rather than a logical-server database command. The minimum subnet mask is /27, but size for instance count, service tiers, virtual-machine groups, future management operations, and growth because scaling can temporarily double the addresses allocated to the instance being scaled. Separately check the subscription's regional vCore-unit quota for the selected hardware generation across all instances: each General Purpose vCore consumes one unit, whereas each Business Critical vCore consumes four. A regional quota failure can require a quota-increase support request or another eligible region even when subnet addresses are sufficient and the requested instance meets its technical limits.

Trap Selecting a subnet that fits only the current managed-instance addresses, although scaling temporarily allocates additional compute and IP addresses.

4 questions test this
SQL VM registration adds management capabilities without making the engine PaaS

For SQL Server on an Azure Windows VM, use a SQL Server Marketplace image for preconfigured SQL deployment or register a manually installed instance with the SQL Server IaaS Agent extension. The extension exposes SQL-specific portal management, licensing flexibility, and supported backup and patching integration; a generic VM resource alone does not provide those SQL management features. Marketplace images are a deployment baseline rather than an ongoing servicing mechanism, so verify and update the engine's patch level after deployment.

Trap Assuming the Azure VM resource's existence is equivalent to SQL IaaS Agent registration, although SQL-specific management depends on the extension integration.

4 questions test this
Managed Instance collation and time zone are creation-time commitments

Choose Managed Instance's instance collation and time zone during creation because neither setting can be changed on that instance afterward. An ARM template exposes the collation and timezoneId properties, and omitting the time-zone selection uses UTC. The instance time zone governs GETDATE() and SQL Server Agent schedules, so preserve the source clock context unless the dependent application and scheduling logic are deliberately changed. Azure SQL Database instead selects collation at database scope and has no configurable server collation or instance time-zone setting; it follows UTC, with AT TIME ZONE available for query-level conversions.

Trap Restoring a database onto a UTC managed instance to reproduce the source server's local-time Agent schedule, although the destination instance retains its own creation-time time-zone setting.

2 questions test this
A useful partition key follows query predicates and data-lifecycle boundaries

When a large table is queried and aged out by time interval, partition on the relevant date column so interval predicates can eliminate unrelated partitions and maintenance can target one interval. All partitions remain one logical table in one database; queries that do not constrain the partitioning column can still visit every partition and may become slower. Partitioning accepts one partitioning column, and a computed column used for that purpose must be explicitly PERSISTED, so choose the key around actual access and retention patterns rather than row count alone. Although the ceiling is 15,000 partitions per table or index, finer granularity increases index-build memory and maintenance work, especially for nonaligned indexes, so prefer fewer partitions or a lower index-build MAXDOP when resources are constrained. For frequent equijoins, partition both inputs on their join columns with matching data types, partition counts, and boundaries so the optimizer can process paired partitions; partitioning unrelated to the join does not guarantee faster joins.

Trap Partitioning by an unrelated surrogate identifier to accelerate date-filtered reports, although those predicates cannot identify which identifier partitions to skip.

2 questions test this
Partition storage plans must respect the platform's filegroup capabilities

Azure SQL Database places every partition on PRIMARY because it exposes no additional filegroups, but it still supports partition-level maintenance and switching. SQL Server and Azure SQL Managed Instance can map partitions to multiple filegroups; customer-controlled tiered storage placement and independent filegroup backup and restore are SQL Server capabilities, not Managed Instance capabilities. A single filegroup remains a valid simpler design when neither storage tiering nor filegroup recovery is required.

Trap Designing one Azure SQL Database filegroup per month for archival storage, although that service provides only the PRIMARY filegroup.

3 questions test this
List and range shard maps express different key-to-database assignments

When tenants need explicit placement by individual tenant identifier, use a list shard map; multiple individual identifiers can point to the same database, or each can have its own database. Use a range shard map when contiguous intervals of a sharding key belong together, with ranges represented as inclusive low and exclusive high boundaries, [Low, High). Sharding distributes rows across databases to scale beyond one database's resource boundary, unlike table partitioning, which keeps all partitions inside one database.

Trap Choosing a range shard map for arbitrary noncontiguous tenant identifiers that each require explicit placement, although range mappings describe intervals rather than independent key assignments.

3 questions test this
Shard-map metadata changes do not move user rows between databases

When rebalancing existing data, coordinate actual row movement with mapping changes, for example through the elastic database split-merge service; SplitMapping and MergeMappings alone only change range metadata. The shard map manager stores global key-to-database mappings, while local shard maps let data-dependent routing validate cached mappings against the destination. Operations such as UpdateMapping or DeleteMapping require the mapping to be offline, so plan routing availability as well as data movement before relocating a shardlet.

Trap Calling SplitMapping to distribute an overloaded shard's rows to a new database, although the split leaves both resulting ranges mapped to the original shard.

3 questions test this
Tenant requirements determine transaction and management boundaries for shards

Choose a sharding key that keeps each transaction's related data on one shard, such as a tenant identifier for tenant-local operations. Use one database per tenant when independent recovery, isolation, and resource scaling are required; place multiple small tenants in one database when sharing is preferable, with a tenant key in each shared table. Shared tenants compete for that database's resources, so an unusually busy tenant can justify relocation to a dedicated database rather than merely adding another mapping. Data-dependent routing connects a key-specific request to one shard, whereas elastic query supplies cross-database reads; maintaining a shard map alone does not execute a distributed query.

Trap Choosing a shared multitenant shard for a requirement to restore one tenant directly as an independent database, although the native database recovery boundary includes the other tenants sharing that shard.

3 questions test this
Azure Update Manager is the preferred SQL VM patching path for cumulative updates

When SQL Server VMs need centrally assessed and scheduled SQL cumulative updates as well as operating-system updates, use Azure Update Manager and configure the required update sources and maintenance windows. The older SQL VM Automated Patching feature installs only updates classified Important or Critical and is scheduled to retire on September 17, 2027, so it is not the choice for new deployments or general CU automation. Disable Automated Patching before enabling Update Manager, because overlapping patch schedulers can conflict; periodic assessment can check update availability every 24 hours without itself installing updates.

Trap Using SQL VM Automated Patching for every SQL Server cumulative update, although updates outside its Important or Critical classifications require a different servicing path.

4 questions test this
Arc automatic SQL updates operate at Windows-host scope rather than per database

SQL Server enabled by Azure Arc can configure Windows-host automatic updates during a selected local-time maintenance window, affecting all installed SQL Server instances on that host. This feature requires Software Assurance or pay-as-you-go licensing and applies Windows and SQL updates classified Important or Critical; unclassified service packs or CUs require separate installation. Because this particular feature currently works only on Windows, do not treat its schedule as a Linux SQL Server patch policy or as isolation between instances sharing one host.

Trap Enabling Arc automatic updates on one SQL instance to isolate maintenance from another instance on the same host, although the feature configures host-level update services.

5 questions test this
Rolling SQL maintenance updates secondary replicas before the primary

When patching SQL Server instances that host an availability group, update secondary replicas first and verify that a suitable synchronous secondary has caught up before a planned manual failover. After applications have moved to the updated primary, service the former primary; keep replica maintenance windows staggered instead of patching all hosts simultaneously. For a major-version upgrade, failover also upgrades database internals, so mixed versions are a temporary upgrade state and failback to an older-version replica is not a rollback mechanism.

Trap Scheduling every availability replica for the same update window to keep versions identical throughout, although that removes the healthy failover target needed for rolling maintenance.

4 questions test this
Arc management of an existing SQL Server differs from running an Arc data service

When existing SQL Server installations must remain in a datacenter, edge site, or another cloud while gaining Azure inventory, assessment, performance monitoring, and optional security services, enable those servers through Azure Arc. For improvements to an existing instance's performance and security configuration, use best practices assessment to compare its configuration with Microsoft's recommendations rather than migration readiness assessment, which evaluates Azure target suitability. For ongoing performance monitoring of an eligible existing instance without migrating its databases, use Arc performance dashboards in the Azure portal rather than either assessment. Eligible Windows installations can also enable automated native backups (preview) to the instance's default local or network backup location for associated point-in-time restore, rather than obtaining PaaS-managed cloud backup storage. When the requirement is to run a managed-instance-style data service on infrastructure you control, SQL Managed Instance enabled by Azure Arc runs on Kubernetes with an Azure Arc data controller; that service consumes your cluster's capacity and uses a customer-controlled update cadence, whereas connecting an existing host to Arc does not convert it into that Kubernetes service or migrate its databases to Azure.

Trap Installing only the Azure Connected Machine agent to obtain a Kubernetes-hosted managed instance, although host onboarding does not deploy the Arc data controller or data service.

5 questions test this
Arc SQL onboarding connects the machine and discovers its SQL instances separately

Before onboarding an existing non-Azure host, verify a supported 64-bit SQL Server 2014-or-later and operating-system pairing; SQL Server in containers and Windows Server versions earlier than 2016 are unsupported. Connect the host through the Azure Connected Machine agent and ensure the Azure extension for SQL Server is installed; Arc normally deploys the SQL extension automatically when it discovers SQL Server, creating Azure resources for the discovered instances, and an already connected host can also have the extension added explicitly. Troubleshoot a missing SQL instance resource at the SQL extension and discovery layer instead of reinstalling the database engine or moving its data. Both agents communicate with Azure through outbound HTTPS on TCP 443, so Arc onboarding does not require exposing the inbound SQL client port to Azure. Discovery supports Windows and Linux, but best practices assessment and Arc performance monitoring are Windows-only; check feature-specific licensing and install Azure Monitor Agent (AMA) connected to Log Analytics when the selected service, such as best practices assessment, requires it.

Trap Installing the Azure VM SQL IaaS Agent extension on an on-premises host for Arc discovery, although Arc uses the Connected Machine agent and Azure extension for SQL Server.

4 questions test this
Arc subscription choice follows usage-licensing or extended-security needs

Use SQL Server enabled by Azure Arc pay-as-you-go licensing when a short-lived or variable-capacity SQL Server workload should be billed through Azure instead of requiring an upfront license purchase. The PAYG license type uses an hourly software meter, making workload duration and licensed core capacity relevant to cost. For an eligible SQL Server installation that must remain in place after its support lifecycle ends, an Extended Security Updates subscription provides security-update coverage for up to three years rather than migrating its databases. ESU enrollment requires an eligible licensing arrangement such as active Software Assurance, an active SQL Server subscription, or pay-as-you-go software licensing; inventory registration alone is not ESU enrollment.

Trap Assuming Arc inventory registration alone supplies Extended Security Updates, although eligible installations must also be subscribed to the security-update service.

5 questions test this
SQL Server on Linux is installed and serviced through a compatible package repository

For a Linux SQL Server host, select a supported distribution and version pairing and configure the Microsoft repository for the intended SQL Server major version before installing or updating mssql-server. Complete a fresh engine installation with sudo /opt/mssql/bin/mssql-conf setup rather than treating package installation alone as completed SQL Server configuration. On Ubuntu, refresh package metadata with sudo apt-get update and install the engine update with sudo apt-get install mssql-server; RHEL uses sudo yum update mssql-server, while supported SLES deployments use sudo zypper update mssql-server. An update replaces engine binaries without replacing the databases, whereas a major-version upgrade first requires selecting the target repository and validating that the upgrade path is supported.

4 questions test this
Existing SQL Data Sync groups provide selected-data synchronization rather than transactional continuity

Treat an existing SQL Data Sync group as selected-table, eventually consistent synchronization, not as a transactionally consistent migration or disaster-recovery mechanism; choose a supported purpose-built alternative for those requirements. Its insert, update, and delete triggers track changes for synchronization between an Azure SQL Database hub and SQL Server or Azure SQL Database members, with one-way or bidirectional flow; Azure SQL Managed Instance is unsupported. Connections require SQL authentication, synchronized tables require stable primary keys, and an on-premises member needs a local sync agent; schema changes are not automatically replicated and TRUNCATE is not tracked. The group's conflict policy determines which competing row change prevails: Hub wins overwrites the member's change with the hub's, whereas Member wins overwrites the hub's change with the member's. SQL Data Sync retires on September 30, 2027, and subscriptions that have not previously used it cannot create new sync groups, so plan replacement of existing groups instead of recommending unrestricted new deployment.

Trap Using SQL Data Sync to synchronize a Managed Instance member with an Azure SQL Database hub, although the service's supported member offerings exclude Managed Instance.

Configure resources for scale and performance

Read full chapter
  • Serverless minimum and maximum vCores bound elasticity and active compute cost
  • Serverless auto-pause requires both idleness and compatible feature settings
  • Provisioned SQL Database scaling must fit the target's performance and storage boundaries
  • Elastic pool sizing must accommodate concurrent compute demand and allocated storage
  • Pool capacity and per-database limits govern different resource boundaries
  • Business Critical provides local-storage performance and persistent In-Memory OLTP support
  • Hyperscale separates large database storage growth from compute scaling
  • Read-intent routing offloads reads but does not guarantee immediate visibility of primary writes
  • Memory-optimized table durability determines whether rows survive restart or failover
  • In-Memory OLTP object choice should match the source of processing overhead
  • Managed Instance scaling is an asynchronous instance-level management operation
  • Managed Instance compute and reserved storage require separate capacity decisions
  • Next-gen General Purpose makes instance I/O configurable without removing compute or feature limits
  • Instance pools reserve dedicated compute for separate small managed instances on shared infrastructure
  • Stopping an eligible General Purpose managed instance removes idle compute and license charges
  • Flexible memory increases eligible Managed Instance RAM without increasing vCores
  • Managed Instance structural limits require more than spare reserved storage
  • SQL VM storage performance is bounded by both the disks and the VM
  • SQL data and transaction-log disks need different caching and acceleration policies
  • Striped SQL VM storage needs its layout planned before the pool is created
  • SQL VM sizing must satisfy workload needs and supported scaling boundaries
  • Ephemeral local storage suits tempdb only when startup provisioning is reliable
  • Azure Elastic SAN suits consolidated SQL VM storage better than an isolated SQL workload
  • Partition functions assign rows while partition schemes assign storage
  • Aligned partitioned indexes require compatible boundaries and uniqueness keys
  • Sliding-window maintenance should split and merge empty boundary partitions
  • Partition switching reassigns existing storage rather than copying rows
  • Partition-level lock escalation requires the AUTO table option
  • Page compression adds prefix and dictionary savings to row compression
  • Compression settings belong to individual objects and partitions
  • Columnstore archival compression favors infrequently accessed data
  • Compression estimates predict storage savings rather than workload speed

Unlock with Premium — includes all practice exams and the complete study guide.

Plan and implement a migration strategy

Read full chapter
  • Migration readiness assessment identifies blockers before data movement starts
  • Performance-based migration sizing needs representative workload history
  • Moving user databases does not move the instance state they depend on
  • Online migration reduces the final write-freeze window rather than removing cutover
  • DMS online migration support depends on the selected Azure SQL target
  • A DMS backup migration needs access to the backup location and every final log
  • LRS continuous mode accepts new backups while autocomplete consumes a fixed chain
  • LRS needs a valid backup chain organized separately for each database
  • LRS migration to Business Critical includes secondary seeding in the final outage
  • Managed Instance link supports online migration with a readable Azure secondary
  • A link migration's return path depends on SQL Server version and Managed Instance update policy
  • Native Managed Instance migration restores SQL Server backups from Blob Storage URLs
  • Native migration of a TDE-protected database requires its source encryption certificate
  • A BACPAC export needs a write-free source or a transactionally consistent copy
  • BACPAC import creates a new database or loads an empty one rather than merging schema
  • An offline return from Managed Instance to SQL Server requires a format-aligned update policy
  • bcp transfers selected row data into prepared tables rather than packaging a database
  • bcp import options control value preservation and relational integrity
  • SQL Database copy creates an independent transactionally consistent database
  • Transactional replication can move selected tables while the source remains active
  • Managed Instance copy preserves the source while move transfers ownership of the workload
  • Managed Instance relocation requires replication connectivity as well as control-plane permission
  • A replication migration needs a supported distributor and separate snapshot-share connectivity
  • Managed Instance copy and move require compatible update policies and eligible replication state
  • LRS storage-access failures require validation of the exact token and database path
  • Asynchronous migration progress requires the status interface for the selected operation
  • Migration throughput tuning must target the constrained stage of the transfer
  • BACPAC import client selection must satisfy authentication and network requirements
  • An emergency replication reset discards pending delivery and requires reinitialization

Unlock with Premium — includes all practice exams and the complete study guide.

Implement a secure environment

Configure database authentication and authorization

Read full chapter

Cheat sheet

Sharp facts the exam loves — scan these before test day.

A Microsoft Entra administrator establishes Entra authentication for Azure SQL

Azure SQL Database and Azure SQL Managed Instance require a configured Microsoft Entra administrator before Microsoft Entra identities can connect. Set the administrator on the logical server or managed instance, then use an authorized Entra connection to establish the required SQL principals. The administrator property accepts one identity; selecting a group lets multiple administrators operate through that identity. Removing the administrator disables Entra-authenticated connections even when database users and their permissions still exist. Identities managed in another tenant must first be imported as external users into the SQL resource's associated tenant before contained-user provisioning, or gain access through an appropriately provisioned Entra group; configuring the administrator does not permit direct provisioning of an unrelated tenant's identity.

Trap Assigning the Azure SQL Server Contributor role to the intended administrator, which delegates resource management rather than configuring the server's Entra administrator property.

4 questions test this
Interactive administrators and unattended workloads need different Entra authentication methods

When a human administrator must satisfy multifactor authentication, use an interactive Microsoft Entra authentication method in a supporting client and enforce MFA through Conditional Access. For an unattended Azure workload, prefer its managed identity so Azure supplies tokens without an application-managed password. A service principal is another workload identity, but using a client secret introduces a credential that must be protected and rotated.

Trap Using Microsoft Entra Password authentication for a person subject to an MFA challenge, which cannot complete the required interactive verification.

5 questions test this
Microsoft Entra-only authentication changes which existing accounts can connect

Microsoft Entra-only authentication restricts Azure SQL connections to Entra identities rather than merely adding another sign-in option. Enabling it disables SQL-authenticated connections, including the SQL administrator, without requiring that their accounts be deleted. Validate application authentication methods before enabling it, and disable the setting when an authorized requirement genuinely needs another supported method.

Trap Rotating SQL login passwords as the remedy for failures after an Entra-only rollout, which leaves the rejected authentication method unchanged.

3 questions test this
SQL Server on Azure VMs uses a managed identity to resolve Entra principals

SQL Server 2022 and later on Azure Windows VMs can use Microsoft Entra authentication through the SQL IaaS Agent integration. Configure a system-assigned or user-assigned managed identity in the VM's tenant and grant that identity User.Read.All, GroupMember.Read.All, and Application.Read.All Microsoft Graph application permissions, or the broader Directory Readers role. SQL Server uses this identity for directory lookups when creating Entra logins and authenticating them, so a directory-permission failure belongs to that identity rather than the connecting application's database role.

Trap Granting db_owner to the application user to repair Microsoft Graph lookup failures, which changes database authorization rather than the VM identity's directory permissions.

3 questions test this
Azure Arc and SQL IaaS Agent provide different host integration paths for Entra authentication

For SQL Server 2022 and later on supported on-premises Windows or Linux hosts, Azure Arc registration with the Azure Connected Machine agent and Azure extension for SQL Server provides an Entra authentication configuration path. Azure-hosted SQL Server VMs use SQL IaaS Agent integration instead of requiring Arc registration for this purpose. Supported Windows installations also have a documented manual configuration path without Arc. Standalone instances and availability groups support Entra authentication, whereas SQL Server failover cluster instances do not.

Trap Selecting a SQL Server failover cluster instance for a deployment that requires Entra database sign-in, which uses an unsupported instance topology.

3 questions test this
SQL-authenticated logins require mixed mode on SQL Server

SQL Server Windows Authentication mode rejects SQL-authenticated logins; mixed mode permits both Windows and SQL authentication. In SSMS, change the instance's Properties, Security, Server authentication setting and restart the Database Engine for the new mode to take effect; restart SQL Server Agent as well if it is running. Changing from Windows-only to mixed mode does not automatically enable sa, and applications should use specifically provisioned logins rather than enabling sa merely to make SQL authentication work.

Trap Creating an identically named Windows login for a legacy SQL-password connection, which does not change the authentication protocol used by that client.

3 questions test this
Managed Instance can preserve Windows authentication through Microsoft Entra Kerberos

When a legacy application must retain Windows authentication while its database moves to Azure SQL Managed Instance, use Windows Authentication for Microsoft Entra principals. Microsoft Entra Kerberos supports this experience from appropriately configured Active Directory-joined, Entra-joined, or hybrid-joined devices without deploying Microsoft Entra Domain Services solely for the feature. This differs from changing the application to an Entra-aware driver's token authentication method and can preserve an otherwise unmodifiable authentication stack.

Trap Replacing an unchanged Integrated Security connection with a SQL login on the instance, which provisions a different credential type without satisfying the client's Windows authentication flow.

3 questions test this
Login provisioning must match the credential type and the platform's password controls

On SQL Server, CREATE LOGIN [AppLogin] WITH PASSWORD = '<strong-password>' provisions a SQL-password login, while CREATE LOGIN [CONTOSO\AppUser] FROM WINDOWS maps an existing Windows principal rather than storing its domain password in SQL. DEFAULT_DATABASE = [AppDb] selects the login's initial database when the client does not specify one; omitting it defaults the login to master. For SQL Server SQL logins, CHECK_POLICY defaults to ON and CHECK_EXPIRATION defaults to OFF; MUST_CHANGE requires both ON, and CHECK_EXPIRATION = ON cannot be combined with CHECK_POLICY = OFF. Azure SQL Database enforces password complexity rather than SQL Server's full Windows password-expiration and lockout-policy model, so those policy controls must not be assumed to operate identically on the two platforms.

Trap Combining MUST_CHANGE with CHECK_EXPIRATION = OFF for a SQL Server login, which makes login creation fail because mandatory first-use password change requires expiration enforcement.

3 questions test this
Creation-only principal delegation uses version-appropriate permissions at separate security scopes

For creation-only delegation on SQL Server 2022 and later, grant CREATE LOGIN at server scope and CREATE USER in the target database instead of broader authority to alter existing principals. Earlier SQL Server versions require ALTER ANY LOGIN or securityadmin membership for login creation, and ALTER ANY USER in the database for user creation. On SQL Server 2022 and later, GRANT CREATE LOGIN TO [ProvisionLogin] delegates login creation, while GRANT CREATE USER TO [ProvisionUser] must be issued in each intended database to delegate user creation there. Diagnose a provisioning permission failure at the scope of the failing statement rather than assuming that database user-creation authority also permits server login creation.

Trap Using securityadmin for a SQL Server 2022 delegate restricted to creating logins, which also grants broader login-management authority than the creation-only requirement.

3 questions test this
A login and its mapped database user serve different security scopes

A login authenticates at server scope, while a mapped user supplies the database identity to which roles and object permissions are assigned. Create the login first, then run CREATE USER [AppUser] FOR LOGIN [AppLogin] in each required database; the names can differ because the mapping uses security identifiers. One login can map to users in multiple databases, but only one user per database. A database role groups database principals, not server logins. CREATE USER [AppUser] FOR LOGIN [AppLogin] WITH DEFAULT_SCHEMA = [Reporting] makes Reporting the first schema searched for unqualified object names without granting access to its objects; correct the schema setting or qualify the object name for a resolution problem, and grant the required permission only for an authorization problem.

Trap Adding AppLogin directly to a database role, which uses a server principal where the role expects a database principal.

4 questions test this
Contained users reduce server dependencies when databases move

When a tenant database must move independently of its hosting server, a contained database user keeps authentication at database scope rather than depending on a login in master. A SQL contained user is created with CREATE USER [TenantUser] WITH PASSWORD = '<strong-password>'; the connection must explicitly name that database. Azure SQL Database supports this pattern directly, while SQL Server additionally requires contained database authentication to be enabled and the database configured for containment. Separate contained SQL users in multiple databases have independently managed passwords.

Trap Creating the tenant account only in virtual master to simplify database moves, which leaves the tenant database dependent on a principal outside its containment boundary.

3 questions test this
public supplies shared database rights while guest supplies a fallback database identity

public supplies baseline rights to every database user, whereas enabled guest access in SQL Server lets authenticated logins without their own database user access the database with guest's permissions. Every database user belongs to public and cannot be removed from that role, so grant application-specific business access to a dedicated role instead of public and review public when removing unexpected baseline access. A denial through public can also affect users who receive grants through other roles because permission evaluation includes their public membership. The guest user already exists and is enabled with GRANT CONNECT TO guest, not CREATE USER, so review that fallback access path independently of public membership and explicitly mapped users.

Trap Using public as a convenient reporting role for one department, which gives the reporting entitlement to unrelated current and future database users.

4 questions test this
A database user for an Entra group separates SQL permissions from individual membership

When a team needs the same database access, connect with an authorized Entra identity and create the group user in the target database with CREATE USER [TeamReaders] FROM EXTERNAL PROVIDER. Assign that user to a suitable database role, for example ALTER ROLE db_datareader ADD MEMBER [TeamReaders] when all user tables and views must be readable. Individual onboarding and removal can then occur through Entra group membership rather than separate SQL grants for every person. Scope the role more narrowly when the group needs only selected objects. If database login fails despite correct principal provisioning and SQL grants, check the connecting identity's group memberships: Entra users and service principals belonging to more than 2048 Entra security groups are unsupported for database login to Azure SQL Database and Azure SQL Managed Instance.

Trap Provisioning the group only as a user in master, which does not establish its database principal and role membership in the application database.

4 questions test this

A contained user with the same name as a server login is an independent principal, so matching names do not establish a link to that login's privileges. CREATE USER [IdentityName] FROM EXTERNAL PROVIDER creates a contained Entra database user, whereas CREATE LOGIN [IdentityName] FROM EXTERNAL PROVIDER followed by CREATE USER [IdentityName] FROM LOGIN [IdentityName] establishes a login-based user. Use the login-based pattern when the database identity must be linked to the server principal's permissions. When diagnosing Azure SQL Entra mappings, inspect sys.database_principals: a contained user's SID is 16 bytes, whereas a login-based user's SID is 18 bytes with an AADE suffix; remove that suffix before correlating the user's SID with its login in sys.server_principals rather than treating the unequal raw SIDs as proof of an orphaned user.

Trap Changing the contained user's display name to match the privileged login, which leaves the underlying principal relationship unchanged.

4 questions test this
The SQL resource's identity supplies directory lookup authorization

For service-principal-driven CREATE USER ... FROM EXTERNAL PROVIDER in Azure SQL Database or Managed Instance, configure the SQL resource's managed identity with directory-read access so the engine can resolve the requested Entra principal. Azure SQL cannot impersonate the calling application for Microsoft Graph lookups, so successful provisioning by an interactive Entra user does not establish that the same statement will work under automation. Assigning Directory Readers to the server or instance identity is a supported way to supply directory-read access and requires a Privileged Role Administrator or higher tenant role; the provisioning caller separately needs SQL permission to create the principal. If SQL error 33134 contains an underlying Entra access-denied or MFA-enrollment message, investigate Conditional Access for Microsoft Graph application 00000003-0000-0000-c000-000000000000 rather than automatically attributing the failure to a missing directory grant or adding broader SQL roles.

Trap Granting Graph permissions only to the deployment application's identity, which leaves the SQL server identity unable to perform its own required directory lookup.

4 questions test this
Entra user creation must identify the intended directory object unambiguously

On Azure SQL Database or Managed Instance, use ordinary name-based provisioning for a unique name and WITH OBJECT_ID to disambiguate a nonunique group or application display name, even when the identity exists and the caller has provisioning permission. For Azure SQL contained-user provisioning, CREATE USER [adrian@contoso.com] FROM EXTERNAL PROVIDER identifies an individual by user principal name, whereas groups, service principals, and managed identities use their display names. For an application, CREATE USER [myapp_suffix] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '<service-principal-object-id>' uses the display name plus a distinguishing suffix as the SQL alias; take that object ID from Enterprise applications in the SQL resource's tenant, not the application/client ID or the different application-object ID under App registrations. As an alternative to validated lookup in Azure SQL Database, use CREATE USER [IdentityAlias] WITH SID = <binary-identity-id>, TYPE = E for an Entra user or service principal, including a managed identity, or TYPE = X for a group. For this SID-and-TYPE route, convert the user or group object ID, or the application or managed identity client ID, to binary(16) and independently verify the supplied identity because the Database Engine does not validate it in Entra.

Trap Using the App registrations object ID in WITH OBJECT_ID, which identifies the application object instead of the tenant's enterprise-application service principal.

4 questions test this
Azure SQL Database supports explicit Entra logins for additional administrative identities

In Azure SQL Database, the Microsoft Entra administrator must create the first Entra login in virtual master with CREATE LOGIN [IdentityName] FROM EXTERNAL PROVIDER; a SQL-authenticated administrator cannot perform this Entra provisioning. An Entra group login cannot be assigned Azure SQL Database fixed server roles or made a database owner, so use a supported individual or application principal when those administrative capabilities are required. Authenticating through a group login does not implicitly create each member's individual login or default schema. If a group member cannot perform CREATE SCHEMA because that individual provisioning is absent, create the individual's Entra login or assign a default schema to the group. An identity authenticating as the configured Entra administrator, individually or through its administrator group, retains administrator effective permissions even if a separate restricted Entra login is created for it, because the administrator identity takes precedence over that login.

Trap Using an Entra group login as the member of ##MS_LoginManager## for team-wide delegation, which targets a fixed server role that does not accept Entra group logins.

4 questions test this
Database-wide read and write roles grant independent capabilities

db_datareader permits reading all user tables and views, while db_datawriter permits inserting, updating, and deleting rows in user tables. Workloads that must both read and modify data commonly need both capabilities, whereas a reporting account needs only the read capability. These fixed roles are database-wide, so selected-table access calls for a custom role with explicit object permissions. Manage membership with ALTER ROLE [RoleName] ADD MEMBER or DROP MEMBER rather than recreating the user.

Trap Assigning db_ddladmin to a data-entry application, which delegates database-definition operations instead of the required row-level read and write permissions.

3 questions test this
Managing role membership is different from granting database access

db_securityadmin can manage permissions and membership of custom database roles, while db_accessadmin manages who can access the database. If an administrator must manage only one custom role, grant ALTER ON ROLE::[ApplicationRole] rather than a database-wide security role. db_securityadmin does not delegate membership management of fixed database roles, and its broad permission-management capability can enable privilege escalation. Choose the smallest administration boundary that meets the requirement.

Trap Assigning db_accessadmin to a team that must maintain custom role memberships, which provides user-access administration rather than the required role-management authority.

4 questions test this
Azure SQL Database distinguishes database provisioning from login management

In Azure SQL Database virtual master, ##MS_DatabaseManager## delegates database creation and deletion, while ##MS_LoginManager## delegates login creation and deletion. Prefer these fixed server roles over the dbmanager and loginmanager database roles: use ALTER SERVER ROLE [##MS_LoginManager##] ADD MEMBER [ProvisionLogin] for login membership, whereas the older roles accept database users in master. ##MS_DatabaseConnector## provides CONNECT to all databases without requiring a separately created database user, but does not by itself grant SELECT or other data permissions. A database-creation delegate becomes owner of databases it creates but does not automatically gain data access to every other database. For SQL-authenticated database creation through the older dbmanager route, use a login-mapped user rather than a password-contained user in master.

Trap Granting db_owner in one application database to a person who must create other databases, which grants authority at the wrong database scope.

4 questions test this
SSMS permission dialogs apply rights to the selected securable and principal

In SSMS, open a securable's Properties and Permissions page to select a database user or role and configure the operation needed on that securable. Choose the table for table-specific SELECT or UPDATE rights, or the database for database-scoped permissions, rather than changing server-role membership. The Grant, With Grant, and Deny choices represent permission use, delegated grant authority, and explicit rejection respectively. The grantor must hold the permission with GRANT OPTION or a higher permission that implies it before delegating that permission.

Trap Using the server login's role-membership page for a single stored procedure entitlement, which configures server roles rather than that object's database permission.

4 questions test this
Database-wide fixed roles are unsuitable for narrowly scoped administration or data exceptions

Use db_owner only when all database configuration and maintenance authority is required; db_ddladmin instead delegates database-wide DDL and can enable privilege escalation by altering code executed with higher privileges. The backup-specific db_backupoperator role is not a useful delegation option in Azure SQL Database because native BACKUP and RESTORE statements are unavailable there. db_denydatareader applies a database-wide SELECT denial to user tables and views, while db_denydatawriter applies INSERT, UPDATE, and DELETE denials to user tables. When a role must retain ordinary table access to selected objects, use narrowly scoped explicit permissions instead of adding it to a database-wide denial role.

Trap Assigning db_ddladmin for a deployment restricted to one schema, which permits definition changes throughout the database and can affect code run by more privileged principals.

4 questions test this
A custom role needs separately configured permissions and membership

CREATE ROLE [ReportRole] AUTHORIZATION [dbo] creates a database role but does not grant its application permissions or enroll its users. Run GRANT SELECT ON OBJECT::Reporting.MonthlyTotals TO [ReportRole] and ALTER ROLE [ReportRole] ADD MEMBER [ReportUser] as separate steps in the target database. On SQL Server or Managed Instance, server-wide delegation instead uses CREATE SERVER ROLE [DiagnosticRole], a server-scoped grant such as GRANT VIEW SERVER STATE TO [DiagnosticRole], and ALTER SERVER ROLE [DiagnosticRole] ADD MEMBER [OperatorLogin]. Azure SQL Database does not support user-defined server roles, so choose a custom database role for database permissions or an appropriate fixed server role for supported logical-server delegation.

4 questions test this
Delegated permissions must match both the required operation and securable scope

Match delegated permissions to both the required operation and securable scope: GRANT SELECT ON SCHEMA::Reporting TO [ReportRole] covers the schema's current and subsequently created objects, whereas a smaller entitlement requires grants on individual objects or selected columns. CONTROL confers ownership-like authority, and ALTER on a schema permits definition changes to contained objects, so neither is a substitute for a read-only requirement. Creating a schema-contained object requires both its database-level CREATE permission and ALTER on the intended schema, for example GRANT CREATE TABLE TO [DeploymentRole] together with GRANT ALTER ON SCHEMA::Reporting TO [DeploymentRole]. REFERENCES on a referenced table authorizes a foreign-key dependency, and REFERENCES on an object is needed for schema-bound views or functions referencing it; this permission does not grant SELECT access to its data.

Trap Granting SELECT at database scope to accommodate future reporting tables, which also includes unrelated schemas outside the required reporting boundary.

3 questions test this
Stored procedure execution can expose an approved operation without exposing unrestricted table access

When an application should perform only a defined database operation, grant EXECUTE on the stored procedure rather than broad direct permissions on the underlying tables. An unbroken ownership chain between a procedure and its referenced objects can avoid separate checks on those objects, while a changed ownership boundary can require additional authorization. Deliberate module execution context can provide controlled access, but it should be scoped to the operation rather than granting the caller permanent administrative membership. CREATE USER [ModuleContext] WITHOUT LOGIN and users mapped with FROM CERTIFICATE or FROM ASYMMETRIC KEY can hold permissions but cannot authenticate; certificate- or asymmetric-key-mapped logins likewise serve code signing rather than client connections. As an alternative to changing execution context, create a certificate-mapped user, grant it the required rights, and sign the procedure with ADD SIGNATURE TO [ProcedureName] BY CERTIFICATE [SigningCertificate] so those rights supplement the caller's permissions during signed execution without permanently elevating the caller.

Trap Assigning db_datawriter to an application that may modify data only through validation procedures, which also permits direct changes outside those procedures.

3 questions test this
Azure resource management and SQL data access have separate authorization systems

Azure RBAC roles such as SQL DB Contributor and SQL Server Contributor authorize management operations on Azure resources, not SQL query access to database data. A managed identity or human Entra identity still needs a SQL principal and appropriate database permissions to execute queries. Conversely, granting a database user SELECT does not permit that identity to resize the Azure resource. Delegate each plane independently when an operational role needs both kinds of access.

Trap Assigning subscription Contributor to a reporting identity that receives SELECT permission errors, which expands resource-management privileges without supplying SQL data-plane authorization.

3 questions test this
Missing catalog rows can reflect metadata visibility rather than missing objects

SQL metadata visibility normally exposes securables a user owns or has some permission on, so a catalog query can return no row and a metadata function can return NULL for an existing object. When an operator must inspect an object's definition without reading its data, grant VIEW DEFINITION at the object scope instead of a data-access role. Broaden that permission to database scope only when inspection of all definitions is required. Test metadata queries under the intended user's context before concluding that a deployment omitted an object.

Trap Assigning db_datareader to an operator who needs definitions only, which reveals business data beyond the metadata inspection requirement.

3 questions test this
Diagnostic delegation should distinguish state reading from state changes and sensitive definition access

For Azure SQL Database diagnostics, choose ##MS_ServerStateReader## for VIEW SERVER STATE and related database-state rights; ##MS_ServerStateManager## adds ALTER SERVER STATE for management operations such as cache clearing. ##MS_DefinitionReader## exposes general catalog definitions through VIEW ANY DEFINITION, whereas ##MS_SecurityDefinitionReader## targets security definitions and additionally grants VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION, including access to SQL-login password hashes. Assign the security-definition role only to trusted personnel who require that sensitive access, rather than treating it as a harmless subset of definition reading. For propagated database permissions to be usable, the login needs a matching database user or connectivity through ##MS_DatabaseConnector##, including for virtual master.

Trap Using ##MS_ServerStateManager## for a read-only DMV collector, which also permits state-changing management operations such as clearing caches.

4 questions test this
REVOKE removes an explicit permission decision while DENY blocks an otherwise available grant

GRANT allows an operation, DENY generally overrides grants received through other roles or groups, and REVOKE removes the explicit grant or denial being targeted. A revoked direct grant can leave access intact through role membership, so review all relevant principals before deciding that access was removed. Permission checks do not constrain sysadmin members or object owners in the same way, and a column-level GRANT is a documented exception to a table-level DENY. Avoid assuming that a direct GRANT overrides an inherited DENY.

Trap Issuing another direct GRANT after a denial inherited from a role, which does not remove the effective denial.

3 questions test this
A restored user's login mapping must match the destination login's SID

A login-mapped user can become orphaned after a restore or after its login is dropped and recreated, even when a destination login has the same name. Compare sys.database_principals with the corresponding login security identifiers rather than relying on names. Use ALTER USER [DatabaseUser] WITH LOGIN = [ExistingLogin] to remap the user, or recreate the intended SQL login with the original SID when preserving that identity is appropriate. Keeping the existing database user preserves its role memberships and permissions.

Trap Dropping and recreating the database user to match a login name, which discards the existing user identity and its grants instead of repairing the mapping.

3 questions test this
Effective-permission tests must run in the security context being diagnosed

sys.fn_my_permissions(NULL, 'DATABASE') reports the current caller's effective database permissions, while HAS_PERMS_BY_NAME can test a particular permission on an object. Use authorized EXECUTE AS USER = 'UserName' and REVERT to examine a database user's actual context; sys.database_permissions alone does not summarize all rights inherited through fixed roles and other memberships. EXECUTE AS USER stays within the database, whereas EXECUTE AS LOGIN is a server-scoped option on supporting instance platforms and is unavailable in Azure SQL Database. Impersonation requires the relevant authority on the principal. To trace inherited role rights, join sys.database_role_members to sys.database_principals on role_principal_id and member_principal_id, or inspect sys.server_role_members for server roles; in Azure SQL Database virtual master, resolve SQL-login members through sys.sql_logins and Entra-login members through sys.server_principals.

Trap Running a successful permission test as the server administrator and treating it as proof of application access, which evaluates a different security token.

3 questions test this
Disabling an Entra login revokes its login-based connection path rather than every path for that identity

In Azure SQL Database, ALTER LOGIN [IdentityName] DISABLE blocks connections through that Entra login and its login-based users, but leaves independent contained users unaffected. Disablement is unsupported for Entra group logins and cannot be used to disable an individual who belongs to an Entra group login in master, including an administrator group. Entra login permission and server-role changes normally take effect when the login reconnects rather than changing existing sessions' rights. When cached authorization delays the change, an authorized administrator can clear the affected database's authentication and token-permission caches with DBCC FLUSHAUTHCACHE and DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS, then require reconnection before retesting.

Trap Using ALTER LOGIN DISABLE to remove access supplied by an independent contained user, which changes a server principal outside that user's authentication path.

3 questions test this

Implement security for data at rest and data in transit

Read full chapter
  • TDE encrypts database storage without changing ordinary application queries
  • SQL Server TDE recovery depends on the certificate protecting the database encryption key
  • TDE configuration and completed encryption are different operational states
  • Customer-managed TDE delegates protector lifecycle control without replacing page encryption
  • Customer-managed TDE depends on durable key access for the SQL resource's managed identity
  • Rotating a TDE protector does not re-key older backups
  • Cell-level encryption protects selected values through keys in the user database
  • Symmetric-key decryption requires an open key in the correct database context
  • A stable authenticator helps detect encrypted values moved between rows
  • Moving a SQL Server database can break automatic opening of its database master key
  • Database-specific IP access requires a rule stored in the target database
  • A narrow database firewall rule does not cancel a broader server allowance
  • Allow Azure services admits Azure-origin traffic beyond the owning subscription
  • Always Encrypted separates database administration from permission to see sensitive values
  • Always Encrypted stores key references and wrapped keys in SQL rather than plaintext master keys
  • Without enclaves, deterministic encryption enables equality operations while randomized encryption limits leakage
  • Always Encrypted query access spans the database, client driver, and key store
  • Always Encrypted values must reach SQL as correctly encrypted parameters
  • Conventional Always Encrypted conversion requires client-side cryptographic tooling
  • Always Encrypted column eligibility constrains the schema before encryption can begin
  • Secure enclaves enable richer comparisons without exposing plaintext to the surrounding engine
  • Enclave-enabled keys allow column cryptographic changes to remain inside the database
  • Enclave technology determines the supported attestation path
  • VBS enclave enablement is a database or elastic-pool configuration rather than a query switch
  • Private endpoints change SQL network resolution without changing the logical server connection name
  • Private endpoint admission and public endpoint shutdown are separate network decisions
  • SQL service endpoints authorize a subnet without allocating a private SQL address
  • Encrypted SQL connections should also validate the server certificate

Unlock with Premium — includes all practice exams and the complete study guide.

Implement compliance controls for sensitive data

Read full chapter
  • Sensitive-column discovery proposes classifications rather than automatically enforcing protection
  • T-SQL classification supports repeatable labeling with narrowly delegated permissions
  • Column classifications enrich audit interpretation only when database activity is also audited
  • Server audit policies cover existing and future databases and are not overridden by database policies
  • Audit storage, analysis, and streaming requirements can justify different destinations
  • An enabled audit policy still needs a working destination delivery configuration
  • Audit identity evidence depends on the authentication stage and effective database principal
  • A healthy Azure SQL audit destination does not establish lossless full-text capture
  • Defender findings and threat alerts complement rather than replace SQL audit evidence
  • A server audit supplies the destination while audit specifications select the events
  • Managed Instance auditing to a storage URL needs a credential for that container
  • Native SQL Server audit targets require destination-specific operating-system authorization
  • An audit configured with ON_FAILURE = SHUTDOWN can make SQL Server unavailable
  • Delegated audit management remains inside the audit's trust boundary
  • Change tracking is appropriate for current-state synchronization rather than full value history
  • Change tracking requires database enablement before individual table enablement
  • A change-tracking consumer must reinitialize when its synchronization version is too old
  • Temporal history cleanup requires both table retention and database-level enablement
  • Change-tracking consumption must preserve complete, retryable version batches
  • Snapshot isolation gives change tracking a consistent multi-step synchronization view
  • CDC exposes log-derived row images through a capture instance rather than synchronous change-tracking metadata
  • Change tracking and CDC require feature-specific permissions beyond ordinary table reads
  • A CDC extraction interval must fit within the retained and already captured LSN range
  • CDC capture and cleanup jobs must operate on the availability-group primary after failover
  • Live change-tracking enumeration belongs on the availability-group primary
  • CDC enumeration can preserve individual changes or collapse an interval to its net effect
  • A cross-server SQL Server restore needs KEEP_CDC to preserve capture metadata
  • CDC administration must match Azure SQL Database's managed-service capabilities
  • Dynamic data masking reduces result exposure while leaving stored values unchanged
  • The masking function determines which parts of a value remain recognizable
  • UNMASK must cover the source data before a reader copies plaintext values
  • Dynamic masking is defined on eligible base columns rather than derived expressions
  • Updatable and append-only ledger tables support different application write models
  • Ledger verification relies on digests protected outside the database
  • Ledger configuration cannot be treated as a reversible switch on existing tables
  • RLS filters control visible rows while block predicates reject invalid writes
  • An RLS security policy binds predicate functions to the protected table
  • SESSION_CONTEXT can carry tenant identity only when the application establishes it safely
  • RLS applies to administrators unless the policy explicitly allows their access
  • RLS protection must be reviewed separately for history and change-data access
  • Reliable RLS isolation requires hardening beyond a normally filtered SELECT

Unlock with Premium — includes all practice exams and the complete study guide.

Monitor, configure, and optimize database resources

Monitor resource activity and performance

Read full chapter

Cheat sheet

Sharp facts the exam loves — scan these before test day.

A useful baseline includes normal peak and off-peak operation

Collect performance measurements at regular intervals while the database is healthy, including peak and off-peak periods, production query response times, and backup and restore completion times. Preserve these observations so later measurements can be compared with the corresponding workload period. A deviation from normal behavior identifies a tuning candidate; a single busy-period snapshot does not establish what normal operation looks like.

3 questions test this
Response time and throughput measure different aspects of service

Response time measures how long a user waits for the first returned row, whereas throughput measures the number of queries handled in a period. Record both alongside user concurrency and resource usage: more completed requests do not establish that individual requests became faster. Use the application's latency and completion requirements to judge whether a change improved service under comparable load.

Trap Using batch throughput alone as the acceptance criterion for an interactive workload whose requirement concerns individual response time.

3 questions test this
Performance Monitor collects SQL Server engine counters on Windows

For SQL Server on a Windows Azure VM, use Windows Performance Monitor to collect SQL Server counter objects such as Buffer Manager and SQL Statistics together with operating-system CPU, memory, and disk counters. This correlates engine activity with host resource consumption over time. Use an event-oriented collector such as Extended Events when individual executions and their context, rather than counter rates, are needed.

Trap Using only Microsoft.Compute virtual-machine platform metrics for buffer-cache and batch-request counters, which are SQL engine instrumentation rather than host platform metrics.

4 questions test this
Azure SQL resource views trade recent detail for historical retention

In Azure SQL Database, query sys.dm_db_resource_stats in the user database for resource percentages sampled over 15-second intervals and retained for approximately one hour. Query sys.resource_stats in the logical server's master database for five-minute aggregates retained for approximately 14 days. Choose the first for a current short-lived spike and the second for historical capacity trends, allowing for its reporting delay. For pool-wide measurements, query sys.dm_elastic_pool_resource_stats from any database in the pool for recent detail, or sys.elastic_pool_resource_stats in master for historical trends over the last 14 days. Pool resource percentages use the elastic pool's limits rather than an individual database's limits, so match the view's scope to the resource boundary being investigated.

Trap Using a current request snapshot to reconstruct a resource-utilization incident from the previous week, after those requests have finished.

4 questions test this
Diagnostic settings route Azure SQL logs to purpose-specific destinations

Configure diagnostic settings on Azure SQL resources to export selected resource-log categories and metrics. Send telemetry to Log Analytics for queryable analysis with other monitoring data, Event Hubs for streaming integrations, or Azure Storage for archival. Choose the required categories explicitly; viewing platform metrics in the portal is a different operation from exporting resource logs for downstream consumption.

Trap Using the Azure Activity log as a substitute for SQL diagnostic resource logs, because it records management-plane operations rather than the selected engine telemetry.

4 questions test this
DTU percentage follows the busiest of three resource dimensions

Azure SQL Database DTU percentage is the highest of CPU percentage, Data IO percentage, and Log IO percentage at a point in time. Inspect those underlying dimensions before choosing a remedy, because a log-write-limited workload needs a different investigation from a CPU-heavy one. A high composite value can therefore occur while two component metrics remain low.

4 questions test this
User CPU and SQL instance CPU percentages have different denominators

Azure SQL Database CPU percentage measures consumption toward the user workload CPU limit, while SQL instance CPU percent includes user and system workloads on an instance scale. These percentages are not directly comparable or interchangeable. Use the user metric to assess the workload's entitlement and the instance metric to investigate total engine pressure, including internal activity.

4 questions test this
High cache memory use does not by itself establish memory pressure

The database engine intentionally caches data in available memory to avoid slower storage reads, so a high Azure SQL memory-utilization percentage can be healthy. Investigate evidence such as waiting memory grants, out-of-memory errors, or excessive physical reads before diagnosing a shortage. For SQL Server Buffer Manager, Page life expectancy measures seconds a page stays without references, Free list stalls/sec counts requests waiting for a free page, and Lazy writes/sec counts dirty buffers written to make buffers reusable. Falling page life expectancy together with rising physical Page reads/sec and free-list stalls suggests cache turnover, while Buffer cache hit ratio measures pages found in cache and can change slowly. Evaluate these counters against the workload and its healthy baseline rather than an isolated universal threshold, and distinguish cache turnover from query execution memory contention.

Trap Clearing the buffer cache to lower the memory-utilization chart, which discards useful cached pages and creates additional storage reads.

3 questions test this
A parallel request can consume several workers within one session

A session is a connection, a request is a submitted query or batch, and a parallel request can require multiple worker threads. In Azure SQL Database, hitting worker or session limits rejects new work, and blocked or long-running parallel requests can exhaust workers even without a large connection count. Correlate worker utilization with blocking, query duration, and parallelism before changing connection limits or compute size.

Trap Increasing the client connection-pool limit to address worker exhaustion, which can admit additional work without reducing workers consumed by existing requests.

3 questions test this
Storage utilization metrics must match the affected data or log boundary

Azure SQL Database Data space used measures occupied data pages, whereas Data space allocated measures the data files, including free space inside them. Deleting rows can make space reusable without reducing allocated file size. Compare both metrics with the database and elastic-pool storage boundaries when deciding whether growth, reclamation, or a capacity change is actually required. For tempdb transaction-log capacity, use Tempdb Percent Log Used (tempdb_log_used_percent), which measures log-space consumption toward tempdb's maximum log size rather than user-database data space or PAGELATCH contention. A low-tempdb-log-space alert can use this signal with Minimum greater than 60 percent over five minutes, evaluated every minute; this documented example detects sustained high utilization rather than defining a universal threshold.

Trap Using user-database Data space used percent to alert on tempdb log exhaustion, which measures occupied data-file space against a different capacity boundary.

3 questions test this
Storage utilization metrics identify the saturated disk or VM allowance

For a SQL Server VM storage incident, split Data Disk IOPS Consumed Percentage and Data Disk Bandwidth Consumed Percentage by LUN to distinguish operation-rate saturation from bytes-per-second saturation on each disk. Compare these with VM Cached and VM Uncached IOPS Consumed Percentage and Bandwidth Consumed Percentage, where supported, to identify which storage allowance is being consumed. A saturated VM metric with unsaturated individual-disk metrics directs investigation to the VM allowance rather than a single disk. Correlate Data Disk Latency in milliseconds and Data Disk Queue Depth with the utilization pattern, because outstanding I/O and completion delay describe the impact rather than identifying the capped dimension by themselves.

Trap Using Data Disk IOPS Consumed Percentage averaged across LUNs to rule out a disk bottleneck, which can conceal one saturated disk among idle disks.

3 questions test this
Compilation rates must be interpreted alongside incoming batch volume

Compare SQL Compilations/sec and SQL Re-Compilations/sec with Batch Requests/sec to distinguish optimization work from the volume of incoming T-SQL batches; SQL Compilations/sec already includes statement recompilations. Rising recompilations call for checking schema or statistics changes and session SET changes such as ANSI_NULLS, rather than assuming that more batches arrived. Query sys.dm_os_performance_counters with object_name LIKE '%SQL Statistics%' for a T-SQL source of these engine counters. For per-second counters, derive interval rates from successive cumulative cntr_value samples and elapsed time instead of treating one raw value as a per-second rate.

Trap Using the sum of SQL Re-Compilations/sec and SQL Compilations/sec as total compilation work, which counts statement recompilations twice.

3 questions test this
Burst-credit exhaustion returns a SQL VM resource to its baseline allowance

For a SQL VM slowdown after a burst, inspect credit telemetry before treating the burst maximum as sustained capacity. On B-series VMs, correlate CPU Credits Remaining with CPU Credits Consumed: running above base CPU performance spends credits, and exhaustion throttles CPU back to the base level until credits accumulate. For credit-based I/O bursting where supported, examine Data Disk Used Burst IO Credits Percentage and Data Disk Used Burst BPS Credits Percentage, plus the corresponding VM Cached or VM Uncached credit metrics, to distinguish depleted operation-count credits from depleted throughput credits at disk or VM scope. These I/O metrics report the percentage already used, not credits remaining, so compare the post-burst workload with baseline IOPS or bandwidth rather than sizing sustained demand from a short burst.

Trap Assuming a high Used Burst BPS Credits Percentage represents spare throughput credit, which reverses a metric that measures credits already consumed.

3 questions test this
A 100 percent Availability value does not establish that every SQL client can connect

Azure SQL Database Availability describes one-minute connection outcomes, not success for every client. For a supported database, a minute reports 100 percent if at least one connection succeeds, all attempts fail from user errors, or there are no attempts; it reports 0 percent when all attempts fail from system errors. Use Failed Connections : User Errors for user-correctable problems such as passwords or firewall rules, and Failed Connections : System Errors for internal service failures, splitting by Error to direct the investigation and choosing the corresponding signal for alerts. The Availability metric is unsupported for serverless compute and displays 100 percent, so that value cannot establish serverless connection health.

Trap Using a 100 percent Availability chart to close a firewall-related client incident, which the metric can classify as available despite failed connections.

3 questions test this
An actionable alert pairs the right metric with a meaningful evaluation window

Configure an Azure Monitor metric alert with the SQL resource scope, signal, aggregation, threshold, evaluation window, and action group appropriate to the condition. A maximum can expose brief saturation that an average conceals, while a total is useful for counting events such as failures over a period. Route notifications through the alert's action group to reach the responsible operator or automation. Metric alerts are stateful by default: a fired alert remains active until resolved rather than sending a fresh firing notification on every evaluation of the same continuing condition.

Trap Enabling automatic tuning as the notification mechanism for connection failures, because tuning recommendations do not define the required connection-failure alert and recipient.

3 questions test this
Dynamic thresholds need historical data before they can detect deviations

Dynamic-threshold metric alerts learn normal patterns and cannot trigger before collecting three days and at least 30 samples of data. Use a suitable static threshold when a known hard boundary must be monitored before that history exists.

Trap Using a newly enabled dynamic-threshold rule for immediate protection of a new resource before sufficient historical samples exist.

4 questions test this
Watcher analysis must distinguish observation time, collection time, and replica identity

Use sample_time_utc for when a watcher observation occurred; collection_time_utc, where present, records when the watcher retrieved it and can be shared by several older samples. Filter or group by the target and replica identity so primary and secondary measurements are not mixed. For cumulative counters such as waits, compare successive samples from the same series and exclude resets before interpreting interval activity.

Trap Summing lifetime cumulative wait counters across samples as interval wait activity, which repeatedly counts earlier waits.

4 questions test this
Pool-level watcher data does not contain every database-level detail

An existing elastic-pool watcher target connects through an anchor database and provides pool-wide metrics plus selected per-database resource and request metrics. Query runtime statistics, database properties, and table or index metadata require individual database targets, while some pool resource and wait information cannot be attributed to a single database. Check which targets were already monitored before concluding that missing query detail or shared-resource pressure is absent.

Trap Diagnosing shared pool contention from only an individual database's query statistics, which does not supply the complete pool-level resource picture.

4 questions test this
Existing watchers continue temporarily but their alerts and stores need a transition plan

Database watcher is a preview retiring on April 30, 2027; since November 9, 2026, new watchers cannot be created and existing watchers cannot be edited. Existing watchers continue collecting until retirement, when they stop and cannot be restarted. Their Azure Monitor log search alerts query the retained data store and are not automatically deleted, and retirement does not deprovision that store, so recreate and test needed monitoring before retirement and retire obsolete rules and retained resources deliberately.

5 questions test this
An existing database watcher centralizes detailed Azure SQL telemetry

Database watcher collects performance, configuration, and health data from SQL system views into a central Azure Data Explorer or Fabric Real-Time Analytics data store. For an already monitored Azure SQL estate, use its dashboards to compare databases, elastic pools, and managed instances, then drill into the affected resource. Query the retained store with KQL or T-SQL when a custom cross-resource investigation needs more detail than the dashboard provides.

Trap Using Query Performance Insight as a complete managed-instance estate monitor, although that portal feature targets query analysis for Azure SQL Database rather than the watcher's supported estate scope.

4 questions test this
Watcher sampling intervals and collection gaps affect incident interpretation

Check ingestion statistics and collection continuity before interpreting an empty watcher chart as proof that the target was idle. Database watcher runs periodic T-SQL queries at dataset-specific frequencies, such as ten seconds for rapidly changing counters and five minutes for relatively static configuration. Application workloads take priority, so contention, short monitoring lock timeouts, or termination of monitoring sessions can create gaps; collected data is typically available for analysis in less than ten seconds.

Trap Treating every missing monitoring sample as a database outage, although monitoring queries can lose priority while the application continues working.

4 questions test this
A collecting watcher does not prove its log-search alert can evaluate or notify

When an existing watcher keeps collecting but expected alerts are absent, inspect the scheduled log-search rule's KQL query, evaluation window, target filtering, and managed identity because evaluation queries the retained data store rather than the SQL target directly. The alert-rule identity needs its own data-store access, separate from the collecting watcher; for an Azure Data Explorer store, verify Reader on the cluster and Viewer on the database. Check the per-target dimensions and resulting alert instances to identify the affected database, elastic pool, or managed instance rather than treating the estate as one undifferentiated condition. If an alert instance fired but delivery failed, follow the action-group delivery requirement in dp-300-f-30011 rather than changing the evaluation query.

Trap Reauthorizing only the collecting watcher's SQL identity to fix alert evaluation, which leaves the alert rule's separate data-store identity unchanged.

4 questions test this
Extended Events session scope depends on the SQL platform

Azure SQL Database event sessions use ON DATABASE and collect events only within that database's scope. SQL Server uses ON SERVER, while Azure SQL Managed Instance supports both scopes and generally favors server-scoped sessions. Select the scope that contains the incident rather than expecting one Azure SQL Database session to observe neighboring databases on its logical server.

Trap Reusing an instance-wide ON SERVER session definition on an Azure SQL Database logical server, which is not a customer-managed SQL Server instance.

5 questions test this
Events, actions, and predicates serve different capture purposes

Choose an Extended Events event for the execution point to observe, such as sql_batch_completed for completed batches. Add an action such as sqlserver.session_id to collect contextual information, and use a predicate to retain only events meeting the investigation's conditions. This combination captures useful evidence while reducing unnecessary event volume and monitoring overhead. Discover supported events, actions, and targets by joining sys.dm_xe_objects.package_guid to sys.dm_xe_packages.guid and filtering object_type to event, action, or target, rather than assuming every platform exposes the same objects. For correlating related events across different server connections, use TRACK_CAUSALITY = ON rather than relying on the session_id action or timestamps alone.

Trap Adding a target to supply session_id context, although a target consumes captured events rather than adding an action's contextual field.

4 questions test this
An event session definition and a running session are different states

Extended Events catalog views describe session definitions, whereas Extended Events DMVs describe started sessions and their runtime targets. Start a defined session to begin collection, and use STARTUP_STATE = ON for a session that should resume after an engine restart. Use STARTUP_STATE = OFF for a short-lived investigation and inspect the appropriate server- or database-scoped runtime views to confirm it is actually running. Before defining redundant collection, inspect the automatically started system_health session on SQL Server or Managed Instance for evidence such as deadlocks and serious errors; Azure SQL Database has no built-in system_health session. A running session can still buffer events before target delivery: MAX_DISPATCH_LATENCY defaults to 30 seconds, a shorter interval starts flushing sooner, and INFINITE flushes only when buffers fill or the session closes, so delayed target output alone does not establish that collection stopped.

Trap Using MAX_DISPATCH_LATENCY = INFINITE for prompt target output from a low-volume session, which can leave events buffered until buffers fill or the session closes.

3 questions test this
An event_file target preserves evidence that an in-memory ring buffer cannot

Use an event_file target when captured Extended Events data must survive the session for later analysis; use ring_buffer for recent in-memory events during an ad hoc investigation. Azure SQL Database and Managed Instance event_file targets write to Azure Storage blobs, whereas SQL Server can use local files or blobs, so a VM's local storage path is not automatically suitable for a managed Azure SQL service. Read event files with the SSMS event viewer or sys.fn_xe_file_target_read_file, using a blob URL for Azure SQL event files. For a ring buffer, cast target_data to XML from sys.dm_xe_session_targets for a server-scoped session or sys.dm_xe_database_session_targets for a database-scoped session, joining the corresponding runtime sessions view to select the intended session. Even while the session runs, ring-buffer XML output can be truncated; set the ring_buffer target MAX_MEMORY to 1,024 KB or less to reduce this output-truncation risk, which is distinct from event loss in session buffers.

Trap Choosing an in-memory ring buffer as the durable incident archive, which does not provide event-file persistence.

4 questions test this
An Azure event-file container must meet the engine's access and storage requirements

For an Azure SQL event_file target, grant the server or managed-instance identity Storage Blob Data Contributor on the container and create the corresponding credential, or store an appropriate container SAS in a credential. A SAS requires Read, Write, Delete, and List permissions and a validity period covering the session; use a database-scoped credential in Azure SQL Database and a server-scoped credential in Managed Instance, because portal access held by the operator is not the engine's storage authorization. Use Hot-tier blobs in a same-region storage account without hierarchical namespace, aligning redundancy with the SQL resource: LRS, GRS, or RA-GRS for locally redundant resources, and ZRS, GZRS, or RA-GZRS for zone-redundant resources. Restrict reader access through storage RBAC, including inherited access, because captured events can contain sensitive query data.

Trap Granting the interactive administrator Reader on the storage resource to authorize event writes, which neither gives the engine a credential nor grants blob data-write access.

4 questions test this
New Database Engine traces should use Extended Events rather than SQL Trace

Choose Extended Events for new Database Engine tracing because SQL Trace and the SQL Trace-based SQL Server Profiler are deprecated. SQL Server Profiler does not support Azure SQL Database, so granting ALTER TRACE or sysadmin does not repair that unsupported connection. SSMS XEvent Profiler is a different tool built on Extended Events, providing a live event viewer on SQL Server and Managed Instance rather than a SQL Trace capture.

Trap Using an ALTER TRACE grant to resolve SQL Server Profiler's Azure SQL Database connection error, which reports a permissions-style message for an unsupported target.

4 questions test this
Extended Events management and evidence viewing have separate SQL permission boundaries

Delegate session administration with ALTER ANY EVENT SESSION for server-scoped SQL Server or Managed Instance sessions, or ALTER ANY DATABASE EVENT SESSION for Azure SQL Database sessions, separately from the engine's blob-write authorization. For creation alone, SQL Server 2022 and later and Managed Instance support CREATE ANY EVENT SESSION, while Azure SQL Database supports CREATE ANY DATABASE EVENT SESSION. Reading server-scoped runtime target data through sys.dm_xe_session_targets requires VIEW SERVER STATE, with SQL Server 2022 and later requiring VIEW SERVER PERFORMANCE STATE. Reading sys.dm_xe_database_session_targets in Azure SQL Database requires VIEW DATABASE STATE; these viewing permissions do not themselves authorize changing a session definition.

Trap Using ALTER ANY EVENT SESSION for a monitoring reader who only needs target data, which delegates session changes instead of the narrower viewing capability.

4 questions test this
Event retention policy trades capture completeness against workload stalls

Where supported, NO_EVENT_LOSS makes event-firing tasks wait for buffer space and can stall user queries, trading workload responsiveness for capture completeness. ALLOW_SINGLE_EVENT_LOSS can drop an event when session buffers are full, whereas ALLOW_MULTIPLE_EVENT_LOSS can discard full buffers containing multiple events. Session MAX_MEMORY, which defaults to 4 MB, controls event buffering separately from the ring_buffer target's MAX_MEMORY. In Azure SQL Database, session-memory budgets are 128 MB per database and 512 MB across an elastic pool, with at most 100 started sessions per database and 100 across a pool; a memory-limit start failure can occur below that count, so run fewer concurrent sessions or reduce session MAX_MEMORY. For event_file targets in Azure SQL Database and in Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, specifying NO_EVENT_LOSS produces warning 25665 and uses ALLOW_SINGLE_EVENT_LOSS instead, so it does not promise lossless blob capture.

Trap Using a larger ring_buffer target MAX_MEMORY to cure full session event buffers, which changes target storage rather than the session buffering allocation.

4 questions test this

Monitor and optimize query performance

Read full chapter
  • Query Store needs a writable operating state to collect new history
  • Capture mode filters incoming queries while cleanup manages retained history
  • Query Store aggregation windows and disk-flush intervals have different purposes
  • Query Store links historical slowdowns to changes in query plans
  • Query Store cost comparisons require a consistent execution, interval, and replica scope
  • Query Store wait categories connect resource contention to affected queries
  • Query Performance Insight exposes captured query history rather than complete workload accounting
  • Query Store's statement coverage cannot establish a complete DDL execution history
  • Plan forcing can stabilize a regressed query without changing application SQL
  • Query Store hints apply a query-specific policy without editing its source text
  • Resolve a blocking incident by following the chain to its head blocker
  • Unfinished client work can retain blocking even when useful processing has stopped
  • Row versioning reduces reader-writer blocking but does not eliminate all conflicts
  • A deadlock graph identifies the resolved victim and the cycle that needs prevention
  • Optimized locking reduces long-lived data locks while retaining transaction protection
  • Cached query statistics describe completed work for the lifetime of a cached plan
  • Wait diagnosis requires the right scope and a comparable observation interval
  • The query memory-grant DMV separates waiting requests from grant holders
  • Per-file IO statistics help distinguish data-read stalls from log-write stalls
  • Tempdb remedies depend on distinguishing latch contention, storage waits, and shared-space pressure
  • ADR's fast rollback does not remove transaction-driven version retention
  • Performance DMV access depends on the specific view and deployment rather than one universal grant
  • Index keys provide access order while included columns can eliminate lookups
  • A filtered index is useful when queries repeatedly target the same well-defined subset
  • Missing-index suggestions are candidates rather than a complete index design
  • A clustered key becomes the row locator carried by nonclustered indexes
  • Indexed-view access requires an eligible match rather than merely naming a view
  • Columnstore indexes favor broad analytical scans rather than selective transactional lookups
  • Searchable predicates expose indexed values instead of obscuring them with expressions
  • Parameterization promotes reuse while targeted recompilation trades reuse for specialization
  • A cheaper query construct is appropriate only when it preserves the required results
  • Query-level grant hints bound workspace memory without changing the whole workload's policy
  • Execution plans expose different evidence before, during, and after execution
  • A seek is not automatically cheaper than a scan for the required row set
  • Join algorithms and spills must be interpreted against input size and ordering
  • Last-known actual-plan retrieval requires prior collection and a surviving cached plan
  • Intelligent Insights detects degradation against a workload-specific baseline
  • The SQLInsights resource log carries the findings needed to investigate an insight

Unlock with Premium — includes all practice exams and the complete study guide.

Configure database solutions for optimal performance

Read full chapter
  • Reorganization favors online incremental work while rebuilding recreates the index
  • Page density and fragmentation must be evaluated together before maintenance
  • A resumable rebuild can preserve progress across maintenance windows
  • Columnstore maintenance addresses deleted rows and undersized rowgroups
  • Statistics updates can target the needed object and sampling precision
  • Asynchronous statistics updates let compilation proceed with existing statistics
  • Incremental statistics avoid rescanning unchanged partitions
  • A statistics histogram describes the first column rather than every column in the object
  • Automatic statistics refresh is driven by data changes and query use
  • Frequent physical checks complement rather than replace full integrity checks
  • A known-good restore is preferred to CHECKDB repair
  • Integrity-check execution must account for resource use and platform support
  • Automatic tuning options are not identical across Azure SQL platforms
  • Automatic tuning validates its changes and reverses actions that do not help
  • Automatic index recommendations have workload and safety boundaries
  • SQL Server memory bounds must leave room for the host and other consumers
  • MAXDOP limits parallel task width rather than the number of concurrent queries
  • The cost threshold controls consideration of parallel plans, not elapsed-time limits
  • Optimize for ad hoc workloads reduces the cache footprint of single-use batches
  • Database-scoped MAXDOP localizes a parallelism policy to one database
  • Database-scoped optimizer switches can isolate behavior without lowering compatibility
  • Plan-cache invalidation should evict only the plans that require recompilation
  • Delayed durability is appropriate only when loss of acknowledged commits is tolerable
  • Automatic database resource reclamation needs an operational justification
  • Resource Governor policies follow the session's workload classification
  • A resource pool's CPU maximum under contention differs from its hard cap
  • Workload-group settings constrain requests without changing every database or query
  • Workload-group contention policies distinguish admission limits from relative priority
  • Resource scaling should address the measured constraint rather than any slow query
  • An online Azure SQL scale operation can still interrupt connections at switchover
  • Table-variable deferred compilation uses the initial execution's actual row count
  • Interleaved execution improves downstream planning for eligible multi-statement TVFs
  • Scalar UDF inlining exposes eligible function work to relational optimization
  • Cardinality and parallelism feedback learn different improvements for recurring queries
  • Optimized plan forcing reduces compilation work for recurring forced queries
  • An Adaptive Join selects between hash and nested loops using observed input rows
  • Batch mode on rowstore can accelerate eligible analytical work without a columnstore index
  • Memory-grant feedback adjusts future grants rather than repairing a running execution
  • Parameter Sensitive Plan optimization retains variants for skewed parameter values
  • Optional parameter optimization separates filtered and unfiltered parameter states
  • Approximate aggregates favor analytical responsiveness when exact results are unnecessary

Unlock with Premium — includes all practice exams and the complete study guide.

Configure and manage automation of tasks

Create and manage SQL Server Agent jobs

Read full chapter

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Agent schedules separate the eligible days from the interval within each day

SQL Server Agent uses @freq_type = 4 with @freq_interval to specify a daily recurrence, while @freq_subday_type and @freq_subday_interval determine repeated starts within each eligible day. Subday units are 2 for seconds, 4 for minutes, and 8 for hours, and the repeat interval must be at least 10 seconds. Set @active_start_time and @active_end_time in HHmmss format to constrain scheduled starts to the maintenance window; @active_start_date and @active_end_date use yyyyMMdd. Create the schedule with msdb.dbo.sp_add_schedule and attach it to the job with sp_attach_schedule.

Trap Using @freq_interval to express minutes between executions, although that parameter controls days when @freq_type is daily.

5 questions test this
Weekly and relative-monthly schedules encode different calendar rules

SQL Server Agent weekly schedules use @freq_type = 8 and combine weekday bit values in @freq_interval, with @freq_recurrence_factor specifying the number of weeks between runs. A fixed day of the month uses @freq_type = 16, whereas a rule such as the last weekday uses @freq_type = 32, @freq_interval = 9 for weekday, and @freq_relative_interval = 16 for last. Review the effective start date after attaching a weekly or monthly schedule because it determines the first eligible execution.

Trap Using a fixed monthly date for last-weekday maintenance, although the matching calendar date changes between months.

5 questions test this
A shared Agent schedule changes every job attached to it

SQL Server Agent schedules are reusable objects: several jobs can share one schedule, and a job can have several schedules. Updating a shared schedule with sp_update_schedule affects all attached jobs, so use a separate schedule when one job needs an independent maintenance window. Disabling a schedule suppresses that schedule for every attached job but still permits a job to start manually or through an alert; detach the schedule when only one association should be removed.

Trap Disabling a shared schedule to pause one maintenance job, which also suppresses scheduled starts for its other attached jobs.

5 questions test this
Agent activation conditions must fit the platform and the job's running state

SQL Server Agent supports one-time, recurring, and service-start schedules; @freq_type = 64 starts a job when Agent starts. Azure SQL Managed Instance keeps Agent running and activates service-start jobs after failover, but does not support the idle-CPU schedule available on SQL Server. Only one instance of a given Agent job can run at a time, so a manual start cannot create a second execution while its scheduled execution is still running.

Trap Using an idle-CPU trigger for Managed Instance maintenance, although that scheduling condition is unsupported on the managed service.

5 questions test this
Agent event alerts match logged SQL errors by number or severity

On SQL Server running on Windows, configure a SQL Server event alert for a particular error number or severity, such as severity 15 syntax errors, but the event must reach the Windows application log for Agent to detect it. Severities 19 through 25 are logged automatically; lower-severity events require sp_altermessage, RAISERROR WITH LOG, or xp_logevent to force logging. Database and message-text filters can narrow the match, but xp_logevent events occur in master, so the alert's @database_name must be 'master' or NULL for those events to trigger it. A matching alert can notify an operator or run a response job, making this the event-driven choice rather than a periodic maintenance schedule.

Trap Choosing a performance-condition alert for severity-15 errors, although a counter threshold does not match SQL error severity.

4 questions test this
Agent performance alerts require a counter and a threshold comparison

When a Windows SQL Server task should respond to a measured resource condition, use a SQL Server performance-condition alert and select its object, counter, and applicable instance. Specify whether the value rises above, falls below, or equals the threshold, and interpret the threshold in that counter's units. Performance counters are sampled periodically, so the alert can follow the threshold crossing after a short delay rather than at the exact instant it occurs.

Trap Choosing an error-number event alert for low tempdb free space, although the requirement concerns a sampled counter value rather than a specific logged error.

4 questions test this
WMI alerts subscribe to events rather than sampling performance counters

A SQL Server Agent WMI alert on Windows identifies an event source with a WMI namespace and uses a WQL query to select the event of interest. Choose it when the required trigger is exposed as a WMI event, rather than as a SQL error number or a numeric performance condition. Azure SQL Managed Instance does not support Agent alerts, so this WMI subscription is a SQL Server on Windows option.

Trap Using a performance-counter alert for a WQL-selected event, which monitors numeric samples instead of subscribing to the requested event source.

4 questions test this
Agent email needs a mail account, a profile, and an operator

SQL Server Agent email uses a Database Mail account for SMTP delivery, a profile containing the account, and an operator identifying the recipient. On SQL Server, enable the mail profile under Agent Properties, Alert System, select Database Mail and the appropriate profile, and restart Agent after configuring it. An operator is an alias for a person or group, while the profile defines how mail is sent; neither object by itself selects which job outcome sends a message.

Trap Creating an Agent proxy to enable failure email, although proxies supply job-step credentials rather than SMTP delivery configuration.

5 questions test this
Job notifications can report failure, success, or every completion without an event alert

When the requirement is to report an Agent job's final result, configure its Notifications page, choose the email operator, and select When the job fails, When the job succeeds, or When the job completes. The completion option includes both success and failure, whereas failure-only notification suppresses routine success messages. A separate SQL Server event alert is appropriate for a matching engine event, not a prerequisite for a job-result email.

Trap Creating a severity alert to report every failed job, although job failure is a job outcome and need not correspond to one selected engine severity.

5 questions test this
Managed Instance Agent email requires its designated Database Mail profile

Azure SQL Managed Instance Agent looks for a Database Mail profile named AzureManagedInstance_dbmail_profile and can use only that profile for its job notifications. Enable Database Mail if necessary, associate an SMTP account with that profile, and allow the required outbound SMTP connection through the instance's network security group. Managed Instance supports email job notifications but not pager notifications or NetSend; see the WMI alert-subscription rule for the separate alert-platform boundary. It does not require the SQL Server procedure of selecting a profile in Agent Properties and restarting Agent.

Trap Reusing a custom-named SQL Server mail profile unchanged on Managed Instance, whose Agent notification integration looks for the designated profile name.

5 questions test this
Agent database roles separate ownership, visibility, and operational control

SQL Server Agent's msdb roles form an increasing permission hierarchy: SQLAgentUserRole manages owned local jobs, SQLAgentReaderRole also sees other jobs and their history, and SQLAgentOperatorRole can start and stop all local jobs. Operator members can enable or disable other owners' local jobs and schedules through sp_update_job or sp_update_schedule using only the identifier and @enabled parameters. These roles do not let a non-sysadmin edit another owner's job definition or change its ownership, so monitoring access is not equivalent to unrestricted job administration. Alert creation is a separate permission boundary: by default, only sysadmin members can execute msdb.dbo.sp_add_alert, so membership in these job-management roles alone does not authorize creating alerts.

Trap Assigning SQLAgentReaderRole to an operator who must restart other users' jobs, although that role adds visibility rather than cross-owner execution control.

5 questions test this
T-SQL job steps use the job owner's context rather than an Agent proxy

A SQL Server Agent T-SQL step specifies the database in which its statements execute and does not use an Agent proxy. A non-sysadmin-owned job runs its T-SQL under the owner's context; a sysadmin-owned job uses the Agent service account context, and sysadmin can specify a database user with @database_user_name in sp_add_jobstep. When a statement works interactively but fails as a job, check the configured database and the effective principal's permissions rather than assuming the interactive session's identity carries over.

Trap Assigning a CmdExec proxy to repair a T-SQL permission failure, although the T-SQL subsystem does not execute through Agent proxies.

5 questions test this
Non-T-SQL job steps need credentials that can access their operating-system resources

On SQL Server, an Agent proxy supplies a credential-backed execution context for a permitted subsystem such as CmdExec or PowerShell. Grant the step identity the required share, file, and executable access, and use an explicit executable path when it is not available through that identity's PATH. A successful interactive test under an administrator's account does not establish that the scheduled identity can reach the same resources; Azure SQL Managed Instance does not support Agent proxies.

Trap Granting a database role to fix a CmdExec file-share access failure, although the failing authorization is for the Windows execution identity on the share.

5 questions test this
Agent activity and history answer different troubleshooting questions

Use SSMS Job Activity Monitor or msdb.dbo.sp_help_jobactivity to inspect the current execution state of Agent jobs. Use View History or msdb.dbo.sp_help_jobhistory to investigate completed attempts and step messages. Agent records activity in msdb.dbo.sysjobactivity for each service session, which can also help identify jobs that were running when the service terminated; a resource's Azure Activity log does not provide these database-engine job-step details.

Trap Reviewing the Managed Instance resource's Azure Activity log for a failed T-SQL job step, although that log records resource-management operations rather than Agent step history.

5 questions test this
Agent step routing determines which work runs next and how the job finishes

For each SQL Server Agent step, configure separate On success and On failure actions to continue, jump to a selected step, or quit with a success or failure result. A job can therefore report success after an earlier step fails if its configured failure path eventually quits successfully. For a CmdExec step, also verify the configured successful process exit code, because Agent uses that code to interpret the external program's result before applying the routing policy.

Trap Changing the failure-email operator when a failed maintenance step is followed by a successful job result, although the job's control flow determines the outcome being notified.

5 questions test this
Agent retries are a step policy rather than a recurring-job schedule

Configure an Agent step's Retry attempts and Retry interval in minutes when a transient failure should be retried before the step is treated as failed. The Advanced page supports 0 through 9999 retry attempts and, when retries are enabled, an interval of 1 through 9999 minutes. After retries are exhausted, the step follows its configured failure action, so coordinate the retry budget with the route that should handle an unrecovered failure.

Trap Adding a frequent recurring schedule to recover a transient step failure, which restarts the whole job on future schedule events rather than applying a bounded step retry policy.

5 questions test this
Agent step output needs deliberate capture when job history is insufficient

SQL Server Agent can capture supported step output to an operating-system file or msdb.dbo.sysjobstepslogs, with overwrite or append behavior selected explicitly. File output requires a sysadmin-executed step, whereas members of the Agent msdb roles can use table output; output included in history can be truncated. T-SQL steps also change TEXTSIZE, so explicitly set TEXTSIZE in the step when returned large character or binary column values are unexpectedly shortened even though the query works in SSMS.

Trap Increasing job-history retention to recover truncated query values, although retention controls how long history remains rather than the step's TEXTSIZE setting or output destination.

5 questions test this
Database Mail distinguishes queue processing from SMTP acceptance and final delivery

A sent item means the SMTP server accepted the message, not that it reached the recipient's inbox, so investigate downstream mail delivery when sysmail_sentitems shows success. For messages not accepted by SMTP, inspect Database Mail's status and queue with msdb.dbo.sysmail_help_status_sp and sysmail_help_queue_sp, then review sysmail_event_log for sending errors. Database Mail requires Database Mail XPs to be enabled and Service Broker to be enabled in msdb.

Trap Rerunning a successful maintenance job to diagnose an email already accepted by SMTP, which repeats the database work without investigating downstream mail delivery.

5 questions test this

Automate deployment of database resources

Read full chapter
  • Bicep expresses the same Azure resource model as an ARM JSON template
  • Bicep references establish dependencies while unrelated resources deploy in parallel
  • Template parameters make environments reusable but secrets require secure parameter types
  • Bicep modules package reusable deployments and expose explicit outputs
  • Bicep loops create named resource collections and can limit deployment concurrency
  • Incremental deployment preserves unlisted resources but reapplies each declared resource's properties
  • What-if previews resource changes without applying them
  • Resource deployment authorization is separate from permission to run SQL statements
  • PowerShell provisioning selects the subscription before creating the SQL resource hierarchy
  • Managed Instance provisioning and updates use instance-specific Az.Sql cmdlets
  • PowerShell database settings and shared pool capacity use different cmdlets
  • Azure CLI uses separate commands for the resource group, logical server, and database
  • Azure CLI changes an existing database's service configuration with db update
  • CLI automation should distinguish submission from the resulting resource state
  • CLI queries select result fields before the output format renders them
  • Managed Instance CLI updates use separate options for tier, hardware, compute, and storage
  • Deployment errors must be diagnosed at the stage where they occur
  • The failed resource operation is more diagnostic than the outer DeploymentFailed message
  • Policy and management locks constrain deployments independently of the caller's resource permissions
  • Resource availability failures must be separated from subscription registration and quota failures
  • A failed deployment can leave successfully created resources in place
  • Deployment retries should respect throttling and operations already in progress

Unlock with Premium — includes all practice exams and the complete study guide.

Create and manage database tasks in Azure

Read full chapter
  • Elastic jobs require a supported job database before the agent is created
  • Elastic job definitions use the jobs schema and inline T-SQL steps
  • Elastic job schedules use UTC and can immediately catch up a missed interval
  • Target groups select databases independently of their resource topology
  • Elastic jobs recommend one user-assigned identity with permissions in every target
  • SQL-authenticated elastic jobs separate database discovery from script execution credentials
  • Elastic jobs use approved service-managed private endpoints to reach private SQL targets
  • An elastic job's network security perimeter checks outbound target access during execution
  • Elastic job retry policies require scripts that tolerate repeated execution
  • Elastic job output collection writes the first result set to a configured SQL table
  • Elastic job scaling distinguishes agent concurrency from pool pressure and metadata performance
  • A runbook must be created in an Automation account and published for normal execution
  • Automation schedules require a runbook link and support a minimum native interval of one hour
  • Runbook Azure authentication should use managed identity and an explicit job context
  • A managed-identity runbook needs a SQL access token and a mapped database user to execute T-SQL
  • Automation credential assets supply stored credentials without an interactive runbook prompt
  • An Automation webhook's token-bearing URL authorizes invocation of its published runbook
  • A Hybrid Runbook Worker supplies private reachability and execution beyond sandbox limits
  • Runbook dependencies must exist in the actual runtime that executes the job
  • Restartable runbooks need durable progress and explicit protection against conflicting jobs
  • Elastic job failures can trigger Azure Monitor notifications or automated responses
  • Elastic job troubleshooting drills from the parent execution into target-level states
  • Automation job-status alerts and content-level error alerts require different diagnostic records
  • A Completed runbook can still contain errors that did not terminate execution

Unlock with Premium — includes all practice exams and the complete study guide.

Plan and configure a high availability and disaster recovery (HA/DR) environment

Plan an HA/DR strategy for database solutions

Read full chapter

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Recovery time and recovery point objectives constrain different parts of recovery

Recovery Time Objective (RTO) expresses how long the application can be unavailable, whereas Recovery Point Objective (RPO) expresses the tolerable time window of lost data. Select a recovery method against both requirements: frequent recoverable copies can reduce data loss without making a lengthy restore fast enough. Measure recovery through restored application service, not merely through promotion of a database replica.

Trap Using a shorter backup interval to satisfy a downtime target when restoring and reconnecting the application still take too long.

5 questions test this
Synchronous commit trades commit latency for protection of acknowledged transactions

Synchronous-commit availability replicas make the primary wait for the participating secondary to harden transaction log records before acknowledging a commit. A synchronized secondary can therefore preserve committed work, but network and log-write latency become part of transaction latency. Asynchronous commit avoids that remote acknowledgement wait and suits distant DR replicas, at the cost of possible loss of transactions not yet hardened there.

Trap Choosing synchronous commit across a high-latency hybrid link solely to avoid data loss without accounting for its effect on transaction response time.

5 questions test this
Availability groups protect selected databases while failover cluster instances protect an instance

Use an Enterprise-edition Always On availability group when selected application databases must move together without moving unrelated databases on the instance. Use an Always On failover cluster instance when the SQL Server instance, including its system databases and SQL Server Agent jobs, must move as one service. An FCI uses shared storage, so instance failover alone does not create an independent remote database copy for disaster recovery. SQL Server Standard's basic availability group instead protects one database with one primary and one secondary, without secondary reads or backup offload. On SQL Server 2022 and later, a contained availability group can also replicate AG-scoped logins and Agent jobs through its contained master and msdb databases, rather than requiring those objects to be maintained separately on every conventional AG replica.

Trap Selecting an FCI for an application-only database failover requirement because its instance-wide failover also moves unrelated databases.

5 questions test this
Readable availability-group secondaries cannot have an independent index design

Create reporting indexes on the primary database when reports use a readable Always On availability-group secondary, because the secondary is a read-only copy and cannot maintain its own independent indexes. The added index therefore becomes part of the primary's physical design rather than a secondary-only optimization. Choose transactional replication for a reporting copy that needs independent indexing or data shaping instead of requiring those changes on the transactional primary.

Trap Using a readable AG secondary as an independently indexed reporting database because its read-only replicated copy preserves the primary's database structure.

A distributed availability group connects two availability groups without extending one cluster

When an on-premises site and Azure must retain separate cluster infrastructures, use a distributed availability group to link their underlying availability groups. The distributed group is managed in SQL Server rather than as an additional WSFC or Pacemaker cluster resource; its underlying groups need listeners, but the distributed group has no listener of its own. The secondary group's primary acts as a forwarder, receiving log records and distributing them to its local secondaries while the global primary remains the writable copy.

Trap Stretching a single WSFC across sites when the design requires independent clusters and separate administrative boundaries.

5 questions test this
A hybrid recovery site needs surviving connectivity and identity services

A conventional domain-based Windows availability group spanning on-premises and Azure needs its cluster nodes to communicate across the networks, commonly through a VPN connection. Plan DNS resolution and a replica domain controller at the recovery site so that loss of the production site does not also remove authentication dependencies. Hybrid log shipping additionally depends on access to the Windows backup share across that connection.

Trap Placing the only domain controller and DNS service at the production site while treating Azure database copies as a complete recovery site.

5 questions test this

Managed Instance link uses distributed availability group technology for near-real-time replication between SQL Server and Azure SQL Managed Instance. Supported SQL Server 2016, 2017, and 2019 builds support one-way replication and cutover to Managed Instance, but that cutover breaks the link and does not support failback. Bidirectional DR and failback require SQL Server 2022 or 2025 with the matching Managed Instance SQL Server update policy; using a higher destination policy for one-way cutover does not establish reverse compatibility.

Trap Choosing a SQL Server 2019 link as a reversible DR arrangement because it can replicate successfully to Managed Instance before cutover.

4 questions test this
Azure Site Recovery provides VM-level disaster recovery for SQL Server workloads

Use Azure Site Recovery when SQL Server disaster recovery must replicate and recover a supported VM rather than maintain only database copies in an availability group. Application-consistent recovery points capture application state as well as disk data; this is VM recovery rather than a SQL backup-and-log restore chain. Recovery plans can sequence recovery of the SQL VM and dependent application VMs. Test failover rehearses recovery without interrupting ongoing production replication.

Trap Assuming a Site Recovery recovery plan supplies a synchronized SQL secondary because the plan orchestrates VM recovery rather than synchronous transaction-log hardening.

5 questions test this
Transactional publication filters must preserve required columns

Transactional publications must retain primary-key columns and every column referenced by a row filter, even when column filtering excludes other data. A static WHERE row filter selects rows, whereas a column filter selects columns, and all subscribers to a filtered publication receive the same row subset. Use separate transactional publications for different fixed row subsets rather than assuming a static filter changes for each subscriber. These filters limit published articles rather than providing a whole-database HA replica. Avoid row filters when every replication node can support a reasonably small full data set, because evaluating the filter clause for each log row written for a published table can add significant overhead.

Trap Using a column filter to remove a sensitive column that is also referenced by the article's row filter because row-filter columns must remain in the publication.

Managed Instance transactional replication requires supported distribution placement and share access

For transactional replication with a Managed Instance Publisher and a remote Managed Instance Distributor, place both in the same virtual network and region. Use SQL authentication between replication participants and an Azure Storage file share for the replication working directory, with outbound TCP 445 allowed from the instance subnet. Delivery from a Managed Instance Publisher or Distributor to a non-Managed-Instance Subscriber also requires outbound TCP 1433; where the existing rule excludes that destination, the allow_linkedserver_outbound NSG rule's Destination Service tag may need changing from virtualnetwork to internet. An on-premises Publisher and Distributor can instead push changes to an Azure subscriber, but splitting the Publisher and Distributor between on-premises and Azure is not a supported arrangement. A Managed Instance Distributor must use push delivery to a subscriber that is not another managed instance; a Managed Instance subscriber can instead use either push or pull delivery.

Trap Splitting an on-premises Publisher from an Azure Distributor to simplify hybrid recovery because supported placement keeps the Publisher and Distributor together in the cloud or on-premises.

Passive DR licensing benefits do not cover production reporting workloads

Reserve passive DR licensing benefits for replicas without production read workloads; choose a normally licensed readable secondary when reports must run before failover. Eligible SQL Server VM replicas can use the SQL Server License HA/DR designation: hybrid deployments require Software Assurance, while pay-as-you-go licensing qualifies when all replicas are hosted in Azure. In a Managed Instance failover group, setting SecondaryType to Standby removes qualifying secondary SQL licensing costs but not compute or storage charges. That standby still permits monitoring, maintenance such as DBCC CHECKDB, backups, and DR drills rather than production application reads.

Trap Keeping a Managed Instance failover-group secondary designated Standby while using it for production reports because the failover licensing benefit restricts the secondary to DR and permitted operational activities.

Availability zones protect against a larger failure boundary than availability sets

Spread SQL Server HA virtual machines across availability zones when the design must survive the loss of an entire datacenter within a region. An availability set isolates equipment failures within a datacenter through fault domains, whose VMs share a power source and network switch, and planned maintenance through update domains, whose VMs can restart together; Azure restarts only one update domain at a time, and neither domain type provides regional DR. A VM is configured for an availability set or an availability zone, not both, and protection against a whole-region outage requires another region. A zonal VM is still a single-zone resource: the operator must deploy SQL VMs across zones and configure replication and failover, unlike a service-managed zone-redundant deployment. Logical zone numbers can map to different physical zones in different subscriptions, so verify the mappings rather than inferring cross-subscription isolation from zone labels alone.

Trap Selecting an availability set for a datacenter-loss requirement because its fault domains do not provide the physical isolation of availability zones.

6 questions test this
Infrastructure redundancy alone does not guarantee SQL Server continuity

Azure VM recovery mechanisms protect the virtual machine infrastructure, but SQL Server can fail while its host VM remains online. Add a database-aware HA solution such as an availability group or FCI when application continuity depends on SQL Server failure detection and failover. Ordinary asynchronous storage geo-replication of separate SQL data and log disks preserves write order within each disk, not across disks, so recovered data files can be ahead of their transaction log and violate SQL Server write-ahead logging. Use a SQL-aware or application-consistent DR method for that multi-disk layout rather than assuming that infrastructure replication supplies a recoverable database.

Trap Relying on Azure VM service healing as SQL Server HA because it does not necessarily detect an unavailable database engine inside a running VM.

4 questions test this
A same-region geo-secondary is a read-scale option rather than regional disaster protection

An Azure SQL Database geo-secondary can reside in the same region to serve read-only traffic, but that placement neither survives loss of the entire region nor guarantees separation into another availability zone. Choose a different region for a regional DR copy and explicitly enable supported zone redundancy for zonal isolation. Geographic replication and zone placement must therefore be evaluated separately.

Trap Treating a second logical server in the same region as guaranteed zone isolation because logical server separation does not control physical zone placement.

5 questions test this
Managed SQL storage architecture determines how local recovery resumes the workload

General Purpose managed SQL uses separate compute and remote storage, allowing a replacement compute node to attach preserved database files but resume with cold caches. Business Critical, and Azure SQL Database Premium, keep local database copies on replicated engine nodes so a synchronized node can take over with less performance disruption. Azure SQL Database Hyperscale instead distributes resilience across compute replicas, page servers, the log service, and persistent storage; choose and test the architecture against recovery performance rather than assuming all tiers recover identically.

Trap Expecting General Purpose failover to preserve a warm compute cache because its durable remote storage does not preserve the failed node's memory state.

4 questions test this
Azure SQL Database zone redundancy must be enabled on a supporting service tier

Azure SQL Database supports zone-redundant availability in General Purpose, Business Critical, and Hyperscale vCore tiers and in the Premium DTU tier, subject to the region and deployment's support. Basic and Standard DTU databases do not support it. Zone redundancy protects committed data through a zone failure with zero RPO, whereas a locally redundant deployment can remain unavailable during a zone-wide outage.

Trap Keeping a Standard DTU database and changing only its backup redundancy to meet a live zone-failure continuity requirement.

4 questions test this
Managed Instance needs zone redundancy enabled to withstand a whole-zone outage

Enable zone redundancy for Azure SQL Managed Instance when local HA must survive a whole-zone outage; the default locally redundant deployment can remain unavailable during that outage. General Purpose and Business Critical support zone redundancy in eligible regions, while Next-gen General Purpose zone redundancy is in preview and additionally requires regional Elastic SAN ZRS support. Set backup storage redundancy to zone-redundant storage (ZRS) or geo-zone-redundant storage (GZRS) first, then enable the instance setting, for example with az sql mi update --zone-redundant true. This setting distributes service components across zones rather than merely increasing backup durability.

Trap Assuming a locally redundant Business Critical instance survives a whole-zone outage because its additional database-engine replicas can remain in the same datacenter.

6 questions test this
A preprovisioned geo-replica avoids the provisioning uncertainty of backup-based regional recovery

Use a continuously maintained geo-replica or failover group for critical applications that cannot tolerate waiting for backup restoration and target capacity after a regional outage. Geo-restore avoids maintaining an additional running database, but its completion depends on backup availability, database size, regional restore demand, and available capacity. Treat published typical RTO and RPO values as planning guidance, not a guarantee that any database restore will meet a short application deadline.

Trap Selecting geo-restore solely because backups are geo-redundant when the application requires a ready failover target and predictable short downtime.

5 questions test this
Database regional redundancy is only one part of application continuity

Plan recovery for the web tier, identity dependencies, storage, DNS, and client routing alongside the database because a promoted database alone cannot restore an unavailable application. Components left in the original region can also add cross-region latency after database failover. Document how those components move or reconnect, and assess the combined recovery path against the application's RTO.

Trap Using database promotion time as the application's recovery time while its web front end remains dependent on the failed region.

5 questions test this
A user-initiated local failover tests client resilience without changing the DR region

Use Invoke-AzSqlDatabaseFailover or Invoke-AzSqlElasticPoolFailover to exercise the effect of an Azure SQL Database local failover on existing sessions and reconnection behavior. This is different from promoting a geo-secondary and is useful for validating retry handling before deployment. The service permits only one failover call per database or elastic pool every 15 minutes, so space repeated tests accordingly.

Trap Using a cross-region forced failover to test ordinary transient connection retries because it adds an unnecessary role change and data-loss risk.

5 questions test this
Replication does not replace historical recovery from backups

Availability replicas propagate committed changes, including an accidental destructive update or deletion, so another current copy is not automatically a pre-error recovery point. Retain recoverable backups for logical mistakes even when HA and regional replication are configured. Choose recovery to an earlier retained state for a logical error, rather than treating a healthy current replica as historical protection.

Trap Failing over to a synchronized replica after an accidental committed table deletion because the same deletion has already been replicated.

5 questions test this
Accelerated database recovery shortens engine recovery through persistent row versions

Accelerated database recovery (ADR) reduces restart-recovery and long-transaction rollback time by using persistent row versions and logical revert instead of undoing every row change from the transaction log. Its persistent version store (PVS) occupies space inside the database, so reserve capacity for retained versions; insufficient room to generate versions can make DML fail. ADR is always enabled in Azure SQL Database and Azure SQL Managed Instance, but changes engine recovery behavior rather than creating a remote DR copy. ADR can truncate the log aggressively despite long-running transactions as checkpoints and backups occur, but transactional replication, snapshot replication, and change data capture (CDC) disable this aggressive truncation so their log readers can collect changes. Frequent transaction aborts can also delay PVS cleanup, so fast rollback does not remove the need for version-storage capacity.

Trap Increasing only tempdb capacity to accommodate ADR versions for a user database because its persistent version store resides inside that user database.

A planned geo-failover is the appropriate drill when data loss is unacceptable

When both Azure SQL geo-replication partners are reachable, use planned failover to finish synchronization before swapping roles. A forced failover promotes the target without waiting for the inaccessible primary and can discard committed changes that were not replicated. Production failover-group drills should normally exercise the planned path; validation must include successful application reads and writes through the recovered environment, not just a completed control-plane operation.

Trap Choosing forced failover for a no-data-loss production drill because its faster role change bypasses the synchronization safeguard.

6 questions test this
Geo-restore drills should validate recovery and data-loss tolerance in a test environment

For a geo-restore drill, create a test copy of the production environment so that simulating loss of the source does not expose production changes to the recovery gap. Exercise restoration to another server, change the test application's connection target, and verify logins, basic functionality, and the recovered data state. A successful test must show both that the recovery procedure works and that the application tolerates the age of the available backup.

Trap Running a production cutover to a recent geo-backup as the first recovery rehearsal because the backup may omit changes already acknowledged to production clients.

5 questions test this
Returning service to the original region requires a deliberate failback procedure

After an Azure SQL geo-failover, the recovered original primary becomes a secondary and catches up to the new primary rather than automatically reclaiming the writable role. Plan a separate manual planned failback after replication is healthy so the return does not introduce another avoidable loss of data. For Managed Instance failover groups, check that both instances have completed the role change and returned to their normal replication status before attempting another switch.

Trap Using a second forced failover immediately after regional recovery because the original primary may not yet contain the changes accepted in the recovery region.

5 questions test this
Readable backup media does not prove that the full recovery procedure succeeds

RESTORE VERIFYONLY checks backup completeness, readability, and available checksums without restoring the database or fully checking its internal data structure. A recovery rehearsal must actually restore the required backup sequence, run integrity checks such as DBCC CHECKDB, and confirm usable application data. Rehearse the combinations in the recovery runbook and record elapsed recovery time rather than treating a backup job's success status as proof of recoverability.

Trap Accepting a successful RESTORE VERIFYONLY result as evidence that the database's structural integrity and end-to-end recovery time have been tested.

5 questions test this
An HA/DR test must demonstrate usable performance on the surviving topology

Test the proposed replica placement under representative write and reporting load because cross-zone or cross-site latency can change replication delay and commit performance. After failover, verify that the surviving compute, storage, and network can handle the production workload rather than merely accept a connection. Comparable replica capacity is especially important when the secondary must immediately become the primary without a time-consuming scale-up.

Trap Approving an undersized recovery replica after an idle connection test because the exercise never measured sustained production throughput.

6 questions test this

Plan and perform backup and restore of a database

Read full chapter
  • Unrestricted point-in-time protection requires full recovery and a usable chain of log backups
  • A differential restore needs its full base, but not necessarily later log backups
  • Instance recovery needs system database backups as well as user database backups
  • Azure Backup offers SQL-aware streaming or instance snapshots for SQL Server on Azure VMs
  • Backup retention needs deletion protection when recoverable SQL copies must survive an attack
  • A copy-only full backup captures an ad hoc copy without resetting the differential base
  • Native backups require SQL permission and storage access for the SQL Server service
  • Native backup tools distinguish the backup type from the number of destination files
  • Backup checksums detect damage but do not repair database pages
  • Backup compression reduces transfer volume at the cost of additional CPU work
  • Azure Backup SQL protection requires its workload service identities to have SQL access
  • Availability-group backup preferences work only when backup jobs evaluate the preferred replica
  • T-SQL storage-snapshot backups require captured database files as well as metadata
  • SSMS can build a restore plan from backup history or explicitly supplied media
  • Restoring to new storage requires logical-file mappings rather than just a new database name
  • Native restore authority differs from database role membership and active connection access
  • NORECOVERY, RECOVERY, and STANDBY provide different access and continuation states
  • A portable backup still needs a compatible engine and its encryption material
  • Piecemeal restore recovers critical filegroups before the rest of a SQL Server database
  • Page restore repairs isolated data-page damage without restoring the whole database
  • Restore metadata preservation requires the matching KEEP option at recovery
  • Database snapshot reversion undoes local changes without providing independent media recovery
  • STOPAT recovers a SQL Server database to a time covered by its log backups
  • A tail-log backup captures the final unbacked log before restoring over a database
  • Marked transactions provide a common recovery boundary for related databases
  • Azure SQL Database automated backup cadence and retention define its recent recovery window
  • Azure SQL Database PITR creates a separate database rather than overwriting the live one
  • Managed Instance PITR has broader instance placement but still requires a same-region target
  • PITR backup storage depends on retained recovery chains and write activity rather than live data size alone
  • Long-term retention preserves selected full backups for as long as ten years
  • An LTR policy governs future backups of its database while it is primary
  • Existing LTR backups can survive deletion of their original database host
  • A long retention policy does not by itself make Azure SQL backups immutable
  • Cross-perimeter LTR restore access requires directional admission rules
  • SAS-authenticated Azure Blob backups bind a container credential to an individual blob URL
  • SQL Server Azure Blob backups require explicit planning for stripe size and blob reuse
  • Managed Instance user-initiated backups are copy-only full backups to Azure Blob Storage
  • Managed Instance native restore uses Azure Blob URLs without SQL Server restore options
  • Azure SQL backup redundancy determines whether backups can support regional recovery
  • SQL Server file-snapshot backups depend on snapshots of database files already stored as Azure blobs

Unlock with Premium — includes all practice exams and the complete study guide.

Configure HA/DR for database solutions

Read full chapter
  • Creating a geo-secondary seeds a database on a different logical server before ongoing replication
  • Active geo-replication does not require the primary and secondary to share a pool layout
  • Waiting for copy synchronization can protect selected committed work without changing the replication mode
  • Promoting a geo-secondary and removing its replication link target different servers
  • Geo-replicated read distribution can extend beyond the direct-secondary limit through chaining
  • Network security perimeter admission checks do not stop established geo-replication data movement
  • Availability group creation requires prepared instances, databases, and transport endpoints
  • A manually prepared secondary joins its availability group while still unrecovered
  • Automatic seeding creates secondary database files but still needs explicit authorization and capacity
  • Automatic Windows availability group failover requires more than synchronous commit
  • Availability group read-only routing requires both server configuration and client read intent
  • Azure listener routing and client reconnection must match the subnet topology
  • Managed Instance exposes a link workflow rather than user administration of its internal availability groups
  • Linux availability group HA uses external cluster control rather than Windows automatic mode
  • Requiring synchronized secondaries can stop primary commits when acknowledgers are unavailable
  • Manual availability-group promotion must match target readiness and cluster ownership control
  • Availability-group server-health policy is different from replica session timeout
  • Strict WSFC connections use the configuration scope of the protected SQL resource
  • SQL Database and Managed Instance use different failover-group membership boundaries
  • Managed Instance failover partners require a coordinated cross-region topology
  • Customer-managed and Microsoft-managed failover policies have different response boundaries
  • Failover-group listeners preserve role-based endpoint names through a geo-failover
  • Read-only listener failover is disabled by default to protect primary workload capacity
  • A geo-secondary needs its own short-term retention configuration for its future primary role
  • Failover-group secondary zone settings inherit differently for Hyperscale and other architectures
  • Failover-group recovery predictability depends on database count and shared-pool capacity
  • Managed Instance failover-group PITR history belongs to the instance that was primary at the requested time
  • SQL Database failover-group commands distinguish creation, enrollment, inspection, and promotion
  • Azure shared disks make FCI storage shareable through explicit attachment and reservation support
  • FCI shared-storage options have different placement and SQL feature constraints
  • FCI setup joins nodes to one clustered instance rather than creating independent SQL copies
  • FCI recovery needs surviving quorum and storage as well as a new service owner
  • Log shipping depends on a coordinated backup, copy, and restore pipeline
  • Log-shipping restore mode and delay determine secondary usability and freshness
  • Log-shipping failover requires explicit recovery and redirection rather than automatic promotion
  • Geo-replication monitoring must distinguish data progress from provisioning progress
  • Availability group monitoring separates runtime roles from database synchronization and cluster readiness
  • The alwayson_health event session provides a history of availability group state changes
  • Managed Instance failover-group readiness depends on successful seeding rather than a status label alone
  • WSFC quorum health must be inspected separately from database synchronization
  • Managed SQL replica health must interpret synchronization state in the context of commit mode
  • Asynchronous RPO problems arise before transaction log is hardened on the secondary
  • A large redo queue can delay recovery even after log records are safely received
  • Read-only reporting can block DDL redo through schema locks
  • Resuming an availability database must respect where its data movement was suspended
  • A geo-secondary must keep up with the primary before it can be a reliable recovery target
  • Healthy database copies still need compatible server-level recovery metadata
  • Log-shipping troubleshooting must separate data-pipeline failures from monitor-connection failures
  • Automatic page repair needs a successful partner-page replacement rather than healthy replication alone
  • Availability-group role changes require change-processing jobs and consumers to follow the correct role
  • An AG-protected Publisher needs listener-based Distributor redirection
  • Managed Instance geo-failover requires role-specific transactional replication recovery

Unlock with Premium — includes all practice exams and the complete study guide.