Microsoft Role-based DP-300 Questions & Answers

• Question 1:

  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 2:

  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: C

  Scenario: SalesSQLDb1 uses database firewall rules and contained database users.

• Question 3:

  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

• Question 4:

  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: D

• Question 5:

  What should you use to migrate the PostgreSQL database?

  A. Azure Data Box

  B. AzCopy

  C. Azure Database Migration Service

  D. Azure Site Recovery

  Correct Answer: C

  Reference: https://docs.microsoft.com/en-us/azure/dms/dms-overview

• Question 6:

  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 7:

  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 8:

  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 9:

  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

  Correct Answer: B

  Scenario: In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The solution must provide automatic failover.

  The auto-failover groups feature allows you to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region. It is a declarative abstraction on top of the existing active georeplication feature, designed to simplify deployment and management of geo-replicated databases at scale. You can initiate failover manually or you can delegate it to the Azure service based on a user-defined policy.

  The latter option allows you to automatically recover multiple related databases in a secondary region after a catastrophic failure or other unplanned event that results in full or partial loss of the SQL Database or SQL Managed Instance

  availability in the primary region.

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview

• Question 10:

  Which audit log destination should you use to meet the monitoring requirements?

  A. Azure Storage

  B. Azure Event Hubs

  C. Azure Log Analytics

  Correct Answer: C

  Scenario: Use a single dashboard to review security and audit data for all the PaaS databases.

  With dashboards can bring together operational data that is most important to IT across all your Azure resources, including telemetry from Azure Log Analytics.

  Note: Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-monitor/visualize/tutorial-logs-dashboards