Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 41:

  You have an Azure subscription.

  Vou need to deploy two instances of SQL Server on Azure virtual machines in a highly available configuration that will use an Always On availability group. The solution must meet the following requirements:

  1.

  Minimize how long it takes to fail over.

  2.

  Maintain existing connections to the primary replica during a failover.

  What should you do?

  A. Connect each virtual machine to a different subnet on a virtual network. Deploy a basic Azure load balancer.

  B. Connect each virtual machine to a different subnet on a single virtual network.

  C. Connect each virtual machine to a single subnet on a single virtual network.

  D. Connect each virtual machine to a single subnet on a virtual network. Deploy a standard Azure load balancer.

  Correct Answer: C

  Connectivity To match the on-premises experience for connecting to your availability group listener, deploy your SQL Server VMs to multiple subnets within the same virtual network. Having multiple subnets negates the need for the extra dependency on an Azure Load Balancer, or a distributed network name (DNN) to route your traffic to your listener.

  If you deploy your SQL Server VMs to a single subnet, you can configure a virtual network name (VNN) and an Azure Load Balancer, or a distributed network name (DNN) to route traffic to your availability group listener.

  Failover time: Failover time is faster when using a DNN listener since there is no need to wait for the network load balancer to detect the failure event and change its routing.

  Existing connections: Connections made to a specific database within a failing-over availability group will close, but other connections to the primary replica will remain open since the DNN stays online during the failover process. This is different than a traditional VNN environment where all connections to the primary replica typically close when the availability group fails over, the listener goes offline, and the primary replica transitions to the secondary role. When using a DNN listener, you may need to adjust application connection strings to ensure that connections are redirected to the new primary replica upon failover.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview

• Question 42:

  You have an Azure subscription that contains 50 instances of SQL Server on Azure Virtual Machines. The instances host 500 Azure SQL databases. You need to ensure that all the databases have the same configuration. The solution must meet the following requirements:

  1.

  Auditing must be enabled.

  2.

  Azure Defender must be enabled.

  3.

  Public network access must be disabled.

  4.

  Administrative effort must be minimized.

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

  NOTE: Each correct selection is worth one point.

  A. an Azure Automation runbook

  B. an Azure Policy initiative

  C. an Azure Policy assignment

  D. an Azure Automation account

  E. an Azure Policy definition

  Correct Answer: BE

• Question 43:

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

  You plan to schedule a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1.

  You need to configure the account that will be used by the agent. The solution must use the principle of least privilege.

  Which operating system user right should you assign to the account?

  A. Increase scheduling priority

  B. Log on as a service

  C. Profile system performance

  D. Log on as a batch job

  Correct Answer: D

  To configure a SQL Server Agent job on an instance of SQL Server on Azure Virtual Machines (VMs) to rebuild indexes of the databases hosted on the VM, you need to assign the Log on as a batch job user right to the account that will be used by the agent.

  Therefore, the correct answer is D. Log on as a batch job.

• Question 44:

  You have an Azure AD tenant and a logical Microsoft SQL server named SQL1 that hosts several Azure SQL databases.

  You plan to assign Azure AD users permissions to the databases automatically by using Azure Automation.

  You need to create the required Automation accounts.

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

  NOTE: Each correct selection is worth one point.

  A. From the Azure Active Directory admin center, create a service principal.

  B. From the Azure Active Directory admin center, create a user-assigned managed identity for SQL1.

  C. On SQL1. create a SQL user in the databases.

  D. On SQL1, create a SQL login.

  E. From the Azure Active Directory admin center, create an external identity.

  Correct Answer: AC

  A: Azure Active Directory (Azure AD) supports user creation in Azure SQL Database (SQL DB) on behalf of Azure AD applications (service principals). This is supported for Azure SQL Database and Azure SQL Managed Instance.

  C: Create the service principal user in Azure SQL Database

  Once a service principal is created in Azure AD, create the user in SQL Database. You'll need to connect to your SQL Database with a valid login with permissions to create users in the database.

  Create the user AppSP in the SQL Database using the following T-SQL command:

  SQL CREATE USER [AppSP] FROM EXTERNAL PROVIDER GO

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal?view=azuresql

• Question 45:

  You plan to deploy two instances of SQL Server on Azure virtual machines in a highly available configuration that will use an Always On availability group.

  You need to recommend a deployment solution that meets the following requirements:

  1.

  Provides a Service Level Agreement (SLA) of at least 99.95%

  2.

  Replicates databases in the same group synchronously

  3.

  Minimizes the latency of database writes

  What should you recommend?

  A. Create two proximity groups and two availability sets. Deploy each virtual machine to a unique availability set. Add one virtual machine to each proximity group.

  B. Create a proximity group and an availability set. Deploy each virtual machine to the availability set. Add both virtual machines to the proximity group.

  C. Create a proximity group and two availability sets. Deploy each virtual machine to a unique availability set. Add both virtual machines to the proximity group.

  D. Create two proximity groups and a single availability set. Deploy both virtual machines to the availability set. Add one virtual machine to each proximity group.

  Correct Answer: B

  Use one proximity placement group, and one availability set.

  To get VMs as close as possible, achieving the lowest possible latency, you should deploy them within a proximity placement group.

  A proximity placement group is a logical grouping used to make sure that Azure compute resources are physically located close to each other. Proximity placement groups are useful for workloads where low latency is a requirement.

  Note:

  *

  Using Proximity Placement Groups

  A proximity placement group is a resource in Azure. You need to create one before using it with other resources. Once created, it could be used with virtual machines, availability sets, or virtual machine scale sets. You specify a proximity

  placement group when creating compute resources providing the proximity placement group ID.

  *

  What is an availability set?

  An availability set is a logical grouping of VMs that allows Azure to understand how your application is built to provide for redundancy and availability. We recommended that two or more VMs are created within an availability set to provide for

  a highly available application and to meet the 99.95% Azure SLA.

  Incorrect:

  Not A, Not D: Create only one placement proximity group, not two.

  Reference:

  https://learn.microsoft.com/en-us/azure/virtual-machines/co-location

  https://learn.microsoft.com/en-us/azure/virtual-machines/availability-set-overview

• Question 46:

  You have an on-premises Microsoft SQL Server 2019 instance named SQL1 that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named MI1 and an Azure Storage account

  named storage1.

  You need to ensure that you can back up db1 to storage1. The solution must meet the following requirements:

  Use block blob storage.

  Maximize security.

  A. Rotate the storage keys

  B. Create an access policy.

  C. Enable infrastructure encryption.

  D. Generate a shared access signature (SAS)

  Correct Answer: C

• Question 47:

  You have an Azure subscription.

  You need to deploy an Azure SQL database. The solution must meet the following requirements:

  1.

  Dynamically scale CPU resources.

  2.

  Ensure that the database can be paused to reduce costs.

  What should you use?

  A. the Business Critical service tier

  B. the serverless compute tier

  C. an elastic pool

  D. the General Purpose service tier

  Correct Answer: B

  Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns. The serverless compute tier is available in the General Purpose service tier and currently in preview in the Hyperscale service tier.

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

• Question 48:

  You have an instance of SQL Server on Azure Virtual Machines.

  You need to ensure that a user named User1 can configure proxy accounts for SQL Server Agent jobs. The solution must use the principle of least privilege.

  Which role should you assign to User1?

  A. sysadmin

  B. SQLAgentUserRole

  C. SQLAgentReaderRole

  D. SQLAgentOperatorRole

  Correct Answer: A

  Permissions

  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.

  Incorrect:

  Not B: 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.

  Not C: 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.

  Not D: 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 49:

  You have an Azure SQL database named DB1. DB1 has a table named Table1 that contains the following columns.

  

  You plan to enable Always Encrypted for Table1.

  Which two columns support encryption? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point

  A. Column1

  B. Column2

  C. Column3

  D. Column4

  E. Column5

  Correct Answer: DE

• Question 50:

  You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that was deployed by using an Azure Marketplace SQL Server 2019 Enterprise image.

  You need to change the Microsoft SQL Server instance on SQLVM1 to the Standard edition. The solution must ensure licensing compliance.

  What should you do first?

  A. From the SQL Server Installation Center on SQLVM1. run the Edition Upgrade wizard.

  B. From SQLVM1, uninstall the SQL Server instance.

  C. From the SQL Server Installation Center on SQLVM1. run the Repair wizard.

  D. From the Azure portal, reconfigure SQLVM1.

  Correct Answer: B

  In-place change of SQL Server edition on Azure VM

  The edition of SQL Server is determined by the product key, and is specified during the installation process using the installation media. The edition dictates what features are available in the SQL Server product. You can change the SQL

  Server edition with the installation media and either downgrade to reduce cost or upgrade to enable more features.

  Downgrade an edition

  To downgrade the edition of SQL Server, you need to completely uninstall SQL Server, and reinstall it again with the desired edition setup media.

  1.

  Back up all databases, including the system databases.

  2.

  Move system databases (master, model, and msdb) to a new location.

  3.

  Completely uninstall SQL Server and all associated services.

  4.

  Restart the virtual machine.

  5.

  Install SQL Server by using the media with the desired edition of SQL Server.

  6.

  Install the latest service packs and cumulative updates.

  7.

  Replace the new system databases that were created during installation with the system databases that you previously moved to a different location.

  8.

  After the SQL Server edition is downgraded, modify the edition property of the SQL Server virtual machine in the Azure portal. This will update the metadata and billing associated with this VM.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/change-sql-server-edition