Microsoft DP-300 - Administering Relational Databases on Microsoft Azure

Basic Concept: This question tests identity and authentication for Azure SQL and SQL Server workloads, including when to use contained users, directory identities, certificate validation, or authentication profiles.

Why C is Correct: A private endpoint exposes an Azure service through a private IP address in a virtual network, avoiding public endpoint dependency. The scenario asks for: You need to recommend a process to automate the management of DB3. That makes Configure a private endpoint for connectivity to DB3. the option that satisfies the required Azure SQL layer and operational outcome.

Why A is Wrong: Configure Microsoft Entra authentication for the logical server that hosts DB3. is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It is not the right enforcement point for this case; the scenario needs the control that governs the data or identity path being tested.

Why B is Wrong: Create a database that has database-scoped credentials. is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It does not satisfy the required identity, encryption, firewall, auditing, or data-exposure boundary described in the scenario: You need to recommend a process to automate the management of DB3.

Why D is Wrong: Create data base-scoped credentials in DB3. is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It is not the right enforcement point for this case; the scenario needs the control that governs the data or identity path being tested.

To generate an email alert to admin@contoso.com when CPU percentage utilization for db1 is higher than average, you can use the Azure portal to create an alert rule based on the CPU percentage metric. Here are the steps to do that:

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

Select Alerts in the Monitoring section and click on New alert rule.

In the Condition section, click Add and select the CPU percentage metric.

In the Configure signal logic page, set the threshold type to Dynamic. This will compare the current metric value to the historical average and trigger the alert when it deviates significantly1.

Set the operator to Greater than, the aggregation type to Average, the aggregation granularity to 1 minute, and the frequency of evaluation to 5 minutes.

Click Done to save the condition.

In the Action group section, click Create and enter a name and a short name for the action group.

In the Notifications section, click Add and select Email/SMS message/Push/Voice.

Enter admin@contoso.com in the Email field and click OK.

Click OK to save the action group.

In the Alert rule details section, enter a name and a description for the alert rule, choose a severity level, and make sure the rule is enabled.

Click Create alert rule to create the alert rule.

This alert rule will send an email to admin@contoso.com when the CPU percentage utilization for db1 is higher than average. You can also add other actions to the alert rule, such as calling a webhook or running an automation script

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

To prevent db1 from reverting to the last known good query plan, you need to disable the automatic plan correction feature for the database. This feature is enabled by default and allows the Query Store to detect and fix plan performance regressions by forcing the last good plan1. However, if you want to test the performance of different plans without interference from the Query Store, you can turn off this feature by using the ALTER DATABASE SCOPED CONFIGURATION statement2.

Here are the steps to disable the automatic plan correction feature 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: ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF); GO

This command will disable the automatic plan correction feature for db1 and allow the Query Optimizer to choose the best plan based on the current statistics and parameters3.

To verify that the automatic plan correction feature is disabled for db1, you can query the sys.database_scoped_configurations catalog view. The value of the force_last_good_plan column should be 0 for db1.

These are the steps to disable the automatic plan correction feature for db1.

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 configure your user account as the Azure AD admin for the server named sql3700689S, you can use the Azure portal or the Azure CLI. Here are the steps for both methods:

Using the Azure portal:

Go to the Azure portal and select SQL Server – Azure Arc.

Select the server named sql3700689S and click on Active Directory admin.

Click on Set admin and choose your user account from the list of Azure AD users.

Click on Select and then Save to confirm the change.

You can verify the Azure AD admin by clicking on Active Directory admin again and checking the current admin.

Using the Azure CLI:

Install the Azure CLI and log in with your Azure account.

Run the following command to get the object ID of your user account: az ad user show --id < your-user-name > --query objectId -o tsv

Run the following command to set your user account as the Azure AD admin for the server: az sql server ad-admin create --server sql3700689S --object-id < your-object-id > --display-name < your-user-name >

You can verify the Azure AD admin by running the following command: az sql server ad-admin show --server sql3700689S

These are the steps to configure your user account as the Azure AD admin for the server named sql3700689S.

To configure sq1370O6895 to support the creation of new database users from Azure AD identities, you need to do the following steps:

Set up a Microsoft Entra tenant and associate it with your Azure subscription. You can use the Microsoft Entra portal or the Azure portal to create and manage your Microsoft Entra users and groups12.

Configure a Microsoft Entra admin for sq1370O6895.  You can use the Azure portal or the Azure CLI to set a Microsoft Entra user as the admin for the server34. The Microsoft Entra admin can create other database users from Microsoft Entra identities5.

Connect to db1 using the Microsoft Entra admin account and run the following Transact-SQL statement to create a new database user from a Microsoft Entra identity: CREATE USER [Microsoft Entra user name] FROM EXTERNAL PROVIDER;6 You can replace the Microsoft Entra user name with the name of the user or group that you want to create in the database.

Grant the appropriate permissions to the new database user by adding them to a database role or granting them specific privileges. For example, you can run the following Transact-SQL statement to add the new user to the db_datareader role: ALTER ROLE db_datareader ADD MEMBER [Microsoft Entra user name];

These are the steps to configure sq1370O6895 to support the creation of new database users from Azure AD identities.

Requirement: Configure active geo-replication for Azure SQL Database db1. The geo-replica must be created in East US or East US 2 on the logical Azure SQL server:

sql60152867-dr.database.windows.net

The important point: in Azure SQL, the logical server name used in portal/T-SQL is usually:

sql60152867-dr

not the full FQDN.

Microsoft states that active geo-replication is configured per database, and a geo-secondary is created for an existing Azure SQL Database. After creation and seeding, changes from the primary are replicated asynchronously to the secondary.

Method 1 — Azure Portal Method

This is the safest method for the simulation if the portal is available.

Step 1: Open the primary database

Sign in to the Azure portal.

Search for SQL databases.

Select the database named db1.

Confirm you are looking at the primary database, not an existing secondary.

Step 2: Open the Replicas blade

In the left menu of the db1 database page, scroll to Data management.

Select Replicas.

Select Create replica.

Microsoft’s portal workflow is: open the database, go to Data management > Replicas, and choose Create replica.

Step 3: Configure the geo-secondary replica

On the Create SQL Database replica page, configure the target like this:

Setting

Value

Database

db1

Replica type

Geo replica / Active geo-replication

Target server

sql60152867-dr

Region

East US or East US 2

Database name

db1

Compute + storage

Keep same or compatible with primary

Elastic pool

Only choose this if the lab specifically requires an elastic pool

Do not create a failover group unless the task asks for one. This task says active geo replication, so configure a database-level geo-replica, not a failover group. Microsoft explicitly separates active geo-replication from failover groups and notes that active geo-replication is configured per database.

Step 4: Review and create

Select Review + create.

Confirm the target server is:

sql60152867-dr

Confirm the region is either:

East US

or

East US 2

Select Create.

Azure will create the secondary database and begin the seeding process. Microsoft notes that the secondary database has the same name as the primary by default and begins replication after it is created and seeded.

Step 5: Verify replication

After deployment completes:

Go back to the primary database db1.

Open Replicas again.

Under Geo replicas, confirm that a replica exists on:

sql60152867-dr.database.windows.net

Confirm the replica status is healthy, online, or synchronizing.

You can also open the target SQL server sql60152867-dr and verify that a database named db1 now exists there.

Method 2 — SSMS / T-SQL Method

Use this method if the portal is awkward or the exam simulation expects T-SQL.

Step 1: Allow SSMS connectivity

Before connecting with SSMS:

In Azure portal, open the primary SQL server hosting db1.

Go to Networking or Firewalls and virtual networks.

Add your client IP address.

Repeat this on the secondary server:

sql60152867-dr.database.windows.net

This matters because SSMS must be able to connect to the Azure SQL logical server.

Step 2: Connect to the primary server in SSMS

Open SQL Server Management Studio.

Connect to the primary Azure SQL logical server that hosts db1.

Use SQL admin credentials or Microsoft Entra admin credentials.

In Connection Properties, connect to the database:

master

This is important. For Azure SQL Database geo-replication setup through T-SQL, run the command from the master database on the primary server.

Step 3: Run the active geo-replication command

Run this query:

ALTER DATABASE [db1]

ADD SECONDARY ON SERVER [sql60152867-dr]

WITH (ALLOW_CONNECTIONS = ALL);

Microsoft documents that ALTER DATABASE ... ADD SECONDARY ON SERVER creates a secondary database for an existing Azure SQL Database and starts data replication. The official example also uses WITH (ALLOW_CONNECTIONS = ALL) to create a readable geo-secondary.

Step 4: Verify the replication link

Still connected to the primary server, run:

SELECT *

FROM sys.geo_replication_links;

Or use:

SELECT *

FROM sys.dm_geo_replication_link_status;

Microsoft lists sys.geo_replication_links and sys.dm_geo_replication_link_status as views used to return information about existing replication links and replication status.

Optional PowerShell Method

Only use this if the lab gives you Cloud Shell or PowerShell access.

New-AzSqlDatabaseSecondary `

-ResourceGroupName " < PrimaryResourceGroupName > " `

-ServerName " < PrimaryServerName > " `

-DatabaseName " db1 " `

-PartnerResourceGroupName " < SecondaryResourceGroupName > " `

-PartnerServerName " sql60152867-dr " `

-PartnerDatabaseName " db1 "

Microsoft identifies New-AzSqlDatabaseSecondary as the PowerShell cmdlet that creates a secondary database for an existing Azure SQL Database and starts replication.

Final Answer / What You Must Achieve

The task is complete when:

Database db1 still exists on the primary server.

A secondary database named db1 exists on:

sql60152867-dr.database.windows.net

The target server is in East US or East US 2.

The replica appears under db1 > Replicas > Geo replicas.

Replication status is healthy, online, seeding, or synchronizing.

You did not configure a failover group unless separately requested.

To configure high availability for dbl, you can use the failover groups feature of Azure SQL Database. Failover groups allow you to manage the replication and failover of a group of databases across different regions with the same connection strings1. You can choose all, or a subset of, user databases in a logical server to be replicated to another logical server in a different region. You can also specify the failover policy, such as manual or automatic, and the grace period for data loss.

Here are the steps to create a failover group for dbl:

Using the Azure portal:

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

Select Failover groups in the left menu and click on Add group.

Enter a name for the failover group and select a secondary region that is different from the primary region.

Click on Create a new server and enter the details for the secondary server, such as server name, admin login, password, and subscription.

Click on Select existing database(s) and choose dbl from the list of databases on the primary server.

Click on Configure failover policy and select the failover mode, grace period, and read-write failover endpoint mode according to your preferences.

Click on Create to create the failover group and start the replication of dbl to the secondary server.

Using PowerShell commands:

Install the Azure PowerShell module and log in with your Azure account.

Run the following command to create a new server in the secondary region: New-AzSqlServer -ResourceGroupName < your-resource-group-name > -ServerName < your-secondary-server-name > -Location " < secondary-region-name > " -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList " < your-admin-login > " , $(ConvertTo-SecureString -String " < your-password > " -AsPlainText -Force))

Run the following command to create a new failover group with dbl: New-AzSqlDatabaseFailoverGroup -ResourceGroupName < your-resource-group-name > -ServerName < your-primary-server-name > -PartnerResourceGroupName < your-resource-group-name > -PartnerServerName < your-secondary-server-name > -FailoverGroupName < your-failover-group-name > -Database dbl -FailoverPolicy < manual-or-automatic > -GracePeriodWithDataLossHours < grace-period-in-hours > -ReadWriteFailoverEndpoint " < enabled-or-disabled > "

You can modify the parameters of the command according to your preferences, such as the failover policy, grace period, and read-write failover endpoint mode.

These are the steps to create a failover group for dbl