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

Free Microsoft DP-300 Exam Questions (page: 7)

Page 7 of 54
PDF with 418 Questions

HOTSPOT (Drag and Drop is not supported)

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.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: Add a secondary replica to AG1.
Migrate an availability group to SQL Server on Azure VM

To ensure the migrated server is synchronized with the source server, stop the SQL Server service on every replica in the availability group, starting with secondary replicas (in SQL Server Configuration Manager > Services) while ensuring the disks hosting SQL data are online.

Incorrect:
* Upgrade to SQL Server 2019
No need to upgrade to SQL Server 2019

* Create a SQL Server 2019 Always ON available group on VM1.
You do that afterwards.
After the migration, enable Always On availability groups for the database.

Box 2: Azure Migrate
You can migrate your SQL Server Always On availability group to SQL Server on Azure VMs using the Azure Migrate: Server Migration tool. Using the migration tool, you will be able to migrate each replica in the availability group to an Azure VM hosting SQL Server, as well as the cluster metadata, availability group metadata and other necessary high availability components.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/virtual-machines/sql-server-availability- group-to-sql-on-azure-vm



DRAG DROP (Drag and Drop is not supported)

You have an Azure SQL database named DB1.

You need to create a partitioned table in DB1.

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

Select and Place:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






Create partitioned tables and indexes
Creating a partitioned table or index typically happens in three or four parts:

1. Optionally create a filegroup or filegroups and corresponding data files that will hold the partitions specified by the partition scheme. The main reason to place partitions on multiple filegroups is to ensure you can independently perform backup and restore operations on filegroups. If this is not required, you may choose to assign all partitions to a single filegroup, using either an existing filegroup, such as PRIMARY, or a new filegroup with related data file(s). In nearly all scenarios, you will achieve all benefits of partitioning whether or not you use multiple filegroups.

2. Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column. You can use a single partition function to partition multiple objects. (Step 1)

3. Create a partition scheme that maps the partitions of a partitioned table or index to one filegroup or to multiple filegroups. You can use a single partition scheme to partition multiple objects. (Step 2)

4. Create or alter a table or index and specify the partition scheme as the storage location, along with the column that will serve as the partitioning column. (Step 3)

Aligns all indexes of the table that are partitioned with the same partition scheme.
When a table and its indexes are aligned, you can move partitions in and out of partitioned tables more effectively, because your data is partitioned in the same way.

Step 1: a partition function

Step 2: a partition scheme

Step 3: an aligned index


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes



DRAG DROP (Drag and Drop is not supported)

You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a virtual machine named Server1 that runs Windows Server. SQLVM1 and Server1 are joined to an Active Directory Domain Services (AD DS) domain. Server1 hosts a file share named Share1.

You need to ensure that a SQL Server Agent job step on SQLVM1 can access the files in Share1. The solution must use the principle of least privilege.

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.

Select and Place:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






Step 1: Create a credential
You must create a credential before you create a proxy if one is not already available.

Note: SQL Server Agent uses Proxies to define the security context for job steps. Basically, a proxy is an object that provides SQL Server Agent access to stored credentials for a Windows user.
When running a job step that is configured to use a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step using that security context.

Step 2: Create a proxy
A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

Step 3: Assign the proxy to the job step


Reference:

https://learn.microsoft.com/en-us/answers/questions/613796/ssis-proxy-and-credential-manager https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy



You have an Azure subscription.

You need to deploy an instance of SQL Server on Azure Virtual Machines. The solution must meet the following requirements:

Custom performance configuration, such as IOPS, capacity, and throughout, must be supported.

Costs must be minimized.

Which type of disk should you include in the solution?

  1. Premium SSD v2
  2. Premium SSD
  3. Standard SSD
  4. Ultra SSD

Answer(s): A

Explanation:

Differences between Premium SSD and Premium SSD v2
Unlike Premium SSDs, Premium SSD v2 doesn't have dedicated sizes. You can set a Premium SSD v2 to any supported size you prefer, and make granular adjustments to the performance without downtime. Premium SSD v2 doesn't support host caching but, benefits significantly from lower latency, which addresses some of the same core problems host caching addresses. The ability to adjust IOPS, throughput, and size at any time also means you can avoid the maintenance overhead of having to stripe disks to meet your needs.


Reference:

https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types



You have an on-premises datacenter that contains a 14-TB Microsoft SQL Server database.

You plan to create an Azure SQL managed instance and migrate the on-premises database to the new instance.

Which three service tiers support the SQL managed instance? Each correct answer presents a complete solution.

Note: Each correct selection is worth one point.

  1. General Purpose Standard
  2. Business Critical Memory Optimized Premium
  3. General Purpose Premium
  4. Business Critical Premium
  5. Business Critical Standard

Answer(s): A,B,C

Explanation:

Hardware configurations have different characteristics, as described in the following table:


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits



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



Viewing page 7 of 54



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

DP-300 Exam Discussions & Posts