Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 71:

  You have an instance of SQL Server on Azure Virtual Machine named SQL1.

  You need to monitor SQL1 and query the metrics by using Kusto query language. The solution must minimize administrative effort.

  Where should you store the metrics?

  A. a Log Analytics workspace

  B. Azure Event Hubs

  C. Azure SQL Database

  D. an Azure Blob storage container

  Correct Answer: A

• Question 72:

  You have a on-premises Microsoft SQL Server named SQL1 that hosts five databases.

  You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss.

  What should you use?

  A. log shipping

  B. Always On availability groups

  C. Database Migration Assistant

  D. Backup and Restore

  Correct Answer: D

  DMA does not support database migrations to Azure SQL Managed Instance. Recommendation is to use the Azure SQL migration extension for Azure Data Studio, which supports both online and offline database migrations to Azure SQL Managed Instance, but here we don´t have the option "Azure SQL migration extension for Azure Data Studio". https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16#capabilities

  Regarding log shipping to Managed Instance is not possible; it only supports the restore of full backups. https://dba.stackexchange.com/questions/232332/is-it-possible-to-log-ship-from-on-premise-sql-server-to-azure-sql-managed-insta

• Question 73:

  You have an on-premises Microsoft SQL server that uses the FileTables and Filestream features.

  You plan to migrate to Azure SQL.

  Which service should you use?

  A. Azure SQL Database

  B. SQL Server on an Azure Virtual Machine

  C. Azure SQL Managed Instance

  D. Azure Database for MySQL

  Correct Answer: B

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-overview

• Question 74:

  You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime. What should you do?

  A. Configure Transaction Log Shipping.

  B. Implement Always On availability groups.

  C. Configure transactional replication.

  D. Import a BACPAC.

  Correct Answer: C

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/migrate-to-database-from-sql-server#method-1-migration-with-downtime-during-the-migration

• Question 75:

  You have an Azure SQL database named DB1.

  You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled.

  During a database audit, you discover that none of the fields contain more than 150 characters.

  You need to ensure that you can apply page compression to Table1.

  What should you do?

  A. Configure the columns as sparse.

  B. Change the column type to NVARCHAR(MAX).

  C. Change the column type to VARCHAR(MAX).

  D. Change the column type to VARCHAR(200).

  Correct Answer: D

  Reference:

  https://www.sqlshack.com/sql-varchar-data-type-deep-dive/

  https://36chambers.wordpress.com/2020/06/18/nvarchar-everywhere-a-thought-experiment/

• Question 76:

  You have an Azure SQL managed instance named MI1.

  You need to implement automatic tuning for the databases of MI1.

  What should you do?

  A. Use the REST API to call the patch operation and modify the AutomaticTuningServerMode property.

  B. Use Transact-SQL to enable the force_last_good_plan option.

  C. From the Azure portal, configure automatic tuning.

  Correct Answer: B

  For Azure SQL Managed Instance, the supported option FORCE_LAST_GOOD_PLAN can only be configured through T-SQL.

  Enable automatic tuning on server On the server level you can choose to inherit automatic tuning configuration from "Azure Defaults" or not to inherit the configuration. Azure defaults are FORCE_LAST_GOOD_PLAN enabled, CREATE_INDEX disabled, and DROP_INDEX disabled.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable

• Question 77:

  You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.

  You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.

  You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:

  1.

  Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.

  2.

  Restrict network connectivity to SqlSrv1. What should you create on VNet1?

  A. a VPN gateway

  B. a service endpoint

  C. a private endpoint

  D. an ExpressRoute gateway

  Correct Answer: C

  A private endpoint is a network interface that uses a private IP address from your virtual network. This network interface connects you privately and securely to a service powered by Azure Private Link. By enabling a private endpoint, you're bringing the service into your virtual network.

  The service could be an Azure service such as:

  1.

  Azure Storage

  2.

  Azure Cosmos DB

  3.

  Azure SQL Database

  4.

  Your own service using a Private Link Service.

  Reference: https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview

• Question 78:

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

  You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a virtual network named VNET1. SQLMI1 resides on VNET1. The on-premises network connects to VNET1 by using an ExpressRoute

  connection.

  You plan to migrate DB1 to SQLMI1 by using Azure Database Migration Service.

  You need to configure VNET1 to support the migration.

  What should you do?

  A. Configure service endpoints.

  B. Configure virtual network peering.

  C. Deploy an Azure firewall.

  D. Configure network security groups (NSGs).

  Correct Answer: A

  Reference: https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-managed-instance

• Question 79:

  You have a new Azure subscription.

  You create an Azure SQL Database instance named DB1 on an Azure SQL Database server named Server1.

  You need to ensure that users can connect to DB1 in the event of an Azure regional outage. In the event of an outage, applications that connect to DB1 must be able to connect without having to update the connection strings. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

  A. From the properties of DB1, configure geo-replication.

  B. From the properties of Server1, add a failover group.

  C. Create a new Azure SQL Database server named Server2.

  D. From the properties of Server1, configure retention for DB1.

  E. Create a new Azure SQL Database instance named DB2.

  Correct Answer: BC

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell#best-practices-for-sql-database https://docs.microsoft.com/en-us/azure/azure-sql/database/failover-group-add-single-database-tutorial?tabs=azure-portal

• Question 80:

  You have an Azure SQL managed instance.

  You need to enable SQL Agent Job email notifications.

  What should you do?

  A. Use the Agent XPs option.

  B. Enable the SQL Server Agent.

  C. Run the sp_configurecommand.

  D. Run the sp_set_agent_propertiescommand.

  Correct Answer: C

  Reference: https://techcommunity.microsoft.com/t5/azure-sql-blog/sending-emails-in-azure-sql-managed-instance/ba-p/386235