DP-300 Administering Relational Databases on Microsoft Azure Question and Answers

To enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table in db1, you can use the following Transact-SQL script:

-- Connect to the Azure SQL database named db1

USE db1;

GO

-- Enable page compression on the clustered index

ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader

REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

This script will rebuild the clustered index with page compression, which can reduce the storage space and improve the query performance

The script solution consists of three parts:

The first part is USE db1; GO. This part connects to the Azure SQL database named db1, where the SalesLT.SalesOrderHeader table is located. The GO command separates the batches of Transact-SQL statements and sends them to the server.

The second part is ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader REBUILD WITH (DATA_COMPRESSION = PAGE); GO. This part enables page compression on the clustered index named PK_SalesOrderHeader_SalesOrderlD, which is defined on the SalesLT.SalesOrderHeader table. The ALTER INDEX statement modifies the properties of an existing index. The REBUILD option rebuilds the index from scratch, which is required to change the compression setting. The DATA_COMPRESSION = PAGE option specifies that page compression is applied to the index, which means that both row and prefix compression are used. Page compression can reduce the storage space and improve the query performance by compressing the data at the page level. The GO command ends the batch of statements.

The third part is optional, but it can be useful to verify the compression status of the index. It is SELECT name, index_id, data_compression_desc FROM sys.indexes WHERE object_id = OBJECT_ID( ' SalesLT.SalesOrderHeader ' );. This part queries the sys.indexes catalog view, which contains information about the indexes in the database. The SELECT statement returns the name, index_id, and data_compression_desc columns for the indexes that belong to the SalesLT.SalesOrderHeader table. The OBJECT_ID function returns the object identification number for the table name. The data_compression_desc column shows the compression type of the index, which should be PAGE for the clustered index after the script is executed.

These are the steps of the script solution for enabling page compression on the clustered index of the SalesLT.SalesOrderHeader table in db1.

Requirement: Configure differential backups for Azure SQL Database db1 to run once a day instead of twice a day.

Correct setting: Change Differential backup frequency from 12 Hours to 24 Hours.

Azure SQL Database supports differential backup frequency of either 12 hours or 24 hours. A 12-hour frequency means twice per day; a 24-hour frequency means once per day. Microsoft also notes that 24-hour differential backup frequency can increase restore time compared with 12-hour frequency.

Method 1 — Azure Portal Method

This is the best method for the simulation.

Step 1: Open the Azure SQL logical server

Sign in to the Azure portal.

Search for SQL servers.

Open the logical SQL server that hosts database db1.

Do not start from SQL Server Management Studio for this task. The differential backup frequency is an Azure SQL backup policy setting, not a normal T-SQL database setting.

Step 2: Open the Backups page

In the SQL server left menu, select Backups.

Select the Retention policies tab.

Microsoft’s documented portal path is to go to the logical SQL server, select Backups, then select the Retention policies tab.

Step 3: Select database db1

In the list of databases, locate db1.

Select the checkbox next to db1.

Select Configure policies from the action bar.

Step 4: Change the differential backup frequency

In the policy configuration pane:

Find Differential backup frequency.

Change it from:

12 Hours

to:

24 Hours

Leave the PITR retention period unchanged unless the task specifically tells you to change it.

Select Apply or Save.

Microsoft’s documented option is exactly 12 Hours or 24 hours under Differential backup frequency.

Method 2 — Azure CLI Method

Use this if the simulation provides Cloud Shell.

az sql db str-policy set \

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

--server < server-name > \

--name db1 \

--retention-days < current-retention-days > \

--diffbackup-hours 24

Example:

az sql db str-policy set \

--resource-group RG1 \

--server sql60152867 \

--name db1 \

--retention-days 7 \

--diffbackup-hours 24

Microsoft documents az sql db str-policy set with --diffbackup-hours 24 for changing active database differential backup frequency. Valid values are 12 or 24 hours.

Be careful: do not guess the retention days blindly in a real environment. In the exam lab, use the existing retention value shown in the portal unless the task also asks you to change retention.

Method 3 — PowerShell Method

Use this if Azure PowerShell is available.

Set-AzSqlDatabaseBackupShortTermRetentionPolicy `

-ResourceGroupName " < resource-group-name > " `

-ServerName " < server-name > " `

-DatabaseName " db1 " `

-RetentionDays < current-retention-days > `

-DiffBackupIntervalInHours 24

Microsoft documents Set-AzSqlDatabaseBackupShortTermRetentionPolicy with -DiffBackupIntervalInHours 24 for setting Azure SQL Database differential backup frequency.

SSMS / T-SQL Clarification

For this task, SSMS is not the right tool to change the setting.

There is no normal ALTER DATABASE T-SQL command in Azure SQL Database to change automated differential backup frequency. Microsoft documents this change through:

Azure portal

Azure CLI

PowerShell

REST API

not SSMS/T-SQL.

You may use SSMS only to confirm the database exists and is accessible, but the backup frequency setting must be changed from Azure management tools.

Enable Automatic tuning at the server level and turn Create Index to On.

Microsoft states that Azure SQL Database automatic tuning can create indexes automatically, verify performance improvement, and roll back changes if performance regresses. Server-level automatic tuning settings are applied to all databases on the server by default, unless a database has its own override.

Azure Portal Method — Recommended for Simulation

Step 1: Open the SQL logical server

Sign in to the Azure portal.

Search for SQL servers.

Open:

sql60152867

Do not open only db1. The task says all databases on sql60152867, so configure this at the server level.

Step 2: Open Automatic tuning

In the SQL server left menu:

Go to Intelligent Performance.

Select Automatic tuning.

Microsoft’s documented path is to open the Azure SQL server in the portal and select Automatic tuning from the menu.

Step 3: Enable automatic index creation

On the Automatic tuning page, configure:

Setting

Value

Create index

On

Drop index

Leave unchanged unless required

Force last good plan

Leave unchanged unless required

The only required setting is:

Create index = On

Do not confuse this with Drop index. The task only asks to ensure indexes are created automatically.

Step 4: Apply the setting

Select Apply or Save.

Wait for the portal confirmation.

The setting is now configured at the SQL logical server level.

Important Database Inheritance Check

This is the part people miss.

Server-level automatic tuning applies to databases that inherit from the server. Microsoft states that individual databases can override server-level automatic tuning settings. Therefore, if the simulation shows any database with custom automatic tuning settings, set that database to Inherit from server or manually set Create index = On for that database.

For each database, the correct inherited state should be:

Automatic tuning = Inherit from server

Create index = Inherited / On

If a database is set to:

Custom

Create index = Off

then the server-level setting might not affect that database. Fix it.

Database-Level T-SQL Method

Use this only if the portal is unavailable or you need to correct a specific database override.

Connect to each database and run:

ALTER DATABASE CURRENT

SET AUTOMATIC_TUNING (

CREATE_INDEX = ON

);

Microsoft documents this exact syntax for enabling the CREATE_INDEX automatic tuning option by T-SQL on an individual Azure SQL Database.

To make an individual database inherit server settings, run:

ALTER DATABASE CURRENT

SET AUTOMATIC_TUNING = INHERIT;

But this must be executed inside each database, not from master as a single server-wide T-SQL command.

Verification with T-SQL

Connect to a database and run:

SELECT

name,

desired_state_desc,

actual_state_desc

FROM sys.database_automatic_tuning_options

WHERE name = ' CREATE_INDEX ' ;

Expected result:

name CREATE_INDEX

desired_state_desc ON or DEFAULT

actual_state_desc ON

If desired_state_desc is DEFAULT, that usually means the database is inheriting the server-level setting. The key result is:

actual_state_desc = ON

SSMS Clarification

SSMS can be used to verify or configure the setting per database using T-SQL, but it is not the best tool for the requirement as written.

Because the task says:

all the databases on sql60152867

the correct primary method is:

Azure portal > SQL server > Automatic tuning > Create index = On

Final Exam-Lab Action

Configure this:

SQL server: sql60152867

Automatic tuning

Create index: On

Apply

Then verify databases are inheriting the server setting or have CREATE_INDEX = ON.

That completes the task.

Apply an Azure Resource Manager Delete lock / CanNotDelete lock directly to the Azure SQL database resource db1.

Microsoft states that Azure resource locks can be applied at subscription, resource group, or resource scope to protect resources from accidental deletion or modification. In the Azure portal, the lock types are shown as Delete and Read-only; in CLI/PowerShell, they are called CanNotDelete and ReadOnly. A CanNotDelete/Delete lock allows users to read and modify the resource, but prevents deletion.

Azure Portal Method — Recommended for Simulation

Step 1: Open the database resource

Sign in to the Azure portal.

In the search bar, search for SQL databases.

Select the database named db1.

Make sure you select the database resource itself, not only the SQL logical server.

Step 2: Open Locks

In the left menu of db1, scroll to Settings.

Select Locks.

Select Add.

Step 3: Create the delete lock

Configure the lock as follows:

Setting

Value

Lock name

PreventDelete-db1

Lock type

Delete

Notes

Prevent accidental deletion of db1

Then select OK or Save.

In the portal, choose Delete, not Read-only. A Read-only lock is too restrictive because it can block management updates. For this task, the requirement is only to stop accidental deletion, so Delete / CanNotDelete is the correct lock type. Microsoft confirms that CanNotDelete prevents deletion but still permits reading and modifying the resource.

Step 4: Verify the lock

Stay on the db1 database page.

Go back to Locks.

Confirm the lock exists with:

Name: PreventDelete-db1

Lock type: Delete

The task is complete once db1 has a Delete lock applied.

PowerShell Method

Use this if the lab provides Azure PowerShell.

New-AzResourceLock `

-LockLevel CanNotDelete `

-LockName " PreventDelete-db1 " `

-LockNotes " Prevent accidental deletion of db1 " `

-ResourceGroupName " < resource-group-name > " `

-ResourceName " < sql-server-name > /db1 " `

-ResourceType " Microsoft.Sql/servers/databases "

Microsoft’s New-AzResourceLock documentation includes an Azure SQL Database example using resource type Microsoft.Sql/servers/databases and resource name format serverName/databaseName.

Example format:

New-AzResourceLock `

-LockLevel CanNotDelete `

-LockName " PreventDelete-db1 " `

-LockNotes " Prevent accidental deletion of db1 " `

-ResourceGroupName " RG1 " `

-ResourceName " sql60152867/db1 " `

-ResourceType " Microsoft.Sql/servers/databases "

Replace RG1 and sql60152867 with the actual resource group and SQL logical server that hosts db1.

Azure CLI Method

Use Azure CLI only if the lab gives Cloud Shell and you know the full resource ID.

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

Then create the lock:

az resource lock create \

--name PreventDelete-db1 \

--lock-type CanNotDelete \

--resource < database-resource-id > \

--notes " Prevent accidental deletion of db1 "

Azure CLI supports resource-level lock creation with --lock-type CanNotDelete or ReadOnly.

SSMS / T-SQL Clarification

SSMS is not the correct tool for this task.

A delete lock is an Azure Resource Manager control-plane setting, not a SQL data-plane setting. SQL Server Management Studio can manage database objects and run T-SQL, but it cannot create Azure portal deletion protection locks for an Azure SQL Database.

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.

Configure Dynamic Data Masking on the email column in:

SalesLT.Customer

The column is normally:

EmailAddress

Use the built-in masking function:

email()

Microsoft documents that Dynamic Data Masking hides sensitive data in query results for nonprivileged users without changing the stored data. The built-in email() masking function exposes the first letter and returns the masked format aXXX@XXXX.com, which exactly matches the requirement.

Method 1 — SSMS / T-SQL Method

This is the fastest and most reliable method.

Step 1: Connect to db1

Open SQL Server Management Studio.

Connect to the Azure SQL logical server that hosts db1.

Open a query window against database:

db1

Step 2: Apply the email mask

Run:

ALTER TABLE [SalesLT].[Customer]

ALTER COLUMN [EmailAddress]

ADD MASKED WITH (FUNCTION = ' email() ' );

This adds a Dynamic Data Masking rule to the EmailAddress column. The actual email address remains stored in the table, but users without permission to view unmasked data will see the masked value. Microsoft’s documented syntax for adding an email mask is ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ' email() ' ).

Step 3: Verify that the column is masked

Run:

SELECT

OBJECT_SCHEMA_NAME(mc.object_id) AS schema_name,

OBJECT_NAME(mc.object_id) AS table_name,

c.name AS column_name,

mc.masking_function

FROM sys.masked_columns AS mc

JOIN sys.columns AS c

ON mc.object_id = c.object_id

AND mc.column_id = c.column_id

WHERE OBJECT_SCHEMA_NAME(mc.object_id) = ' SalesLT '

AND OBJECT_NAME(mc.object_id) = ' Customer '

AND c.name = ' EmailAddress ' ;

Expected result:

schema_name SalesLT

table_name Customer

column_name EmailAddress

masking_function email()

Step 4: Test as a non-administrative user

If you have a test user, run:

EXECUTE AS USER = ' TestUser ' ;

SELECT TOP (10)

EmailAddress

FROM SalesLT.Customer;

REVERT;

Expected output should look like:

aXXX@XXXX.com

bXXX@XXXX.com

cXXX@XXXX.com

A user with administrative privileges, db_owner, or UNMASK permission can still see the original email value. Microsoft states that users with administrative rights such as server admin, Microsoft Entra admin, and db_owner can view the original unmasked data.

Method 2 — Azure Portal Method

Use this if the simulation expects portal configuration.

Step 1: Open db1

Sign in to the Azure portal.

Search for SQL databases.

Open database:

db1

Step 2: Open Dynamic Data Masking

From the db1 page:

Security > Dynamic Data Masking

Microsoft states that for Azure SQL Database, Dynamic Data Masking can be configured in the Azure portal from the SQL database configuration pane under Security > Dynamic Data Masking.

Step 3: Add a masking rule

Add a mask for the email column:

Setting

Value

Schema

SalesLT

Table

Customer

Column

EmailAddress

Masking field format

Email

Masking function

email()

Then select:

Save

The portal may show the mask type simply as:

Email

That is the correct option because it maps to the email() masking function.

Important Permission Check

Dynamic Data Masking only affects users who do not have permission to view unmasked data.

If a non-administrative user was previously granted UNMASK, remove it:

REVOKE UNMASK TO [UserName];

Or, if a role was granted UNMASK, revoke it from the role:

REVOKE UNMASK TO [RoleName];

Do not grant UNMASK to normal users. UNMASK allows users to bypass masking and see the original values. Microsoft documents that UNMASK permission controls whether users can view masked or original data.

Final Exam-Lab Action

Run this against db1:

ALTER TABLE [SalesLT].[Customer]

ALTER COLUMN [EmailAddress]

ADD MASKED WITH (FUNCTION = ' email() ' );

Then verify:

SELECT

OBJECT_SCHEMA_NAME(object_id) AS schema_name,

OBJECT_NAME(object_id) AS table_name,

name AS column_name,

masking_function

FROM sys.masked_columns

WHERE OBJECT_SCHEMA_NAME(object_id) = ' SalesLT '

AND OBJECT_NAME(object_id) = ' Customer '

AND name = ' EmailAddress ' ;

The task is complete when non-administrative users querying SalesLT.Customer.EmailAddress see masked email values such as:

aXXX@XXXX.com

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.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why B is Correct: an Azure Blob Storage container matches the expected DP-300 administration action. an Azure Blob Storage container is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. The question is not asking for a general Azure capability; it is asking for the feature that produces this result: You need to identify the event_flle target for monitonng DB3 after the migration to Azure SQL Database.

Why A is Wrong: an Azure SQL database is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.

Why C is Wrong: a SQL Server filegroup is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It would solve a neighboring problem, but not the deployment, sizing, or migration requirement in this item.

Why D is Wrong: an Azure Files share is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why B is Correct: an Azure Blob Storage container is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. The scenario asks for: You need to identify the event_file target for monitonng DB3 after the migration to Azure SQL Database. That makes an Azure Blob Storage container the option that satisfies the required Azure SQL layer and operational outcome.

Why A is Wrong: an Azure SQL database is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It applies to a different Azure data-platform design than the one the question is testing.

Why C is Wrong: a SQL Server filegroup is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.

Why D is Wrong: an Azure Files share is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It applies to a different Azure data-platform design than the one the question is testing.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

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 recommend a service tier and a method to offload analytical workloads for the databases migrated from SVR1.

Why the alternate choices are Wrong: The alternate selections would either use a migration tier that does not match the downtime requirement, choose a network resource that does not support the migration path, or add an unnecessary deployment component.

Basic Concept: This question tests secure database administration, where the control must match the data state, access boundary, identity model, or compliance requirement.

Why B is Correct: a private endpoint matches the expected DP-300 administration action. A private endpoint exposes an Azure service through a private IP address in a virtual network, avoiding public endpoint dependency. The question is not asking for a general Azure capability; it is asking for the feature that produces this result: You need to recommend a solution that will enable remote developers to access DB1 and DB2.

Why A is Wrong: a public endpoint via a database-level firewall rule 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 solution that will enable remote developers to access DB1 and DB2.

Why C is Wrong: a public endpoint via a server-level firewall rule 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 D is Wrong: a Point-to-Site (P2S) VPN 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 solution that will enable remote developers to access DB1 and DB2.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

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 recommend an authentication solution for App1 access to DB1 and DB2 after their migration to Instance1.

Why the alternate choices are Wrong: The alternate selections would either use a migration tier that does not match the downtime requirement, choose a network resource that does not support the migration path, or add an unnecessary deployment component.

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.

To ensure that db1 is compatible with all the features and syntax of SQL Server 2012, you need to set the compatibility level of the database to 110, which is the compatibility level for SQL Server 20121. The compatibility level affects the behavior of certain Transact-SQL statements and features, and determines how the database engine interprets the SQL code2.

You can set the compatibility level of db1 by using the Azure portal or Transact-SQL statements. Here are the steps for both methods:

Using the Azure portal:

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

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

Click on Configure Query Store and select 110 from the Compatibility level dropdown list.

Click on Save to apply the change.

Using Transact-SQL statements:

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 db1 SET COMPATIBILITY_LEVEL = 110; GO

This command will set the compatibility level of db1 to 110, which is equivalent to SQL Server 2012.

These are the steps to set the compatibility level of db1 to 110. 

Enable Azure SQL Auditing at the server level on the Azure SQL logical server:

sql60152867

and configure the audit destination as the Azure Storage account:

sa60152867

This is the correct solution because the task says all databases on sql60152867. Database-level auditing would only apply to one database. Server-level auditing applies to all existing and newly created databases on the logical server. Microsoft states that server auditing policies apply to all existing and newly created databases on the server. The default auditing policy includes BATCH_COMPLETED_GROUP, which audits queries and stored procedures executed against the database, plus successful and failed authentication events.

Azure Portal Method — Recommended for Simulation

Step 1: Open the Azure SQL logical server

Sign in to the Azure portal.

Search for:

SQL servers

Open the SQL logical server named:

sql60152867

Be careful with the name. In your prompt, it appears as sql60l 52867, but the earlier tasks strongly indicate the real server name is probably:

sql60152867

Use the SQL logical server that hosts the databases, not a single SQL database.

Step 2: Open Auditing

On the SQL server page:

In the left menu, go to Security.

Select Auditing.

Microsoft’s setup path is to navigate to Auditing under the Security heading in either the SQL database or SQL server pane. For this task, you must use the SQL server pane because the requirement covers all databases.

Step 3: Enable server-level auditing

Set:

Enable Azure SQL Auditing = On

or:

Auditing = On

depending on the portal wording.

This enables the server-level audit policy.

Step 4: Select Storage as the audit destination

Under Audit log destination, select:

Storage

Then choose the storage account:

sa60152867

Microsoft states that Azure SQL auditing can write database events to an Azure storage account, Log Analytics workspace, or Event Hubs. For this simulation, the destination must be the storage account sa60152867, so do not choose Log Analytics or Event Hub unless the task separately asks for them.

Step 5: Configure storage authentication

If the portal asks for authentication type, use one of these depending on what is available in the lab:

Option

Use when

Managed Identity

Preferred if available

Storage access keys

Use if the lab expects the older/default storage-key method

Microsoft recommends managed identity for storage authentication because storage access keys are a security risk. However, many exam simulations still accept the default storage-key flow as long as the audit destination is correctly set to the required storage account.

Step 6: Leave default audit action groups enabled

Do not remove the default audit action groups.

The default Azure SQL auditing policy includes:

BATCH_COMPLETED_GROUP

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

FAILED_DATABASE_AUTHENTICATION_GROUP

BATCH_COMPLETED_GROUP is the key one here because it captures completed T-SQL batches, meaning the queries executed against the databases. Authentication and principal fields identify the login/user context. Microsoft states that the default auditing policy audits all queries and stored procedures executed against the database, plus successful and failed logins.

Step 7: Save the auditing configuration

Select:

Save

Wait for the portal notification that the auditing settings were saved successfully.

That completes the required configuration.

Verification

Verify from the Azure portal

Open the SQL server:

sql60152867

Go to:

Security > Auditing

Confirm:

Auditing: On

Destination: Storage

Storage account: sa60152867

Scope: Server-level

Verify storage output

After users execute queries, audit files should appear in the storage account.

Open storage account:

sa60152867

Go to Containers.

Open:

sqldbauditlogs

Azure SQL audit logs written to Azure Storage are stored in a container named sqldbauditlogs. Audit logs are written as .xel files and can be opened with SQL Server Management Studio.

What Information Is Captured?

The audit log includes the exact fields needed for this task.

Required information

Audit log field

Query executed

statement / statement_s

Database name

database_name / database_name_s

User context

database_principal_name / database_principal_name_s

Login context

server_principal_name / server_principal_name_s

Session principal

session_server_principal_name / session_server_principal_name_s

Time of execution

event_time / event_time_t

Client IP

client_ip / client_ip_s

Microsoft’s audit log format documents statement as the T-SQL statement that was executed, database_principal_name as the database user context, and server_principal_name as the current login.

SSMS Method — Viewing the Audit Logs

SSMS is not the main tool to enable Azure SQL auditing in this simulation, but it can be used to read the .xel audit files after they are written.

Step 1: Download or access the .xel files

From the storage account:

sa60152867 > Containers > sqldbauditlogs

Download the .xel audit file, or access it through supported tooling.

Step 2: Open the audit file in SSMS

Open SQL Server Management Studio.

Go to:

File > Open > File

Select the .xel audit file.

Review fields such as:

statement

database_principal_name

server_principal_name

database_name

event_time

client_ip

This verifies that the queries and the users that executed them are being captured.

Azure CLI Method

Use this only if Cloud Shell is available and you know the resource group names.

az sql server audit-policy update \

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

--name sql60152867 \

--state Enabled \

--storage-account sa60152867

Depending on the environment, you may also need to specify the storage endpoint and access key, or use managed identity configuration. In the portal simulation, the UI normally handles this for you.

PowerShell Method

Use this if Azure PowerShell is available.

Set-AzSqlServerAudit `

-ResourceGroupName " < resource-group-name > " `

-ServerName " sql60152867 " `

-BlobStorageTargetState Enabled `

-StorageAccountResourceId " /subscriptions/ < subscription-id > /resourceGroups/ < storage-rg > /providers/Microsoft.Storage/storageAccounts/sa60152867 "

This configures auditing at the server level, which is the correct scope for all databases.

Final Exam-Lab Action

Configure this in the Azure portal:

SQL server: sql60152867

Security > Auditing

Auditing: On

Audit destination: Storage

Storage account: sa60152867

Default audit action groups: Enabled

Save

That captures queries and the users who executed them for all databases on the SQL logical server and stores the audit records in sa60152867.

To configure a disaster recovery solution for db1, 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 also use active geo-replication as an alternative, but you will need to update the connection strings manually after a failover2.

Here are the steps to create a failover group for db1 with the secondary server in the West US 3 region:

Using the Azure portal:

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

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

Enter a name for the failover group and select West US 3 as the secondary 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 db1 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 db1 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 West US 3 region: New-AzSqlServer -ResourceGroupName < your-resource-group-name > -ServerName < your-secondary-server-name > -Location " West US 3 " -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 db1: 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 db1 -FailoverPolicy Manual -GracePeriodWithDataLossHours 1 -ReadWriteFailoverEndpoint " Enabled "

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 db1 with the secondary server in the West US 3 region.

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 ensure that any enhancements made to the Query Optimizer through patches are available to dbl and db2 on sql37006895, you need to enable the query optimizer hotfixes option for each database. This option allows you to use the latest query optimization improvements that are not enabled by default1. You can enable this option by using the ALTER DATABASE SCOPED CONFIGURATION statement2.

Here are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895:

Connect to sql37006895 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 commands for each database:

-- Switch to the database context

USE dbl;

GO

-- Enable the query optimizer hotfixes option

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

GO

Repeat the same commands for db2, replacing dbl with db2 in the USE statement.

To verify that the query optimizer hotfixes option is enabled for each database, you can query the sys.database_scoped_configurations catalog view. The value of the query_optimizer_hotfixes column should be 1 for both databases.

These are the steps to enable the query optimizer hotfixes option for dbl and db2 on sql37006895.

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

SQL injection attacks are a type of cyberattack that exploit a vulnerability in the application code that interacts with the database. An attacker can inject malicious SQL statements into the user input, such as a form field or a URL parameter, and execute them on the database server, resulting in data theft, corruption, or unauthorized access1.

To protect all the databases on sql37006S95 from SQL injection attacks, you need to follow some best practices for securing your application and database layers. Here are some of the recommended steps:

Use parameterized queries or stored procedures to separate the SQL code from the user input. This will prevent the user input from being interpreted as part of the SQL statement and avoid SQL injection23.

Validate and sanitize the user input before passing it to the database. This will ensure that the input conforms to the expected format and type, and remove any potentially harmful characters or keywords4.

Implement least privilege access for the database users and roles. This will limit the permissions and actions that the application can perform on the database, and reduce the impact of a successful SQL injection attack5.

Enable Advanced Threat Protection for Azure SQL Database. This is a feature that detects and alerts you of anomalous activities and potential threats on your database, such as SQL injection, brute force attacks, or unusual access patterns. You can configure the alert settings and notifications using the Azure portal or PowerShell.

These are some of the steps to protect all the databases on sql37006S95 from SQL injection attacks.

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.

Connect to db1 in SQL Server Management Studio and run:

ALTER DATABASE [db1]

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000);

Microsoft documents that Query Store maximum storage size is changed with:

ALTER DATABASE < database_name >

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = < size > );

For Azure SQL Database, the maximum allowed MAX_STORAGE_SIZE_MB value is 10,240 MB, so 4,000 MB is valid.

Method 1 — SSMS / T-SQL Method

This is the cleanest method for the simulation.

Step 1: Open SQL Server Management Studio

Open SQL Server Management Studio.

Connect to the Azure SQL logical server that hosts db1.

Use SQL authentication or Microsoft Entra authentication.

In Connection Properties, select:

db1

You can also connect to master, but for this task it is cleaner to open a query window in db1.

Step 2: Run the Query Store configuration command

Open a new query window and run:

ALTER DATABASE [db1]

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000);

This changes the Query Store maximum allocated storage size to 4,000 MB.

Step 3: Verify the setting

Run this query in db1:

SELECT

actual_state_desc,

desired_state_desc,

current_storage_size_mb,

max_storage_size_mb

FROM sys.database_query_store_options;

Expected result:

max_storage_size_mb = 4000

Microsoft documents sys.database_query_store_options as the catalog view that returns Query Store configuration, including max_storage_size_mb.

Method 2 — SSMS Graphical Method

Use this if the simulation expects a GUI action.

Step 1: Connect to the server

Open SQL Server Management Studio.

Connect to the Azure SQL logical server.

Expand Databases.

Right-click db1.

Select Properties.

Step 2: Open Query Store settings

In the Database Properties window, select Query Store.

Find the setting:

Max Size (MB)

or:

Maximum Storage Size (MB)

Change the value to:

4000

Select OK.

Step 3: Verify

Run:

SELECT max_storage_size_mb

FROM sys.database_query_store_options;

Expected value:

4000

Azure Portal Clarification

The Azure portal is not the best tool for this specific Query Store configuration. Query Store size is a database engine setting, not a normal Azure resource setting like backup retention, locks, alerts, or private endpoints.

Use SSMS/T-SQL for this task.

Important Notes

Query Store must remain enabled

Do not disable Query Store. Azure SQL Database uses Query Store heavily, and Microsoft notes that Query Store is enabled by default for Azure SQL Database. The task only asks to increase the maximum storage size, not to change capture mode, cleanup policy, or operation mode.

4,000 MB is valid

Azure SQL Database allows Query Store MAX_STORAGE_SIZE_MB up to 10,240 MB, so this setting is within the allowed range.

Final Exam-Lab Action

Run this against db1:

ALTER DATABASE [db1]

SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000);

Then verify with:

SELECT max_storage_size_mb

FROM sys.database_query_store_options;

The task is complete when the result shows:

4000

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

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.

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

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 recommend which service and target endpoint to use when migrating the databases from SVR1 to Instance1.

Why the alternate choices are Wrong: The alternate choices do not satisfy the stated availability design because quorum and listener resources must match Azure failover-cluster behavior and the required SLA.

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

Why C is Correct: differential is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. The scenario asks for: You need to recommend a backup solution to restore DB3. That makes differential the option that satisfies the required Azure SQL layer and operational outcome.

Why A is Wrong: transaction log 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 B is Wrong: point-in-time restore (PITR) 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 D is Wrong: long-term retention (LTR) 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.

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

Why D is Correct: Configure connection strings that reference the read-write listener. is correct because it is the feature whose normal purpose matches the stated requirement. Configure connection strings that reference the read-write listener. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. The scenario wording points to that specific behavior: You need to recommend which configuration to perform twice to enable access to the primary and secondary replicas of DB3.

Why A is Wrong: A service endpoint secures traffic to an Azure service over the Azure backbone but still targets the service public endpoint rather than giving it a private IP in the VNet. It is not wrong technology in general, but it is the wrong HA/DR control for this scenario ' s failure model.

Why B is Wrong: Database-level firewall rules restrict access to a specific Azure SQL database and are more granular than server-level firewall rules. It does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Why C is Wrong: Create database-scoped credentials. 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.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why C is Correct: vertical scaling is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. In this scenario, the important constraint is: You need to recommend a solution to ensure that the performance of DB3 is optimized after the migration to Azure SQL Database. vertical scaling satisfies that constraint without adding an unrelated service or manual process.

Why A is Wrong: Resource Governor is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. It does not expose the required metric, query history, wait/blocking signal, or tuning mechanism; using it would not give the administrator the evidence requested.

Why B is Wrong: a custom resource pool is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. It is useful in other troubleshooting paths, but this scenario requires a more specific monitoring or optimization feature.

Why D is Wrong: horizontal scaling is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. It does not expose the required metric, query history, wait/blocking signal, or tuning mechanism; using it would not give the administrator the evidence requested.

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

Basic Concept: This question tests plan and configure a high availability and disaster recovery (ha/dr) environment in the DP-300 exam context. The correct choice is determined by the exact service boundary and operational requirement stated in the scenario.

Why B is Correct: For each customer, create an additional schema and grant the customer ddl_admin to the new schema. matches the expected DP-300 administration action. For each customer, create an additional schema and grant the customer ddl_admin to the new schema. is part of the availability or recovery design space, but the correct choice must satisfy the specified failover, restore, quorum, RPO, or RTO requirement. The question is not asking for a general Azure capability; it is asking for the feature that produces this result: You need to recommend a solution to ensure that the customers can create the database objects.

Why A is Wrong: For each customer, grant the customer ddl_admin to the existing schema. 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 C is Wrong: For each customer, create an additional schema and grant the customer db_writerto the new schema. 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 does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Why D is Wrong: For each customer, grant the customer db_writerto the existing schema. 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.

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

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.

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn’t frequently join with data from other tables.

When there are no obvious keys to join.

The lifecycle management policy lets you:

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

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.

There are CPU and Data I/O spikes for the PaaS prototype. Business Critical 4-vCore is needed.

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.

Box 1: Yes

DBAGroup1 is member of the Contributor role.

The Contributor role grants full access to manage all resources, but does not allow you to assign roles in Azure RBAC, manage assignments in Azure Blueprints, or share image galleries.

Box 2: No

Box 3: Yes

DBAGroup2 is member of the SQL DB Contributor role.

The SQL DB Contributor role lets you manage SQL databases, but not access to them. Also, you can ' t manage their security-related policies or their parent SQL servers. As a member of this role you can create and manage SQL databases.

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 geo-replication 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.

https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

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 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 create a new database user for app1.

Why the alternate choices are Wrong: The alternate actions fail because Entra authentication must be established at the server/administrator level first, then mapped to contained database users, and finally used by the client connection.

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

Why C is Correct: Azure SQL Database is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. The scenario asks for: You need to monitor SQL1 and query the metrics by using Kusto query language. That makes Azure SQL Database the option that satisfies the required Azure SQL layer and operational outcome.

Why A is Wrong: Log Analytics stores logs and metrics in a queryable workspace and supports workbooks, dashboards, alerting, and cross-resource analysis. It would produce a different operational signal than the one needed to investigate, alert, or tune the workload in this question.

Why B is Wrong: Event Hubs is a streaming ingestion service used to send telemetry to external SIEM or downstream processing systems rather than to build native Azure dashboards. It does not expose the required metric, query history, wait/blocking signal, or tuning mechanism; using it would not give the administrator the evidence requested.

Why D is Wrong: an Azure Blob storage container is related to operational monitoring or tuning, but it must match the exact signal needed: query history, resource utilization, wait/blocking detail, or automatic remediation. It would produce a different operational signal than the one needed to investigate, alert, or tune the workload in this question.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why D is Correct: Business Critical provides low-latency local SSD storage and high availability for demanding workloads, but it is usually more expensive than necessary for bursty idle workloads. In this scenario, the important constraint is: You need to create the SQL managed instance. Business Critical satisfies that constraint without adding an unrelated service or manual process.

Why A is Wrong: Hyperscale is designed for very large databases and fast scale-out storage architecture, not merely for intermittent CPU bursts. It mismatches the target platform, migration method, compute tier, or deployment model described in the scenario, so it would not satisfy the workload as stated.

Why B is Wrong: General Purpose is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It applies to a different Azure data-platform design than the one the question is testing.

Why C is Wrong: Premium is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It would solve a neighboring problem, but not the deployment, sizing, or migration requirement in this item.

The Watermark delay metric is computed as the wall clock time of the processing node minus the largest watermark it has seen so far.

The watermark delay metric can rise due to:

1. Not enough processing resources in Stream Analytics to handle the volume of input events.

2. Not enough throughput within the input event brokers, so they are throttled.

3. Output sinks are not provisioned with enough capacity, so they are throttled.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why B is Correct: Elastic pools let multiple Azure SQL databases share a fixed pool of resources, which is cost-effective when individual database usage is variable and unpredictable. In this scenario, the important constraint is: You have five on-premises servers that have Microsoft SQL Server 2022 Enterprise installed Each server contains multiple database that store data for web apps and are backed up by using a third-party backup solution. in Azure SQL Database elastic pool satisfies that constraint without adding an unrelated service or manual process.

Why A is Wrong: SQL Server on Azure Virtual Machines is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It would solve a neighboring problem, but not the deployment, sizing, or migration requirement in this item.

Why C is Wrong: Azure SQL Database is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It applies to a different Azure data-platform design than the one the question is testing.

Why D is Wrong: Azure SQL Managed Instance is an Azure data-platform feature, but it must match the workload model, service tier, migration path, or deployment constraint described in the scenario. It would solve a neighboring problem, but not the deployment, sizing, or migration requirement in this item.

Basic Concept: This question tests database auditing and query visibility. DP-300 expects you to distinguish between collecting audit events, retaining logs, and analyzing query behavior.

Why C and E is Correct: an Azure Policy initiative is correct because it is the feature whose normal purpose matches the stated requirement. an Azure Policy initiative can participate in administration workflows, but automation questions require the mechanism that can execute repeatable tasks with the correct scope and schedule. The scenario wording points to that specific behavior: You need to ensure that all the databases have the same configuration. an Azure Policy definition is correct because it is the feature whose normal purpose matches the stated requirement. an Azure Policy definition can participate in administration workflows, but automation questions require the mechanism that can execute repeatable tasks with the correct scope and schedule. The scenario wording points to that specific behavior: You need to ensure that all the databases have the same configuration.

Why A is Wrong: an Azure Policy assignment can participate in administration workflows, but automation questions require the mechanism that can execute repeatable tasks with the correct scope and schedule. It does not provide the required repeatable execution, scheduling, or deployment automation requirement, so it would not complete the administrative workflow reliably.

Why B is Wrong: Azure Automation runbooks execute PowerShell or Python tasks on a schedule, making them useful for Azure SQL maintenance workflows. It belongs to a different step of operations; this question needs the mechanism that can run or deploy the task repeatedly.

Why D is Wrong: Azure Automation runbooks execute PowerShell or Python tasks on a schedule, making them useful for Azure SQL maintenance workflows. It does not provide the required repeatable execution, scheduling, or deployment automation requirement, so it would not complete the administrative workflow reliably.

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

Why C is Correct: Active geo-replication creates readable secondary databases, but failover groups add the managed listener and automatic failover abstraction needed for application continuity at scale. This fits the case because the requested outcome is: You need to ensure that you have a secondary replica of SQLI in the same region. The selected feature addresses that outcome directly rather than relying on a workaround.

Why A is Wrong: Log shipping is a SQL Server HA/DR technique that copies and restores log backups to a secondary database; it is more manual and operationally heavier for Azure SQL Managed Instance migration than LRS. It does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Why B is Wrong: Auto-failover groups provide managed geo-replication and listener-based failover for Azure SQL Database or Managed Instance, including automatic failover policies for regional outages. It handles a different resilience pattern and would not deliver the failover or recovery behavior required here.

Why D is Wrong: Microsoft SQL Server failover clusters 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 does not meet the failover, restore, quorum, or cross-region continuity target stated in the question, even if it is valid in a different availability design.

Basic Concept: This question tests plan and configure a high availability and disaster recovery (ha/dr) environment in the DP-300 exam context. The correct choice is determined by the exact service boundary and operational requirement stated in the scenario.

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 attach an Azure ultra disk to VMI.

Why the alternate choices are Wrong: The alternate selections apply to different administrative stages or different scopes. In a hotspot or drag-drop task, order and scope are part of the answer, so a technically valid Azure feature can still be wrong if it is placed in the wrong step.

This table lists the key features for PolyBase and the products in which they ' re available.

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);

Basic Concept: This question tests database auditing and query visibility. DP-300 expects you to distinguish between collecting audit events, retaining logs, and analyzing query behavior.

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 have an on-premises Microsoft SQL Server 2022 database named DB1.

Why the alternate choices are Wrong: The alternate selections apply to different administrative stages or different scopes. In a hotspot or drag-drop task, order and scope are part of the answer, so a technically valid Azure feature can still be wrong if it is placed in the wrong step.