DP-300: Administering Relational Databases on Microsoft Azure Topic 1
Question #: 1
Topic #: 2
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.
NOTE: Each correct selection is worth one point.
A. Turn on auditing and write audit logs to an Azure Storage account.
B. Add extended properties to the column.
C. Turn on auditing and write audit logs to an Event Hub
D. Apply sensitivity labels named Highly Confidential to the column.
E. Turn on Azure Defender for SQL
Selected Answer: AD
Question #: 1
Topic #: 4
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.
NOTE: Each correct selection is worth one point.
A. Enable Last_Query_Plan_Stats in the master database
B. Enable Lightweight_Query_Profiling in DB1
C. Enable Last_Query_Plan_Stats in DB1
D. Enable Lightweight_Query_Profiling in the master database
E. Enable PARAMETER_SNIFFING in DB1
Selected Answer: BE
Question #: 1
Topic #: 20
Which windowing function should you use to perform the streaming aggregation of the sales data?
A. Sliding
B. Hopping
C. Session
D. Tumbling
Selected Answer: D
Question #: 1
Topic #: 15
You need to design a data retention solution for the Twitter feed data records. The solution must meet the customer sentiment analytics requirements.
Which Azure Storage functionality should you include in the solution?
A. time-based retention
B. change feed
C. lifecycle management
D. soft delete
Selected Answer: C
Question #: 1
Topic #: 12
What should you implement to meet the disaster recovery requirements for the PaaS solution?
A. Availability Zones
B. failover groups
C. Always On availability groups
D. geo-replication
Selected Answer: B
Question #: 1
Topic #: 21
You need to identify the cause of the performance issues on SalesSQLDb1.
Which two dynamic management views should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. sys.dm_pdw_nodes_tran_locks
B. sys.dm_exec_compute_node_errors
C. sys.dm_exec_requests
D. sys.dm_cdc_errors
E. sys.dm_pdw_nodes_os_wait_stats
F. sys.dm_tran_locks
Selected Answer: B
Question #: 1
Topic #: 6
You plan to move two 100-GB databases to Azure.
You need to dynamically scale resources consumption based on workloads. The solution must minimize downtime during scaling operations.
What should you use?
A. An Azure SQL Database elastic pool
B. SQL Server on Azure virtual machines
C. an Azure SQL Database managed instance
D. Azure SQL databases
Selected Answer: A
Question #: 1
Topic #: 1
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.
NOTE: Each correct selection is worth one point.
A. total size of all the databases
B. geo-replication support
C. number of concurrently peaking databases * peak CPU utilization per database
D. maximum number of concurrent sessions for all the databases
E. total number of databases * average CPU utilization per database
Selected Answer: ACE
Question #: 1
Topic #: 7
You have an Azure SQL database named DB3.
You need to provide a user named DevUser with the ability to view the properties of DB3 from Microsoft SQL Server Management Studio (SSMS) as shown in the exhibit. (Click the Exhibit tab.)
Which Transact-SQL command should you run?
A. GRANT SHOWPLAN TO DevUser
B. GRANT VIEW DEFINITION TO DevUser
C. GRANT VIEW DATABASE STATE TO DevUser
D. GRANT SELECT TO DevUser
Selected Answer: C
Question #: 1
Topic #: 3
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. Double the value of Data Flush interval
B. Decrease by half the value of Data Flush Interval
C. Double the value of Statistics Collection Interval
D. Decrease by half the value of Statistics Collection interval
Selected Answer: B
Question #: 1
Topic #: 9
You need to implement a solution to notify the administrators. The solution must meet the monitoring requirements.
What should you do?
A. Create an Azure Monitor alert rule that has a static threshold and assign the alert rule to an action group.
B. Add a diagnostic setting that logs QueryStoreRuntimeStatistics and streams to an Azure event hub.
C. Add a diagnostic setting that logs Timeouts and streams to an Azure event hub.
D. Create an Azure Monitor alert rule that has a dynamic threshold and assign the alert rule to an action group.
Selected Answer: D
Question #: 1
Topic #: 8
What should you use to migrate the PostgreSQL database?
A. Azure Data Box
B. AzCopy
C. Azure Database Migration Service
D. Azure Site Recovery
Selected Answer: C
Question #: 1
Topic #: 19
Based on the PaaS prototype, which Azure SQL Database compute tier should you use?
A. Business Critical 4-vCore
B. Hyperscale
C. General Purpose v-vCore
D. Serverless
Selected Answer: D
Question #: 1
Topic #: 13
You need to implement authentication for ResearchDB1. The solution must meet the security and compliance requirements.
What should you run as part of the implementation?
A. CREATE LOGIN and the FROM WINDOWS clause
B. CREATE USER and the FROM CERTIFICATE clause
C. CREATE USER and the FROM LOGIN clause
D. CREATE USER and the ASYMMETRIC KEY clause
E. CREATE USER and the FROM EXTERNAL PROVIDER clause
Selected Answer: E
Question #: 1
Topic #: 11
You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements.
What should you include in the plan?
A. Configure the Deleted databases settings for ResearchSrv01.
B. Deploy and configure an Azure Backup server.
C. Configure the Advanced Data Security settings for ResearchDB1.
D. Configure the Manage Backups settings for ResearchSrv01.
Selected Answer: D
Question #: 2
Topic #: 15
You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements.
What should you create?
A. a table that has a FOREIGN KEY constraint
B. a table the has an IDENTITY property
C. a user-defined SEQUENCE object
D. a system-versioned temporal table
Selected Answer: B
Question #: 2
Topic #: 20
Which counter should you monitor for real-time processing to meet the technical requirements?
A. SU% Utilization
B. CPU% utilization
C. Concurrent users
D. Data Conversion Errors
Selected Answer: B
Question #: 2
Topic #: 6
You have 10 Azure virtual machines that have SQL Server installed.
You need to implement a backup strategy to ensure that you can restore specific databases to other SQL Server instances. The solution must provide centralized management of the backups.
What should you include in the backup strategy?
A. Automated Backup in the SQL virtual machine settings
B. Azure Backup
C. Azure Site Recovery
D. SQL Server Agent jobs
Selected Answer: B
Question #: 2
Topic #: 2
You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:
✑ Ensure that all traffic to the public endpoint of SqlSrv1 is blocked.
✑ Minimize the possibility of VM1 exfiltrating data stored in SqlDb1.
What should you create on VNet1?
A. a VPN gateway
B. a service endpoint
C. a private link
D. an ExpressRoute gateway
Selected Answer: C
Question #: 2
Topic #: 5
You have the following Azure Data Factory pipelines:
✑ Ingest Data from System1
Ingest Data from System2 –
✑ Populate Dimensions
✑ Populate Facts
Ingest Data from System1 and Ingest Data from System2 have no dependencies. Populate Dimensions must execute after Ingest Data from System1 and Ingest
Data from System2. Populate Facts must execute after the Populate Dimensions pipeline. All the pipelines must execute every eight hours.
What should you do to schedule the pipelines for execution?
A. Add a schedule trigger to all four pipelines.
B. Add an event trigger to all four pipelines.
C. Create a parent pipeline that contains the four pipelines and use an event trigger.
D. Create a parent pipeline that contains the four pipelines and use a schedule trigger.
Selected Answer: D
Question #: 2
Topic #: 19
Which audit log destination should you use to meet the monitoring requirements?
A. Azure Storage
B. Azure Event Hubs
C. Azure Log Analytics
Selected Answer: C
Question #: 2
Topic #: 3
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. 8
D. 64
Selected Answer: C
Question #: 2
Topic #: 17
You need to recommend a solution to ensure that the customers can create the database objects. The solution must meet the business goals.
What should you include in the recommendation?
A. For each customer, grant the customer ddl_admin to the existing schema.
B. For each customer, create an additional schema and grant the customer ddl_admin to the new schema.
C. For each customer, create an additional schema and grant the customer db_writer to the new schema.
D. For each customer, grant the customer db_writer to the existing schema.
Selected Answer: B
Question #: 3
Topic #: 6
You need to recommend an availability strategy for an Azure SQL database. The strategy must meet the following requirements:
✑ Support failovers that do not require client applications to change their connection strings.
✑ Replicate the database to a secondary Azure region.
✑ Support failover to the secondary region.
What should you include in the recommendation?
A. failover groups
B. transactional replication
C. Availability Zones
D. geo-replication
Selected Answer: A
Question #: 3
Topic #: 1
You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.
FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance.
Which type of compression provides the greatest space reduction for the database?
A. page compression
B. row compression
C. columnstore compression
D. columnstore archival compression
Selected Answer: D
Question #: 3
Topic #: 17
You are evaluating the business goals.
Which feature should you use to provide customers with the required level of access based on their service agreement?
A. dynamic data masking
B. Conditional Access in Azure
C. service principals
D. row-level security (RLS)
Selected Answer: D
Question #: 3
Topic #: 4
You deploy a database to an Azure SQL Database managed instance.
You need to prevent read queries from blocking queries that are trying to write to the database.
Which database option should set?
A. PARAMETERIZATION to FORCED
B. PARAMETERIZATION to SIMPLE
C. Delayed Durability to Forced
D. READ_COMMITTED_SNAPSHOT to ON
Selected Answer: D
Question #: 3
Topic #: 5
You have an Azure Data Factory pipeline that performs an incremental load of source data to an Azure Data Lake Storage Gen2 account.
Data to be loaded is identified by a column named LastUpdatedDate in the source table.
You plan to execute the pipeline every four hours.
You need to ensure that the pipeline execution meets the following requirements:
✑ Automatically retries the execution when the pipeline run fails due to concurrency or throttling limits.
✑ Supports backfilling existing data in the table.
Which type of trigger should you use?
A. tumbling window
B. on-demand
C. event
D. schedule
Selected Answer: A
Question #: 3
Topic #: 11
What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?
A. Configure SalesSQLDb1 as writable.
B. Update the connection strings of SalesSQLDb1App1.
C. Update the firewall rules of SalesSQLDb1.
D. Update the users in SalesSQLDb1.
Selected Answer: B
Question #: 4
Topic #: 7
You have the following Transact-SQL query.
Which column returned by the query represents the free space in each file?
A. ColumnA
B. ColumnB
C. ColumnC
D. ColumnD
Selected Answer: C
Question #: 4
Topic #: 4
You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.
You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.
You need relieve the memory pressure.
What should you configure?
A. LEGACY_CARDINALITY_ESTIMATION
B. QUERY_OPTIMIZER_HOTFIXES
C. OPTIMIZE_FOR_AD_HOC_WORKLOADS
D. ACCELERATED_PLAN_FORCING
Selected Answer: C
Question #: 4
Topic #: 1
You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.
✑ Clustered columnstore indexes
✑ Automatic tuning
✑ Change tracking
✑ PolyBase
You plan to migrate DB1 to an Azure SQL database.
What feature should be removed or replaced before DB1 can be migrated?
A. Clustered columnstore indexes
B. PolyBase
C. Change tracking
D. Automatic tuning
Selected Answer: B
Question #: 4
Topic #: 3
You have 50 Azure SQL databases.
You need to notify the database owner when the database settings, such as the database size and pricing tier, are modified in Azure.
What should you do?
A. Create a diagnostic setting for the activity log that has the Security log enabled.
B. For the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled.
C. Create an alert rule that uses a Metric signal type.
D. Create an alert rule that uses an Activity Log signal type.
Selected Answer: B
Question #: 4
Topic #: 5
You have an Azure Data Factory that contains 10 pipelines.
You need to label each pipeline with its main purpose of either ingest, transform, or load. The labels must be available for grouping and filtering when using the monitoring experience in Data Factory.
What should you add to each pipeline?
A. an annotation
B. a resource tag
C. a run group ID
D. a user property
E. a correlation ID
Selected Answer: A
Question #: 5
Topic #: 6
You are building a database backup solution for a SQL Server database hosted on an Azure virtual machine.
In the event of an Azure regional outage, you need to be able to restore the database backups. The solution must minimize costs.
Which type of storage accounts should you use for the backups?
A. locally-redundant storage (LRS)
B. read-access geo-redundant storage (RA-GRS)
C. zone-redundant storage (ZRS)
D. geo-redundant storage (GRS)
Selected Answer: D
Question #: 5
Topic #: 3
You have several Azure SQL databases on the same Azure SQL Database server in a resource group named ResourceGroup1.
You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any additional databases that are created on the Azure
SQL server.
Which resource type should you use to create the alert?
A. Resource Groups
B. SQL Servers
C. SQL Databases
D. SQL Virtual Machines
Selected Answer: B
Question #: 5
Topic #: 4
You have SQL Server on an Azure virtual machine that contains a database named DB1.
You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit:
What should you do to ensure that the query uses the execution plan which executes in the least amount of time?
A. Force the query execution plan for plan 1221065.
B. Run the DBCC FREEPROCCACHE command.
C. Force the query execution plan for plan 1220917.
D. Disable parameter sniffing.
Selected Answer: A
Question #: 5
Topic #: 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. Always On Availability Group
D. Azure Database Migration Service
Selected Answer: D
Question #: 6
Topic #: 3
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 8 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. 8
D. 64
Selected Answer: C
Question #: 6
Topic #: 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
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 Data Lake Storage account that contains a staging zone.
You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes mapping data flow, and then inserts the data into the data warehouse.
Does this meet the goal?
A. Yes
B. No
Selected Answer: D
Question #: 6
Topic #: 6
You have SQL Server on Azure virtual machines in an availability group.
You have a database named DB1 that is NOT in the availability group.
You create a full database backup of DB1.
You need to add DB1 to the availability group.
Which restore option should you use on the secondary replica?
A. Restore with Recovery
B. Restore with Norecovery
C. Restore with Standby
Selected Answer: B
Question #: 7
Topic #: 3
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.
NOTE: Each correct selection is worth one point.
A. server audit specification
B. SQL Server audit
C. database audit specification
D. a server principal
Selected Answer: BC
Question #: 7
Topic #: 6
You are planning disaster recovery for the failover group of an Azure SQL Database managed instance.
Your company’s SLA requires that the database in the failover group become available as quickly as possible if a major outage occurs.
You set the Read/Write failover policy to Automatic.
What are two results of the configuration? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. In the event of a datacenter or Azure regional outage, the databases will fail over automatically.
B. In the event of an outage, the databases in the primary instance will fail over immediately.
C. In the event of an outage, you can selectively fail over individual databases.
D. In the event of an outage, you can set a different grace period to fail over each database.
E. In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour.
Selected Answer: AE
Question #: 7
Topic #: 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
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 Data Lake Storage account that contains a staging zone.
You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You schedule an Azure Databricks job that executes an R notebook, and then inserts the data into the data warehouse.
Does this meet the goal?
A. Yes
B. No
Selected Answer: A
Question #: 7
Topic #: 4
You have an Azure SQL database named DB1. You run a query while connected to DB1.
You review the actual execution plan for the query, and you add an index to a table referenced by the query.
You need to compare the previous actual execution plan for the query to the Live Query Statistics.
What should you do first in Microsoft SQL Server Management Studio (SSMS)?
A. For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
B. Run the SET SHOWPLAN_ALL Transact-SQL statement.
C. Save the actual execution plan.
D. Enable Query Store for DB1.
Selected Answer: C
Question #: 7
Topic #: 2
You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server.
You need to ensure that each customer can only connect to and access their respective database.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Implement row-level security (RLS).
B. Create users in each database.
C. Configure the database firewall.
D. Configure the server firewall.
E. Create logins in the master database.
F. Implement Always Encrypted.
Selected Answer: BE
Question #: 7
Topic #: 7
You plan to perform batch processing in Azure Databricks once daily.
Which type of Databricks cluster should you use?
A. automated
B. interactive
C. High Concurrency
Selected Answer: A
Question #: 8
Topic #: 6
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
A. Azure SQL Database Premium
B. Azure SQL Database serverless
C. Azure SQL Database Basic
D. Azure SQL Database Standard
Selected Answer: D
Question #: 8
Topic #: 4
You have an Azure SQL database.
Users report that the executions of a stored procedure are slower than usual. You suspect that a regressed query is causing the performance issue.
You need to view the query execution plan to verify whether a regressed query is causing the issue. The solution must minimize effort.
What should you use?
A. Performance Recommendations in the Azure portal
B. Extended Events in Microsoft SQL Server Management Studio (SSMS)
C. Query Store in Microsoft SQL Server Management Studio (SSMS)
D. Query Performance Insight in the Azure portal
Selected Answer: C
Question #: 8
Topic #: 3
You have an Azure virtual machine based on a custom image named VM1.
VM1 hosts an instance of Microsoft SQL Server 2019 Standard.
You need to automate the maintenance of VM1 to meet the following requirements:
✑ Automate the patching of SQL Server and Windows Server.
✑ Automate full database backups and transaction log backups of the databases on VM1.
✑ Minimize administrative effort.
What should you do first?
A. Enable a system-assigned managed identity for VM1
B. Register the Azure subscription to the Microsoft.Sql resource provider
C. Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1
D. Register the Azure subscription to the Microsoft.SqlVirtualMachine resource provider
Selected Answer: A
Question #: 8
Topic #: 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
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 Data Lake Storage account that contains a staging zone.
You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes an Azure Databricks notebook, and then inserts the data into the data warehouse.
Does this meet the goal?
A. Yes
B. No
Selected Answer: B
