Microsoft · Free Practice Questions · Last reviewed May 2026
36real exam-style questions organised by domain, each with the correct answer highlighted and a plain-English explanation of why it's right — and why the others are wrong.
17% of exam · 6 sample questions below
You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?
Configure SQL Server Managed Backup to Microsoft Azure.
SQL Server Managed Backup to Microsoft Azure automates full, differential and transaction log backups to Blob Storage with built-in scheduling and retention, requiring no custom scripts. It satisfies both constraints: least administrative effort and point-in-time restore through log chain continuity.
Install Azure Backup Server on-premises and configure backup of SQL Server databases.
Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.
Use Azure Data Factory to copy database backups to Blob Storage.
You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?
Use Azure Data Factory to run stored procedures for schema changes.
Use SQL Server Agent jobs to run deployment scripts on schedule.
Use Azure DevOps with a database project and release pipelines.
Azure DevOps database projects keep schema definitions in version control, and release pipelines deploy them across regions with tracked, repeatable steps. This satisfies both the rollback requirement, via redeploying prior versions, and version control of schema changes.
Use Azure Automation with PowerShell scripts to execute T-SQL scripts.
You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?
Backup compression.
Backup frequency (full, differential, log).
Point-in-time restore interval.
Backup retention period (in days).
Configurable from 7 to 35 days.
Geo-redundant storage (GRS) for backups.
You can choose between LRS and GRS for backup storage.
You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?
Automatically rebuild fragmented indexes.
Automatically scale the database to a higher service tier.
Stream diagnostic logs to a Log Analytics workspace.
Diagnostic settings allow log streaming.
Set up an alert when DTU consumption exceeds 80% for 5 minutes.
Azure Monitor can alert on performance metrics.
Create a custom metric alert based on a KQL query.
Custom log searches can be used for alerts.
A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?
Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.
This is correct because Azure Automation provides a native scheduler for PowerShell runbooks, and Export-AzureRmSqlDatabase creates a BACPAC file (a transactionally consistent backup) in Azure Storage. The cmdlet orchestrates the export through the Azure SQL Database management plane, ensuring a consistent snapshot of the live database. Running nightly via schedule meets the backup requirement without manual intervention, making it the simplest and most reliable option for automated database export.
Create an Elastic Database Job that runs a T-SQL script to export the database.
Deploy an Azure Data Factory pipeline with a Copy activity to export the database.
Use an Azure Logic App with the SQL Server connector to export the database.
You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?
SQL Server Agent
Elastic Database Jobs
Elastic Database Jobs run T-SQL against Azure SQL Database on a defined recurrence, satisfying the scheduled index-maintenance requirement. Unlike SQL Agent, which is unavailable in Azure SQL Database, elastic jobs target logical servers and databases directly, executing scripts such as index rebuilds without external orchestration.
Azure Automation Runbook
Azure Logic Apps
Want more Configure and manage automation of tasks practice?
Practice this domain11% of exam · 6 sample questions below
A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?
Configure Log Shipping to a secondary VM
Deploy a Failover Cluster Instance using Azure Shared Disks
Create an Always On Availability Group with an availability group listener
An Always On Availability Group with a listener provides automatic failover across separate nodes without shared storage, satisfying the no-shared-storage constraint, while planned maintenance triggers a manual failover to the secondary replica, minimising downtime for the critical database.
Use Database Mirroring with automatic failover
Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?
Internal Load Balancer
An internal load balancer is required so clients and listeners reach the availability group's listener IP across the cluster nodes, since Azure networking does not support the floating IP that a WSFC listener normally uses. It distributes listener traffic to the current primary replica.
Azure Files share for witness
Windows Server Failover Cluster
Windows Server Failover Cluster underpins the availability group, providing quorum, health detection and automatic failover between the SQL Server instances on the Azure VMs. Without the cluster, the availability group cannot be created or managed.
Azure SQL Database
VPN gateway between regions
You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?
Implement Azure Site Recovery to replicate the VM to a secondary region.
Set up log shipping to a secondary SQL Server in a different region and perform manual failover.
Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.
An Always On availability group with a synchronous-commit replica in a secondary Azure region replicates every transaction to the secondary before committing, so the RPO is effectively 0 within the failover policy, and automatic failover can bring the listener online in minutes. This is the only option that satisfies both a 30-minute RTO and near-zero data loss, because failover is orchestrated and scripted rather than manual and does not require restoring backups. In Azure, you must place the VMs in the same cloud service or use a load balancer to route traffic to the listener to enable seamless application redirection.
Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.
Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.
Select the Azure SQL Database, then navigate to Automatic tuning settings, then enable the desired options, then save changes.
This is the correct order because you must first select the database to configure, then access its automatic tuning settings, then enable the specific tuning options, and finally save the configuration.
Navigate to Automatic tuning settings, then select the Azure SQL Database, then enable the desired options, then save changes.
Select the Azure SQL Database, then enable the desired options, then navigate to Automatic tuning settings, then save changes.
Enable the desired options, then select the Azure SQL Database, then navigate to Automatic tuning settings, then save changes.
Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.
Connect to the instance, create a job, define steps, schedule, then enable and start.
This is the correct order because you must first establish a connection, then create the job object, define its steps, set a schedule, and finally enable and start the job.
Create a job, define steps, schedule, connect to the instance, then enable and start.
Connect to the instance, define steps, create a job, schedule, then enable and start.
Connect to the instance, create a job, schedule, define steps, then enable and start.
Match each Azure SQL Database security feature to its purpose.
Transparent Data Encryption (TDE): Encrypts data at rest
TDE encrypts the database files on disk, protecting data at rest.
Always Encrypted: Encrypts data in use on client side
Always Encrypted encrypts sensitive data on the client side and never reveals the encryption keys to the database engine.
Dynamic Data Masking: Masks sensitive data in query results
Dynamic Data Masking obfuscates sensitive data in query results for non-privileged users.
Transparent Data Encryption (TDE): Encrypts data in use on client side
Always Encrypted: Encrypts data at rest
Want more Plan and configure high availability and disaster recovery practice?
Practice this domain22% of exam · 6 sample questions below
You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?
Add a firewall rule allowing all Azure IP addresses.
Configure a virtual network service endpoint and a virtual network firewall rule.
A virtual network service endpoint routes traffic to Azure SQL over the Microsoft backbone, keeping it off the public internet. The virtual network firewall rule then permits only that subnet, minimising both latency and exposure for same-region VMs.
Add a firewall rule for each VM's public IP address.
Add a firewall rule allowing all Azure services to access the database.
You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?
Azure SQL Auditing
Azure SQL Auditing captures both successful and failed authentication attempts, plus the originating IP and user, writing them to a storage account, Log Analytics workspace, or Event Hubs. This directly satisfies the requirement to audit all login attempts.
Advanced Threat Protection
Transparent Data Encryption (TDE)
SQL Vulnerability Assessment
You are designing a secure environment for Azure SQL Database. Which authentication method provides the strongest security and supports multi-factor authentication?
Certificate-based authentication
Microsoft Entra ID authentication
Microsoft Entra ID authentication centralises identity in a directory that enforces multi-factor authentication, conditional access and passwordless methods, satisfying the stem's demand for the strongest security. Unlike SQL logins, credentials are not stored in the database, and tokens are issued per user, enabling auditing and revocation.
SQL authentication with strong passwords
Windows authentication
Which TWO of the following are best practices for securing Azure SQL Database?
Enable Auditing to block malicious queries.
Enable TDE to prevent SQL injection attacks.
Use SQL authentication with complex passwords.
Enable firewall rules to restrict access to specific IP addresses.
Restricting access to specific IP addresses via firewall rules satisfies the requirement to limit network exposure of Azure SQL Database. Server-level and database-level firewall rules block connections originating outside approved ranges, ensuring only known clients reach the logical server, which directly reduces the attack surface from arbitrary internet traffic.
Use Microsoft Entra ID authentication instead of SQL authentication.
Replacing SQL authentication with Microsoft Entra ID authentication centralises identity management and enables conditional access, multifactor authentication and passwordless sign-in. This directly satisfies the stem's security requirement by removing static database credentials, which are prone to credential theft and cannot enforce tenant-wide policies such as MFA or risk-based access controls.
Which TWO of the following are valid methods to connect to Azure SQL Database securely?
Connect using Microsoft Entra ID authentication with multi-factor authentication.
Microsoft Entra ID authentication with multi-factor authentication verifies identity through Entra ID and adds a second factor, defeating stolen-password attacks. Traffic remains encrypted via TLS, satisfying the stem's secure connection requirement without embedding credentials in connection strings.
Connect using a shared access key from Azure Storage.
Connect using a private endpoint within a virtual network.
A private endpoint assigns a private IP from your virtual network to the Azure SQL logical server, so traffic traverses the Microsoft backbone rather than the public internet. This satisfies the secure-connectivity requirement by removing public exposure and letting you restrict access through network security groups and private DNS.
Connect directly using the server's public IP address without encryption.
Connect using SQL authentication with a simple password.
You are configuring Azure SQL Database firewall rules. You need to allow a range of IP addresses (192.168.1.0 to 192.168.1.255) to connect to the database. Which firewall rule should you create?
Start IP: 192.168.0.0, End IP: 192.168.2.255
Start IP: 192.168.1.0, End IP: 192.168.1.255
Azure SQL Database firewall rules accept a contiguous start and end IP range, so 192.168.1.0 to 192.168.1.255 covers the entire /24 subnet in a single rule, satisfying the requirement to permit every address in that range.
Start IP: 192.168.1.255, End IP: 192.168.1.0
Start IP: 192.168.1.0, End IP: 192.168.1.0
Want more Implement a secure environment practice?
Practice this domain11% of exam · 6 sample questions below
You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?
Set up log shipping from West Europe to North Europe.
Configure active geo-replication between the instances.
Create a failover group, but note the secondary is not readable.
Create a failover group with the secondary instance in North Europe.
A failover group provides an auto-failover endpoint and a readable secondary, satisfying the automated failover and read-access requirements. The secondary instance in North Europe also supports backups, meeting the cross-region disaster recovery constraint. Geo-replication alone lacks the automatic failover that failover groups deliver.
Your company has an Azure SQL Database in the General Purpose tier. You need to reduce the recovery point objective (RPO) from 1 hour to less than 1 minute for disaster recovery. Which action should you take?
Increase the backup retention period to 35 days.
Upgrade the database to Business Critical tier.
Upgrading to Business Critical adds synchronous replicas within the region for local HA, but geo-replication remains asynchronous with ~1 hour RPO. It does not achieve sub-minute geo RPO.
Enable zone redundancy on the database.
Increase the DTU or vCore count.
Which THREE components are required to configure a failover group for Azure SQL Database? (Choose three.)
Failover group listener.
At least one database on the primary server.
Failover groups replicate databases, so at least one database must exist on the primary server before the group can be created. Without a database to replicate, there is nothing for the secondary server to receive.
Secondary server.
A failover group requires a secondary server to host the replicated databases, satisfying the geo-replication constraint. The secondary server must reside in a different Azure region from the primary, and Microsoft Entra ID authentication is configured independently on each server, since logins and users are not replicated automatically.
Primary server.
A failover group requires a primary server to host the read-write database and act as the active endpoint. Azure SQL Database failover groups pair this primary with a secondary server in a different region, so the primary server is a mandatory component.
An elastic pool.
Which TWO disaster recovery options are available for Azure SQL Database? (Choose two.)
Database mirroring.
Auto-failover groups.
Auto-failover groups provide automated geo-replication across Azure regions, enabling a secondary server to take over if the primary fails. This satisfies the disaster recovery requirement by delivering cross-region failover for Azure SQL Database, including read-write listener endpoints that redirect connections without manual intervention.
Always On availability groups.
Log shipping.
Active geo-replication.
Active geo-replication continuously replicates a database to up to four secondary servers in different Azure regions, providing a readable secondary and a manually initiated failover. This satisfies the disaster recovery requirement by enabling recovery from a region-wide outage without restoring from backup.
You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?
The database has one high-availability replica.
The configuration is invalid because General Purpose tier cannot be zone-redundant.
The database uses locally-redundant backup storage.
The database is in General Purpose tier and supports zone redundancy.
The General Purpose service tier supports zone redundancy when the configuration enables it, which the exhibit's properties confirm. This distinguishes it from Business Critical, which also offers zone redundancy, and from Basic or Standard tiers, which do not support it.
You have an Azure SQL Managed Instance in the East US region. To meet a 1-hour RPO and 2-hour RTO, you configure a failover group with a secondary in West US using automatic failover. During a test, you notice that the RTO is consistently 10 minutes longer than required. What is the most likely cause?
The failover group uses Microsoft Entra ID authentication, which adds latency.
The secondary database is configured with asynchronous commit, causing delay.
The failover group has a grace period of 20 minutes configured.
Correct: The grace period adds to RTO.
The secondary database is not seeded and needs to be restored.
Want more Plan and configure a high availability and disaster recovery environment practice?
Practice this domain17% of exam · 6 sample questions below
A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?
Active geo-replication with failover group
Active geo-replication with a failover group is correct because it links an Azure SQL Database to a secondary in a different region and automatically fails over the primary and secondary endpoints when the failover group's health policy detects an outage, within a configurable grace period. The asynchronous replication maintains an RPO of up to 5 seconds, and the failover group manages the DNS endpoint so applications are redirected to the new primary without manual intervention or code changes. Note that the replication is asynchronous, not synchronous, meaning the 5-second RPO is a lag target rather than a zero-data-loss guarantee.
Standard geo-replication
Local redundancy with automatic failover
Read-scale out
A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?
Transparent data encryption (TDE) and TLS 1.2
Transparent data encryption performs real-time encryption and decryption of the database, backups, and transaction logs at rest without application changes, while TLS 1.2 secures data in transit between clients and the server. Together they satisfy the healthcare company's dual requirement for Azure SQL Database.
Dynamic data masking and row-level security
Microsoft Entra ID authentication and firewall rules
Always Encrypted and transparent data encryption (TDE)
A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?
Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.
Azure SQL Managed Instance preserves SQL Server Agent, including proxies, and supports cross-database queries within the same instance, unlike Azure SQL Database. This makes it the viable target for a database depending on both features.
Migrate to SQL Server on Azure Virtual Machines for full control.
Migrate to Azure SQL Database elastic query to handle cross-database queries.
Migrate to Azure SQL Database instead to reduce costs.
You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?
Use Azure Data Studio to import the certificate directly from the local machine store during migration.
Disable TDE on the source database, migrate the backup, then enable TDE on the target.
Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.
Exporting the TDE certificate and private key to a .pfx, restoring it to the managed instance, then restoring the backup preserves encryption because the instance holds the same protector. This satisfies the stem's requirement to migrate while preserving TDE.
Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.
You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?
There is a blocking chain due to unoptimized queries.
The disk IOPS limit is being reached, causing queuing.
The DTU limit is being reached, causing CPU throttling.
The database is experiencing memory pressure due to concurrent queries exceeding available memory.
RESOURCE_SEMAPHORE waits occur when queries cannot obtain a memory grant because concurrent queries have consumed the available workspace memory. At 100 DTUs the General Purpose tier caps memory, so peak-hour concurrency triggers the intermittent slowdowns.
Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.
Create secondary server in another region, then configure the secondary database, then initiate replication, then verify the replication link.
This is the correct order because you must first have a secondary server to host the replica, then configure a database on that server, then start replicating data, and finally confirm the link is active.
Create secondary server in another region, then initiate replication, then configure the secondary database, then verify the replication link.
Initiate replication, then create secondary server in another region, then configure the secondary database, then verify the replication link.
Create secondary server in another region, then configure the secondary database, then verify the replication link, then initiate replication.
Want more Plan and implement data platform resources practice?
Practice this domain22% of exam · 6 sample questions below
A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?
Increase the maximum number of concurrent workers.
Identify and create missing indexes.
Missing indexes force full table or index scans, so creating them lets the query optimiser seek directly to matching rows, cutting CPU consumed per query. This satisfies the constraint of reducing CPU usage while remaining on the S3 service tier.
Reduce MAXDOP to 1.
Increase MAXDOP to 8.
You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?
Fragmented clustered index; rebuild the clustered index.
Insufficient memory; increase the service tier.
Missing nonclustered index; create an index on the predicates.
A clustered index scan reading the whole table indicates the predicate columns lack a supporting nonclustered index, forcing full scans that inflate duration and CPU. Creating a nonclustered index on those predicate columns enables seeks, directly addressing the observed plan.
Parameter sniffing; add OPTION (RECOMPILE).
You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?
Azure Advisor
Intelligent Insights
Automatic tuning
Automatic tuning continuously analyses workload telemetry and applies index create, drop, and rebuild recommendations without manual intervention, directly satisfying the requirement to adjust indexing based on workload patterns. Unlike manual index maintenance or Query Store alone, it acts automatically, making it the appropriate choice for Azure SQL Database.
Query Store
You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?
Data classification
Server-level audit
Diagnostic settings for SQLInsights
Query Store
Query Store continuously captures query text, execution plans and runtime statistics into internal catalog views, giving historical performance analysis without trace overhead. It satisfies the requirement that all queries be logged for later analysis, and can be enabled per database in Azure SQL Database.
You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?
Blocking
CPU bottleneck
I/O subsystem bottleneck
`PAGEIOLATCH_SH` waits occur when sessions block acquiring shared latches while pages are read from disk into the buffer pool, so sustained dominance points to the storage layer rather than CPU or locking. This satisfies the stem's heavy-workload constraint by identifying the I/O subsystem as the bottleneck, prompting investigation of disk latency and throughput.
Memory pressure
You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?
Automatic tuning
Query Performance Insight
Azure Monitor Alerts
Azure Monitor Alerts evaluates metric rules against Azure SQL Database telemetry and triggers notifications when thresholds such as CPU above 80% are breached. It provides the sustained monitoring and alerting the stem requires, unlike query-level tools or auditing features.
SQL Assessment
Want more Monitor, configure, and optimize database resources practice?
Practice this domainThe DP-300 exam has 50 questions and must be completed in 120 minutes. The passing score is 700/1000.
Scenario-based questions covering exam objectives with detailed answer explanations.
The exam covers 6 domains: Configure and manage automation of tasks, Plan and configure high availability and disaster recovery, Implement a secure environment, Plan and configure a high availability and disaster recovery environment, Plan and implement data platform resources, Monitor, configure, and optimize database resources. Questions are weighted by domain — higher-weight domains appear more on your actual exam.
No. These are original exam-style practice questions written against the official Microsoft DP-300 exam objectives. They are not copied from the real exam. Courseiva focuses on genuine understanding, not memorisation of braindumps.
Courseiva tracks your accuracy per domain and routes you toward weak areas automatically. Free, no account required.