CertifyCloudxAce every cloud certification.

DP-300 sample questions with answers

10 free practice questions for the Microsoft Certified: Azure Database Administrator Associate exam. Try each one, then open the answer to see why the right option wins and every other option loses.

Question 1Plan and implement data platform resources

Proseware must migrate a 4 TB SQL Server 2022 database to Azure SQL Managed Instance. Cutover downtime must be a few minutes, the source must stay fully writable until cutover, and the team wants to keep the source instance available to fail back to for a week afterwards. Which migration approach should you choose?

  1. A.

    Export the database to a BACPAC file in Azure Blob Storage and import it into the managed instance

  2. B.

    Use the Log Replay Service to restore full, differential and log backups that you copy to Azure Blob Storage, keeping the database in RESTORING mode until you run the cutover command

  3. C.

    Take a full backup to a URL and restore it to the managed instance with RESTORE DATABASE FROM URL

  4. D.

    Configure a Managed Instance link between the SQL Server instance and the managed instance, then fail over when the replica is synchronized

Show answer

Answer: D

Only the Managed Instance link keeps a continuously replicated, near-real-time copy that can be failed over in minutes and can also replicate back to SQL Server for fallback.

  • A. A BACPAC export is an offline operation; the source must be quiesced and a 4 TB export and import far exceeds a few minutes.
  • B. The Log Replay Service does give a low-downtime cutover, but the target database stays in RESTORING mode and is unusable until cutover, the chain can be interrupted by managed instance updates or failovers, and it offers no fail-back path to the source instance afterwards.
  • C. A single full backup and restore is an offline migration; every change made after the backup would be lost at cutover.
  • D. The Managed Instance link replicates the log continuously and fails over in minutes, and it can replicate back to SQL Server for fallback.
Question 2Plan and implement data platform resources

A TDE-protected SQL Server database will be migrated to Azure SQL Managed Instance by native backup and restore. You have already backed up the certificate that protects the database encryption key, with its private key, and converted the pair to a .pfx file. What must you do next so that the restore succeeds?

  1. A.

    Import the .pfx into a key vault and set the managed instance TDE protector to that key before starting the restore

  2. B.

    Upload the .pfx to the managed instance with Add-AzSqlManagedInstanceTransparentDataEncryptionCertificate, passing the base-64 encoded file and its password

  3. C.

    Create a database master key on the managed instance and restore the certificate into master with RESTORE CERTIFICATE ... WITH PRIVATE KEY

  4. D.

    Connect to the managed instance and run CREATE CERTIFICATE ... FROM FILE against the .pfx staged in Azure Blob Storage

Show answer

Answer: B

On a managed instance the source certificate is uploaded through the Azure control plane with Add-AzSqlManagedInstanceTransparentDataEncryptionCertificate; the Transact-SQL routes used on SQL Server are not available.

  • A. Setting the TDE protector configures which key encrypts the instance's databases from now on. It does not make the source certificate available, so the restore still fails.
  • B. This is the documented path: the base-64 encoded .pfx and its password are uploaded to the instance through Azure PowerShell, after which the encrypted backup can be restored.
  • C. This is the correct procedure on SQL Server, not on a managed instance. The instance does not let you restore a certificate into master; certificates for a TDE restore arrive through the Azure control plane.
  • D. CREATE CERTIFICATE ... FROM FILE reads from the instance's file system, which a managed instance does not expose to you. A blob path is not a substitute.
Question 3Plan and implement data platform resources

A 900 GB fact table in Azure SQL Managed Instance is queried almost exclusively by aggregate reports over date ranges. Rows older than one year are read a few times per quarter but must remain online. You must minimize storage consumption and IO for the analytic queries. Which two actions should you perform? (Choose TWO.)

Choose 2.

  1. A.

    Create a clustered columnstore index on the fact table

  2. B.

    Rebuild the partitions older than one year with DATACOMPRESSION = COLUMNSTOREARCHIVE

  3. C.

    Add a nonclustered rowstore index on every column used in the reports

  4. D.

    Schedule a weekly DBCC SHRINKDATABASE job

  5. E.

    Apply DATA_COMPRESSION = ROW to the clustered rowstore index and leave the table as a heap for older data

  6. F.

    Enable transparent data encryption on the database

Show answer

Answer: A, B

A clustered columnstore index gives the best compression and batch-mode scan performance for aggregate queries, and COLUMNSTORE_ARCHIVE further shrinks the cold partitions that are rarely read.

  • A. Clustered columnstore delivers the highest compression ratio and batch-mode scans for aggregate reporting over large fact tables.
  • B. COLUMNSTORE_ARCHIVE adds a second compression pass that suits rarely read partitions where extra CPU on read is acceptable.
  • C. Extra nonclustered rowstore indexes add storage and maintenance overhead, which is the opposite of the stated goal.
  • D. Shrinking reclaims free space but causes severe fragmentation and does not compress the data pages themselves.
  • E. Row compression gives the smallest space saving of the available options and keeps the reports in row-mode execution.
  • F. Transparent data encryption encrypts data at rest; it never reduces the size of the data or the IO required to read it.
Question 4Plan and implement data platform resources

Northwind Traders operates 60 SQL Server instances in its own datacenter and 12 more on another public cloud. Governance requires a single Azure inventory of those instances, best-practices assessments, and Microsoft Defender for SQL alerts, but the databases must stay where they are. Which approach should you recommend?

  1. A.

    Migrate every instance to SQL Server on Azure Virtual Machines and register them with the SQL IaaS Agent extension

  2. B.

    Deploy Azure Arc-enabled SQL Managed Instance on an Azure Arc-enabled Kubernetes cluster

  3. C.

    Install the Azure Monitor agent on each server and forward the SQL error log to a Log Analytics workspace

  4. D.

    Onboard each instance as an Azure Arc-enabled SQL Server resource

Show answer

Answer: D

Azure Arc-enabled SQL Server projects on-premises and other-cloud SQL Server instances into Azure Resource Manager so they gain inventory, assessments and Defender for SQL without moving.

  • A. Migrating to Azure virtual machines moves the databases, which the requirement explicitly forbids, and adds a large migration project.
  • B. Arc-enabled SQL Managed Instance is a containerized service that requires you to move the databases onto an Arc-enabled Kubernetes cluster.
  • C. The Azure Monitor agent only collects telemetry; it creates no SQL resource inventory and cannot enable Defender for SQL or assessments.
  • D. Arc-enabled SQL Server registers existing instances as Azure resources, giving inventory, best-practices assessment and Defender for SQL while the databases stay put.
Question 5Plan and implement data platform resources

Contoso runs a SQL Server instance on-premises that is needed heavily for six weeks each year and is scaled down or switched off the rest of the time. Finance wants to stop buying perpetual core licences for it and to be billed for actual use through the existing Azure agreement. What should you recommend?

  1. A.

    Migrate the instance to SQL Server on Azure Virtual Machines and choose a pay-as-you-go image

  2. B.

    Leave the instance on-premises and apply the Azure Hybrid Benefit to it

  3. C.

    Connect the instance to Azure Arc and subscribe it to Extended Security Updates

  4. D.

    Connect the instance to Azure Arc and set its licence type to pay-as-you-go so that it is billed through Azure

Show answer

Answer: D

SQL Server enabled by Azure Arc offers a pay-as-you-go licence type, which bills the on-premises instance hourly through Azure instead of requiring purchased licences.

  • A. This satisfies the billing requirement only by migrating the workload, which the scenario rules out by asking to keep the instance where it is.
  • B. Azure Hybrid Benefit applies licences you already own to Azure resources. It does not turn an on-premises instance into a consumption-billed one.
  • C. Extended Security Updates keeps an out-of-support version patched. It is not a licence for a supported version and does not change the billing model.
  • D. Arc-connected instances can be set to pay-as-you-go licensing and are then billed hourly through Azure for the cores in use, with no migration.
Question 6Plan and implement data platform resources

You are preparing to migrate a database from SQL Server 2022 to Azure SQL Managed Instance by using the Managed Instance link. Which two prerequisites must be in place? (Choose TWO.)

Choose 2.

  1. A.

    A private network connection, such as a VPN or Azure ExpressRoute, between the SQL Server network and the managed instance virtual network

  2. B.

    Certificate-based trust between the SQL Server instance and the managed instance

  3. C.

    The public endpoint of the managed instance enabled for replication traffic

  4. D.

    Change data capture enabled on the source database

  5. E.

    An auto-failover group configured on the managed instance

  6. F.

    The source database placed in the SIMPLE recovery model

Show answer

Answer: A, B

The link needs a private network path to the managed instance VNet-local endpoint and a certificate exchange that establishes trust; the other items are wrong or actively incompatible.

  • A. The link requires private connectivity to the managed instance VNet-local endpoint, provided by a VPN, ExpressRoute or virtual network peering.
  • B. Trust between the two systems is established by exchanging certificate public keys; Windows authentication cannot be used.
  • C. The public endpoint carries client traffic only and cannot be used to establish or carry link replication.
  • D. Change data capture is used by other replication approaches. The link uses distributed availability group log transport.
  • E. Failover groups and the link are mutually exclusive on a managed instance, so this would block the link rather than enable it.
  • F. The link replicates log records through an availability group, so the source cannot be in the SIMPLE recovery model.
Question 7Plan and implement data platform resources

A 40 GB Azure SQL Database supports an internal expenses application. The team has rewritten its nightly reconciliation to run as a SQL Server Agent job and now needs the database to live on an Azure SQL Managed Instance in the same subscription and region. A short outage over a weekend is acceptable, and the team has asked for the path that Microsoft supports rather than the fastest one. What should you do?

  1. A.

    Configure active geo-replication from the database to the managed instance and fail over when the secondary is synchronized, then remove the geo-replication link

  2. B.

    Take a point-in-time restore of the database directly onto the managed instance

  3. C.

    Export the database to a BACPAC file in Azure Blob Storage and import it into the managed instance

  4. D.

    Use az sql midb move start to relocate the database to the managed instance

Show answer

Answer: C

There is no native restore or move path from Azure SQL Database to a managed instance, so a BACPAC export and import is the supported way across that boundary.

  • A. Active geo-replication pairs an Azure SQL Database with another Azure SQL Database. It cannot pair a database with a managed instance.
  • B. Point-in-time restore targets the same service the backups came from; there is no cross-service restore.
  • C. A BACPAC is a logical package of schema and data, which is what makes it able to cross from Azure SQL Database to Azure SQL Managed Instance.
  • D. az sql midb move operates between managed instances. Its source must already be a managed database.
Question 8Plan and implement data platform resources

Contoso Foods runs an order-entry database on SQL Server 2019. The application depends on SQL Server Agent jobs, cross-database joins, and Service Broker. You must move the workload to a fully managed platform-as-a-service offering with the least application rework, and you must not patch operating systems. Which deployment option should you recommend?

  1. A.

    SQL Server on an Azure virtual machine sized for the workload

  2. B.

    Azure SQL Database, single database in the General Purpose service tier

  3. C.

    Azure SQL Database elastic pool in the Business Critical service tier

  4. D.

    Azure SQL Managed Instance, General Purpose service tier

Show answer

Answer: D

Only Azure SQL Managed Instance is a PaaS offering that provides SQL Server Agent, cross-database queries and Service Broker, so the application moves with almost no rework.

  • A. SQL Server on an Azure VM is infrastructure as a service; you remain responsible for patching the operating system and SQL Server.
  • B. Azure SQL Database has no SQL Server Agent or Service Broker and isolates each database, so the three dependencies would all require rework.
  • C. An elastic pool only shares resources across Azure SQL databases; it does not add Agent, Service Broker or cross-database query support.
  • D. Managed Instance is PaaS and supports SQL Server Agent, cross-database queries and Service Broker, so the application moves with minimal change.
Question 9Plan and implement data platform resources

A General Purpose managed instance is used only for user acceptance testing during a two-week window each quarter. Between windows it must keep its databases, its name and its network configuration, but it must not accrue compute or licensing charges. What should you do?

  1. A.

    Convert the instance to the serverless compute tier with an auto-pause delay

  2. B.

    Scale the instance down to its smallest supported vCore size between test windows

  3. C.

    Export each database to a BACPAC file, delete the instance, and recreate the instance and import the databases before each test window

  4. D.

    Stop the instance between test windows and start it again when testing resumes

Show answer

Answer: D

Stopping a General Purpose managed instance is the supported way to stop paying for compute and licensing while keeping the instance and its databases intact.

  • A. Serverless and auto-pause are Azure SQL Database compute tier features. They are not available for Azure SQL Managed Instance.
  • B. A running instance bills for its provisioned vCores and licensing at every size, so scaling down reduces the bill without eliminating it.
  • C. This destroys the instance name, network configuration and instance-scoped objects, and both provisioning and BACPAC import are slow, size-of-data operations.
  • D. Stopping a General Purpose managed instance stops compute and licensing charges while the instance, its name, its networking and its databases are retained.
Question 10Plan and implement data platform resources

A data warehouse load on SQL Server on an Azure virtual machine is capped by storage. The team has measured that it needs far more IOPS than a single premium SSD can provide, and it needs submillisecond latency on the transaction log. Which two actions should you take? (Choose TWO.)

Choose 2.

  1. A.

    Format the data disks with a 4 KB allocation unit size

  2. B.

    Stripe several premium SSD data disks into one storage pool by using Storage Spaces

  3. C.

    Set host caching to read/write on every data disk

  4. D.

    Place the transaction log on a Premium SSD v2 or an Azure Ultra Disk

  5. E.

    Move the data files to the local ephemeral D: drive

  6. F.

    Enable backup compression on the instance

Show answer

Answer: B, D

Striping aggregates IOPS across several disks, and Premium SSD v2 or Ultra Disk is the documented choice when the transaction log needs submillisecond latency.

  • A. SQL Server data disks should be formatted with a 64 KB allocation unit size; 4 KB is the default for the temporary drive only.
  • B. Striping disks with Storage Spaces aggregates IOPS and throughput up to the virtual machine limits, which is the documented way past a single disk ceiling.
  • C. Read/write host caching must not be enabled on disks holding SQL Server data or log files.
  • D. Premium SSD v2 and Azure Ultra Disk are the documented choices when the transaction log requires submillisecond latency.
  • E. The ephemeral disk is supported for tempdb only. Data files placed there are lost when the virtual machine is deallocated or moved.
  • F. Backup compression reduces backup size and backup I/O. It has no effect on the load workload that is capped by storage.

Keep going with 508 more DP-300 questions

Free papers every day, in the real exam formats, with progress by exam domain. Unlock every paper and timed mock exam when you are ready.

Practise freeSee all DP-300 papers
DP-300 sample questions with answers (10 free) · CertifyCloudx