Free Microsoft DP-300 Practice Exam with Questions & Answers

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.

Box 1: DTU

Scenario:

The 30 new databases must scale automatically.

Once all requirements are met, minimize costs whenever possible.

You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model.

In short, for simplicity, the DTU model has an advantage. Plus, if you’re just getting started with Azure SQL Database, the DTU model offers more options at the lower end of performance, so you can get started at a lower price point than with vCore.

Box 2: An Azure SQL database elastic pool

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

Create an Azure Monitor metric alert rule on database db1 using the metric:

CPU percentage

Configure the condition as:

Aggregation: Average

Operator: Greater than

Threshold: 50

Aggregation granularity / Period: 1 minute

Frequency of evaluation: 1 minute

Evaluation period / Lookback window: 5 minutes

Action group email: admin@contoso.com

Azure SQL Database exposes CPU percentage as a platform metric, and Azure Monitor metric alerts can send notifications through action groups such as email. Microsoft describes SQL Database alerts as metric-based alerts that can send email when metrics such as CPU usage reach a defined threshold.

Azure Portal Method — Recommended for Simulation

Step 1: Open the db1 Azure SQL database

Sign in to the Azure portal.

Search for SQL databases.

Open the database named:

db1

Do not open the SQL logical server unless the alert needs to apply to all databases. This task is specifically for db1, so the alert scope must be the db1 database resource.

Step 2: Create a new alert rule

From the db1 database page:

In the left menu, select Alerts.

Select Create.

Select Alert rule.

Microsoft’s Azure Monitor workflow allows you to create an alert rule directly from the target resource. When you create it from a resource, the resource is automatically set as the alert scope.

Step 3: Confirm the alert scope

On the alert rule page, confirm the scope is the Azure SQL database:

db1

The resource type should be similar to:

SQL database

Microsoft.Sql/servers/databases

If the scope is the SQL server instead of the database, remove it and select the db1 database resource.

Step 4: Add the alert condition

Under Condition, select Add condition.

Choose the metric:

CPU percentage

Sometimes it appears as:

CPU percent

or metric name:

cpu_percent

For Azure SQL Database, CPU percentage represents CPU consumption toward the database workload limit, expressed as a percentage.

Step 5: Configure the signal logic

Configure the alert logic exactly like this:

Setting

Value

Threshold type

Static

Aggregation type

Average

Operator

Greater than

Threshold value

50

Unit

Percent

Aggregation granularity / Period

1 minute

Frequency of evaluation

1 minute

Evaluation period / Lookback window

5 minutes

This means Azure Monitor evaluates the CPU metric every minute, using one-minute metric samples, and fires the alert only when the average CPU percentage is greater than 50% across the five-minute evaluation window.

Be precise here. The task says:

average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals

So the correct choices are:

Average

Greater than 50

Every 1 minute

Over the last 5 minutes

Azure Monitor metric alert rules combine the monitored resource, the metric condition, and action groups that run when the condition is met.

Step 6: Create the email action group

Under Actions, select:

Create action group

Configure the basics:

Setting

Value

Subscription

Use the current subscription

Resource group

Use the lab resource group

Action group name

AG-db1-CPU-Email

Display name

db1CPU

Then go to Notifications.

Add a notification:

Setting

Value

Notification type

Email/SMS message/Push/Voice

Name

EmailAdmin

Email

admin@contoso.com

Select OK, then Review + create, then Create.

Action groups define the notification or automation action that runs when an alert fires. Microsoft documents email as a supported action group notification type.

Step 7: Configure alert rule details

Under Details, configure:

Setting

Value

Severity

2 or 3

Alert rule name

db1 CPU greater than 50 percent

Description

Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes.

Enable upon creation

Yes

Severity is usually not specified by the task, so any reasonable severity is acceptable. In an exam lab, I would use Severity 2 for CPU performance impact.

Step 8: Review and create

Select Review + create.

Confirm the condition says something equivalent to:

Whenever the average CPU percentage is greater than 50

Confirm the evaluation settings are:

Check every 1 minute

Lookback period 5 minutes

Confirm the action group sends email to:

admin@contoso.com

Select Create.

Verification

After creation:

Open db1.

Go to Alerts.

Select Alert rules.

Confirm the alert rule exists and is enabled.

Open the rule and verify:

Scope: db1

Signal: CPU percentage

Aggregation: Average

Operator: Greater than

Threshold: 50

Evaluation frequency: 1 minute

Window size: 5 minutes

Action group: admin@contoso.com

Azure CLI Method

Use this only if the simulation gives you Cloud Shell.

First get the database resource ID:

az sql db show \

--resource-group < resource-group-name > \

--server < sql-server-name > \

--name db1 \

--query id \

--output tsv

Create the action group:

az monitor action-group create \

--resource-group < resource-group-name > \

--name AG-db1-CPU-Email \

--short-name db1CPU \

--action email EmailAdmin admin@contoso.com

Create the metric alert:

az monitor metrics alert create \

--name " db1 CPU greater than 50 percent " \

--resource-group < resource-group-name > \

--scopes < db1-resource-id > \

--condition " avg cpu_percent > 50 " \

--window-size 5m \

--evaluation-frequency 1m \

--action AG-db1-CPU-Email \

--description " Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes. "

The metric name commonly used for Azure SQL Database CPU percentage in CLI/ARM contexts is:

cpu_percent

SSMS Clarification

SSMS is not the correct tool for this task.

Do not configure Database Mail. Azure SQL Database does not use SQL Server Agent/Database Mail in the same way as SQL Server on a VM or SQL Managed Instance. This requirement is an Azure Monitor metric alert requirement.

Correct tool:

Azure portal > db1 > Alerts > Create alert rule

Wrong tool:

SSMS Database Mail

Final Exam-Lab Configuration

Use this exact configuration:

Resource: db1

Alert type: Metric alert

Metric: CPU percentage

Aggregation: Average

Operator: Greater than

Threshold: 50

Aggregation granularity: 1 minute

Evaluation frequency: 1 minute

Evaluation period/window: 5 minutes

Action group notification: Email

Email recipient: admin@contoso.com

That completes the task.

To enable change data capture (CDC) for db1, you need to run the stored procedure sys.sp_cdc_enable_db in the database context. CDC is a feature that records activity on a database when tables and rows have been modified1. CDC can be used for various scenarios, such as data synchronization, auditing, or ETL processes2.

Here are the steps to enable CDC for db1:

Connect to db1 using SQL Server Management Studio, Azure Data Studio, or any other tool that supports Transact-SQL statements.

Open a new query window and run the following command: EXEC sys.sp_cdc_enable_db; GO

This command will enable CDC for the database and create the cdc schema, cdc user, metadata tables, and other system objects for the database3.

To verify that CDC is enabled for db1, you can query the is_cdc_enabled column in the sys.databases catalog view. The value should be 1 for db1.

These are the steps to enable CDC for db1

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

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.

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.