Cisco
CompTIA
ISC
EC-Council
EXIN
Fortinet
ITIL
Juniper
Microsoft
VMware
Avaya
SAP
Google
NVIDIA
Salesforce
Amazon
Palo Alto Networks
ISC2
Splunk
ServiceNow
All Vendors
  2. Home
  3. Exams
  4. Microsoft
  5. DP-300

Microsoft DP-300 Exam Questions
Administering Microsoft Azure SQL Solutions (Page 11 )

Updated On: 17-Feb-2026
Page 11 of 85
PDF with 430 Questions

SIMULATION

You need to configure db1 to pause automatically after one hour of inactivity.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Azure SQL elastic pool pausing feature
Aan elastic pool vCore provisioned server can be paused. You can do this by changing the compute tier of the elastic pool to Serverless. In the serverless compute tier, the database is automatically paused when it is inactive for a period of time that you specify. The database is automatically resumed when the next login or other activity occurs.

Part 1: Change to the serverless compute tier

To change the compute tier of an elastic pool, you can follow these steps:
Step 1: Go to the Azure portal and sign in to your account.

Step 2: Select the Azure SQL resource that contains the elastic pool.

Step 3: In the left navigation menu, select Elastic Pools.

Step 4: Select the elastic pool that you want to change the compute tier for.

Step 5: In the Settings section, select Compute Tier.

Step 6: Select Serverless.

Step 7: Click Save.

Part 2: Configure the Auto-pause setting.

Step 1: Select db1.
Once you have changed the compute tier to serverless, the database will be paused after the idle time that you specified. You can view the idle time in the Auto-pause delay setting.

Step 2: Open database settings.

Step 3: Change the Auto-pause delay setting to 1 hour.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-quickstart



SIMULATION

You need to ensure that any enhancements made to the Query Optimizer through patches are available to db1 and db2 on sql12345678.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Stored procedure execution
You can use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.

Use SQL Server Management Studio

Step 1: In Object Explorer, connect to an instance of SQL Server or Azure SQL Database [here sql12345678], expand that instance, and then expand Databases.

Step 2: Expand the database that you want [Here first db1], expand Programmability, and then expand Stored Procedures.

Step 3: Right-click the stored procedure that you want to run [Here: ALTER DATABASE SCOPED CONFIGURATION] and select Execute Stored Procedure.

Step 4: Add parameter: QUERY_OPTIMIZER_HOTFIXES = ON

Step 5: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK.

Step 6: Repeated step 2 to step 5 for db2.

Note: The default approach that SQL Server uses for the query optimizer enables any fixes for the latest database compatibility level for a given product release. This means any fixes for the query optimizer will be used up to the compatibility level of the database, but any hotfixes beyond that compatibility level will not be used. Microsoft provides an option to enable query optimizer hotfixes using the scoped configuration QUERY_OPTIMIZER_HOTFIXES.

Use the following command to enable optimizer hotfixes:

ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON;

This command configures the database to use all query optimizer hotfixes. For example, optimizations that were included in a recent database cumulative update will apply if the administrator has altered the scope to include query optimizer hotfixes. Using QUERY_OPTIMIZER_HOTFIXES applies at the database level, meaning the change would have to be made for each database.


Reference:

https://infohub.delltechnologies.com/en-US/l/performance-best-practices-4/sql-server-2019-query-optimizer- hotfixes https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql



SIMULATION

You plan to create an automation runbook that will create database users in db1 from Microsoft Entra identities.

You need to configure sql12345678 to support the creation of new database users.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Provision Microsoft Entra admin (SQL Managed Instance)
Your SQL Managed Instance needs permission to read Microsoft Entra ID to accomplish tasks such as authentication of users through security group membership or creation of new users. For this to work, you must grant the SQL Managed Instance permission to read Microsoft Entra ID. You can do this using the Azure portal or PowerShell.

To grant your SQL Managed Instance read permissions to Microsoft Entra ID using the Azure portal, sign in as a Global Administrator and follow these steps:

Step 1: In the Azure portal, in the upper-right corner select your account, and then choose Switch directories to confirm which directory is your Current directory. Switch directories, if necessary.



Step 2: Choose the correct Microsoft Entra directory as the Current directory.

This step links the subscription associated with Microsoft Entra ID to the SQL Managed Instance, ensuring the Microsoft Entra tenant and SQL Managed Instance use the same subscription.

Step 3: Now, you can choose your Microsoft Entra admin for your SQL Managed Instance. For that, go to your managed instance resource in the Azure portal and select Microsoft Entra admin under Settings.



Step 4: Select the banner on top of the Microsoft Entra admin page and grant permission to the current user.



Step 5: After the operation succeeds, the following notification will show up in the top-right corner:



Note: Microsoft Entra authentication uses identities in Microsoft Entra ID to access data sources such as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Microsoft.Data.SqlClient namespace allows client applications to specify Microsoft Entra credentials in different authentication modes when they're connecting to Azure SQL Database and Azure SQL Managed Instance. To use Microsoft Entra authentication with Azure SQL, you must configure and manage Microsoft Entra authentication with Azure SQL.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication



SIMULATION

You plan to perform performance testing of db1.

You need prevent db1 from reverting to the last known good query plan.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Automatic tuning options
The automatic tuning options available in Azure SQL Database and Azure SQL Managed Instance are:

* FORCE LAST GOOD PLAN (automatic plan correction)
Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and forces queries to use the last known good plan instead of the regressed plan.

* Etc.

You can either manually apply tuning recommendations using the Azure portal, or you can let automatic tuning autonomously apply tuning recommendations for you.

Azure defaults are set to FORCE_LAST_GOOD_PLAN enabled.

We need to disable it.

With transact-SQL:
ALTER DATABASE SET options (Transact-SQL)
Parameters include:
<automatic_tuning_option> ::=
{
AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } ) }

Solution with T-SQL

Stored procedure execution
You can use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window to execute a stored procedure. Always use the latest version of SSMS.

Use SQL Server Management Studio

Step 1: In Object Explorer, connect to an instance of SQL Server or Azure SQL Database, expand that instance, and then expand Databases.

Step 2: Expand the database that you want, expand Programmability, and then expand Stored Procedures.

Step 3: Right-click the stored procedure that you want to run [here: ALTER DATABASE SET] and select Execute Stored Procedure.

Step 4: Set the parameters: AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { OFF } )

Step 5: Select OK to execute the stored procedure. If the stored procedure doesn't have any parameters, just select OK.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure



SIMULATION

You have a legacy application written for Microsoft SQL Server 2012. The application will be the only application that accesses db1.

You need to ensure that db1 is compatible with all the features and syntax of SQL Server 2012.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

View or change the compatibility level of a database
You can view or change the compatibility level of a database in SQL Server, Azure SQL Database, or Azure

SQL Managed Instance by using SQL Server Management Studio or Transact-SQL.
Use SQL Server Management Studio
To view or change the compatibility level of a database using SQL Server Management Studio (SSMS)

Step 1: Connect to the appropriate server or instance hosting your database [Here db1].

Step 2: Select the server name in Object Explorer.

Step 3: Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

Step 4: Right-click the database, and then select Properties.

The Database Properties dialog box opens.

Step 5: In the Select a page pane, select Options.

The current compatibility level is displayed in the Compatibility level list box.

Step 6: To change the compatibility level, select a different option from the list [Select SQL Server 2012]


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of- a-database






Post your Comments and Discuss Microsoft DP-300 exam dumps with other Community members:

Join the DP-300 Discussion