Home
Microsoft
DP-300: Administering Microsoft Azure SQL Solutions Dumps

Free Microsoft DP-300 Exam Dumps July 2026

Here you can find all the free questions related with Microsoft Administering Microsoft Azure SQL Solutions (DP-300) exam. You can also find on this page links to recently updated premium files with which you can practice for actual Microsoft Administering Microsoft Azure SQL Solutions Exam. These premium versions are provided as DP-300 exam practice tests, both as desktop software and browser based application, you can use whatever suits your style. Feel free to try the Administering Microsoft Azure SQL Solutions Exam premium files for free, Good luck with your Microsoft Administering Microsoft Azure SQL Solutions Exam.

Question No: 1

MultipleChoice

You plan to deploy an app that includes an Azure SQL database and an Azure web app. The app has the following requirements:

The web app must be hosted on an Azure virtual network.

The Azure SQL database must be assigned a private IP address.

The Azure SQL database must allow connections only from the virtual network.

You need to recommend a solution that meets the requirements.

What should you include in the recommendation?

Options

AAzure Private Link

Ba network security group (NSG)

Ca database-level firewall

Da server-level firewall

Answer
AExplanation

https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview

Question No: 2

MultipleChoice

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have SQL Server 2019 on an Azure virtual machine.

You are troubleshooting performance issues for a query in a SQL Server instance.

To gather more information, you query sys.dm_exec_requests and discover that the wait type is PAGELATCH_UP and the wait_resource is 2:3:905856.

You need to improve system performance.

Solution: You shrink the transaction log file.

Does this meet the goal?

Options

AYes

BNo

Answer
BExplanation

https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Question No: 3

MultipleChoice

You have an Azure subscription that contains a user named user1 and an Azure SQL database named OBI. You configure long-term retention (LTR) for DB1.

You need to ensure that User1 can delete LTR backups of DBl. The solution must follow the principle of least privilege. Which role should you assign to User1?

Options

ASQL Server Contributor

BOwner

CSQL Security Manager

DContributor

Answer
A

Question No: 4

OrderList

You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.

Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer

Question No: 5

OrderList

You have SQL Server on an Azure virtual machine.

You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer

Question No: 6

OrderList

You have an Azure SQL database that contains a table named Employees. Employees contains a column named Salary.

You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the Salary column and must provide the most secure encryption.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer

Question No: 7

MultipleChoice

Task 5

You need to configure a disaster recovery solution for db1. When a failover occurs, the connection strings to the database must remain the same. The secondary server must be in the West US 3 Azure region.

Options

ASee the explanation part for the complete Solution

Answer
AExplanation

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.

SelectFailover groupsin the left menu and click onAdd group.

Enter a name for the failover group and selectWest US 3as the secondary region.

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

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

Click onConfigure failover policyand select the failover mode, grace period, and read-write failover endpoint mode according to your preferences.

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

Question No: 8

MultipleChoice

Task 4

You need to enable change data capture (CDC) for db1.

Options

ASee the explanation part for the complete Solution

Answer
AExplanation

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

Here are the steps to enable CDC for db1:

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

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

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

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

These are the steps to enable CDC for db1

Question No: 9

MultipleChoice

SIMULATION

You have two on-premises servers that run Windows Server 2019 and host a Microsoft SQL Server 2017 Always On availability group named AG1. AG1 contains a single database named DB1.

You have an Azure subscription. The subscription contains a virtual machine named VM1 that runs Linux.

You need to migrate DB1 to a SQL Server 2019 instance on VM1. The solution must minimize the downtime of DB1 during the migration.

What should you do? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Options

Asee the answer below in explanation

Answer
AExplanation

Answer as in image below.

Question No: 10

MultipleChoice

You have an Azure subscription that uses a domain named contoso.com.

You have two Azure VMs named DBServer1 and DBServer2. Each of them hosts a default SQL Server instance. DBServer1 is in the East US Azure region and contains a database named Database

Options

ADBServer2 is in the West US Azure region.
DBServer1 has a high volume of data changes and low latency requirements for data writes.
You need to configure a new availability group for DatabaseA. The secondary replica will reside on DBServer2.
What should you do?

AConfigure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Asynchronous.

BConfigure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Synchronous.

CConfigure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Asynchronous.

DConfigure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Synchronous.

Answer
CExplanation

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15

Question No: 11

MultipleChoice

Task 3

You need to ensure that all queries executed against dbl are captured in the Query Store.

Options

ASee the explanation part for the complete Solution

Answer
AExplanation

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 onQuery Performance Insightin the left menu.

Click onConfigure Query Storeand turn on theQuery Storeswitch.

In theQuery Capture Modedropdown, selectAlland click onSave.

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.

Question No: 12

MultipleChoice

Task 2

You need to configure your user account as the Azure AD admin for the server named sql3700689S.

Options

ASee the explanation part for the complete Solution

Answer
AExplanation

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 selectSQL Server -- Azure Arc.

Select the server namedsql3700689Sand click onActive Directory admin.

Click onSet adminand choose your user account from the list of Azure AD users.

Click onSelectand thenSaveto confirm the change.

You can verify the Azure AD admin by clicking onActive Directory adminagain 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.

Question No: 13

MultipleChoice

Task 1

In an Azure SQL database named db1, you need to enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table.

Options

ASee the explanation part for the complete Solution

Answer
AExplanation

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;

-- Enable page compression on the clustered index

ALTER INDEX PK_SalesOrderHeader_SalesOrderlD ON SalesLT.SalesOrderHeader

REBUILD WITH (DATA_COMPRESSION = PAGE);

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 isUSE db1; GO. This part connects to the Azure SQL database named db1, where the SalesLT.SalesOrderHeader table is located. TheGOcommand separates the batches of Transact-SQL statements and sends them to the server.

The second part isALTER 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. TheALTER INDEXstatement modifies the properties of an existing index. TheREBUILDoption rebuilds the index from scratch, which is required to change the compression setting. TheDATA_COMPRESSION = PAGEoption 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. TheGOcommand ends the batch of statements.

The third part is optional, but it can be useful to verify the compression status of the index. It isSELECT 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. TheSELECTstatement returns the name, index_id, and data_compression_desc columns for the indexes that belong to the SalesLT.SalesOrderHeader table. TheOBJECT_IDfunction 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.

Question No: 14

Hotspot

You need to recommend the appropriate purchasing model and deployment option for the 30 new databases. The solution must meet the technical requirements and the business requirements.

What should you recommend? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Answer

Explanation

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview

https://docs.microsoft.com/en-us/azure/azure-sql/database/reserved-capacity-overview

Question No: 15

Hotspot

You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

Answer

Question No: 16

Hotspot

You have an Azure SQL database.

You need to identify whether a delayed query execution is associated to a RESOURCE wait.

How should you complete the Transact -SQL statement? To answer, select the appropriate option in the answer area.

NOTE: Each correct selection is worth one point.

Answer

Question No: 17

MultipleChoice

You have an Azure SQL database named DB1.

A user named User 1 has an Azure AD account.

You need to provide User1 with the ability to add and remove columns from the tables m DBV The solution must use the principle of least privilege.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point

Options

AAssign the database user the db.ddladmm role.

BAssign the database user the db.owner role.

CCreate a contained database user.

DCreate a login and an associated database user.

Answer
A, D

Question No: 18

Hotspot

You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

Answer

Question No: 19

Hotspot

You have a SQL Server on Azure Virtual Machines instance that hosts a database named Db1.

You need to configure the autogrow and autoshrink settings for DB1.

Which statements should you use? To answer, select the appropriate options in the answer are

NOTE: Each correct selection is worth one point.

https://learn.microsoft.com/en-us/troubleshoot/sql/admin/considerations-autogrow-autoshrink

Answer

Question No: 20

Hotspot

You have a SQL Server on Azure Virtual Machines instance named VM1 that hosts a database named DB1. You run the following query.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer

Limited Time Offer

25%

Off

Get Premium DP-300 Questions as Interactive Web-Based Practice Test or PDF