Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 21:

  You have an Azure subscription.

  You plan to deploy an instance of SQL Server on Azure Virtual Machines that supports Write Accelerator.

  Which virtual machine series should you use?

  A. E-series

  B. G-series

  C. H-series

  D. M-series

  Correct Answer: D

  Enabling Write Accelerator on a specific disk

  The next few sections will describe how Write Accelerator can be enabled on Azure Premium Storage VHDs.

  Prerequisites

  The following prerequisites apply to the usage of Write Accelerator at this point in time:

  The disks you want to apply Azure Write Accelerator against need to be Azure managed disks on Premium Storage.

  You must be using an M-series VM

  Reference:

  https://learn.microsoft.com/en-us/azure/virtual-machines/how-to-enable-write-accelerator¨

• Question 22:

  You have an on-premises datacenter that contains a 2-TB Microsoft SQL Server 2019 database named DB1.

  You need to recommend a solution to migrate DB1 to an Azure SQL managed instance. The solution must minimize downtime and administrative effort.

  What should you include in the recommendation?

  A. Log Replay Service (LRS)

  B. log shipping

  C. transactional replication

  D. SQL Data Sync

  Correct Answer: A

  Migration overview: SQL Server to Azure SQL Managed Instance

  We recommend the following migration tools:

  *

  Log Replay Service This cloud service is enabled for SQL Managed Instance based on SQL Server log-shipping technology. It's a migration option for customers who can provide full, differential, and log database backups to Azure Storage. Log Replay Service is used to restore backup files from Azure Blob Storage to SQL Managed Instance.

  *

  Etc.

  The following table lists alternative migration tools:

  *

  Transactional replication

  Replicate data from source SQL Server database tables to SQL Managed Instance by providing a publisher-subscriber type migration option while maintaining transactional consistency.

  *

  Etc.

  Compare migration options

  Compare migration options to choose the path that's appropriate to your business needs.

  The following table compares the recommended migration options:

  * Log Replay Service

  -Migrate individual line-of-business application databases.

  -More control is needed for database migrations.

  Considerations:

  -

  The migration entails making full database backups on SQL Server and copying backup files to Azure Blob Storage. Log Replay Service is used to restore backup files from Azure Blob Storage to SQL Managed Instance.

  -

  Databases being restored during the migration process will be in a restoring mode and can't be used for read or write workloads until the process is complete.

  *

  Etc.

  The following table compares the alternative migration options:

  *

  Transactional replication

  -

  Migrate by continuously publishing changes from source database tables to target SQL Managed Instance database tables.

  -

  Do full or partial database migrations of selected tables (subset of a database).

  Considerations:

  -

  Setup is relatively complex compared to other migration options.

  -

  Provides a continuous replication option to migrate data (without taking the databases offline).

  * Etc.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-overview

• Question 23:

  You have an Azure SQL database named DB1 in the General Purpose service tier. The performance metrics for DB1 are shown in the following exhibit.

  

  You need to reduce the Log IO percentage. The solution must minimize costs.

  What should you do?

  A. Change Service tier to Business Critical.

  B. Increase the number of vCores.

  C. Perform a checkpoint operation.

  D. Change Recovery model to Simple.

  Correct Answer: A

  High Log IO is usually seen when you hit the IOPS/throughput limits for the respective pricing tier.

  * Data/Log IOPS (approximate) General Purpose service tier: 500 - 7500 per file Increase file size to get more IOPS

  Business Critical:

  16 K - 320 K (4000 IOPS/vCore)

  Add more vCores to get better IO performance.

  Reference:

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

• Question 24:

  You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.

  You have an Azure subscription that contains an Azure SQL database named SQLDB1.

  You need to replicate DB1 to SQLDB1.

  Which type of replication should you use?

  A. transactional

  B. peer-to-peer

  C. snapshot

  D. merge

  Correct Answer: A

  Scenarios Typical Replication Scenario Create a transactional replication publication on a SQL Server database.

  1.

  On SQL Server use the New Subscription Wizard or Transact-SQL statements to create a push to subscription to Azure SQL Database.

  2.

  With single and pooled databases in Azure SQL Database, the initial data set is a snapshot that is created by the Snapshot Agent and distributed and applied by the Distribution Agent. With a SQL Managed Instance publisher, you can also use a database backup to seed the Azure SQL Database subscriber.

  Data migration scenario

  1.

  Use transactional replication to replicate data from a SQL Server database to Azure SQL Database.

  2.

  Redirect the client or middle-tier applications to update the database copy.

  3.

  Stop updating the SQL Server version of the table and remove the publication.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database

• Question 25:

  You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named VM1 and an Azure Active Directory Domain Services (Azure AD DS) domain that contains two users named User1 and User 2.

  On the default instance of SQL Server on VM1, you create a credential named Credential1 for User1.

  You need to ensure that User2 can create a SQL Server Agent proxy that will use Credential1. The solution must use the principle of least privilege.

  Which role should you assign to User2?

  A. SQLAgentUserRole

  B. SQLAgentReaderRole

  C. SQLAgentOperatorRole

  D. sysadmin

  Correct Answer: D

  Only members of the sysadmin fixed server role have permission to create, modify, or delete proxy accounts. Users who are not members of the sysadmin fixed server role must be added to one of the following SQL Server Agent fixed

  database roles in the msdb database to use proxies: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

  SQL Server Agent Fixed Database Roles

  SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

  SQLAgentUserRole

  SQLAgentReaderRole

  SQLAgentOperatorRole

  Incorrect:

  *

  SQLAgentUserRole SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own.

  *

  SQLAgentReaderRole SQLAgentReaderRole Permissions SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

  *

  SQLAgentOperatorRole Permissions SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

  Reference: https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles

• Question 26:

  You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

  You scale up the virtual machine to 8 vCPUs and 64 GB of memory.

  You need to reduce tempdb contention without negatively affecting server performance.

  What is the number of secondary data files that you should configure for tempdb?

  A. 2

  B. 4

  C. 8

  D. 64

  Correct Answer: C

  The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. Then if contention continues, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload/code.

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

• Question 27:

  You deploy an instance of SQL Server on Azure Virtual Machines named VM1.

  You need to create a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. The solution must use the principle of least privilege.

  What should you create first?

  A. a local Windows account

  B. a user-assigned managed identity in Azure AD

  C. a system-assigned managed identity in Azure AD

  D. an Elastic Job agent

  Correct Answer: C

  You can use a Windows VM system-assigned managed identity to access Azure SQL.

  Use a system-assigned identity for a Windows virtual machine (VM) to access Azure SQL Database. Managed Service Identities are automatically managed by Azure and enable you to authenticate to services that support Azure AD authentication, without needing to insert credentials into your code. Reference: https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql

• Question 28:

  You have the following resources:

  1.

  15 SQL Server on Azure Virtual Machines instances

  2.

  20 Azure SQL databases

  You need to recommend a solution to centrally monitor the resources for security vulnerabilities.

  What should you include in the recommendation?

  A. database audits

  B. Microsoft Defender

  C. SQL insights

  D. Azure SQL Auditing

  Correct Answer: B

  Keyword is the "vulnerabilities" https://learn.microsoft.com/en-us/azure/defender-for-cloud/defender-for-sql-introduction

• Question 29:

  You have an Azure subscription that contains an Azure SQL database named db1.

  You need to implement SQL insights for db1.

  Which two resources should you create first? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. a storage account

  B. a virtual machine

  C. an Azure logic app

  D. an Azure function

  E. a Log Analytics workspace

  Correct Answer: AE

  A. a storage account

  E. a Log Analytics workspace Azure SQL insights is a feature of Azure Monitor that collects, aggregates, and serves SQL performance metrics. It uses a Log Analytics workspace to store and provide advanced analytics on the performance data. You also need a storage account to maintain the SQL assessment data over time. These two resources are essential for setting up SQL insights. A virtual machine, an Azure logic app, and an Azure function are not necessary for this particular task.

• Question 30:

  You have an Azure subscription.

  You create a logical SQL server that hosts four databases. Each database will be used by a separate customer.

  You need to ensure that each customer can access only its own database. The solution must minimize administrative effort.

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

  NOTE: Each correct selection is worth one point.

  A. Deny public access.

  B. Create a private endpoint.

  C. Create a database-level firewall rule.

  D. Create a network security group (NSG).

  E. Create a server-level firewall rule.

  Correct Answer: B

  You can connect to an Azure SQL server using an Azure Private Endpoint.

  Azure Private endpoint is the fundamental building block for Private Link in Azure. It enables Azure resources, like virtual machines (VMs), to privately and securely communicate with Private Link resources such as Azure SQL server.

  Reference:

  https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal