Sample Questions For DP-300: Administering Relational Databases on Microsoft Azure

Hello, Data enthusiasts. Here are some sample questions for Microsoft Azure Database Administrator Associate, DP-300 Certification Exam. Correct answers with explanations are at the bottom. Check out the TestPrep reference link at the end of this blog post.

Here goes the Quiz:

1. You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.
   You plan to migrate DB1 to an Azure SQL Database managed instance.
   What should you use to minimize downtime and data loss during the migration?
   a. distributed availability groups
   b. database mirroring
   c. log shipping
   d. Database Migration Assistant
2. You have 20 Azure SQL databases provisioned by using the vCore purchasing model.
   You plan to create an Azure SQL Database elastic pool and add the 20 databases.
   Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution.
   a. total size of all the databases
   b. number of concurrently peaking databases * peak CPU utilization per database
   c. maximum number of concurrent sessions for all the databases
   d. total number of databases * average CPU utilization per database
   e. geo-replication support.
3. You have a new Azure SQL database. The database contains a column that stores confidential information.
   You need to track each time values from the column are returned in a query. The tracking information must be stored for 365 days from the date the query was executed.
   Which three actions should you perform? Each correct answer presents part of the solution.
   a. Add extended properties to the column.
   b. Apply sensitivity labels named Highly Confidential to the column.
   c. Turn on Azure Advanced Threat Protection (ATP).
   d. Turn on auditing and write audit logs to an Azure Storage account.
   e. Turn on Advanced-Data Security for the Azure SQL server.
4. You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 contains a table named CustomerPII.
   You need to record whenever users query the CustomerPII table.
   Which two options should you enable? Each correct answer presents part of the solution.
   a. SQL Server audit
   b. database audit specification
   c. server audit specification
   d. a server principal.
5. You have an Azure SQL database named sqldb1.
   You need to minimize the possibility of Query Store transitioning to a read-only state.
   What should you do?
   a. Decrease by half the value of Statistics Collection interval
   b. Double the value of Statistics Collection Interval
   c. Decrease by half the value of Data Flush Interval
   d. Double the value of Data Flush interval
6. You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.
   You scale up the virtual machine to 16 vCPUSs and 64 GB of memory.
   You need to provide the lowest latency for tempdb.
   What is the total number of data files that tempdb should contain?
   a. 2
   b. 4
   c. 6
   d. 8
7. You have SQL Server on an Azure virtual machine that contains a database named DB1. You have an application that queries DB1 to generate a sales report. You need to see the parameter values from the last time the query was executed. Which two actions should you perform? Each correct answer presents part of the solution.
   a. Enable Lightweight_Query_Profiling in DB1
   b. Enable PARAMETER_SNIFFING in DB1
   c. Enable Last_Query_Plan_Stats in the master database
   d. Enable Last_Query_Plan_Stats in DB1
   e. Enable Lightweight_Query_Profiling in the master database

8. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
   You have an Azure SQL database named Sales.
   You need to implement disaster recovery for Sales to meet the following requirements:
   * During normal operations provide at least two readable copies of Sales.
   * Ensure that Sales remains available if a datacenter fails.
   Solution: You deploy an Azure SQL database that uses the General Purpose service tier and failover groups.
   Does this meet the goal?
   a. Yes
   b. No
9. You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.
   You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
   Solution: You run the Remove-AzSqlDatabase PowerShell cmdlet for Database1 on Server2. You run the Restore-AzSqlDatabase PowerShell cmdlet for
   Database1 on Server2.
   Does this meet the goal?
   a. Yes
   b. No

10. You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
Solution: From the Azure portal, you delete Database1 from Server2, and then you create a new database on Server2 by using the backup of Database1 from Server1.
Does this meet the goal?
a. Yes
b. No

Check if your answers are correct or not here:

1. a
   Explanation: The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
2. a b & d
   Explanation: Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool
   Estimate the vCores needed for the pool as follows:
   For vCore-based purchasing model: MAX(<Total number of DBs X average vCore utilization per DB>, <Number of concurrently peaking DBs X Peak vCore utilization per DB)
3. b, d &e
   Explanation: You can apply sensitivity-classification labels persistently to columns by using new metadata attributes that have been added to the SQL Server database engine. This metadata can then be used for advanced, sensitivity-based auditing and protection scenarios. An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. Advanced-Data Security (ADS) is a unified package for advanced SQL security capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive data.
4. b & c
   Explanation: An auditing policy can be defined for a specific database or as a default server policy in Azure (which hosts SQL Database or Azure Synapse):
   A server policy applies to all existing and newly created databases on the server.
   If server auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings.
   Enabling auditing on the database, in addition to enabling it on the server, does not override or change any of the settings of the server auditing. Both audits will exist side by side. A Database Audit Specification defines which Audit Action Groups will be audited for the specific database in which the specification is created.
5. c.
   Explanation: The Max Size (MB) limit is not strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval
   (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode.
6. d.
   Explanation: The number of files depends on the number of (logical) processors on the machine. As a rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files, and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.
7. c & d
   Explanation: Last_Query_Plan_Stats allows you to enable or disable the collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.
   Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE
   SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.
8. b.
   Explanation: Instead, deploy an Azure SQL database that uses the business-critical service tier and Availability Zones. High availability is achieved by replicating both compute and storage to additional nodes creating a three to the four-node cluster.
9. b.
   Explanation: Instead, restore Database1 from Server1 to Server2 by using the RESTORE Transact-SQL command and the REPLACE option.
10. b.
    Explanation: Instead, restore Database1 from Server1 to Server2 by using the RESTORE Transact-SQL command and the REPLACE option.

Did you get a score above 70%? Then you are ready to crack the DP-300: Microsoft Azure Database Administrator Associate Certification Exam

Also, more sample questions are coming up, so keep checking. Please share your feedback in the below comments section.

Check out the TestPrep material here.

Disclaimer: These questions are NOT appearing in the certification exam. I personally or CloudThat do not have any official tie-up with Microsoft regarding the certification or the kind of questions asked. These are my best guesses for the kind of questions to expect with Microsoft in general and with the examination.

Enroll in the instructor-led Certification BootCamp for Exam DP-300: Administering Relational Databases on Microsoft Azure and grab your Microsoft Badge soon.

Feel free to drop any questions in the comment box, I would love to address them. I hope you enjoyed the article. Best of luck!

WRITTEN BY Anusha Shanbhag

Anusha Shanbhag is an AWS Certified Cloud Practitioner Technical Content Writer specializing in technical content strategizing with over 10+ years of professional experience in technical content writing, process documentation, tech blog writing, and end-to-end case studies publishing, catering to consulting and marketing requirements for B2B and B2C audiences. She is a public speaker and ex-president of the corporate Toastmaster club.