DP-300 Cheat Sheet
Plan and implement data platform resources
Plan and deploy Azure SQL solutions
Read full chapterCheat 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
- You are migrating an on-premises application that uses Service Broker to exchange messages between two databases on the same instance and SQL Server Agent jobs for nightly processing. You need a platf
- You are choosing between Azure SQL Database and Azure SQL Managed Instance for a migrated application. The application needs Always Encrypted columns, ledger tables, Query Store on secondary replicas,
- You are migrating a database that contains a SAFE CLR assembly to Azure SQL Managed Instance. The on-premises deployment script runs CREATE ASSEMBLY with a path to a DLL on a local drive. You need to
- You have two databases named SalesDb and RefDb in Azure SQL Database. A report uses three-part names to join SalesDb tables to RefDb tables. Company policy requires the databases to remain in Azure SQ
- 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
- You migrate a SQL Server instance to SQL Server on Azure Virtual Machines because it needs operating system access for a third-party monitoring agent. Management asks who must make sure SQL Server cum
- You plan to migrate a SQL Server instance to SQL Server on Azure Virtual Machines instead of Azure SQL Managed Instance. The operations team asks which capability they will gain by choosing the virtua
- You are comparing Azure SQL Managed Instance and SQL Server on Azure Virtual Machines for a document application. It needs SQL Server Agent jobs, cross-database transactions, a SAFE CLR assembly deplo
- You are migrating a SQL Server 2019 database that has an EXTERNAL_ACCESS CLR assembly. The assembly reads CSV files from a Windows file share every hour. You need to move the database to Azure without
- 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
- You manage an Azure SQL Database named db1 that uses the General Purpose service tier with provisioned compute. You verify that your organization has SQL Server licenses with active Software Assurance
- You plan to create a new Azure SQL Database named db2 in the Hyperscale service tier with 16 vCores. Finance wants to apply existing SQL Server licenses through Azure Hybrid Benefit to lower the cost
- Your company plans to buy one-year reserved capacity for Azure SQL Database. The databases are db1 (Standard S3), db2 (Premium P2), db3 (Basic), and db4 (General Purpose, 8 vCores, provisioned). You n
- You have an Azure SQL Database named db1 in the Premium P4 service objective. Your company has SQL Server Enterprise licenses with active Software Assurance and wants to use Azure Hybrid Benefit for d
- 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
- You are planning a new order-tracking application for a team whose reports, notebooks, and Power BI models already live in one Microsoft Fabric workspace. The application needs a transactional databas
- You mirror an Azure SQL Database named SalesDb into Microsoft Fabric. A developer wants a Fabric notebook to correct product prices so that the change also appears in the e-commerce application that r
- You have an existing Azure SQL Database named SalesDb that serves an e-commerce application. SalesDb must stay in Azure SQL Database. Analysts need SalesDb data in OneLake near real time for Power BI
- You have a SQL database in Fabric named AppDb that an order-entry application updates throughout the day. A data engineer is building Spark notebooks in the same Fabric workspace that must read AppDb'
- 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
- You have a SQL database in Fabric named AppDb. A line-of-business application must run INSERT and UPDATE statements against AppDb tables, and Power BI reports must query the same data. You need to con
- A report developer must connect SQL Server Management Studio to the SQL analytics endpoint of a SQL database in Fabric named AppDb to build views for analysts. The developer reused a connection string
- You connect to the SQL analytics endpoint of a SQL database in Fabric named AppDb. Analysts need a reusable object on that endpoint that joins two replicated tables and exposes only selected columns.
- You have a SQL database in Fabric named AppDb that serves an order-entry application. During business hours, analysts run large read-only T-SQL reports against AppDb's database endpoint, and order tra
- 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
- A Bicep file declares a logical server named SQL1, an elastic pool named Pool1, and 10 databases created by a for loop. Each database references Pool1 and SQL1, and no explicit dependsOn entries are u
- A colleague proposes storing the team's SQL infrastructure definition as a set of commands typed into Azure Cloud Shell, because Cloud Shell includes Azure CLI and Azure PowerShell. You need a definit
- Your release pipeline can run only Azure PowerShell tasks. You need the pipeline to deploy a logical server named SQL1 and a database named db1 from a versioned definition that declares their desired
- 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
- You publish a DACPAC to an existing database named db1 by using SqlPackage with default properties. The new schema changes a column from nvarchar(200) to nvarchar(50), and the column contains data. Th
- Your pipeline publishes a DACPAC with SqlPackage to a logical server named SQL1. The target database named db2 doesn't exist yet. The pipeline's login is a member of the dbmanager role in master on SQ
- Your pipeline publishes a DACPAC to an existing Azure SQL Database named db1 by using SqlPackage with a Microsoft Entra service principal. The principal is a member of db_ddladmin in db1, and the publ
- Developers add three stored procedures and a new column to a SQL project for an existing Azure SQL Database named db1. Your release pipeline already deploys SQL1 and db1 by using a Bicep file. You nee
- 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
- You run az sql server create to deploy SQL1 in a resource group named RG1 for a new application. A teammate plans to sign in to SQL1's operating system, install SQL Server cumulative updates, and then
- You run az sql server create to deploy a logical server named SQL1 in a resource group named RG1. A report must list the databases on SQL1 before the next step of the script creates any user database.
- You create a logical server named SQL1 and a database named db1 by using an Azure CLI script. A developer on the corporate network can't connect to db1 by using SQL Server Management Studio, and the e
- You have a logical server named SQL1 in a resource group named RG1. A teammate's script runs az sql mi create to add a database named db1 at the Standard S0 service objective, and the command asks for
- 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
- You run az sql mi create to deploy a SQL Managed Instance into a subnet named Subnet1 that already hosts two Azure virtual machines used by the application tier. The deployment fails validation. You n
- A deployment of a new SQL Managed Instance in East US fails because the subscription's regional vCore quota is exhausted. The project can't wait for a support request to be processed, and the business
- You plan to deploy one General Purpose SQL Managed Instance now and add more instances and scaling operations later. The network team wants to allocate the smallest subnet that SQL Managed Instance al
- Your subscription has a regional quota of 64 vCore units for SQL Managed Instance on standard-series hardware. You already run a General Purpose instance with 16 vCores in that region. You need to kno
- 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
- You installed SQL Server 2022 yourself on an Azure Windows VM named VM1 that was created from a custom image. You need VM1 to appear as a SQL virtual machine resource so that you can use portal manage
- A team created an Azure VM named VM5 from a plain Windows Server image and installed SQL Server 2022 Standard themselves. The CEIP service hasn't detected the instance, so VM5 isn't registered with th
- Your subscription contains 60 existing Azure VMs on which teams self-installed SQL Server, and new teams keep adding similar VMs. You need all current and future VMs that have SQL Server installed to
- You register a self-installed SQL Server Enterprise instance on an Azure VM named VM2 with the SQL IaaS Agent extension. Your company has licenses with active Software Assurance and wants to use them
- 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
- You migrated an application to an Azure SQL Managed Instance named SQLMI1 that was created with default settings. SQL Server Agent jobs scheduled for 01:00 now start at 02:00 or 03:00 Central European
- You plan to migrate an on-premises SQL Server instance that uses the Latin1_General_100_CI_AS_SC server collation to Azure SQL Managed Instance. Application queries join user tables to temporary table
- 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
- You have a 3-billion-row table named Sales in Azure SQL Database. Most queries filter on a range of OrderDate values, and each month the oldest month of data must be removed quickly. SalesOrderID is a
- You plan to partition a table named Shipments in SQL Server on Azure Virtual Machines by a combination of Region and ShipYear. Because only one column can be the partitioning column, you add a compute
- 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
- You are choosing a platform for a 20-TB partitioned table named Claims. Partitions older than three years must sit on cheaper, slower disks that you choose, and each year's filegroup must be backed up
- You migrate a partitioned table to Azure SQL Managed Instance and keep one filegroup per year. The script runs ALTER DATABASE db1 ADD FILE (NAME = 'f2024', FILENAME = 'E:\Data\f2024.ndf') TO FILEGROUP
- You are partitioning a 5-TB Sales table by month in SQL Server on Azure Virtual Machines. All data files sit on the same storage pool, and the database is always backed up and restored as a whole. A c
- 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
- A multitenant database named db1 in Azure SQL Database has reached the largest compute and storage size you can buy for its tier, and the growth plan requires spreading tenants across many databases.
- You use a range shard map for TenantId. The range [100,200) already maps to a database named DBC. You need tenants 400 through 599 to be stored in DBC as well, while tenants 200 through 399 remain in
- You manage a range shard map for TenantId in Azure SQL Database. The map has two mappings: [0,1000) points to a shard named DB1, and [1000,2000) points to a shard named DB2. A new tenant is assigned T
- 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
- A range shard map stores tenants [0,1000) in a shard named DB1, which is overloaded. You created an empty shard named DB2 and want tenants 500 through 999 to live in DB2, with the shard map updated to
- You must change the range [200,300) in a range shard map so that it points to a different shard, and you will coordinate the data move yourself. The UpdateMapping call is rejected. You need to make th
- A range shard map maps [0,1000) to a shard named DB1. An administrator calls SplitMapping at 500 so that [500,1000) can later be served by a new shard named DB2, and then checks DB2. You need to expla
- 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
- You are designing a sharded multitenant SaaS database in Azure SQL Database. Every transaction reads and writes rows for a single tenant, and the application must route each connection to one shard by
- Your SaaS application stores tenant data in Azure SQL Database. Contracts require the operations team to return any single tenant to an earlier point in time by using only the built-in point-in-time r
- Your sharded multitenant SaaS application stores 200 small tenants per shard in Azure SQL Database. One tenant's growing workload is slowing down the other tenants on its shard. You need to isolate th
- 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
- You have 30 SQL Server VMs in Azure that are managed by using Azure Update Manager. A change board must approve every update before installation. You need a daily report of the missing SQL Server and
- You enable Azure Update Manager for a SQL Server on Azure Virtual Machines instance named SQLVM1. The assessment results for SQLVM1 list Windows operating system updates but no SQL Server updates. You
- You manage 12 SQL Server 2022 instances that run on Azure virtual machines. You need to centrally automate the installation of SQL Server cumulative updates and Windows updates during scheduled mainte
- You have an Always On availability group that has replicas on two Azure virtual machines named SQLVM1 and SQLVM2. You move both VMs to Azure Update Manager. You need to ensure that monthly updates nev
- 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
- You have an Azure Arc-enabled SQL Server instance named SQL1 that uses the pay-as-you-go license type and has automatic updates enabled. After the latest maintenance window, a Critical security update
- You have SQL Server 2022 on an on-premises Ubuntu server named LNX1 and SQL Server 2022 on an on-premises Windows server named WIN1. Both servers are connected to Azure Arc. You need to keep both inst
- A Windows server named SQL1 is located in a datacenter in Chicago and is connected to Azure Arc. The Azure Arc resource for SQL1 is in the West Europe region. You configure automatic updates for SQL1
- You have an on-premises SQL Server 2019 instance on a Windows server named SQL1 that is connected to Azure Arc. SQL1 uses the license only license type. You need to configure automatic updates for SQL
- An on-premises Windows server named HOST1 is connected to Azure Arc. HOST1 runs two SQL Server instances named INST1 and INST2. You enable automatic updates in the SQL Server configuration of HOST1 to
- 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
- You have an availability group named AG1 that is used only for disaster recovery. It has a primary replica SQL1 and an asynchronous-commit secondary SQL2 at a remote site. You upgrade SQL2 to a newer
- You have an availability group named AG1 with two synchronous-commit replicas. You upgraded the secondary, SQLVM2, to SQL Server 2025 and manually failed AG1 over to it, and SQLVM1 still runs SQL Serv
- You have an availability group named AG1 with a primary replica SQLVM1, a local synchronous-commit secondary SQLVM2, and an asynchronous-commit secondary SQLVM3 at a disaster recovery site. You need t
- You have an availability group named AG1 with a primary replica SQL1 and a readable synchronous-commit secondary SQL2 that hosts reporting queries. You upgrade SQL2 to a newer SQL Server version. You
- 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
- You have an on-premises SQL Server 2022 instance named SQL1 on Windows Server 2022. Regulations require SQL1 and its databases to stay in your datacenter. You need to manage SQL1 from the Azure portal
- You configure automated backups for an Azure Arc-enabled SQL Server instance named SQL1 by using the default schedule. The storage team asks where the backup files will be written so that they can siz
- Your company runs a validated Kubernetes cluster in its own datacenter. You need to deploy SQL Managed Instance enabled by Azure Arc on the cluster so that the company controls the upgrade cadence of
- You have an Azure Arc-enabled SQL Server 2019 instance on Windows named SQL1 that uses the pay-as-you-go license type. You need SQL1 databases to be backed up on a policy with a 14-day retention so th
- You have 40 on-premises SQL Server 2019 instances on Windows that are connected to Azure Arc and use the pay-as-you-go license type. You need to evaluate each instance's configuration, index managemen
- 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
- You have Azure Arc-enabled SQL Server instances on Windows that use the Software Assurance (Paid) license type. You plan to enable best practices assessment for the instances. You need to provide the
- You onboard an on-premises Windows server named SRV1 to Azure Arc, and the Azure Arc server resource shows a Connected status. SRV1 hosts a SQL Server 2022 instance, but no SQL Server enabled by Azure
- You plan to connect four SQL Server instances to Azure Arc. SQL1 is SQL Server 2012 on Windows Server 2016. SQL2 is SQL Server 2019 on an Azure virtual machine. SQL3 is SQL Server 2016 on Windows Serv
- You plan to connect on-premises SQL Server instances to Azure Arc. The security team allows no inbound connections from the internet to the datacenter. You need to configure the firewall so that the C
- 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
- You have an out-of-support SQL Server 2014 instance on Windows that is connected to Azure Arc with the license only type. The license has no Software Assurance and no SQL Server subscription. You need
- Your company runs an out-of-support SQL Server version on premises and connects the servers to Azure Arc to subscribe to Extended Security Updates while it plans an upgrade. Management asks for the ma
- Your organization connected 30 out-of-support SQL Server 2014 instances to Azure Arc last month for inventory and uses the pay-as-you-go license type. A manager believes the instances now receive Exte
- Your QA team runs 20 on-premises SQL Server Enterprise VMs that are powered on for only about 10 days each quarter. The VMs are connected to Azure Arc. Finance wants to pay for SQL Server software onl
- You run SQL Server 2022 on an on-premises Ubuntu server named LNX2 as a standby instance that receives log backups for disaster recovery. LNX2 is connected to Azure Arc with the pay-as-you-go license
- 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
- You run SQL Server 2022 on an Azure VM named LNX1 that uses Ubuntu 22.04 with the Microsoft CU repository configured. You need to update SQL Server to the latest cumulative update for the same major v
- A new Ubuntu 22.04 VM named LNX7 has the mssql-server package for SQL Server 2022 installed from the Microsoft repository, but the instance has never been configured. You need to choose the Developer
- You have SQL Server 2022 on an Ubuntu 22.04 server named LNX4 and need to upgrade it to SQL Server 2025, which supports Ubuntu 22.04. Running sudo apt-get update and sudo apt-get install mssql-server
- You plan to deploy SQL Server 2025 on Linux virtual machines in Azure. The infrastructure team offers four approved images: SLES 15, Ubuntu 24.04, Ubuntu 20.04 LTS, and RHEL 8 Server. You need to choo
- 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 chapterUnlock with Premium — includes all practice exams and the complete study guide.
Plan and implement a migration strategy
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Implement a secure environment
Configure database authentication and authorization
Read full chapterCheat 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
- You have an Azure SQL Database named db1 on a logical server that uses Microsoft Entra-only authentication. The subscription is associated with the contoso.com tenant. A consultant must sign in to db1
- You have an Azure SQL Managed Instance named SQLMI1. Five DBAs must all have Microsoft Entra administrator rights on SQLMI1, and DBAs join and leave the team regularly. When you try to add a second us
- You have an Azure SQL Database logical server named SQL1. During a cleanup, an administrator selected Remove admin on the Microsoft Entra ID page of SQL1. Since then, every Microsoft Entra user fails
- You have a new Azure SQL Database logical server named SQL1 that was deployed with SQL authentication only. A DBA named User1 must connect by using a Microsoft Entra account and then create contained
- 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
- You have an Azure SQL Database named db1. An application on an Azure virtual machine connects to db1 by using Microsoft.Data.SqlClient. The VM has no system-assigned managed identity and two user-assi
- You have an Azure SQL Managed Instance named SQLMI1 that has a Microsoft Entra admin. DBAs connect by using SQL Server Management Studio with Microsoft Entra accounts. A new security policy requires e
- You have an Azure SQL Database named db1. A DBA runs an internal .NET administration tool that connects to db1 by using Microsoft.Data.SqlClient. The DBA's Microsoft Entra account is subject to mandat
- You have an Azure SQL Database named db1. A DBA connects from a Linux jump host that has no web browser, through an SSH session, by using an internal .NET tool built on Microsoft.Data.SqlClient. The D
- You have an Azure SQL Database named db1. A nightly job runs on an on-premises Linux server that has no Azure managed identity and no signed-in user. The job must authenticate to db1 through Microsoft
- 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
- You have an Azure SQL Database logical server named SQL1 that has a Microsoft Entra admin. Compliance requires that the SQL server admin and every SQL authentication login stop connecting immediately,
- You have an Azure SQL Database logical server named SQL1. After Microsoft Entra-only authentication was enabled on SQL1, a legacy reporting application named App1 that connects by using a SQL authenti
- You have an Azure SQL Managed Instance named SQLMI1 on which Microsoft Entra-only authentication is enabled. A DBA who is a member of the Microsoft Entra admin group creates a new SQL authentication l
- 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
- You have a SQL Server 2022 instance on an Azure virtual machine named SQLVM1 that is registered with the SQL IaaS Agent extension. When you enable Microsoft Entra authentication with the VM's system-a
- You have 12 SQL Server 2022 instances on Azure virtual machines that are registered with the SQL IaaS Agent extension. All 12 must use Microsoft Entra authentication. A Privileged Role Administrator w
- You have a SQL Server 2022 instance on an Azure virtual machine named SQLVM2 that uses Microsoft Entra authentication with a user-assigned managed identity. After a tenant cleanup, new Microsoft Entra
- 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
- You are designing a high-availability deployment of SQL Server 2022 on Azure virtual machines for an application named App1. The design must provide automatic failover, and all users must sign in by u
- You migrated a SQL Server 2022 instance to an Azure virtual machine named SQLVM3. A consultant recommends installing the Azure Connected Machine agent on SQLVM3 so that Microsoft Entra authentication
- You have an on-premises SQL Server 2022 instance that runs on Red Hat Enterprise Linux in your datacenter. You need to enable Microsoft Entra authentication for the instance so that DBAs can sign in w
- 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
- You have a SQL Server 2022 instance named SQL2 on an Azure virtual machine. An administrator changed Server authentication from Windows Authentication mode to SQL Server and Windows Authentication mod
- You have a SQL Server 2022 instance on an Azure virtual machine that now uses SQL Server and Windows Authentication mode. A vendor's installation guide says to enable sa so that the vendor application
- You have a SQL Server 2022 instance named SQL1 on an Azure virtual machine that was installed with Windows Authentication mode. A vendor application must connect by using a SQL authentication login na
- 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
- You are moving a database to Azure SQL Managed Instance. A .NET application that uses it is being rewritten, will run on Azure App Service, and will use a current Microsoft.Data.SqlClient driver. The
- You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. A legacy line-of-business application connects with Integrated Security=SSPI, its source code is unavailable, and it
- You plan to enable Windows Authentication for Microsoft Entra principals on an Azure SQL Managed Instance named SQLMI1 by using Microsoft Entra Kerberos. Users connect from several kinds of devices. Y
- 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
- You have a SQL Server 2022 instance on an Azure virtual machine. A SQL authentication login named ReportLogin was created without any options, and a user for it exists in a database named Reports. A r
- You have a SQL Server 2022 instance on an Azure virtual machine that is joined to the contoso.com Active Directory domain. A domain user account named CONTOSO\Analyst1 must connect to the instance by
- You have a SQL Server 2022 instance on an Azure virtual machine. You run CREATE LOGIN Temp1 WITH PASSWORD = '<strong password>' MUST_CHANGE, CHECK_EXPIRATION = OFF so that Temp1 must change the passwo
- 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
- You have a SQL Server 2022 instance on an Azure virtual machine. A provisioning service uses a login named Prov1 that must create new logins. Prov1 must not alter or drop existing logins. The solution
- You have a SQL Server 2019 instance on an Azure virtual machine. A user named Prov2 in a database named Sales has ALTER ANY USER and successfully creates users there. Prov2's login now fails when it r
- You have a SQL Server 2022 instance on an Azure virtual machine. A user named ProvUser in a database named Sales must create new database users in Sales only. ProvUser must not modify or drop existing
- 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
- You have a SQL Server 2022 instance on an Azure virtual machine. An application connects by using a SQL authentication login named svc_web_prod. Database naming standards require the database principa
- You have a SQL Server 2022 instance on an Azure virtual machine. A deployment script runs CREATE USER Billing in a database named db4 without a FOR LOGIN clause. A SQL authentication login named Billi
- You have a SQL Server on Azure Virtual Machines instance that hosts a database named db1. A user named AppUser, mapped to a login named AppLogin, has SELECT permission on the Reporting schema. The que
- You have a SQL Server 2022 database named db1. A user named EtlUser, mapped to a login named EtlLogin, was created WITH DEFAULT_SCHEMA = Staging. EtlUser has CREATE TABLE permission and ALTER permissi
- 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
- You have an Azure SQL Database elastic pool that hosts one database per customer. Customer databases are regularly moved to other logical servers. Each customer has one SQL authentication account that
- You have an Azure SQL Database named Tenant07 that contains a SQL authentication contained database user named T07Admin. T07Admin connects successfully from an application whose connection string incl
- You have a SQL Server 2022 instance on an Azure virtual machine. A database named Tenant4 will move between instances, so its users must not depend on logins in master. When you run CREATE USER T4User
- 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
- You have a SQL Server 2022 database named Sales. Users from the Finance department need SELECT on a view named Rpt.MonthlyKpis. A developer proposes granting SELECT on the view to public because every
- You have a SQL Server 2022 database named Pricing. After an administrator ran DENY SELECT ON dbo.Prices TO public, members of a role named Analysts, which is granted SELECT on dbo.Prices, can no longe
- You have a SQL Server 2022 database named Archive. Auditors found that several logins without a database user in Archive can still query it by using permissions granted to guest. Users who have their
- You have a SQL Server 2022 database named Ops. Every database user can execute a stored procedure named dbo.uspPurgeTemp, even though no user or custom role has been granted EXECUTE on it and nobody i
- 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
- You have an Azure SQL Database named db1 on a logical server named SQL1 that has a Microsoft Entra admin. Twelve analysts in a Microsoft Entra security group named TeamReaders need to read every user
- You have an Azure SQL Database named db1 on a logical server named SQL1. To give a Microsoft Entra group named Auditors read access to db1, an administrator connected to master as the Microsoft Entra
- You have an Azure SQL Managed Instance named SQLMI1. A Microsoft Entra group named Finance has a user in a database named db1, and the user is a member of db_datareader. User1, a Finance member, canno
- You have an Azure SQL Database named db1. A database user created FROM EXTERNAL PROVIDER for a Microsoft Entra group named Sales is a member of db_datawriter. User2 has moved to another department and
- An Entra contained user does not inherit a same-named server login's privileges
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
- You have an Azure SQL Database logical server named SQL1. A Microsoft Entra login named Ops1 exists in master and is a member of ##MS_ServerStateReader##. Ops1 must query database state DMVs in a data
- You have an Azure SQL Database logical server named SQL1. Several Microsoft Entra logins in master are members of fixed server roles. You used SQL Server Management Studio to script the Microsoft Entr
- You have an Azure SQL Database named db1 that is copied to a different logical server each quarter for an external audit. A Microsoft Entra user named auditor1@contoso.com needs read access to db1. Th
- You have an Azure SQL Database named db1. You are auditing Microsoft Entra principals in db1 and must determine which users were created from Microsoft Entra logins in master and which are contained d
- 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
- You have an Azure SQL Managed Instance named SQLMI1 that has a system-assigned managed identity. Microsoft Entra users cannot authenticate through security group membership, and the Microsoft Entra ID
- You have an Azure SQL Database logical server named SQL1. An Azure DevOps pipeline authenticates as a Microsoft Entra service principal that is the Microsoft Entra admin for SQL1. The pipeline's CREAT
- You have an Azure SQL Database named db1. A DBA who connects as a Microsoft Entra user runs CREATE USER [Team1] FROM EXTERNAL PROVIDER and receives SQL error 33134. The embedded Microsoft Entra messag
- You have an Azure SQL Database logical server named SQL1 that has a system-assigned managed identity. An automation service principal will run CREATE USER ... FROM EXTERNAL PROVIDER to create users on
- 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
- You have an Azure SQL Database named db1. You need to create a database user for a user-assigned managed identity named uami-etl. The logical server identity has no Microsoft Graph permissions, and th
- You have an Azure SQL Managed Instance named SQLMI1. You need to create a login by using CREATE LOGIN [hrapp7c1d2] FROM EXTERNAL PROVIDER WITH OBJECT_ID for an application named hrapp whose display na
- You have an Azure SQL Database named db1. A Microsoft Entra user has the display name Adrian King and the user principal name adrian@contoso.com. No other identity shares either name. You need to crea
- You have an Azure SQL Database logical server named SQL1. A colleague created a login named reportapp3fa21 by using CREATE LOGIN ... FROM EXTERNAL PROVIDER WITH OBJECT_ID for one of three service prin
- 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
- You have an Azure SQL Database logical server named SQL1. A Microsoft Entra group named LoginOps has a group login in master. Members of LoginOps must be able to create and delete logins on SQL1. The
- You have an Azure SQL Database named db1. To share ownership among a team, you run ALTER AUTHORIZATION ON DATABASE::db1 TO [DbaTeam], where DbaTeam is a Microsoft Entra group login. The statement fail
- You have an Azure SQL Database named db1. A Microsoft Entra group named Devs has a group login in master and a login-based user in db1 that is a member of db_ddladmin. Developers who connect through t
- You have an Azure SQL Database logical server named SQL1 with both a SQL server admin and a Microsoft Entra admin configured. No Microsoft Entra logins exist in master. While connected as the SQL serv
- 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
- You have an Azure SQL Database named db1. A database user named App1 is a member of db_datawriter only. App1 can insert rows into Sales.Orders, but its statement UPDATE Sales.Orders SET Status = 'Clos
- You have an Azure SQL Database named db1. A data-entry application connects as a user named Entry1 that was added to db_ddladmin during development. Entry1 only reads and modifies rows in user tables.
- You have an Azure SQL Database named db1. A service account user named App1 is a member of a custom role named OrderEntry and of db_datareader. App1 is being replaced by a new user named App2 that mus
- 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
- You have an Azure SQL Database named db1 that contains 12 custom database roles. A support team must add and remove members of every custom role and adjust the permissions granted to those roles. The
- You have an Azure SQL Database named db1. A team lead named Lead1 must add and remove members of an existing custom role named ApproverRole. Lead1 must not be able to change the membership of any othe
- You have an Azure SQL Database named db1. You created a custom role by running CREATE ROLE Approvers AUTHORIZATION Lead1. Lead1 has no other permissions in db1. Lead1 now needs to add three users to A
- You have an Azure SQL Database named db1. A help desk team must create database users for new employees and remove users for departing employees. The team must not be able to change role memberships o
- 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
- You have an Azure SQL Database logical server named SQL1. A new DBA named Dba2 has a SQL authentication login in master. Dba2 must create new databases on SQL1 for project teams. Dba2 must not create
- You have an Azure SQL Database logical server named SQL1 that hosts 40 databases. A monitoring login named Mon1 was added to ##MS_DatabaseConnector##. Mon1 connects to db7, which has no user for Mon1,
- You have an Azure SQL Database logical server named SQL1. To delegate database creation, an administrator created a contained database user with a password in master and added it to dbmanager. The use
- You have an Azure SQL Database logical server named SQL1. A SQL authentication login named Prov1 exists in master, and no database user for Prov1 exists in master. Prov1 must create and delete logins
- 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
- You have a SQL Server 2022 database named Sales. A team lead named Lead2 must read the dbo.Customers table and must be able to give the same read access to new team members. Lead2 must receive no perm
- You have a SQL Server 2022 database named db1. A developer named Dev3 already has ALTER permission on the Staging schema. When Dev3 runs CREATE VIEW Staging.vOrders, the statement fails because Dev3 l
- You have a SQL Server 2022 database named db1. Analysts must run the stored procedure Rpt.uspMonthly, and other users must not run it. Analysts join and leave every month, and their access must follow
- You have a SQL Server 2022 database named db1. A database role named Recruiting needs EXECUTE permission on a stored procedure named HR.uspUpdateHire and no other permissions. You use SQL Server Manag
- 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
- You have an Azure SQL Database named db1. A DBA named Dba3 must perform all configuration and maintenance activities in db1, including managing users, roles, permissions, schemas, and database-scoped
- You have an Azure SQL Database named db1. During a month-end audit, members of a role named Clerks, which is a member of db_datareader and db_datawriter, must keep reading all user tables but must be
- You have an Azure SQL Database named db1. A role named Analysts is a member of db_datareader. Analysts must keep reading every user table except HR.Salaries, which they must not read. You need to bloc
- You have an Azure SQL Database named db1. A deployment pipeline user named Deploy1 must create and alter tables and views in the Staging schema only. Other schemas in db1 contain procedures that run u
- 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
- You have a SQL Server 2019 instance on an Azure virtual machine. Four operators' logins must read server-wide state through dynamic management views, and new operators join often. Security policy forb
- You have a SQL Server 2022 instance on an Azure virtual machine. A DBA ran CREATE SERVER ROLE [DiagnosticRole] and then GRANT VIEW SERVER STATE TO [DiagnosticRole]. A login named OperatorLogin still c
- You have an Azure SQL Database named db1. An administrator ran CREATE ROLE [ReportRole] AUTHORIZATION [dbo] and GRANT SELECT ON OBJECT::Reporting.MonthlyTotals TO [ReportRole]. A contained user named
- You have an Azure SQL Database named db1. Three analysts who have contained database users in db1 must query only the Reporting.MonthlyTotals view. A colleague proposes creating a user-defined server
- 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
- You have an Azure SQL Database named db1. A support team role named Support must look up customers by reading only the CustomerName and Email columns of the Sales.Customers table. Other columns contai
- You have an Azure SQL Database named db1 that contains the Reporting, Sales, and HR schemas. Developers add new tables to Reporting every month. A role named ReportRole must read every current and fut
- You have an Azure SQL Database named db1. A developer named Dev2 owns tables in the Orders schema and must create a FOREIGN KEY constraint in Orders.Invoices that references Sales.Customers. Dev2 must
- 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
- You have a SQL Server 2022 database named Ops. You signed a stored procedure with a certificate named SignCert and need a database principal that holds SELECT on the tables the procedure reads. The pr
- You have an Azure SQL Database named db1. An application must change rows in the Billing.Invoices table only through the stored procedures Billing.uspApprove and Billing.uspVoid, which validate input.
- You have a SQL Server 2022 database named Ops. Users have EXECUTE on the procedure App.uspSummary, which reads the table Audit.Events. The App and Audit schemas have different owners, so callers get a
- 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
- You have an Azure SQL Database named db1. A reporting service uses a user-assigned managed identity named mi-report. To fix SELECT permission errors, an administrator assigned mi-report the Contributo
- You have an Azure SQL Database named db1. An Azure Automation runbook uses a user-assigned managed identity. Each night, the runbook calls Set-AzSqlDatabase to scale db1 up and then connects to db1 to
- You have an Azure SQL Database named db1. An operations engineer is a member of db_owner in db1 but cannot change the service objective of db1 in the Azure portal. The engineer must scale db1 without
- 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
- You have an Azure SQL Database named db1. After a deployment, a verification script that runs as the application user App1 queries sys.objects for the table Finance.Ledger and returns no rows. When a
- You have an Azure SQL Database named db1 that contains more than 2,000 tables, views, and procedures. An external auditor named Aud1 must inspect the definitions of all objects in db1 for a code revie
- You have a SQL Server 2022 database named Sales. An operator named Op1 must review the T-SQL definitions of the procedure dbo.uspPricing and the view dbo.vMargins to troubleshoot logic errors. Op1 mus
- 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
- You have an Azure SQL Database logical server named SQL1. During approved tuning windows, a performance engineer who signs in with a SQL login named Tune2 must remove all elements from the plan cache
- You have an Azure SQL Database logical server named SQL1. A security auditor named Aud1 has a SQL login on SQL1. Aud1 must review the password hashes of the SQL authentication logins to find accounts
- You have an Azure SQL Database logical server named SQL1 that hosts 30 databases. A capacity-planning service signs in with a SQL login named Cap1 and must query DMVs that require VIEW DATABASE STATE
- You have an Azure SQL Database logical server named SQL1. A documentation tool must read object definitions in all databases on SQL1. Your security policy prohibits any access to password hashes of SQ
- 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
- You have a SQL Server 2022 database named HR. A role named Readers has DENY SELECT on the table dbo.Employees. A user named User3 in Readers can still run SELECT FullName FROM dbo.Employees, but SELEC
- You have an Azure SQL Database named db1. During an access review, you ran REVOKE SELECT ON Sales.Customers FROM User2 to remove a direct grant. User2 can still query Sales.Customers. User2 is a membe
- You have an Azure SQL Database named db1. A database role named Analysts has SELECT on the Sales schema and EXECUTE on the Reporting schema. User5 is a member of Analysts, owns no objects, and has no
- 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
- You have a SQL Server 2022 instance on an Azure virtual machine that serves as a disaster recovery site. You restore 12 databases to it. Each database contains a user mapped to a SQL authentication lo
- You have a SQL Server 2022 instance on an Azure virtual machine. After you attach databases copied from another server, some users can't sign in. Several of their SQL authentication logins were re-cre
- You restored a database named Sales from a SQL Server 2019 instance to a SQL Server 2022 instance on an Azure virtual machine. A SQL authentication login named SalesApp already exists on the new insta
- 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
- You have an Azure SQL Database named db1. An application connects as a contained user named AppUser that receives permissions through direct grants, two custom roles, and db_datawriter. You are adding
- You have an Azure SQL Database named db1. A user named AppUser receives permissions through direct grants, custom roles, and fixed database roles. You are connected as the Microsoft Entra admin and ha
- You have an Azure SQL Database named db1. To troubleshoot an application, you run EXECUTE AS LOGIN = 'AppLogin' followed by a test query, and the statement fails. AppLogin is mapped to a database user
- 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
- You have an Azure SQL Database logical server named SQL1. A contractor has a Microsoft Entra login with a login-based user in db1 and a separate contained database user created FROM EXTERNAL PROVIDER
- You have an Azure SQL Database named db1. You removed a Microsoft Entra login from ##MS_ServerStateReader## and ran ALTER LOGIN with DISABLE for a second Microsoft Entra login. Sessions that both logi
- You have an Azure SQL Database logical server named SQL1. A contractor named Con1 has an individual Microsoft Entra login in master and login-based users in four databases. Con1 has no contained users
Implement security for data at rest and data in transit
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Implement compliance controls for sensitive data
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Monitor, configure, and optimize database resources
Monitor resource activity and performance
Read full chapterCheat 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
- You have an Azure SQL Managed Instance named SQLMI1 that hosts db1. A nightly maintenance window of three hours must contain both the full backup and a test restore of db1 to a separate database. The
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts an order-processing database used by staff in two time zones. You plan to tune SQLVM1 next quarter. You need to create
- You have an Azure SQL Managed Instance named SQLMI1. Your performance baseline, recorded while the system was healthy, shows that the nightly inventory queries in db2 finish in 12 minutes. Since a sch
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts db1 for a call-center app. Agents complain that screens are slow to start showing results, while the nightly batch job
- You have an Azure SQL Database named db1. Its baseline records response times measured with about 250 concurrent users of a reporting app. After an index change, a test with 25 concurrent users shows
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A nightly job must process 2 million orders within a three-hour window, and no user waits on individual results. You are adding a
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server. You need to chart Batch Requests/sec and Page life expectancy every 15 seconds alongside the Windows dis
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Performance Monitor shows that SQL Compilations/sec spikes every few minutes. You need to identify the individual statements that
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server and has heavy disk activity. You plan to record Buffer Manager and SQL Statistics counters together with
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server. CPU peaks at 90 percent several times a day. You need to determine whether the peaks rise with the volum
- 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
- You have an elastic pool named Pool1 that contains 15 Azure SQL databases. Your monitoring account can connect only to the database named db3 in Pool1 and can't connect to master. You need recent, gra
- You have an Azure SQL Database named db1. Six days ago, between 14:00 and 15:00, users reported slow responses. You need to review db1's CPU, data I/O, and log write percentages for that hour. You can
- You manage an elastic pool named Pool4 that hosts 12 Azure SQL databases. A dashboard query runs SELECT avg_cpu_percent FROM sys.dm_elastic_pool_resource_stats in a pooled database named db7 and label
- You have an Azure SQL logical server named SQL1 that hosts three elastic pools. Finance asks whether Pool2 has consistently used less than half of its resources over the last 10 days so that it can be
- 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
- You have an Azure SQL Managed Instance named SQLMI1 that hosts 20 databases. Auditors require the instance's vCore count, average CPU percentage, I/O requests, and reserved and used storage space to b
- You have an elastic pool named Pool1 that contains 10 Azure SQL databases. You need to send Pool1's Basic metrics and every pooled database's Errors and Deadlocks logs to a Log Analytics workspace. Yo
- You have an Azure SQL Database named db1. A diagnostic setting streams Basic metrics and the Errors category to a Log Analytics workspace. Analysts must also chart tempdb data file size and tempdb per
- You have an Azure SQL Managed Instance named SQLMI1 that hosts a database named db1. You open the diagnostic settings of db1 to stream deadlock, blocking, and timeout telemetry to a Log Analytics work
- 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
- You have an Azure SQL Database named db1 in the Standard service tier. A reporting script reads sys.dm_db_resource_stats in db1 and already returns avg_cpu_percent, avg_data_io_percent, and avg_log_wr
- You have an Azure SQL Database named db1 in the Premium service tier. Order-entry latency doubles every morning while the DTU percentage metric holds near 95 percent. A developer wants to rewrite the
- You have an Azure SQL Database named db1 in the Standard service tier. A custom dashboard calculates DTU percentage as the highest of avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, and a
- You have an Azure SQL Database named db3 in the Standard service tier. A metric alert on DTU percentage above 90 percent pages the on-call engineer several times a week. Each time, the engineer must o
- 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
- You have an Azure SQL Database named db1 on a small vCore compute size. While a geo-replica of db1 is being created, application latency rises. CPU percentage stays at 45 percent, but SQL instance CPU
- You have an Azure SQL Database named db1 that uses the vCore purchasing model. During a promotion, query latency doubles. Azure Monitor shows CPU percentage at 100 percent for 30 minutes, while SQL in
- You have an Azure SQL Database named db1 on a small vCore compute size. Users report slow queries during the nightly backup window, although CPU percentage stays near 40 percent. You are connected to
- You have an Azure SQL Database named db1. Since a new release, average query duration has risen 35 percent. During business hours, CPU percentage averages 82 percent and SQL instance CPU percent avera
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Users report slower reports since a new data load process started. You plan to use Performance Monitor counters and compare them
- You have an Azure SQL Database named db1 that has served a steady OLTP workload for two weeks. SQL instance memory percent stays between 96 and 98 percent. sys.dm_os_out_of_memory_events returns no ro
- You have an Azure SQL Database named db1 on a small compute size. Some reporting queries fail intermittently with insufficient memory errors while memory utilization is only 70 percent. A developer pr
- 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
- You have an Azure SQL Database named db1 that serves parallel reporting queries. The application's connection pool is capped at 60 connections. Last week, during a reporting burst, new requests were r
- You have an Azure SQL Database named db1. During peak hours, Workers percentage reaches 100 percent and some requests are rejected. A developer proposes raising the maximum connection pool size in the
- You have an Azure SQL Database named db1 with MAXDOP set to 0. During a report burst, clients receive error 10928 stating that the request limit has been reached, although the application opens only 1
- 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
- You have an Azure SQL Database named db1. You delete 200 GB of archived rows from db1. Afterward, the Data space used metric falls by about 200 GB, but Data space allocated remains unchanged. You need
- You have an Azure SQL Database named db1 on a vCore service objective that supports up to 1 TB of data. db1 was created with a 250 GB maximum data size. The Low data space alert fires because Data spa
- You have an Azure SQL Database named db1. Large sort and hash operations from a nightly job have filled the tempdb transaction log twice this month, causing the job to fail. The data files of db1 are
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. The transaction log file is on a data disk at LUN 3. Commits are slow, but Data Disk IOPS Consumed Percentage for LUN 3 is only 7
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During nightly index rebuilds, metrics split by LUN show Data Disk Bandwidth Consumed Percentage at 100 percent and Data Disk IOP
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with four premium data disks on LUN 0 through LUN 3. Data Disk IOPS Consumed Percentage averages 45 percent, yet queries that read
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. After an online sales campaign starts, Performance Monitor shows Batch Requests/sec rising from 900 to 2,700 and SQL Compilations
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A monitoring script queries sys.dm_os_performance_counters for the Batch Requests/sec counter in the SQL Statistics object and ch
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Over a stable workload period, Batch Requests/sec averages 500, SQL Compilations/sec averages 400, and SQL Re-Compilations/sec av
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A premium SSD data disk on LUN 2 uses credit-based bursting. After a 40-minute backup to that disk, throughput in MB/s drops to a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 on a B-series burstable VM. Query performance was good for the first two hours of a month-end run and then dropped sharply. CPU Cr
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 whose four premium SSD data disks use no host caching. After a 30-minute ETL burst, IOPS drop. VM Uncached Used Burst IO Credits P
- 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
- You have an Azure SQL Database named db1. After a firewall change, a branch office's clients can't connect for two hours, yet db1's Availability metric shows 100 percent for every minute because other
- You have a serverless Azure SQL Database named db2. Users report intermittent login failures during the morning, but the Availability metric for db2 shows a flat 100 percent line. A manager wants to c
- You have an Azure SQL Database named db1 on provisioned compute. At 09:15, a deployment rotated the application's password, but one web server kept the old password. In the minute starting at 09:20, e
- 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
- You have an Azure SQL Database named db1. CPU saturates at 100 percent for one or two minutes several times an hour, causing timeouts. An alert rule that uses Average aggregation of CPU percentage ove
- You have an Azure SQL Database named db1. Clients occasionally fail to connect because of internal service errors. You need a metric alert that warns when more than 10 such failed connection attempts
- You have an Azure SQL Database named db1. An existing metric alert rule on db1 fires correctly and its alerts appear on the Azure Monitor Alerts page, but the on-call engineers never receive an email
- 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
- You have an Azure SQL Database named db2 that was created yesterday. You added a metric alert rule for Deadlocks that uses a dynamic threshold with Medium sensitivity. The application already produced
- You have an Azure SQL Database named db1 whose connection volume peaks every Monday morning. A Successful Connections alert rule with a dynamic threshold has 10 days of history and still fires on most
- You have an Azure SQL Database named db4 that was created on Monday at 08:00. At 09:00 the same day, you create a metric alert rule with a dynamic threshold on CPU percentage, which is emitted every m
- You have an Azure SQL Database named db9 that was created this morning by restoring a template database. Policy requires an alert starting today whenever Workers percentage exceeds 80 percent for five
- 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
- You have a Business Critical Azure SQL Database named db6 monitored by an existing database watcher. The estate heatmap shows two hexagons for db6. A KQL query that averages CPU utilization for db6 re
- You have an existing database watcher that monitors an Azure SQL Database named db1. You are writing a KQL query against the sqldb_database_resource_utilization dataset to find the exact times when CP
- You have an Azure SQL Managed Instance named SQLMI1 monitored by an existing database watcher. An analyst sums the wait time column of every sqlmi_wait_stats row for one day and reports total waits th
- You have a Hyperscale Azure SQL Database named db5 that has two high availability secondary replicas and is monitored by an existing database watcher. A KQL query filters sqldb_database_resource_utili
- 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
- You have an elastic pool named Pool2 that an existing database watcher has monitored for six months as a SQL elastic pool target through an empty anchor database. None of the pooled databases is a dat
- You have an elastic pool named Pool3 that contains 30 databases and is monitored by an existing database watcher through an anchor database. Pool3's CPU is saturated. A colleague reviews the query sta
- You are documenting an existing database watcher before its retirement. It monitors a dense elastic pool of 200 small databases as one elastic pool target instead of adding each database. A reviewer a
- You have an Azure SQL Database named db2 in an elastic pool, and db2 is a database target of an existing database watcher. An analyst expected the sqldb_database_wait_stats dataset for db2 to show all
- 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
- Operators depend on nine log search alert rules that your team created from database watcher templates for blocked requests and low transaction log storage. You are planning the move away from databas
- On December 1, 2026, a colleague tries to add an Azure SQL Managed Instance to your organization's existing database watcher and to change the watcher's data store. Both changes fail. The watcher's da
- In June 2027, after database watcher retired, a compliance review needs CPU utilization for an Azure SQL Database named db1 from February 2027. The watcher's Azure Data Explorer database has a one-yea
- Your organization's existing database watcher monitors 18 Azure SQL databases. On November 10, 2026, a new single database named db40, which isn't in an elastic pool, goes into production and needs pe
- Your organization used database watcher with a dedicated Azure Data Explorer cluster as its data store. On May 5, 2027, after the retirement date, finance reports that the cluster is still billing. Yo
- 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
- You have an existing database watcher that monitors the Azure SQL databases of three business units across two subscriptions. You need to rank the queries that consume the most CPU across all monitore
- Your organization has an existing database watcher that monitors 40 Azure SQL databases, three elastic pools, and two Azure SQL Managed Instances, with an Azure Data Explorer data store. You need to s
- You have an existing database watcher that stores its monitoring data in an Azure Data Explorer database. An analyst needs an ad hoc analysis that correlates wait statistics with CPU utilization for s
- You have an existing database watcher that stores data from 20 Azure SQL databases in an Azure Data Explorer data store. Management wants one custom dashboard that combines the watcher's CPU and wait
- 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
- You have an Azure SQL Database named db2 monitored by an existing database watcher. Every night at 01:00, a SQL Agent-style automation job kills sessions that have run longer than 10 minutes, and the
- You have an Azure SQL Database named db5 monitored by an existing database watcher. During a period of heavy blocking and CPU near 100 percent, the watcher shows larger than expected intervals between
- You have an Azure SQL Database named db3 monitored by an existing database watcher. At 10:00 you change a database-scoped configuration on db3. At 10:01 the resource dashboard still shows the old sett
- You have an Azure SQL Database named db7 monitored by an existing database watcher. The resource dashboard shows no data points for db7 during the last 20 minutes, and a manager wants to declare an ou
- 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
- You have an existing database watcher with an Azure Data Explorer data store. A user-assigned managed identity named AlertId2 was set as the default alert rule identity by a user who doesn't have the
- You have an existing database watcher. After a colleague edited the KQL query of an alert rule that was created from a watcher template, its fired alerts list the Azure Data Explorer cluster as the af
- Your existing database watcher monitors Azure SQL databases and two Azure SQL Managed Instances. Its only CPU alert rule was created from the High CPU utilization template for the SQL database target
- You have an existing database watcher where db8 is a SQL database target that appears on the estate dashboard. Last month, a colleague customized the KQL query of the high CPU alert rule. The rule now
- 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
- You have an Azure SQL Database named db1. A script that worked on SQL Server runs the following statement in db1 and fails: CREATE EVENT SESSION s1 ON SERVER ADD EVENT sqlserver.sql_batch_completed; Y
- You have an Azure SQL logical server named SQL1 that hosts three databases named db1, db2, and db3. You need to capture deadlock events from all three databases by using Extended Events. The solution
- You have an Azure SQL Managed Instance named SQLMI1 that hosts 25 user databases. You need to capture lock escalation events from every database on SQLMI1 by using Extended Events. The solution must f
- You are moving monitoring scripts from SQL Server to an Azure SQL Database named db1. One script lists defined and running Extended Events sessions by querying sys.server_event_sessions and sys.dm_xe_
- You have an Azure SQL logical server named SQL1 that hosts db1 and db2. You create an Extended Events session in db1 that captures sql_batch_completed events without a predicate. The session runs, but
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You create an event session that captures the sqlserver.sql_batch_completed event. Analysts must see which session ran each batch
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application uses separate connections to submit an order and then update inventory. You are designing an event session to diag
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You plan to build an event session for tempdb spills, but you don't know the exact names of the relevant events or the packages t
- You have an Azure SQL Database named db1 that runs thousands of batches per minute. You create an event session that captures sql_batch_completed events to an event_file target, but the files grow qui
- 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
- You have an Azure SQL Managed Instance named SQLMI1. Last night, an application reported several deadlock errors. No user-defined Extended Events sessions exist on SQLMI1. You need the deadlock graphs
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with 15 event session definitions. A health script must report which of these sessions are currently running so operators can rest
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An event session writes deadlock reports to an event_file target. Operators watch the file and report that a deadlock often takes
- 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
- You have an Azure SQL Database named db1. An event session in db1 writes .xel files to a blob container, and a database-scoped credential for the container already exists. Analysts must query the capt
- You have an Azure SQL Database named db1. A running event session named s1 in db1 uses a ring_buffer target for a short troubleshooting exercise. You hold VIEW DATABASE STATE in db1. You need to retri
- You have an Azure SQL Database named db1. Compliance requires that captured login failure events be kept for 30 days and remain available after the session is stopped. You are adding a target to an Ex
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An event session uses a ring_buffer target with MAX_MEMORY = 8192 KB. When you read the target_data XML, the output is often cut
- 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
- You have an Azure SQL Database named db1. You create a database-scoped credential named https://st1.blob.core.windows.net/xe/ and an event session in db1 with an event_file target in the xe container.
- You have an Azure SQL Database named db1. An event session in db1 writes to an event_file target in a blob container by using a SAS token stored in a database scoped credential named for the container
- You have an Azure SQL Database named db1 on a logical server that has a system-assigned managed identity. An event session in db1 must write to a blob container by using managed identity authenticatio
- You have an Azure SQL Database named db1 on a logical server whose managed identity holds Storage Blob Data Contributor on a container in a storage account named st2. The credential is named for the c
- 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
- Your organization is writing a monitoring standard for SQL Server on Azure Virtual Machines and Azure SQL Managed Instance. Several teams still create server-side traces with SQL Trace stored procedur
- You have an Azure SQL Database named db1. A developer wants to trace the statements an application sends to db1 during a test run and starts SQL Server Profiler, but the connection fails with a mislea
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During incidents, DBAs open the T-SQL session of the SSMS XEvent Profiler to watch incoming statements. A security review flags t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A DBA needs a quick live view of incoming queries during a short incident, similar to the old Profiler experience. The solution m
- 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
- You have an Azure SQL Database named db1. A database-scoped event session with a ring_buffer target runs in db1. An analyst named User1 must query sys.dm_xe_database_session_targets in db1 to read the
- You have an Azure SQL Database named db1. A support engineer named User2 must be allowed to create new database-scoped event sessions in db1 for an investigation. Another DBA owns the existing session
- You have a SQL Server 2022 instance on an Azure virtual machine named SQLVM1. A monitoring account named Mon1 must read the target data of running server-scoped event sessions by querying sys.dm_xe_se
- You have an Azure SQL Managed Instance named SQLMI1. A junior DBA must be able to create, start, stop, and modify server-scoped event sessions on SQLMI1 during troubleshooting. The DBA must not be add
- 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
- You have an Azure SQL Database named db1 with 12 started event sessions, each created with MAX_MEMORY = 10 MB. Starting a 13th session fails with an error that references a memory limit. A colleague s
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that processes 20,000 batches per second. You need an event session that samples lock acquisitions for trend analysis in productio
- You have an Azure SQL Database named db1. An auditor asks you to prove that an Extended Events session with an event_file target in Azure Storage captures every login failure. You created the session
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 in a test environment. During a one-hour compliance test, an event session that writes to a local event_file target must not lose
Monitor and optimize query performance
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Configure database solutions for optimal performance
Read full chapterUnlock 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 chapterCheat 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job uses a daily schedule that repeats every 10 minutes. Scheduled starts must occur only between 01:00 and 05
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You created a schedule named Sched1 by running msdb.dbo.sp_add_schedule. Sched1 is enabled. A job named Job1 has never started on
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a daily schedule for a SQL Server Agent job by using sp_add_schedule with @freq_type = 4 and @freq_interval = 1.
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent maintenance job must run once at 22:00 every other day. You are creating the schedule by using sp_add_schedule with @freq_subday
- You have an Azure SQL Managed Instance named SQLMI1. You run sp_add_schedule with @freq_type = 4, @freq_subday_type = 2 and @freq_subday_interval = 5 to start a polling job every five seconds. The sch
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job must run on Sunday every second week. You are creating the schedule by using sp_add_schedule with @freq_type = 8 and @freq_i
- You have an Azure SQL Managed Instance named SQLMI1. On Wednesday, November 11, 2026, you run sp_add_schedule with @freq_type = 8, @freq_interval = 2, @freq_recurrence_factor = 2 and @active_start_dat
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You review a schedule that a former administrator created for a statistics job by running sp_add_schedule with @freq_type = 32, @
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job must run every Monday, Wednesday and Friday at 23:00. You are creating the schedule by using sp_add_schedu
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. At 10:00 on Monday, November 9, 2026, you attach a schedule that runs every other Monday at 08:00 to a new job named Job1. Job1 m
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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A schedule named Sched1 is attached to four jobs. One of the jobs is currently running. You run sp_update_schedule to change the
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Five SQL Server Agent jobs share a schedule named Nightly that starts at 01:00. One job named Job3 must start at 03:00 instead. T
- You have an Azure SQL Managed Instance named SQLMI1. A schedule named Sched1 is attached to Job1 and Job2. Job1 must permanently stop using Sched1 and will later get a different schedule. Job2 keeps r
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A schedule named Sched1 was disabled during an outage. Afterward, you edited Sched1 to change its start time. The jobs attached t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You disabled a schedule named Sched1, which is attached to a job named Job1. Job1 has no other schedules. The job history shows t
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A data correction job named Job1 must run exactly once, at 23:00 on December 5, 2026, after a vendor release. Job1 must not run again unless an adm
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 runs a load procedure for the EU region every night and takes about three hours. A US load that uses the same pr
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A cache-warming job named Job1 must run each time the SQL Server Agent service starts and also every day at 06:00. The solution m
- You have an Azure SQL Managed Instance named SQLMI1. A job named Job1 uses a schedule of type Start automatically when SQL Server Agent starts. Job1 ran at 03:12 today, although nobody started it and
- You have an Azure SQL Managed Instance named SQLMI1. A job named Job1 uses a Start automatically when SQL Server Agent starts schedule and is idle. You need to run Job1 now to test it. The solution mu
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A stored procedure in a database named Sales uses xp_logevent to log message 60000. An alert for message 60000 with @database_nam
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application raises error 50010 WITH LOG when an import batch is rejected. You need a cleanup job named Job1 to run as soon as
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application raises user-defined message 50001 at severity 16 by using RAISERROR without WITH LOG. An error number alert for 50
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts a production database named Prod and a reporting database named Rpt. You need an alert for severity 17 errors that occu
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts databases named db1 and db2. You are creating a performance condition alert that must fire when the transaction log of
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with a performance condition alert on a lock wait counter. During a test, the counter crosses the threshold, but the alert respons
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a performance condition alert on the SQLServer:Transactions object. The alert must fire when the free space in t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During memory pressure, pages leave the buffer pool too quickly. You need a SQL Server Agent alert that notifies Operator1 when p
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You need an Agent job to run whenever a table in the Sales database is altered. Altering a table raises no error, and the event i
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that uses a WMI event alert to start a job when a DDL event occurs. You plan to migrate the databases and Agent jobs to an Azure S
- You have SQL Server on Azure Virtual Machines instances named SQLVM1 and SQLVM2. On SQLVM1, you create a WMI event alert whose namespace points to the server events namespace on SQLVM2. The alert neve
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are creating a SQL Server Agent WMI event alert in SSMS. The alert must respond to ALTER_TABLE events in a database named Sal
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent on SQLVM1 already sends email through Database Mail. You need to define a single alias for the database team so
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends notifications by using a Database Mail profile named Profile1 that contains one account for an SMTP server
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Database Mail on SQLVM1 has an account and a profile, and test messages sent by using sp_send_dbmail arrive. Job1 is configured t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends job notifications by using Database Mail. The SMTP relay that the Database Mail account uses requires a us
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. In SQL Server Agent Properties, you select Enable mail profile on the Alert System page, choose Database Mail and select a profil
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. SQL Server Agent sends email through Database Mail to an operator named Operator1. You need Operator1 to be emailed whenever a jo
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Export1 creates a nightly file. An operator named Team1 starts a downstream process from the file. T
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are scripting job notifications by using sp_update_job. A job named Job1 must send email to an existing operator named DBA1 w
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 already names an email operator. You need to use sp_update_job so that the operator receives an
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 emails an operator named Operator1 after every run. Operator1 now wants email only when Job1 fa
- 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
- You have an Azure SQL Managed Instance named SQLMI1. A Database Mail account named Account1 and a profile named AzureManagedInstance_dbmail_profile both exist. Agent job notifications are not sent bec
- You have an Azure SQL Managed Instance named SQLMI1. You plan to configure email notifications for SQL Server Agent jobs. When you try to create a Database Mail account, the Database Mail procedures a
- You have an Azure SQL Managed Instance named SQLMI1. SQL Server Agent notifications use AzureManagedInstance_dbmail_profile, which sends through an authenticated SMTP relay on port 587. The messages f
- You have an Azure SQL Managed Instance named SQLMI1 with a working AzureManagedInstance_dbmail_profile. An operator named Operator1 has an email address and a pager address. Job1 is configured to page
- You have an Azure SQL Managed Instance named SQLMI1. You migrated Database Mail from SQL Server, including a profile named DBA_Mail that contains a working SMTP account. SQL Server Agent job email not
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentOperatorRole. A job named Job2 is owned by User2. User1 must disable Job2 so that it does not run du
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A developer who owns 12 SQL Server Agent jobs is leaving, and the jobs must be reassigned to a login named svcJobs. User1, a memb
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin grants SQLAgentUserRole access to a CmdExec proxy named Proxy1. User2 is a member of SQLAgentOperatorRole only, and Us
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole in msdb and owns three SQL Server Agent jobs. User1 must be able to delete the job history
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentOperatorRole in msdb. User1 runs msdb.dbo.sp_add_alert to create a severity alert, and the statement
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts a database named db1. A script adds a Transact-SQL step to Job1 by using sp_add_jobstep without specifying a database.
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by User1, who isn't a sysadmin. A script set @database_name = N'db1' and @database_user_name = N'Report
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by a sysadmin login and has a Transact-SQL step in db1. The step does not specify a database user. An a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1, owned by a non-sysadmin login named User1, has a Transact-SQL step that fails with a permission error on Table2 in db1. An
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A job named Job1 is owned by a sysadmin login. A Transact-SQL step in Job1 must run in db1 under the permissions of a database us
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1 has a PowerShell step that runs as a proxy named Proxy1. After a security review, the step fails because Proxy1 no longer ha
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job1 has an Operating system (CmdExec) step that runs as a proxy named Proxy1 and copies files to a share named \\FS1\Exports. Th
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A CmdExec step in Job1 runs a program named extract.exe from D:\Tools by using a proxy. The step fails because the program cannot
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole and is not a sysadmin. User1 must create a job step that runs an operating system command.
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin-owned job has a CmdExec step that copies files to \\FS1\Exports and runs without a proxy. The step fails with access d
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs 25 SQL Server Agent jobs. You need to use Transact-SQL to list every failed job and failed job step recorded in the job
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs 40 SQL Server Agent jobs. Users report slow queries during business hours. You need to identify which Agent jobs on SQLV
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 has three steps. You need to use Transact-SQL to return the runtime state of Job1, including th
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Overnight, the SQL Server Agent service on SQLVM1 stopped unexpectedly and was restarted 30 minutes later. You need to determine
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. The copy step of an Agent job named NightlyCopy retries up to three times when the network drops. A weekly report must show how m
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 has three steps. Step 2 failed last night, but Job1 reported success and no failure email was s
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job named Job1 has five steps. Step 5 removes staging tables. You need to ensure that if step 1 fails, Job1 skips steps 2 throug
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A deployment script creates Job1 and adds three Transact-SQL steps by calling sp_add_jobstep with only @job_name, @step_name, @su
- You have an Azure SQL Managed Instance named SQLMI1. Job1 has three steps. Step 3, the last step, refreshes an optional reporting summary table and must still run after step 2 succeeds. When step 3 fa
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job step runs a vendor executable as an Operating system (CmdExec) step. The executable returns exit code 1 wh
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A script created a SQL Server Agent job step by using sp_add_jobstep with only the job name, step name, subsystem and command. Th
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job step is configured with three retry attempts and an On failure action of Go to step 5. The step fails on every attempt. When
- You have an Azure SQL Managed Instance named SQLMI1. A SQL Server Agent job named Job1 has four steps. Only step 3 calls a remote service that occasionally times out. You need to retry the remote call
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent job named Job1 copies data from a linked server. The copy step occasionally fails because of a brief network o
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are adding a step to a SQL Server Agent job named Job1 by using sp_add_jobstep. If the step fails, it must be retried up to f
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. User1 is a member of SQLAgentUserRole and owns a SQL Server Agent job named Job1. User1 needs to capture the full output of the T
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A sysadmin owns a SQL Server Agent job with a Transact-SQL step that returns several result sets. The step is configured to inclu
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A SQL Server Agent Transact-SQL job step returns nvarchar(max) values. The query returns complete values in SSMS, but the values
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Several SQL Server Agent job steps on SQLVM1 are configured to log their output to a table. You need to query the logged output a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You are adding a Transact-SQL step to a SQL Server Agent job by using sp_add_jobstep. The step must log its output to a table in
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Agent notifications remain queued, sysmail_event_log has no entries, and sysmail_allitems shows no status updates. The is_broker_
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Agent notifications in sysmail_allitems have shown the retrying status for several hours, and none of them has reached the SMTP s
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A maintenance job emails an operator when it completes. msdb.dbo.sysmail_sentitems lists last night's notification, but the opera
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Several Agent job notifications appear in msdb.dbo.sysmail_faileditems. You need to retrieve the error message that the Database
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Job failure notifications have stopped arriving, and Service Broker is enabled in msdb. You need to determine whether Database Ma
Automate deployment of database resources
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Create and manage database tasks in Azure
Read full chapterUnlock 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 chapterCheat 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
- You have an Azure SQL Database named db1 in a failover group named FG1 with a geo-secondary in another region. You create an Azure Monitor alert that fires when the geo-secondary falls more than 30 se
- You are reviewing a proposal for SQL1, which has a one-hour RTO that is currently missed because restoring backups takes three hours. The proposal changes log backups from every 30 minutes to every 5
- App1 has the following requirements: it must be available within 2 hours of an outage, at most 15 minutes of transactions may be lost, backups must be kept for 35 days, and maintenance runs in a weekl
- You run a DR test for App1, which uses a failover group named FG1. The database failover completes in 40 seconds, but App1 doesn't serve users again until its web tier restarts in the DR region 25 min
- You are writing the DR plan for App1, which uses an Azure SQL Database failover group named FG1. The business sets a recovery time objective of two hours. The database team says the two hours end when
- 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
- You change the Azure secondary replica of AG1, named SQLVM6, from asynchronous commit to synchronous commit. Since the change, commit times on the primary replica, SQLVM5, have roughly doubled. You ne
- You plan a replica for db1 in a second datacenter 900 km away. Round-trip latency is 25 ms. The business requests zero data loss but also requires that commit response times don't increase. You need t
- An availability group named AG1 has its primary replica on SQL1 on-premises and an asynchronous-commit secondary replica on SQLVM5 in Azure. The on-premises site fails and will stay offline for severa
- You have an availability group named AG1 with a synchronous-commit secondary on SQLVM2 in the same region. The secondary is in the SYNCHRONIZED state. You need to describe what this configuration guar
- You have an on-premises availability group named AG1 for an OLTP application that is sensitive to commit latency. You plan to add a DR replica on an Azure VM. The network round-trip time to Azure is 4
- 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
- You have SQL Server Enterprise edition on SQLVM1. The instance hosts App2's two databases and 14 databases for other applications. A colleague proposes a failover cluster instance so that App2's datab
- You have SQL Server 2022 Standard edition on SQLVM1 and SQLVM2. You need to protect a database named db1 with automatic failover. A reporting team also asks for read-only queries against the secondary
- You have SQL Server 2019 Standard edition on two Azure VMs named SQLVM3 and SQLVM4. A single database named Sales must fail over automatically to SQLVM4 if SQLVM3 fails. No secondary reads are needed,
- You plan an availability group named AG1 on SQL Server 2022 for App1. The App1 databases rely on several SQL logins and SQL Server Agent jobs. You need those logins and jobs to be available on whichev
- You have a two-node failover cluster instance named FCI1 on Azure VMs in one region that uses Azure shared disks. A new requirement states that the databases must be recoverable from an independent co
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
- You have a distributed availability group named DAG1. AG1 on-premises is the global primary, and AG2 in Azure has a primary replica on SQLVM3 and a secondary replica on SQLVM4. You need to determine h
- Your company runs an availability group named AG1 on Linux with Pacemaker on-premises and plans an availability group named AG2 on Windows Server failover clusters in Azure. The on-premises team and t
- You manage a distributed availability group named DAG1 that links AG1 and AG2. Each availability group runs on its own Windows Server failover cluster. You need to view and change the configuration of
- You have an availability group named AG1 on an on-premises Windows Server failover cluster and an availability group named AG2 on a separate cluster in Azure. You need to link AG1 and AG2 by running C
- You have a distributed availability group named DAG1. AG1 in your datacenter is the global primary, and AG2 in Azure is the secondary availability group. A reporting app in Azure connects through AG2'
- 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
- You plan log shipping for db4 from SQL4 in your on-premises datacenter to SQLVM4 on an Azure VM for cross-site DR. The backup job will write log backups to a share on SQL4's network. The network team
- You plan to add an Azure replica to an on-premises domain-based availability group named AG1. The solution must meet the following requirements: - The cluster nodes must communicate privately across s
- You plan to add a replica in an Azure virtual network to an on-premises domain-based availability group named AG1. The cluster nodes on-premises and in Azure must communicate privately across the two
- You have a hybrid availability group named AG1 with a listener named AG1-L. DNS is hosted only on two on-premises DNS servers. During a DR test, AG1 fails over to the Azure replica, but clients in Azu
- You plan to extend an on-premises availability group named AG1 on Windows by adding a secondary replica on an Azure VM, instead of creating a second availability group. The on-premises nodes and the A
- Managed Instance link recovery capabilities depend on the SQL Server version and update policy
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
- You use Managed Instance link to replicate db1 from SQL1, which runs SQL Server 2019, to an Azure SQL Managed Instance named SQLMI1. You fail over db1 to SQLMI1 to complete a migration. You need to id
- You have SQL2, which runs SQL Server 2025 Standard edition and hosts db2. An existing Azure SQL Managed Instance named SQLMI2 in the General Purpose tier uses the SQL Server 2022 update policy. You pl
- You need to design DR between an on-premises SQL Server and Azure SQL Managed Instance by using Managed Instance link. The solution must meet the following requirements: - Allow failover to Azure and
- You have SQL1, which runs SQL Server 2022, and an existing Azure SQL Managed Instance named SQLMI1 that uses the Always-up-to-date update policy. You plan to replicate db1 to SQLMI1 by using Managed I
- 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
- You have a SQL Server VM named SQLVM1 that runs Standard edition and has custom OS settings and installed agents. You need DR in another region that recovers the whole VM, not only the databases. The
- You protect SQLVM1, which runs SQL Server, by using Azure Site Recovery replication to a DR region. The DR plan requires that each recovery point used for a failover captures the disk data together wi
- A consultant proposes Azure Site Recovery with a recovery plan for SQLVM1 and its app VMs and claims that it gives db1 a synchronized secondary with no data loss on failover. db1 has a business RPO of
- You use Azure Site Recovery to replicate SQLVM1, which runs SQL Server, and two web VMs named Web1 and Web2 to a DR region. During a regional failover, SQLVM1 must start first, a script must then upda
- You protect SQLVM1 and its application VMs with Azure Site Recovery and a recovery plan named RP1. Auditors require a quarterly rehearsal of RP1 in the DR region. Production replication must continue
- 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.
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
- You have one SQL Server VM named SQLVM7 that is pinned to availability zone 1 and hosts db7. The operations lead assumes that a zonal VM fails over to another zone on its own. You need db7 to stay ava
- SQLVM8 and SQLVM9 host an availability group in an availability set with two fault domains. A new policy requires surviving the loss of a whole datacenter in the same region, which supports availabili
- You have an availability group named AG1 with synchronous-commit replicas on SQLVM1 in zone 1 and SQLVM2 in zone 2 of the West Europe region. A new requirement states that the databases must remain re
- Your company is building a new SQL Server deployment on two Azure VMs in the Central US region for an availability group named AG2. The design must keep running if power, cooling, or networking fails
- You plan an availability group for App4 on two SQL Server VMs in an Azure region that has no availability zones. The design must reduce the chance that a hardware failure in one rack or a planned host
- You are planning SQL Server HA/DR on Azure VMs for an availability group named AG3 in North Europe. The solution must meet the following requirements: - Fail over automatically with no data loss if on
- 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
- Yesterday the SQL Server service on SQLVM2 stopped responding for 45 minutes. Azure reported SQLVM2 as running and healthy the whole time, and no automatic recovery started. You need to explain why Az
- A project manager states that placing SQLVM1 and SQLVM2 in an availability set gives a 99.95 percent SLA, so the SQL Server instances on them need no further HA configuration to meet App2's RTO. Each
- SQLVM2 hosts one database named db2. Last week, the SQL Server service on SQLVM2 hung for 30 minutes while Azure reported the VM as healthy. You need db2 to fail over automatically to a second VM when
- SQLVM1 and SQLVM2 in East US run the SQL Server instance for App9, which relies on instance-level logins and many SQL Server Agent jobs. Last month, the SQL Server service failed while its VM stayed r
- 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
- You have a General Purpose Azure SQL Database named db10 in Canada Central. A reporting app in Canada Central needs a read-only copy of db10 that stays current within seconds and has the lowest networ
- A colleague designed db1 with its primary on Server1 and a geo-secondary on Server2. Both logical servers are in Australia East. The colleague claims this design provides both zone isolation and regio
- You have an Azure SQL Database named db1 on a logical server in East US. A geo-secondary of db1 on another logical server in East US serves reporting queries. You need to ensure that db1 can be recove
- You have an Azure SQL Database named db1 in Central US. A geo-secondary on another logical server in Central US serves read-only reporting for App2. Management now requires a failover target for a reg
- You have a Business Critical Azure SQL Database named db9 in France Central with a geo-secondary on another logical server in France Central. db9 must keep running through an availability zone failure
- 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
- You are choosing a vCore service tier for an Azure SQL Database named db11 that runs an order-entry workload with a very high transaction rate. During failovers and maintenance, the database must suff
- You have a Hyperscale Azure SQL Database named db12 that has zero high-availability replicas. After a hardware failure on the primary compute replica, recovery took longer than expected. The same data
- You have a Business Critical Azure SQL Managed Instance named SQLMI1. During failover tests, the workload resumes quickly with little performance impact. To reduce cost, the team proposes moving SQLMI
- You have a General Purpose Azure SQL Database named db1. During an unplanned failover, the compute node that hosts db1 fails and the service starts a new database engine process on another node. You n
- 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
- You have a Hyperscale Azure SQL Database named db14 that was created without zone redundancy. It has one high-availability replica and uses geo-zone-redundant backup storage. You need db14's workload
- You have a Standard S6 Azure SQL Database named db1. To meet a requirement that db1 keeps serving App1 during a zone outage, an engineer changes db1's backup storage redundancy to zone-redundant stora
- You have a General Purpose Azure SQL Database named db1 on a logical server named SQL1 in a region that supports availability zones. db1 isn't zone-redundant. You need to make db1 zone-redundant by us
- You have a zone-redundant Hyperscale Azure SQL Database named db13 in a region with three availability zones. App13 commits orders to db13 around the clock. An auditor asks what happens to committed t
- 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
- An engineer changed the backup storage redundancy of SQLMI6, a General Purpose Azure SQL Managed Instance, to GZRS. The engineer now reports SQLMI6 as protected against a whole-zone outage. Zone redun
- You have a Business Critical Azure SQL Managed Instance named SQLMI1 that uses local redundancy. A colleague says SQLMI1 already survives a whole-zone outage because Business Critical keeps several da
- SQLMI4 is a General Purpose Azure SQL Managed Instance in resource group RG4. Its backup storage redundancy is already set to zone-redundant storage, and its region supports zone redundancy. In Azure
- You have a locally redundant Business Critical Azure SQL Managed Instance named SQLMI2. It must stay available through the loss of an entire availability zone in its region, without failing over to an
- You plan a Next-gen General Purpose Azure SQL Managed Instance named SQLMI5 that must be zone-redundant. The target region supports zone redundancy for SQL Managed Instance in general. You need to con
- You have a General Purpose Azure SQL Managed Instance named SQLMI3 in a region that supports zone redundancy. SQLMI3 uses locally redundant backup storage. You must enable zone redundancy for SQLMI3 s
- 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
- You have an Azure SQL Database named db1 that supports a payment application. The application must be back online within 30 minutes of a regional outage in West Europe, with predictable downtime. Back
- You have an Azure SQL Database named db1 that is 3 TB. The documentation lists a typical RTO for geo-restore. During a large regional outage, a restore of db1 takes much longer than that value. You ne
- You have an Azure SQL Database named pay1 for a payments service in East US 2. To save cost, the team proposes creating a geo-secondary of pay1 in West US only after an outage in East US 2 is declared
- Your team proposes meeting a one-hour regional RTO for an Azure SQL Database named db1 by scripting geo-restore with Bicep and Azure CLI, which avoids paying for a secondary database. db1 is 1.5 TB. Y
- You have an Azure SQL Database named arch2 for an internal archive app in East US 2. After a regional outage, the business accepts recovery from the most recent geo-replicated backups and a recovery t
- 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
- App6 runs on web servers in East US and uses db6, which is in a failover group with West US. An outage hit only SQL Database in East US, so db6 failed over and is now primary in West US. SQL Database
- You have App1 deployed in East US and West US. Its Azure SQL Database named db1 is in a failover group across the two regions. You need users to be routed automatically to the West US web front end wh
- App1 runs on web servers in East US and uses an Azure SQL Database named db1 in a failover group with West US. During a DR test, db1 fails over successfully, but users still can't use App1 because Eas
- App1 stores uploaded documents in a storage account in East US and their metadata in an Azure SQL Database named db1 that is in a failover group with West US. You need to ensure that App1 can serve do
- Your DR runbook states that App1's RTO is met because its failover group promotes db1 in under one minute. App1's web front end and its identity configuration are deployed only in the primary region.
- 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
- You have a zone-redundant General Purpose Azure SQL Database named db1. You run Invoke-AzSqlDatabaseFailover to test App1. Besides session reconnects, you need to check another effect that this test c
- Pool7 is an elastic pool that holds 15 databases for App8. Before release, the test plan requires one user-initiated local failover of the whole pool as a single operation, so that all 15 databases fa
- You have a single Azure SQL Database named db7 on a logical server named SQL7. Before App7 goes live, you need to test how App7's retry logic handles a user-initiated failover of db7 within its region
- A developer plans to test App1's handling of brief connection drops by running a forced failover of db1's failover group to the secondary region every week. App1 only needs to handle transient reconne
- You test App1's retry logic by running Invoke-AzSqlDatabaseFailover against db1, a General Purpose Azure SQL Database whose primary must stay in East US. Five minutes later, you run the same command a
- 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
- You have an Azure SQL Database named db1 that is zone-redundant and in a failover group with another region. To cut costs, a manager proposes reducing db1's short-term backup retention to one day beca
- You are designing protection for an Azure SQL Database named db1. The solution must survive a zone outage, recover from a regional outage, and recover the database to a point before an accidental bulk
- You have an Azure SQL Database named db12 with a geo-secondary in another region. Short-term retention is 35 days, and long-term retention keeps weekly full backups for 12 weeks. Auditors find that a
- You have an availability group named AG6 with a synchronous-commit secondary on SQLVM9 that is SYNCHRONIZED. A developer drops Table9 in db9 on the primary, and the transaction commits. A colleague su
- db14 runs in an availability group on SQL Server VMs. It uses the FULL recovery model, with log backups every 10 minutes. At 14:32, a developer ran an UPDATE without a WHERE clause on dbo.Rates, and t
- 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
- You have a failover group named FG1 for an Azure SQL Database named db1. Both the primary and secondary servers are healthy. You must run a production DR drill each quarter, and the business doesn't a
- During a DR drill, you start a planned failover of a failover group named FG1. Replication lag to the secondary is higher than usual because of a heavy batch load on the primary. The failover takes lo
- You plan a DR drill for a failover group named FG1 on the logical server Server1. Both servers in FG1 are reachable. You will run Switch-AzSqlDatabaseFailoverGroup against the secondary server, and th
- You have a failover group named FG1 for db1 and db2 on Azure SQL Database. The solution must meet the following requirements: - Run a quarterly DR drill in production. - Lose no committed data during
- Your team plans a DR drill for a failover group named FG1. A team member proposes a forced failover because it switches roles faster than a planned failover. Both regions are healthy. You need to expl
- You run a planned failover of a failover group named FG1 during a DR drill. The Azure portal shows that the operation completed and that the secondary server is now the primary. You need to confirm th
- 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
- You run a geo-restore drill against a test copy named sales1test on Server5. You need to simulate the outage so the test application gets connectivity failures. The simulation must be easy to reverse
- Your team proposes that the first geo-restore rehearsal for an Azure SQL Database named db1 should be a production cutover to the most recent geo-replicated backup, so the test is realistic. You need
- Your team schedules its first geo-restore drill for an Azure SQL Database named sales1 that App5 uses in production. The drill must avoid any data loss for production users and must follow the documen
- During a geo-restore drill, you restore a test copy of db1 to a server named Server3 in another region. The test instance of App1 still points to the original test server. You need to complete the nex
- You finish a geo-restore drill for a test copy of db1. The test application now connects to the restored database on another server. The business RPO for db1 is two hours. You need to decide whether t
- 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
- You have a failover group named FOG1 for Azure SQL Managed Instances named SQLMI1 and SQLMI2. You performed a failover from SQLMI1 to SQLMI2 one minute ago, and DNS already points to SQLMI2. You plan
- Your failover group FG1 failed over to West US during an outage. East US has just come back online, and its database is still catching up to the West US primary. A manager asks you to fail back right
- After a regional outage, you forced FG1 to fail over from East US to West US. East US is healthy again, and its database is now a synchronized secondary. You need to move the primary role back to East
- You have a failover group named FG1 for db1. During a regional outage in East US, you performed a forced failover to the secondary in West US. The East US region has now recovered. You need to identif
- After a regional outage, you forced a failover group named FG1 from East US to West US, and App1 has written to West US for six hours. East US recovers ten minutes later. A colleague proposes an immed
- 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
- A nightly job on SQLVM3 runs RESTORE VERIFYONLY ... WITH CHECKSUM against the latest full backup of db3, and it always succeeds. An auditor asks you to prove that the data in that backup has no struct
- You are writing a quarterly recovery rehearsal plan for db1 on SQL Server. The plan must prove that the database can be recovered and used within the RTO. Recovery uses full, differential, and log bac
- An auditor reviews your backup evidence for db1. Each night, a job runs RESTORE VERIFYONLY WITH CHECKSUM on the latest full backup and records success. The auditor accepts this as proof that db1 is re
- A database administrator runs RESTORE VERIFYONLY FROM DISK against a full backup of db1 that was taken WITH CHECKSUM. The command succeeds. The backup is stored on a network share. You need to describ
- Your recovery runbook for db1 on SQLVM1 uses one full backup, one differential backup, and 24 log backups stored in Azure Blob Storage. The RTO is two hours. Nightly RESTORE VERIFYONLY checks of each
- 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
- During a DR load test of an Azure SQL Database named db17, write throughput on the primary drops. Queries show the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type in sys.dm_exec_requests. You need to
- A DR test for an Azure SQL Database named db1 opened one idle connection to its geo-secondary after failover, and the team approved the design. The secondary has half the compute of the primary. You n
- In production, App15 writes orders to an Azure SQL Database named db15 while its reports run on db15's readable geo-secondary. The draft DR test replays only the order writes against the primary. The
- You fail over a failover group named FG1 during a DR test. App1 connects successfully to the new primary in West US. The test plan must prove that the recovered environment can run the production work
- Order processing on SQLVM1 commits to an availability group named AG5. You plan to move its synchronous-commit secondary from the same zone as the primary to another availability zone. The order team
- You are writing an HA/DR test procedure for db16 with a replica at a remote site. The procedure must meet the following requirements: - Measure how the replica placement affects commit performance. -
Plan and perform backup and restore of a database
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.
Configure HA/DR for database solutions
Read full chapterUnlock with Premium — includes all practice exams and the complete study guide.