Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 191:

  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.

  Correct Answer: D

  Reference: https://azure.microsoft.com/en-gb/blog/announcing-azure-monitor-aiops-alerts-with-dynamic-thresholds/

• Question 192:

  Which windowing function should you use to perform the streaming aggregation of the sales data?

  A. Sliding

  B. Hopping

  C. Session

  D. Tumbling

  Correct Answer: D

  Scenario: The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.

  Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.

  

  Reference: https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md

• Question 193:

  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

  Correct Answer: B

  Scenario: Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.

  To monitor the performance of a database in Azure SQL Database and Azure SQL Managed Instance, start by monitoring the CPU and IO resources used by your workload relative to the level of database performance you chose in selecting a particular service tier and performance level.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview

• Question 194:

  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

  Correct Answer: C

  The lifecycle management policy lets you:

  Delete blobs, blob versions, and blob snapshots at the end of their lifecycles

  Scenario:

  1.

  Purge Twitter feed data records that are older than two years.

  2.

  Store Twitter feeds in Azure Storage by using Event Hubs Capture. The feeds will be converted into Parquet files.

  3.

  Minimize administrative effort to maintain the Twitter feed data records. Incorrect Answers:

  A: Time-based retention policy support: Users can set policies to store data for a specified interval. When a time-based retention policy is set, blobs can be created and read, but not modified or deleted. After the retention period has expired, blobs can be deleted but not overwritten.

  Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-lifecycle-management-concepts

• Question 195:

  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 KEYconstraint

  B. a table the has an IDENTITY property

  C. a user-defined SEQUENCE object

  D. a system-versioned temporal table

  Correct Answer: B

  Scenario: Contoso requirements for the sales transaction dataset include: Implement a surrogate key to account for changes to the retail store addresses.

  A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

• Question 196:

  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

  Correct Answer: C

  The exhibits displays Database [State] properties.

  To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

  Reference:

  https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-options-page

• Question 197:

  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

  Correct Answer: C

  Example:

  Free space for the file in the below query result set will be returned by the FreeSpaceMB column.

  SELECT DB_NAME() AS DbName,

  name AS FileName,

  type_desc,

  size/128.0 AS CurrentSizeMB,

  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files

  WHERE type IN (0,1);

  Reference:

  https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/

• Question 198:

  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.

  Correct Answer: B

• Question 199:

  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

  Correct Answer: E

  Scenario: Authenticate database users by using Active Directory credentials.

  (Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01.)

  Authenticate the user in SQL Database or SQL Data Warehouse based on an Azure Active Directory user: CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql

• Question 200:

  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

  Correct Answer: AE

  SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.

  A: Use sys.dm_pdw_nodes_tran_locks instead of sys.dm_tran_locks from Azure Synapse Analytics (SQL Data Warehouse) or Parallel Data Warehouse.

  E: Example:

  The following query will show blocking information.

  SELECT

  t1.resource_type,

  t1.resource_database_id,

  t1.resource_associated_entity_id,

  t1.request_mode,

  t1.request_session_id,

  t2.blocking_session_id

  FROM sys.dm_tran_locks as t1

  INNER JOIN sys.dm_os_waiting_tasks as t2

  ON t1.lock_owner_address = t2.resource_address;

  Note: Depending on the system you’re working with you can access these wait statistics from one of three locations:

  sys.dm_os_wait_stats: for SQL Server sys.dm_db_wait_stats: for Azure SQL Database sys.dm_pdw_nodes_os_wait_stats: for Azure SQL Data Warehouse

  Incorrect Answers:

  F: sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2019 (15.x). Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be

  granted.

  Instead use sys.dm_pdw_nodes_tran_locks.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql