Microsoft Azure Database Administrator Associate

Question 1

A query on an Azure SQL Database joins a large orders table to a filtered set of customers. The number of qualifying customer rows varies dramatically between executions depending on the WHERE predicate values. You observe that some executions use a nested loops join (fast for small inputs) while others suffer badly because a nested loops plan was cached and reused when the input was actually large. The database is on a recent compatibility level. Which Intelligent Query Processing feature will let the engine defer the choice of physical join type until the actual number of rows is known at runtime?

Accepted Answer

Adaptive joins

Answer

Memory grant feedback

Answer

Interleaved execution

Answer

Table variable deferred compilation

Question 2

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure hosting a 900 GB database. You must add a third secondary replica in a different region. The corporate WAN link to that region is reliable but you want to avoid manually backing up the database to a file share, copying it across the WAN, and restoring it on the new replica. You need the new replica to be initialized with minimal administrative steps directly over the AG's data movement channel. Which method should you configure when adding the secondary replica?

Accepted Answer

Automatic seeding on the availability group replica

Answer

Full and log backups with the Join only option in the New Availability Group Wizard

Answer

Log shipping to pre-stage the database, then join it to the availability group

Answer

Azure Blob Storage backup with URL-based restore on the secondary

Question 3

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure, with both replicas in the same region and Availability Zone-aligned. The business requires zero data loss (RPO = 0) on automatic failover, but a recent audit shows transaction latency increased after the AG was deployed. The primary replica currently uses asynchronous-commit mode with manual failover. Which configuration change best meets the RPO = 0 automatic failover requirement?

Accepted Answer

Set both replicas to synchronous-commit mode and configure automatic failover mode.

Answer

Keep asynchronous-commit mode but change the failover mode to automatic.

Answer

Add a third asynchronous-commit replica in a paired region and enable automatic failover to it.

Answer

Set the primary to synchronous-commit and leave the secondary in asynchronous-commit mode with automatic failover.

Question 4

You manage a two-node Always On availability group spread across two Azure regions using SQL Server on Azure VMs. The primary is in East US and the secondary is in West US. During a recent East US zone disruption, the Windows Server Failover Cluster lost quorum and both nodes went offline even though the West US node was healthy. You need to choose a quorum configuration that keeps the cluster online when a single region or node is lost, while minimizing cost and administrative overhead. What should you configure?

Accepted Answer

Configure a cloud witness backed by an Azure Storage account placed in a third region

Answer

Configure a disk witness on Azure shared premium storage attached to both nodes

Answer

Add a file share witness hosted on the East US primary node

Answer

Remove the witness and rely on Node Majority quorum for the two-node cluster

Question 5

You administer an Always On availability group hosted on two SQL Server on Azure VMs deployed across two Azure regions, placing the replicas in different subnets. The availability group listener has two IP addresses, one in each subnet. After a failover to the secondary region, a .NET application experiences long connection delays (roughly 20 seconds) before it eventually connects to the listener. You must minimize these connection times without changing the network topology. What should you do?

Accepted Answer

Add MultiSubnetFailover=True to the application's connection string.

Answer

Increase the listener's RegisterAllProvidersIP setting timeout to 1200 seconds.

Answer

Reduce the connection string's Connect Timeout value to 5 seconds.

Answer

Change the availability group to synchronous-commit mode with automatic failover.

Question 6

You manage a three-node Always On availability group on Azure SQL VMs across a single region. The configuration uses Node and File Share Majority quorum with the file share witness hosted on the primary node's local disk. During a planned reboot of the primary node for OS patching, the availability group unexpectedly goes offline and no automatic failover occurs to either secondary replica. What is the root cause?

Accepted Answer

The file share witness is hosted on the primary node, so rebooting it caused simultaneous loss of both a node vote and the witness vote, dropping the cluster below quorum majority

Answer

Automatic failover requires synchronous-commit availability mode, which cannot be configured across three nodes in Azure

Answer

Azure SQL VMs do not support automatic failover for availability groups without an Azure Load Balancer configured as the listener

Answer

Node and File Share Majority quorum is deprecated and must be replaced with a disk witness for availability groups on Azure VMs

Question 7

You are implementing Always Encrypted (without secure enclaves) on an Azure SQL Database. The Customers table has two sensitive columns: SSN, which must support equality lookups in WHERE clauses and be used as a join key, and MedicalNotes, which is only ever displayed and never filtered. You want the strongest encryption that still satisfies the application's query requirements for each column. Which encryption type should you assign to each column?

Accepted Answer

Use deterministic encryption for SSN and randomized encryption for MedicalNotes.

Answer

Use randomized encryption for SSN and deterministic encryption for MedicalNotes.

Answer

Use deterministic encryption for both columns to maximize query flexibility.

Answer

Use randomized encryption for both columns since it is always more secure.

Question 8

A healthcare application stores patient Social Security numbers and salary values in an Azure SQL Database. Compliance requires that these columns remain encrypted at all times, including in memory during query processing, and that the database engine never has access to the plaintext encryption keys. However, analysts must run range queries (for example, WHERE Salary BETWEEN 50000 AND 90000) directly against the encrypted salary column without decrypting the data on the client first. Which configuration meets all of these requirements?

Accepted Answer

Enable Always Encrypted with secure enclaves (VBS enclaves) using randomized encryption, and configure enclave-enabled column encryption keys.

Answer

Enable Always Encrypted with randomized encryption on both columns and perform filtering in the client application after decryption.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.

Answer

Enable Always Encrypted with deterministic encryption on both columns to allow equality and range comparisons.

Question 9

You manage a three-node Always On availability group on Azure SQL Server VMs. The primary replica hosts a high-throughput OLTP workload, and business users complain that nightly full backups cause CPU and I/O contention on the primary. You want to offload backup operations away from the primary while still meeting your recovery requirements. The availability group has two synchronous secondary replicas configured for automatic failover. What should you configure?

Accepted Answer

Set the availability group backup preference to 'Prefer Secondary' and configure backup priority values on the replicas, then schedule backup jobs on all replicas using a script that checks sys.fn_hadr_backup_is_preferred_replica

Answer

Set the backup preference to 'Primary' and reschedule the backups to run during a lower-activity maintenance window on the primary replica

Answer

Remove one secondary replica from the availability group and run standalone full backups against it independently

Answer

Configure copy-only backups on the primary replica only, since transaction log backups cannot be taken on secondary replicas

Question 10

You manage a SQL Server 2022 Standard Edition instance running on an Azure VM. The business wants automatic failover for a single 200 GB production database to a second VM in the same region, with a synchronous secondary. Management refuses to pay for Enterprise Edition. Which high availability solution can you configure to meet these requirements while staying on Standard Edition?

Accepted Answer

A Basic Availability Group with one primary and one secondary replica

Answer

A traditional Always On availability group with three synchronous replicas and readable secondaries

Answer

Active geo-replication between the two VMs

Answer

A distributed availability group spanning both VMs

Question 11

Your company runs SQL Server workloads across on-premises data centers and a third-party cloud provider. Management wants a single control plane in Azure to apply consistent governance, automated backups, and point-in-time restore to these SQL instances without migrating the databases into Azure SQL Database or Managed Instance. They also require the ability to run the service where the data currently resides for data residency reasons. Which solution should you recommend?

Accepted Answer

Deploy Azure Arc-enabled SQL Managed Instance on the existing infrastructure and connect it to Azure through Azure Arc.

Answer

Migrate the databases to Azure SQL Managed Instance using the Azure Database Migration Service.

Answer

Deploy SQL Server on Azure Virtual Machines in a region closest to each data center.

Answer

Provision Azure SQL Database Hyperscale and configure Azure Data Sync with the on-premises instances.

Question 12

Your team maintains an ARM template that deploys an Azure SQL logical server and database across dev, test, and prod environments. The administrator login name, database SKU, and server name must change per environment when the pipeline runs, but the resource API versions and a computed database resource ID used in an output must stay consistent regardless of environment. Which approach correctly separates the values that vary at deployment time from the values that should be defined internally?

Accepted Answer

Define the login name, SKU, and server name as parameters supplied at deployment time, and define the API versions and computed resource ID using variables and template functions.

Answer

Define all values (login name, SKU, server name, API versions, and computed resource ID) as parameters so they can be overridden per environment.

Answer

Define all values as variables so the template remains fully self-contained and requires no external input.

Answer

Define the API versions and resource ID as parameters, and define the login name, SKU, and server name as variables that read from environment variables.

Question 13

You manage an Azure SQL Database that stores regulated healthcare data. A compliance auditor requires evidence that captures every successful and failed login attempt to the database, including the client IP address and time of each attempt. You have already enabled Azure SQL Auditing to a Log Analytics workspace. Which audit action group must be included in the audit specification to satisfy this requirement?

Accepted Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP

Answer

SCHEMA_OBJECT_ACCESS_GROUP

Answer

DATABASE_PERMISSION_CHANGE_GROUP

Answer

BATCH_COMPLETED_GROUP

Question 14

You manage an Azure SQL Database with a business application deployed across two regions. You have configured an auto-failover group between the primary server (East US) and secondary server (West US). The application's connection string currently points directly to the primary server's fully qualified server name. After a recent regional outage triggered an automatic failover, the application could not reconnect to the database even though the secondary was successfully promoted. What change should you make to ensure the application automatically follows the primary during future failovers?

Accepted Answer

Update the connection string to use the failover group's read-write listener endpoint (<fog-name>.database.windows.net).

Answer

Increase the connection timeout value in the application so it waits longer for the primary server to come back online.

Answer

Update the connection string to point directly to the secondary server's fully qualified name in West US.

Answer

Change the failover policy from Automatic to Manual so the application controls when failover occurs.

Question 15

You manage an Azure SQL Database supporting a high-concurrency OLTP application. Users intermittently report query timeouts that coincide with statistics being updated. Investigation shows queries are waiting during query compilation because the optimizer triggers a synchronous statistics update on a large table before the plan is generated. You want to reduce this compilation-time blocking without disabling automatic statistics updates. Which database setting change should you make?

Accepted Answer

Enable AUTO_UPDATE_STATISTICS_ASYNC so statistics updates run in the background and queries compile with existing statistics

Answer

Disable AUTO_UPDATE_STATISTICS entirely and rely on a scheduled maintenance job

Answer

Enable AUTO_CREATE_STATISTICS to generate additional column statistics

Answer

Set the database compatibility level to a lower value to disable Intelligent Query Processing

Question 16

Your organization requires that all Azure SQL Database deployments be fully automated, version-controlled in a Git repository, and repeatable so that redeploying the same template does not create duplicate resources or fail if the resources already exist. The DevOps team wants a native Azure infrastructure-as-code approach that integrates with Azure Pipelines and produces a declarative definition of the database and its logical server. Which deployment method should you recommend?

Accepted Answer

Use Bicep templates checked into the Git repository and deployed through an Azure Pipelines task.

Answer

Write a PowerShell script using New-AzSqlDatabase and schedule it as a recurring pipeline task.

Answer

Export a BACPAC of an existing database and import it into each new environment through the portal.

Answer

Create the database manually in the Azure portal and document the steps in a runbook.

Question 17

You manage an Azure SQL Database supporting an OLTP application. Over the past weeks, several queries have degraded because commonly filtered columns lack supporting indexes, while other unused indexes consume write overhead. Your team wants Azure SQL to automatically create beneficial indexes and drop redundant ones, but only after validating that each change actually improves performance—and to revert changes that cause regressions. Which configuration should you enable?

Accepted Answer

Enable the automatic tuning options CREATE INDEX and DROP INDEX (using the Azure default), which apply and verify recommendations and automatically revert changes that regress performance.

Answer

Enable Query Store and manually review the missing index DMVs each week, applying CREATE INDEX statements yourself.

Answer

Enable the FORCE LAST GOOD PLAN automatic tuning option so the engine forces prior plans that used the correct indexes.

Answer

Configure Resource Governor to prioritize the degraded queries so they receive more CPU and I/O resources.

Question 18

You manage an Azure SQL Database supporting an OLTP application. Users intermittently report slow response times after application deployments and statistics updates. Investigation shows that certain queries suddenly choose worse execution plans, causing CPU spikes that resolve on their own hours later. You want Azure to automatically detect these plan regressions and revert to the previously well-performing plan without manual intervention. Which configuration should you enable?

Accepted Answer

Enable the FORCE_LAST_GOOD_PLAN automatic tuning option

Answer

Enable the CREATE_INDEX automatic tuning option

Answer

Configure Resource Governor to cap CPU for the affected workload

Answer

Enable Parameter Sensitive Plan optimization under intelligent query processing

Question 19

You created an Azure Automation account with a system-assigned managed identity to run a PowerShell runbook that executes maintenance T-SQL against an Azure SQL Database each night. The runbook authenticates using the managed identity, but every execution fails with a login error stating the token principal cannot connect. What must you do so the runbook can authenticate and run T-SQL successfully?

Accepted Answer

In the target database, create a contained database user mapped to the Automation account's managed identity and grant it the required permissions.

Answer

Add the Automation account's managed identity to the SQL Server Agent operators list on the logical server.

Answer

Store the SQL administrator username and password as unencrypted runbook variables and connect with SQL authentication instead.

Answer

Assign the Contributor RBAC role on the SQL Database resource to the Automation account's managed identity.

Question 20

Your company runs a provisioned vCore Azure SQL Database that experiences heavy batch processing every weekend and light activity on weekdays. Management wants to automatically scale the database up to a higher service objective on Friday evening and scale it back down on Monday morning to reduce cost, with no dedicated server to host SQL Server Agent. The solution must run unattended and require minimal ongoing administration. Which approach should you implement?

Accepted Answer

Create an Azure Automation account with a PowerShell runbook that calls Set-AzSqlDatabase, and link two schedules to run the scale-up and scale-down operations.

Answer

Create a SQL Server Agent job on the Azure SQL Database with two schedules that execute ALTER DATABASE ... MODIFY (SERVICE_OBJECTIVE = ...).

Answer

Configure an Elastic Job agent with a target group pointing at the database and schedule a T-SQL job that changes the service tier.

Answer

Create an Azure Monitor alert on CPU percentage that triggers a Logic App to resize the database each time the threshold is crossed.

Question 21

Your team maintains an Azure DevOps pipeline that provisions a new Azure SQL Database in an existing logical server for each feature branch. The pipeline uses a Linux agent and authenticates with a service principal that has Contributor rights on the resource group. You need to add a single command-line step that creates the database at the S1 service objective and is safe to re-run without error if the database already exists. Which approach should you use?

Accepted Answer

Run 'az sql db create' with the --service-objective S1 parameter, since the command returns the existing database without failure when it already exists.

Answer

Run 'New-AzSqlDatabase' in an Azure PowerShell task, because only PowerShell cmdlets can run non-interactively on a Linux build agent.

Answer

Execute a T-SQL 'CREATE DATABASE' statement through sqlcmd wrapped in an IF NOT EXISTS check against sys.databases.

Answer

Run 'az deployment group create' referencing an ARM template, which is the only way to make the deployment idempotent.

Question 22

You are automating the setup of Elastic Jobs to run scheduled index maintenance across 40 Azure SQL databases. Your deployment pipeline uses the Azure CLI. Before you can create an Elastic Job agent named 'jobagent1', which prerequisite must the Azure CLI command reference, and how is it satisfied?

Accepted Answer

An existing Azure SQL Database to serve as the job database, referenced when creating the agent so it can store job definitions, execution history, and target group metadata

Answer

An existing Azure SQL Database that hosts the job metadata, passed via the --database argument when running 'az sql elastic-pool' commands

Answer

A SQL Server Agent service running on the logical server, enabled through 'az sql server update --enable-agent true'

Answer

An Azure Automation account linked to the logical server, referenced by resource ID during agent creation

Question 23

You manage a mission-critical SQL Server 2022 database on an Azure VM. Compliance requires that every full backup written to an Azure Blob Storage container be validated so that any media corruption is detected before the backup is ever needed for a disaster recovery restore. You need to configure the backup process to detect page-level corruption during the backup operation and then confirm that the backup set on the URL is complete and readable, without actually restoring the database. Which combination of actions should you implement?

Accepted Answer

Run BACKUP DATABASE ... TO URL WITH CHECKSUM, then run RESTORE VERIFYONLY FROM URL WITH CHECKSUM.

Answer

Run BACKUP DATABASE ... TO URL WITH COPY_ONLY, then run DBCC CHECKDB against the backup file.

Answer

Run BACKUP DATABASE ... TO URL WITH NO_CHECKSUM, then run RESTORE HEADERONLY FROM URL.

Answer

Run BACKUP DATABASE ... TO URL WITH COMPRESSION, then run RESTORE FILELISTONLY FROM URL.

Question 24

An Azure SQL Database (compatibility level 150) hosts a large rowstore-only reporting table. Analysts run heavy aggregation queries (SUM, GROUP BY) over millions of rows, and execution plans show these queries process rows one at a time, consuming significant CPU. There are no columnstore indexes, and business rules prohibit adding them. Which Intelligent Query Processing feature is designed to improve these CPU-bound analytic queries without a columnstore index?

Accepted Answer

Batch mode on rowstore

Answer

Memory grant feedback

Answer

Table variable deferred compilation

Answer

Approximate percentile

Question 25

Your team maintains Azure SQL Database infrastructure using Bicep files stored in a Git repository. Before applying changes to the production environment through an Azure DevOps pipeline, the change-management policy requires that reviewers see exactly which resources would be created, modified, or deleted—without actually deploying anything. Which action should you add to the pipeline to satisfy this requirement?

Accepted Answer

Run az deployment group what-if with the Bicep template against the target resource group

Answer

Run az deployment group create with the --confirm-with-what-if flag suppressed

Answer

Run bicep build to compile the Bicep file to ARM JSON and inspect the output

Answer

Run az deployment group validate to check the template for syntax errors

Question 26

A financial services company runs a mission-critical OLTP application on Azure SQL Database. The application requires the lowest possible I/O latency and must offload heavy reporting queries to a read-only replica without impacting write performance. The reporting queries must connect to a replica in the same region with in-memory OLTP support. Which service tier should you recommend?

Accepted Answer

Business Critical tier with a read scale-out replica

Answer

General Purpose tier with zone redundancy enabled

Answer

Hyperscale tier with two named replicas

Answer

Serverless General Purpose tier with auto-pause disabled

Question 27

A retail company runs a legacy line-of-business application on SQL Server 2008 R2 hosted on-premises. The database is 300 GB and is used only during store hours (7 AM to 9 PM). Management has approved a scheduled overnight maintenance window and wants to migrate the database to Azure SQL Managed Instance with the least operational complexity. You must recommend the migration approach that meets these requirements while minimizing cost and effort. Which approach should you recommend?

Accepted Answer

Perform an offline migration using the Azure SQL Migration extension in Azure Data Studio during the maintenance window

Answer

Perform an online migration using the Azure SQL Migration extension with continuous log shipping so the application stays available

Answer

Configure a Distributed Availability Group between the on-premises instance and the Managed Instance for continuous replication

Answer

Use transactional replication from SQL Server 2008 R2 to the Managed Instance to keep data synchronized until cutover

Question 28

Your company hosts an Azure SQL Database that must be accessed only by application servers running inside a specific Azure virtual network. A security audit requires that no traffic to the database traverse the public internet and that the database's public endpoint be unreachable. Which combination of actions meets these requirements with the least administrative effort?

Accepted Answer

Create a private endpoint for the SQL logical server in the application subnet and set 'Public network access' to Disabled on the server.

Answer

Add a server-level firewall rule that allows only the virtual network's public IP range and leave public network access enabled.

Answer

Enable a service endpoint for Microsoft.Sql on the subnet and add a virtual network rule, keeping the public endpoint enabled for management.

Answer

Configure Transparent Data Encryption and enforce TLS 1.2 on all client connections to the database.

Question 29

You manage an Azure SQL Database that stores regulated healthcare data. A compliance audit requires that all client connections be encrypted in transit and that clients validate the server certificate to prevent man-in-the-middle attacks. A legacy application uses a connection string that currently sets Encrypt=false. What must you configure in the application's connection string to meet the audit requirement?

Accepted Answer

Set Encrypt=true and TrustServerCertificate=false

Answer

Set Encrypt=true and TrustServerCertificate=true

Answer

Set Encrypt=false and enable Transparent Data Encryption (TDE) on the database

Answer

Set Encrypt=optional and configure a database-level firewall rule

Question 30

A .NET application runs on an Azure App Service and connects to an Azure SQL Database. Security policy requires that no passwords or connection-string secrets be stored anywhere, and the application must authenticate to the database using its system-assigned managed identity with only the ability to read and write data in existing tables. As the DBA, which T-SQL statements should you run in the user database to enable this access following least privilege?

Accepted Answer

CREATE USER [myappservice] FROM EXTERNAL PROVIDER; then ALTER ROLE db_datareader ADD MEMBER [myappservice]; and ALTER ROLE db_datawriter ADD MEMBER [myappservice];

Answer

CREATE LOGIN [myappservice] WITH PASSWORD = '...'; then CREATE USER [myappservice] FROM LOGIN [myappservice]; and ALTER ROLE db_owner ADD MEMBER [myappservice];

Answer

CREATE USER [myappservice] WITH PASSWORD = '...'; then ALTER ROLE db_datareader ADD MEMBER [myappservice];

Answer

CREATE USER [myappservice] FROM EXTERNAL PROVIDER; then ALTER ROLE db_owner ADD MEMBER [myappservice];

Question 31

You manage a SQL Server 2022 instance hosting a production database that uses the FULL recovery model. A nightly full backup and 15-minute transaction log backups are scheduled through a well-established maintenance plan, and these backups form the basis of your point-in-time restore strategy. A developer asks you to take an ad hoc full backup right now so they can refresh a test environment. You must ensure this manual backup does NOT interfere with the existing restore chain used for disaster recovery. Which backup approach should you use?

Accepted Answer

Take a full backup using the COPY_ONLY option

Answer

Take a differential backup so the log chain remains intact

Answer

Take a standard full backup, since full backups never affect the log chain

Answer

Temporarily switch the database to SIMPLE recovery model, take the full backup, then switch back to FULL

Question 32

You administer an Azure SQL Database that stores customer records. A compliance team requires that columns holding national ID numbers and email addresses be discovered, labeled by sensitivity, and that access to these columns be visible in audit reports so that queries touching sensitive data can be reviewed. You want to accomplish this with minimal custom development. Which feature should you implement?

Accepted Answer

SQL Data Discovery & Classification, applying information type and sensitivity labels to the columns

Answer

Dynamic Data Masking rules on the national ID and email columns

Answer

Row-Level Security with a filter predicate function on the customer table

Answer

Transparent Data Encryption with a customer-managed key

Question 33

You manage an Azure SQL Database that must write vulnerability assessment scan results and audit logs to an Azure Storage account. Company policy forbids storing storage account keys or SAS tokens anywhere in the database configuration. You need to configure the database to authenticate to the storage account following least-privilege principles without embedding any secrets. What should you do?

Accepted Answer

Enable a system-assigned managed identity on the logical server, grant it the Storage Blob Data Contributor role on the storage account, and configure auditing to use managed identity authentication.

Answer

Create a DATABASE SCOPED CREDENTIAL that stores a shared access signature (SAS) token for the storage container and reference it in the audit configuration.

Answer

Generate a storage account access key, store it in an Azure Key Vault secret, and paste the key value into the audit destination settings.

Answer

Add the storage account to the server's firewall allow list and enable anonymous blob access on the target container.

Question 34

You manage a fleet of 12 Azure SQL databases and one Azure SQL Managed Instance for a retail company. Leadership wants a centralized, low-overhead monitoring solution that collects detailed performance data (query statistics, wait statistics, and resource utilization) across all these resources, stores the data for historical trend analysis, and integrates with Azure dashboards without requiring you to deploy or maintain agents on each database. Which Azure feature should you implement?

Accepted Answer

Database watcher for Azure SQL, storing collected data in an Azure Data Explorer cluster or free-tier Kusto database

Answer

Enable Query Store on each database individually and query the local Query Store DMVs from each resource

Answer

Deploy a SQL Server instance running the Data Collector feature with a central management data warehouse

Answer

Configure Extended Events sessions on each database writing event files to Azure Blob Storage

Question 35

You manage a 6 TB SQL Server database on an Azure VM. The nightly maintenance window is short, and a full DBCC CHECKDB currently exceeds the window, causing significant tempdb pressure and I/O contention with backups. You must still detect the most common forms of on-disk corruption (torn pages, checksum failures) within the available window. Which approach best meets these requirements?

Accepted Answer

Run DBCC CHECKDB WITH PHYSICAL_ONLY on the schedule, and periodically run a full DBCC CHECKDB during a longer maintenance window

Answer

Run DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS every night to reduce output overhead

Answer

Disable page verification (PAGE_VERIFY NONE) to reduce I/O and rely on backups to detect corruption

Answer

Run DBCC CHECKDB WITH TABLOCK on the schedule to avoid creating an internal database snapshot

Question 36

You are deploying an Azure SQL Managed Instance to host a mission-critical order-processing application. The business requires that the database survive a single datacenter failure within the primary Azure region without any manual intervention or geo-failover to a secondary region. You must minimize cost while meeting this resiliency requirement. Which configuration should you choose?

Accepted Answer

Deploy the Business Critical service tier with zone redundancy enabled

Answer

Deploy the General Purpose service tier and configure an auto-failover group to a secondary region

Answer

Deploy the Business Critical service tier in a single availability zone and rely on automated backups

Answer

Deploy the General Purpose service tier with geo-replication to a paired region

Question 37

Users report that an Azure SQL Database is intermittently unresponsive during business hours. You suspect blocking. You need to identify the root blocking session (the head of the blocking chain) and the query it is running, using the least effort. Which approach should you use?

Accepted Answer

Query sys.dm_exec_requests joined to sys.dm_exec_sessions, filtering where blocking_session_id is not 0, and trace back to the session whose own blocking_session_id is 0.

Answer

Enable Query Store in READ_WRITE mode and review the Top Resource Consuming Queries report.

Answer

Run DBCC CHECKDB with the PHYSICAL_ONLY option during the blocking window.

Answer

Configure Resource Governor to place blocked sessions into a lower-priority workload group.

Question 38

You manage a 2 TB database on a SQL Server 2022 instance running on an Azure VM. The database uses the FULL recovery model. A full backup is taken every Sunday at 01:00, and transaction log backups run every 15 minutes. Restoring 6 days of transaction log backups after a failure late in the week takes several hours, exceeding the agreed RTO. You must reduce restore time without increasing the full backup frequency. What should you add to the backup strategy?

Accepted Answer

Take a nightly differential backup between the weekly full backups

Answer

Switch the database to the SIMPLE recovery model to shorten the log chain

Answer

Increase the transaction log backup interval to every 60 minutes

Answer

Take a COPY_ONLY full backup each night in addition to the weekly full

Question 39

You administer an Azure SQL Managed Instance running an OLTP application. Monitoring shows the plan cache is consuming a large amount of memory, and DMV queries reveal thousands of single-use (adhoc) compiled plans, each with a usecounts value of 1. Available memory for the buffer pool is being squeezed, causing increased physical reads. You want to reduce the memory wasted by these single-use plans with minimal application changes. Which configuration should you apply?

Accepted Answer

Enable the 'optimize for ad hoc workloads' server configuration option

Answer

Set the 'max degree of parallelism' server configuration to 1

Answer

Enable Query Store with the AUTO capture mode

Answer

Run DBCC FREEPROCCACHE on a recurring schedule

Question 40

You manage an Azure SQL Database supporting a nightly ETL process that bulk-loads several million rows into a large staging table before it is queried by reporting jobs. Operators report that queries running immediately after the load sometimes choose poor execution plans with severely underestimated row counts, even though AUTO_UPDATE_STATISTICS is enabled. You confirm that the statistics update threshold has not yet been crossed after the bulk load. Which action best ensures the optimizer has accurate cardinality estimates for the reporting queries?

Accepted Answer

Add a step to the ETL process that runs UPDATE STATISTICS on the staging table WITH FULLSCAN after the bulk load completes.

Answer

Enable AUTO_CREATE_STATISTICS on the database so missing statistics are generated automatically.

Answer

Rebuild all indexes on the staging table after each load to refresh statistics with sampled data.

Answer

Enable the ASYNC option for auto update statistics so the optimizer does not wait for updates.

Question 41

You manage an Azure SQL Database (Business Critical) that uses active geo-replication with a readable secondary in a paired region. Compliance requires an annual disaster recovery drill that validates the application can operate fully against the secondary region, including write operations, while producing an auditable record of the exercise. The production database must return to its original primary region afterward with no data loss. Which approach should you use for the drill?

Accepted Answer

Perform a planned failover of the geo-replication link to the secondary, run the application workload against it, then perform another planned failover back to the original region.

Answer

Trigger a forced (unplanned) failover to the secondary, run the workload, then re-establish geo-replication back to the original region.

Answer

Point the application connection string to the read-only secondary endpoint and execute the full workload there without failing over.

Answer

Run a geo-restore of the latest backup into the secondary region, direct the application to the restored copy, then delete it after the drill.

Question 42

A support team at a retail company queries the Customers table in an Azure SQL Database to help callers. Company policy requires that support agents never see full credit card numbers, but must still be able to view the last four digits to confirm identity. Application service accounts that process payments must continue to see the full value. You want the lowest-effort solution that does not change application queries or physically alter stored data. What should you implement?

Accepted Answer

Apply a Dynamic Data Masking rule with a partial mask on the credit card column and grant the payment service account the UNMASK permission.

Answer

Enable Transparent Data Encryption (TDE) on the database so the credit card column is encrypted at rest.

Answer

Configure Always Encrypted with a deterministic column encryption key on the credit card column.

Answer

Create a row-level security policy with a filter predicate that hides rows containing credit card data from support agents.

Question 43

A retail company stores customer email addresses in an Azure SQL Database. Requirements: (1) support agents querying the table interactively should see only obfuscated emails like aXXX@XXXX.com, (2) the application service account must continue to read the full plaintext value to send order confirmations, and (3) the solution must require no changes to the application code or client drivers. Which feature should you implement?

Accepted Answer

Dynamic Data Masking with an email masking rule, granting the application service account the UNMASK permission

Answer

Always Encrypted with a deterministic column encryption key deployed to all clients

Answer

Transparent Data Encryption (TDE) on the database

Answer

Row-Level Security with a filter predicate on the email column

Question 44

You are designing an automated maintenance solution for 40 Azure SQL databases spread across three logical servers. You decide to use the Elastic Jobs feature to run parameterized T-SQL index maintenance against all targets on a nightly schedule. Before you can create the Elastic Job agent, what prerequisite resource must you provision?

Accepted Answer

A dedicated Azure SQL Database to serve as the job database for the Elastic Job agent

Answer

An Azure SQL Managed Instance to host the SQL Server Agent service

Answer

An Azure Automation account with a Hybrid Runbook Worker

Answer

An Azure Storage account configured with a database-scoped credential

Question 45

You are configuring an Elastic Job agent in Azure SQL Database to run a nightly index maintenance script across 40 databases in a target group. The job must connect to each target database and also write execution output to a central logging database. The job repeatedly fails with authentication errors when connecting to the target databases. You confirm the job's database-scoped credential exists in the job database. What must you also do to allow the job to authenticate successfully against every target database?

Accepted Answer

Create a matching database-scoped credential and a corresponding login/user with the required permissions in each target database, using the same credential name referenced by the job step.

Answer

Grant the Elastic Job agent's system-assigned managed identity the SQL DB Contributor role on each target server in the Azure portal.

Answer

Add a server-level firewall rule on each target logical server to allow the job database's outbound IP address.

Answer

Enable the 'Allow Azure services and resources to access this server' setting on every target logical server.

Question 46

You manage 40 Azure SQL databases spread across three logical servers. You have configured an Elastic Job agent with a target group covering all databases and a T-SQL job that rebuilds indexes. The maintenance job must run automatically every night at 02:00. In the Elastic Job agent, which action correctly automates this recurring execution?

Accepted Answer

Add a schedule to the job by setting its schedule properties to a recurring interval (e.g., every 1 day) with a defined start time, and enable the schedule.

Answer

Create a SQL Server Agent job on each target database and configure a nightly schedule, since Elastic Job agents cannot run on a timer.

Answer

Configure an Azure Automation runbook that calls sp_start_job every night, because Elastic Jobs only support manual execution.

Answer

Set the job's output credential to a scheduled managed identity so the job triggers at the configured retention interval.

Question 47

You are configuring an Elastic Job agent to run maintenance T-SQL against a group of Azure SQL databases. When creating the job step, you need to specify where the T-SQL command text and job metadata are stored and managed. Which resource holds the elastic job definitions, steps, and execution history?

Accepted Answer

The Azure SQL Database designated as the job database when the Elastic Job agent was created

Answer

A dedicated Azure Storage account configured as the agent output target

Answer

The msdb system database on each target Azure SQL database

Answer

An Azure Automation account linked to the Elastic Job agent

Question 48

You manage an Elastic Job agent that runs an index maintenance T-SQL script against all databases on a logical SQL server. A new database named StagingRefresh was added to the same server, but it must NOT be included in the maintenance job because it is dropped and recreated nightly by a separate pipeline. You want the existing job to continue targeting every other database on the server without needing to update the target group each time a new production database is added. What should you configure in the target group?

Accepted Answer

Add a server-level target member for the logical server and add a database-level target member for StagingRefresh with the membership type set to Exclude.

Answer

Add only database-level target members for each production database individually and omit StagingRefresh.

Answer

Add a server-level target member and configure the job step to check DB_NAME() and skip StagingRefresh in the script.

Answer

Create a separate elastic pool target member that contains only the production databases.

Question 49

You manage 40 Azure SQL databases spread across three logical servers that host tenant data for a SaaS application. You need to run the same index maintenance T-SQL script against all 40 databases every night using the Elastic Jobs feature. You have created an Elastic Job agent and a job. Which action correctly defines WHICH databases the job step executes against?

Accepted Answer

Create a target group and add the three logical servers as target members, allowing the agent to expand each server into its member databases at runtime.

Answer

Add each of the 40 databases individually as a linked server on the job agent's control database.

Answer

Enable the SQL Server Agent service on each logical server and register the databases as agent targets.

Answer

Configure a maintenance plan in the Azure portal and assign the 40 databases as subscribers.

Question 50

You manage 40 Azure SQL databases on a single logical server. New databases are provisioned weekly, and you need an Elastic Job that runs a maintenance T-SQL script against every database on the server, automatically including databases added in the future. You are configuring the target group. Which target group member configuration meets this requirement with the least ongoing maintenance?

Accepted Answer

Add the logical server as a target group member (server target) so all current and future databases are included dynamically.

Answer

Add each database individually as a target group member and update the group each week.

Answer

Add an elastic pool as the target group member so future databases are automatically covered.

Answer

Add the master database as a target group member so it fans out to all user databases.

Question 51

You manage an Elastic Job agent that runs a nightly index rebuild against a target group containing 40 Azure SQL databases spread across three logical servers. Each database has a different name, and you need the same T-SQL job step to execute successfully against every database without hardcoding names. The job step currently fails on some databases because they use different schema names for the target table. You want the job step to reliably enumerate and act on each database's local objects at runtime. Which characteristic of Elastic Jobs must you rely on to make the single job step work across all targets?

Accepted Answer

Elastic Jobs automatically connect to and execute the job step's T-SQL independently in the context of each target database, so the script should use dynamic SQL or system catalog views to resolve local objects at runtime.

Answer

Elastic Jobs execute the T-SQL once on the job database and replicate the resulting rows to each target, so you must design the script to write to a shared output table.

Answer

Elastic Jobs require a separate job step per target database, so you must generate 40 job steps that each specify the database name and schema explicitly.

Answer

Elastic Jobs run the T-SQL in the master database of each logical server and use three-part names to reach each target, so the script must fully qualify every object with the database name.

Question 52

You manage an Azure SQL Managed Instance hosting an OLTP database. The compliance team requires a downstream ETL process to incrementally extract all INSERT, UPDATE, and DELETE operations made to a Customers table, including the actual column values before and after each change. The solution must retain the historical change data in system tables that the ETL job can query on a schedule, without modifying the application or the Customers table schema. Which feature should you enable?

Accepted Answer

Change Data Capture (CDC)

Answer

Change Tracking

Answer

Temporal tables (system-versioned)

Answer

SQL Server Audit with a database audit specification

Question 53

You manage an Azure SQL Database supporting a reporting workload. A large fact table (2 billion rows) is queried almost exclusively with analytical scans that read wide ranges of rows; the table is rarely updated after nightly bulk loads. Storage costs are rising and you notice heavy I/O pressure during report execution. You want to reduce storage footprint and physical I/O while keeping CPU overhead acceptable for this read-mostly, scan-heavy pattern. Which data compression option should you apply to the fact table?

Accepted Answer

Apply PAGE compression to the clustered index of the fact table

Answer

Apply ROW compression to the clustered index of the fact table

Answer

Disable compression and instead enable Transparent Data Encryption (TDE)

Answer

Convert the table to a heap and rely on backup compression only

Question 54

A healthcare company must guarantee that a new AuditLog table in an Azure SQL Database can only ever have rows appended — never updated or deleted — and must provide cryptographic proof to external auditors that no historical records have been tampered with. The team wants a native feature that requires no application-layer hashing logic. Which feature should you configure when creating the table?

Accepted Answer

An append-only ledger table

Answer

A system-versioned temporal table with a history table

Answer

Change Data Capture (CDC) on the AuditLog table

Answer

Row-level security with a blocking predicate on UPDATE and DELETE

Question 55

You manage an Azure SQL Database that stores regulated healthcare data. Compliance auditors require that all successful and failed login attempts, plus schema changes, be captured and retained for at least two years in a queryable store that supports Kusto (KQL) analysis and long-term archival without managing your own storage lifecycle policies. Which auditing configuration best meets these requirements?

Accepted Answer

Enable Azure SQL Database auditing and send audit logs to a Log Analytics workspace, configuring the workspace data retention to 730 days.

Answer

Enable server-level auditing writing to an Azure Storage account and set the retention slider on the storage destination to 730 days.

Answer

Enable auditing to Event Hubs and rely on the consumer application to archive events for two years.

Answer

Use Extended Events sessions saved to a local database table and export the data monthly to a CSV file.

Question 56

You manage an Azure SQL Database that stores regulated healthcare data. A compliance requirement states that the organization must control and rotate the encryption key used to protect data at rest, and Microsoft must not have access to the root key material. Transparent Data Encryption (TDE) is currently enabled using the service-managed key. What should you configure to meet the requirement with the least ongoing administrative effort?

Accepted Answer

Configure TDE with a customer-managed key (BYOK) stored in Azure Key Vault and grant the server's managed identity access to the key

Answer

Disable TDE and implement Always Encrypted on every column that contains sensitive data

Answer

Enable TDE with the default service-managed key and schedule a manual key rotation task each month

Answer

Enable Microsoft Entra ID authentication-only mode and rely on the built-in service-managed TDE key

Question 57

Your organization runs an Azure SQL Database that must comply with a new corporate security policy requiring all authentication to flow through Microsoft Entra ID. The compliance team wants to guarantee that no SQL authentication (username/password) logins can be used to connect, even if such logins already exist. You want to enforce this with the least ongoing administrative effort. What should you do?

Accepted Answer

Enable Microsoft Entra-only authentication on the Azure SQL logical server.

Answer

Set an Azure Entra admin for the server and manually drop every existing SQL login.

Answer

Configure a server-level firewall rule that blocks all public network access.

Answer

Enable Transparent Data Encryption (TDE) and rotate the SQL login passwords weekly.

Question 58

You manage an Azure SQL Database supporting a line-of-business application. Users report intermittent slowness during business hours, but performance seems normal when you check it. Before making any configuration or index changes, your manager asks you to quantify what 'normal' performance looks like over a two-week period so future changes can be measured against it. Which approach best establishes this reference for comparison?

Accepted Answer

Capture a performance baseline by collecting key metrics (CPU, DTU/vCore usage, I/O, and query durations) over a representative period and store them for comparison.

Answer

Enable automatic tuning so the database self-corrects any performance issues without manual analysis.

Answer

Run a one-time Extended Events session for 30 minutes during a slow period and use those results as the standard.

Answer

Force the last good execution plan for the most expensive query in Query Store.

Question 59

A production Azure SQL Database intermittently reports transaction rollbacks that users describe as 'the app just freezes and then errors out.' You suspect deadlocks but the errors are sporadic and you cannot reproduce them on demand. You need to capture the full deadlock graph, including the resource and process lists, with the least ongoing overhead so you can analyze the victim and surviving transactions after the fact. Which approach should you use?

Accepted Answer

Create an Extended Events session in Azure SQL Database that captures the sqlserver.xml_deadlock_report event, storing output to a ring_buffer or event file target for later analysis.

Answer

Enable trace flag 1222 on the database so deadlock graphs are written to the error log.

Answer

Query sys.dm_tran_locks joined to sys.dm_os_waiting_tasks on a repeating schedule to catch the deadlock as it occurs.

Answer

Enable Query Store and review the top resource-consuming queries in the regressed plans report to identify the deadlock.

Question 60

You manage an Azure SQL Database and users report intermittent slowness during business hours. You need to capture the exact T-SQL text and duration of statements that run longer than 5 seconds, storing the collected data for later analysis with minimal performance overhead. Which approach should you use?

Accepted Answer

Create an Extended Events session filtering the sql_statement_completed event on duration and targeting an event_file on blob storage

Answer

Enable the SQL Server Profiler trace and leave it running throughout business hours

Answer

Query sys.dm_exec_requests repeatedly in a scheduled job to log any request with a high elapsed time

Answer

Turn on Query Store in Read-Write mode and rely on its default capture policy

Question 61

You manage a set of Azure SQL databases protected by an auto-failover group with automatic failover enabled. The application requires that regional failover happen automatically during a prolonged outage, but the business will not tolerate silent data loss during transient network blips. You configure the failover group's read/write failover policy to Automatic and set GracePeriodWithDataLossHours to 4. During a real regional outage where the primary region is unreachable and replication cannot catch up, what behavior will occur?

Accepted Answer

Failover is delayed for up to 4 hours to allow the primary to recover; if it remains unreachable after the grace period, automatic failover proceeds even though some recent transactions may be lost.

Answer

Automatic failover is triggered immediately with zero data loss because synchronous replication guarantees all committed transactions are copied first.

Answer

Failover never happens automatically and must always be initiated manually because a grace period was configured.

Answer

The grace period is ignored for read/write failover and only applies to the read-only listener endpoint.

Question 62

A retail company runs an application backed by four related Azure SQL databases on a single logical server in the East US region. Leadership requires that, in a regional outage, all four databases fail over together to West US as a group, and the application's connection strings must not need to change after failover. Which HA/DR configuration best meets these requirements?

Accepted Answer

Configure an auto-failover group on the logical server that includes all four databases and connect the application using the failover group's read-write listener endpoint.

Answer

Configure active geo-replication individually on each of the four databases to a secondary server in West US and update connection strings after failover.

Answer

Enable zone-redundant configuration on the databases so they automatically fail over within the region and across regions.

Answer

Configure a Always On availability group across the two regions and add all four databases to the same availability group.

Question 63

You are designing high availability for a mission-critical SQL Server workload on Azure VMs. The application requires instance-level protection (SQL Agent jobs, linked servers, logins, and the entire instance must fail over together), and the business mandates automatic failover with no data loss within a single Azure region. The application vendor does not support databases in an availability group. Which solution should you recommend?

Accepted Answer

Failover Cluster Instance (FCI) using Azure shared disks with nodes across availability zones

Answer

Always On availability group with automatic failover and synchronous-commit replicas

Answer

Active geo-replication between two SQL VMs in the same region

Answer

Log shipping with a standby secondary and automated failover script

Question 64

A retail company runs a mission-critical order-processing database on Azure SQL Database (General Purpose). The business defines an RTO of 5 minutes and an RPO of 5 seconds for a full regional outage. The current configuration relies only on the built-in geo-redundant backups, and during a recent DR drill the team measured a recovery time of over an hour using geo-restore. Which configuration change should you implement to meet both the RTO and RPO requirements?

Accepted Answer

Configure an auto-failover group with a readable secondary in a paired region and set automatic failover policy.

Answer

Increase the point-in-time restore retention period to 35 days and rely on geo-restore during an outage.

Answer

Enable a long-term retention (LTR) policy with weekly and monthly backups replicated to the secondary region.

Answer

Create a copy of the database in the secondary region nightly using the CREATE DATABASE ... AS COPY OF command.

Exam domain	Exam weight	Practice
Plan and implement data platform resources	17%	Practice this topic
Implement a secure environment	23%	Practice this topic
Monitor, configure, and optimize database resources	23%	Practice this topic
Configure and manage automation of tasks	17%	Practice this topic
Plan and configure a high availability and disaster recovery (HA/DR) environment	20%	Practice this topic

Microsoft Azure Database Administrator Associate (DP-300) practice exam & study guide

Start studying DP-300

All DP-300 study resources

DP-300 exam domains

Sample DP-300 questions

Key DP-300 terms

DP-300 frequently asked questions

Plan and implement data platform resources (17%)

Implement a secure environment (23%)

Monitor, configure, and optimize database resources (23%)

Configure and manage automation of tasks (17%)

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