Microsoft DP-300 - Administering Relational Databases on Microsoft Azure

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

Create an Azure Private Endpoint for the Azure SQL logical server that hosts db1, place the private endpoint in VNET1, and integrate it with the private DNS zone:

privatelink.database.windows.net

This is the correct solution because Azure SQL Database is a PaaS service. You do not assign a private IP directly to db1. Instead, Azure creates a private endpoint network interface in the virtual network. That private endpoint receives a private IP address from a subnet in VNET1, and clients in VNET1 use that private IP path to reach the SQL server. Microsoft defines a private endpoint as a network interface that uses a private IP address from your virtual network to connect privately to a Private Link resource such as Azure SQL Database.

Azure Portal Method — Recommended for Simulation

Step 1: Identify the SQL logical server that hosts db1

Sign in to the Azure portal.

Search for SQL databases.

Open db1.

On the database overview page, identify the Server name.

The private endpoint is created for the Azure SQL logical server, not for the database object alone. For Azure SQL Database, the Private Link resource type is:

Microsoft.Sql/servers

and the target subresource is:

sqlServer

Microsoft lists Azure SQL Database private endpoint DNS configuration under Microsoft.Sql/servers with subresource sqlServer.

Step 2: Open the SQL server networking page

Open the Azure SQL logical server that hosts db1.

In the left menu, go to:

Security > Networking

Select the Private access tab.

Select Create a private endpoint.

Microsoft’s Azure SQL private endpoint workflow is performed from the SQL server resource under Networking > Private access, where you can create or manage private endpoint connections.

Step 3: Configure the private endpoint basics

On the Create private endpoint page:

Setting

Value

Subscription

Use the lab subscription

Resource group

Use the lab resource group

Name

pe-db1-sql

Region

Same region as VNET1, if possible

The name is not exam-critical. The critical part is that the endpoint is associated with VNET1 and the SQL server that hosts db1.

Step 4: Configure the target resource

On the Resource tab, configure:

Setting

Value

Connection method

Connect to an Azure resource in my directory

Resource type

Microsoft.Sql/servers

Resource

SQL logical server that hosts db1

Target sub-resource

sqlServer

Do not choose storage, VM, managed instance, or any unrelated resource type. This is Azure SQL Database, so the target subresource must be sqlServer.

Step 5: Configure VNET1 and subnet

On the Virtual Network tab:

Setting

Value

Virtual network

VNET1

Subnet

Select an available subnet in VNET1

Private IP configuration

Dynamic is fine unless the lab requires static

Azure will create a network interface for the private endpoint and assign it a private IP address from the selected subnet. Microsoft notes that the network interface page for the private endpoint shows the private IP address assigned to the private endpoint connection.

Step 6: Configure private DNS integration

On the DNS tab:

Enable private DNS zone integration.

Use or create the private DNS zone:

privatelink.database.windows.net

Link the private DNS zone to:

VNET1

This is not optional in a clean exam solution. Without DNS integration, clients may still resolve the SQL server name to the public endpoint instead of the private endpoint. Microsoft states that DNS is critical because it resolves the private endpoint IP address, and for Azure SQL Database the recommended private DNS zone is privatelink.database.windows.net.

Step 7: Review and create

Select Review + create.

Confirm:

Resource: SQL logical server hosting db1

Target subresource: sqlServer

Virtual network: VNET1

Private DNS zone: privatelink.database.windows.net

Select Create.

After deployment, the SQL server will have a private endpoint connection associated with VNET1.

Step 8: Approve the private endpoint connection if required

In most same-directory deployments, approval may be automatic. If approval is pending:

Open the SQL logical server.

Go to:

Networking > Private access

Select the pending private endpoint connection.

Select Approve.

Microsoft documents that SQL administrators can approve or reject private endpoint connections from the SQL server private access page.

Step 9: Optional but recommended — Disable public network access

The task only says you need to connect by private IP from VNET1. It does not explicitly say to block public access. But if the exam expects private-only access, then disable public access after the private endpoint works.

On the SQL logical server:

Go to:

Security > Networking > Public access

Set Public network access to:

Disabled

or select:

Deny public network access

Save.

Be careful: Microsoft states that adding a private endpoint does not automatically block public routing to the logical server. Public access must be denied separately if you want private-only access.

How to Connect from SSMS

You should connect from a machine that is inside VNET1, such as an Azure VM joined to VNET1.

Step 1: Test DNS from a VM in VNET1

From a VM in VNET1, run:

nslookup < sql-server-name > .database.windows.net

Expected result: the name should resolve through the private endpoint path and return a private IP address from VNET1’s address space.

Microsoft explains that connection URLs do not change; DNS resolution is overridden so the existing service FQDN resolves to the private endpoint private IP address.

Step 2: Connect with SSMS

In SSMS, connect using the normal Azure SQL server name:

< sql-server-name > .database.windows.net

Then select database:

db1

Use normal SQL authentication or Microsoft Entra authentication.

Do not type the raw private IP address into SSMS unless the lab specifically forces it. For Azure SQL, the correct operational pattern is to connect to the SQL server FQDN and allow private DNS to resolve that FQDN to the private endpoint IP. Direct IP connection can cause TLS/certificate name problems because the server certificate matches the DNS name, not the private IP.

Verification

The task is complete when all of these are true:

Private endpoint exists for the SQL logical server hosting db1.

Target subresource is sqlServer.

The private endpoint is deployed into VNET1.

A private IP address is assigned to the private endpoint NIC.

Private DNS zone privatelink.database.windows.net exists.

The private DNS zone is linked to VNET1.

The SQL server FQDN resolves to the private endpoint private IP from inside VNET1.

SSMS can connect to db1 from a VM or client connected to VNET1.

Final Exam-Lab Action

Use the Azure portal and configure:

SQL server hosting db1

> Networking

> Private access

> Create private endpoint

Resource type: Microsoft.Sql/servers

Target subresource: sqlServer

Virtual network: VNET1

Private DNS zone: privatelink.database.windows.net

Then connect from a VM or client in VNET1 using:

< sql-server-name > .database.windows.net

That is the correct way to ensure db1 is reachable through a private IP address on VNET1.

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

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

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.

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

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.