Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 201:

  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)

  Correct Answer: D

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

• Question 202:

  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.

  Correct Answer: B

• Question 203:

  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.

  Correct Answer: D

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure

• Question 204:

  You have an Azure SQL database named DB1.

  You need to encrypt DB1. The solution must meet the following requirements;

  1.

  Encrypt data in motion.

  2.

  Support comparison operators.

  3.

  Provide randomized encryption.

  What should you include in the solution?

  A. Always Encrypted with secure enclaves

  B. Always Encrypted

  C. column-level encryption

  D. Transparent Data Encryption (TDE)

  Correct Answer: A

  Always Encrypted with secure enclaves

  Always Encrypted with secure enclaves expands confidential computing capabilities of Always Encrypted by enabling in-place encryption and richer confidential queries.

  Always Encrypted protects the data by encrypting it on the client side and never allowing the data or the corresponding cryptographic keys to appear in plaintext inside the Database Engine. As a result, the functionality on encrypted columns

  inside the database is severely restricted. The only operations the Database Engine can perform on encrypted data are equality comparisons (only available with deterministic encryption). All other operations, including cryptographic

  operations (initial data encryption or key rotation) and richer queries (for example, pattern matching) aren't supported inside the database. Users need to move their data outside of the database to perform these operations on the client-side.

  Always Encrypted with secure enclaves addresses these limitations by allowing some computations on plaintext data inside a secure enclave on the server side. A secure enclave is a protected region of memory within the Database Engine

  process.

  The two key benefits of Always Encrypted with secure enclaves are in-place encryption and rich confidential queries.

  1.

  In-place encryption In-place encryption allows cryptographic operations on database columns inside the secure enclave, without moving the data outside of the database. In-place encryption improves the performance and the reliability of cryptographic operations.

  In-place encryption is allowed with both deterministic and randomized encryption, as long as the column encryption keys involved in a cryptographic operation are enclave-enabled.

  2.

  Confidential queries

  Confidential queries are DML queries that involve operations on enclave-enabled columns performed inside the secure enclave.

  Incorrect:

  * Always Encrypted Always Encrypted protects the data by encrypting it on the client side and never allowing the data or the corresponding cryptographic keys to appear in plaintext inside the Database Engine. As a result, the functionality on encrypted columns inside the database is severely restricted. The only operations the Database Engine can perform on encrypted data are equality comparisons (only available with deterministic encryption). All other operations, including cryptographic operations (initial data encryption or key rotation) and richer queries (for example, pattern matching) aren't supported inside the database. Users need to move their data outside of the database to perform these operations on the client-side.

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves

• Question 205:

  HOTSPOT

  You have an Azure subscription that contains a logical SQL server. The server hosts two databases named db1 and db2 and an Azure AD service principal named app1.

  You need to ensure that app1 can access db1. The solution must use the principle of least privilege.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: USER

  Azure Active Directory service principal with Azure SQL

  When an Azure AD application is registered using the Azure portal or a PowerShell command, two objects are created in the Azure AD tenant:

  An application object

  A service principal object

  SQL Database and SQL Managed Instance support the following Azure AD objects:

  Azure AD users (managed, federated, and guest)

  Azure AD groups (managed and federated)

  Azure AD applications

  The T-SQL command CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER on behalf of an Azure AD application is now supported for SQL Database.

  Box 2: FROM EXTERNAL PROVIDER

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal

• Question 206:

  HOTSPOT

  You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines. The virtual machine hosts a database named DB1.

  You need to monitor DB1 by using Extended Events. The solution must meet the following requirements:

  1.

  Capture raw event data and store the data in Azure Storage.

  2.

  Minimize the performance impact of capturing extended events.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: event_file

  SQL Server Extended Events tips

  Example:

  We can obtain information about the active event sessions, with the help of the sys.dm_xe_sessions dynamic management view. At the same time, this view returns information about the dropped event and buffer counts. To understand this

  concept more clearly, we will create two different extended event sessions. The first one is retention mode is Multiple event loss and the second one is Single event loss. These events capture the locks and SQL statement executions.

  CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER

  ADD EVENT sqlserver.lock_acquired,

  ADD EVENT sqlserver.lock_released,

  ADD EVENT sqlserver.lock_request_priority_state,

  ADD EVENT sqlserver.query_pre_execution_showplan,

  ADD EVENT sqlserver.sql_statement_completed,

  ADD EVENT sqlserver.sql_statement_starting

  ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')

  SQL Server reserves buffer memory for the event sessions to store the event data temporarily and then dispatch them to the target storage. In some cases, the extended event sessions generate data incredibly fast. However, the collected

  event data can not be dispatched to the event storage as fast as the event data generation speed. To overcome these types of cases, the EVENT_RETENTION_MODE setting allows tolerance to be set for data loss when the extended event

  is under pressure. So that, the event session impact on the database engine performance is minimized.

  ALLOW_SINGLE_EVENT_LOSS: This option is the default option and allows single events to be dropped and lost from the session when the event Session memory buffer is full and can not be sent to the storage target.

  ALLOW_MULTIPLE_EVENT_LOSS: This option affects the performance minimally and allows the entire memory buffer containing multiple events to be dropped and lost when the memory buffers are full.

  NO_EVENT_LOSS: When we use this option, we can ensure that no event data can be lost but it causes to degrade database engine performance.

  Example continued:

  ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')

  WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,

  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,

  TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

  Box 2: ALLOW_MULTIPLE_EVENT_LOSS

  SQL Server Extended Events EVENT_RETENTION_MODE option

  We want to minimize the performance impact of capturing extended events.

  Reference:

  https://www.sqlshack.com/sql-server-extended-events-tips/

• Question 207:

  HOTSPOT

  You have an Azure SQL managed instance named SQLMI1 that hosts multiple databases.

  You need to monitor the performance of SQLMI1 and identify which database uses the most memory and the most disk I/O.

  Which objects should you query? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: sys.dm_os_performance_counters

  sys.dm_os_performance_counters returns a row per performance counter maintained by the server.

  These include:

  * SQLServer:Memory Manager

  Provides information about SQL Server memory usage, such as the total number of lock structures currently allocated.

  Box 2: sys.dm_io_virtual_file_stats

  sys.dm_io_virtual_file_stats returns I/O statistics for data and log files.

  Examples

  A. Return statistics for a log file Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database

  The following example returns statistics for the log file in the AdventureWorks2019 database.

  SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2); GO

  B. Return statistics for file in tempdb Applies to: Azure Synapse Analytics

  SELECT * FROM sys.dm_pdw_nodes_io_virtual_file_stats WHERE database_name = 'tempdb' AND file_id = 2;

  Incorrect:

  *

  sys.dm_os_buffer_descriptors returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. In SQL Server, this dynamic management view also returns information about the data pages in the buffer pool extension file.

  *

  sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself.

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-objects https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql

• Question 208:

  HOTSPOT

  You have an Azure subscription.

  You need to deploy an Azure SQL managed instance that meets the following requirements:

  1.

  Optimize latency.

  2.

  Maximize the memory-to-vCore ratio.

  Which service tier and hardware generation should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Business Critical

  Optimize latency.

  Service tier characteristics

  SQL Managed Instance has two service tiers: General Purpose and Business Critical.

  Storage IO latency (approximate)

  General Purpose: 5-10 ms

  Business Critical: 1-2 ms

  Incorrect:

  * Hyperscale: The Hyperscale service tier is currently only available for Azure SQL Database, and not Azure SQL Managed Instance.

  Box 2: Memory optimized premium-series Maximize the memory-to-vCore ratio.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale

• Question 209:

  HOTSPOT

  You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.

  You need to ensure that User1 can perform the following tasks on DB1:

  1.

  Create jobs.

  2.

  View all jobs.

  3.

  Modify, delete, and disable the jobs the user created.

  The solution must use the principle of least privilege.

  Which built-in database role should you assign to User1, and where is the role defined? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

  Box 1: msdb

  SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

  SQLAgentUserRole

  SQLAgentReaderRole

  SQLAgentOperatorRole

  Box 2: SQLAgentReaderRole

  SQLAgentReaderRole Permissions

  SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and

  job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server

  Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

  Incorrect:

  *

  SQLAgentUserRole Permissions (too few)

  SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job

  schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available

  proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

  *

  SQLAgentOperatorRole Permissions (too much)

  SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and

  proxies, and enumerate available proxies and alerts on the server.

  SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server.

  Reference: https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles

• Question 210:

  HOTSPOT

  You have an Azure SQL database named DB1 in the General Purpose service tier.

  You need to monitor DB1 by using SQL Insights.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

  Box 1: The Azure Monitor agent

  SQL Insights performs all monitoring remotely. Monitoring agents on dedicated virtual machines connect to your SQL resources and remotely gather data. The gathered data is stored in Azure Monitor Logs to enable easy aggregation,

  filtering, and trend analysis. You can view the collected data from the SQL Insights workbook template, or you can delve directly into the data by using log queries.

  Box 2: A Log Analytics workspace

  Log Analytics workspaces

  For the Log Analytics workspaces, you're charged based on the pricing published on the Azure Monitor pricing page. The Log Analytics workspaces that SQL Insights uses will incur costs for data ingestion, data retention, and (optionally) data

  export.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview