DP-300 Exam Dumps - Microsoft Certified: Azure Database Administrator Associate Questions and Answers

To ensure that all queries executed against dbl are captured in the Query Store, you need to enable the Query Store feature for the database and set the query capture mode to ALL. The Query Store feature provides you with insight on query plan choice and performance for Azure SQL Database1. The query capture mode controls whether all queries or only a subset of queries are tracked2.

Here are the steps to enable the Query Store and set the query capture mode to ALL for the database dbl:

Using the Azure portal:

Go to the Azure portal and select your Azure SQL Database server.

Select the database dbl and click on Query Performance Insight in the left menu.

Click on Configure Query Store and turn on the Query Store switch.

In the Query Capture Mode dropdown, select All and click on Save.

Using Transact-SQL statements:

Connect to the Azure SQL Database server and the database dbl using SQL Server Management Studio or Azure Data Studio.

Run the following command to enable the Query Store for the database: ALTER DATABASE dbl SET QUERY_STORE = ON;

Run the following command to set the query capture mode to ALL for the database: ALTER DATABASE dbl SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

These are the steps to ensure that all queries executed against dbl are captured in the Query Store.

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

Scenario: Business Requirements

Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.

Box 1: Cloud witness

If you have a Failover Cluster deployment, where all nodes can reach the internet (by extension of Azure), it is recommended that you configure a Cloud Witness as your quorum witness resource.

Box 2: Azure Basic Load Balancer

Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.

Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An internal listener uses an internal load balancer and only supports clients within the same Virtual Network.

Box 1: The server, the elastic pool, and the database

Senario:

SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool.

Litware technical requirements include: all SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.

Box 2: Azure Event hubs

Scenario: Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.

Event hubs are able to handle custom metrics.

Basic Concept: This question tests Azure monitoring and performance management, including which metric, log destination, or alerting behavior fits the operational requirement.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to implement the monitoring of SalesSQLDb1.

Why the alternate choices are Wrong: The alternate destinations or collection scopes miss part of the telemetry requirement: they either omit the server/pool/database layer or send data somewhere that does not support the requested analysis path.

Automating Azure SQL DB index and statistics maintenance using Azure Automation:

1. Create Azure automation account (Step 1)

2. Import SQLServer module (Step 2)

3. Add Credentials to access SQL DB

This will use secure way to hold login name and password that will be used to access Azure SQL DB

4. Add a runbook to run the maintenance (Step 3)

Steps:

1. Click on " runbooks " at the left panel and then click " add a runbook "

2. Choose " create a new runbook " and then give it a name and choose " Powershell " as the type of the runbook and then click on " create "

5. Schedule task (Step 4)

Steps:

1. Click on Schedules

2. Click on " Add a schedule " and follow the instructions to choose existing schedule or create a new schedule.

Scenario: Authenticate database users by using Active Directory credentials.

The configuration steps include the following procedures to configure and use Azure Active Directory authentication.

Create and populate Azure AD.

Optional: Associate or change the active directory that is currently associated with your Azure Subscription.

Create an Azure Active Directory administrator. (Step 1)

Configure your client computers.

Create contained database users in your database mapped to Azure AD identities. (Step 2)

Connect to your database by using Azure AD identities. (Step 3)

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

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why B is Correct: Update the connection strings of SalesSQLDb1App1. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. In this scenario, the important constraint is: What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1? Update the connection strings of SalesSQLDb1App1. satisfies that constraint without adding an unrelated service or manual process.

Why A is Wrong: Configure SalesSQLDb1 as writable. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It is not wrong technology in general, but it is the wrong HA/DR control for this scenario ' s failure model.

Why C is Wrong: Update the firewall rules of SalesSQLDb1. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It handles a different resilience pattern and would not deliver the failover or recovery behavior required here.

Why D is Wrong: Update the users in SalesSQLDb1. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. It is not wrong technology in general, but it is the wrong HA/DR control for this scenario ' s failure model.

Azure Database Migration service

Box 1: Premium 4-VCore

Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.

Minimize downtime during the migration of the SERVER1 databases.

Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations, and faster migration speeds.