Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 51:

  You have a Microsoft SQL Server 2019 database named DB1 and an Azure SQL managed instance named SQLMI1.

  You need to move a SQL Server Agent job from DB1 to SQLMI1.

  Which job attribute is unsupported in SQLMI1?

  A. log to table

  B. email notifications

  C. schedules

  D. output files

  Correct Answer: D

  SQL Agent job limitations in SQL Managed Instance

  It is worth noting the differences between SQL Agent available in SQL Server and as part of SQL Managed Instance.

  Some of the SQL Agent features that are available in SQL Server are not supported in SQL Managed Instance:

  SQL Managed Instance can't access files.

  SQL Managed Instance can't access external resources, for example, network shares via robocopy.

  Incorrect:

  Not B: Email notification is supported, although it requires that you configure a Database Mail profile. SQL Serve

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server

• Question 52:

  You need to recommend a disaster recovery solution for an on-premises Microsoft SQL Server database. The solution must meet the following requirements:

  1.

  Support real-time data replication to a different geographic region.

  2.

  Use Azure as a disaster recovery target.

  3.

  Minimize costs and administrative effort. What should you include in the recommendation?

  A. database mirroring on an instance of SQL Server on Azure Virtual Machines

  B. availability groups for SQL Server on Azure Virtual Machines

  C. an Azure SQL Managed Instance link

  D. transactional replication to an Azure SQL Managed Instance

  Correct Answer: D

  Transactional replication is a feature of Azure SQL Managed Instance and SQL Server that enables you to replicate data from a table in Azure SQL Managed Instance or a SQL Server instance to tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.D

  You can use transactional replication to push changes made in an Azure SQL Managed Instance to:

  1.

  A SQL Server database - on-premises or on Azure VM

  2.

  A database in Azure SQL Database

  3.

  An instance database in Azure SQL Managed Instance

  Incorrect:

  Not A: Operates within the same location.

  Database mirroring is a solution for increasing the availability of a SQL Server database.

  Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.

  Not B: Always On availability group on SQL Server on Azure VMs - only within a single region

  Always On availability groups on Azure Virtual Machines are similar to Always On availability groups on-premises, and rely on the underlying Windows Server Failover Cluster.

  VM redundancy

  To increase redundancy and high availability, SQL Server VMs should either be in the same availability set, or different availability zones.

  Placing a set of VMs in the same availability set protects from outages within a data center caused by equipment failure (VMs within an Availability Set do not share resources) or from updates (VMs within an availability set are not updated at

  the same time).

  Availability Zones protect against the failure of an entire data center, with each Zone representing a set of data centers within a region.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview

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

• Question 53:

  You have a single availability set that contains two SQL Server on Azure Virtual Machines instances.

  The instances were deployed by using an Azure Marketplace SQL Server 2019 Enterprise image that has the latest cumulative updates applied. The instances are configured as the nodes of a failover cluster instance (FCI) named FCI1.

  You need to ensure that client applications can connect to FCI1. The solution must meet the following requirements:

  1.

  Provide an availability SLA

  2.

  Minimize costs. What should you create?

  A. an Azure Standard Load Balancer

  B. a virtual network name (VNN) resource

  C. a Basic Azure Load Balancer

  D. a distributed network name (DNN) resource

  Correct Answer: D

  Deploying your VMs to a single subnet requires an additional dependency on an Azure Load Balancer or distributed network name (DNN) to route traffic to your FCI.

  With a DNN, the cluster name object (CNO) is automatically registered with the IP addresses for all the nodes of the cluster, eliminating the need for a dedicated windows cluster IP address.

  By using DNN, you don't need an Azure Load Balancer, and can simply use the same method you’ve been using on-premises for automated failover.

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-prepare-vm https://azure.microsoft.com/en-us/updates/distributed-network-name-for-sql-server-on-virtual-machines-is-now-available/

• Question 54:

  You have an Azure subscription that contain an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.

  You need to collect performance metrics for SQLMI1 and stream the metrics to Workspace1.

  A. Create the private endpoint connection on SQLMI1.

  B. Configure Azure SQL Analytics to use Workspace1.

  C. Modify the Computer + storage settings for SQLMI1.

  D. Modify the diagnostic settings for SQLMI1.

  Correct Answer: D

  Instances in Azure SQL Managed Instance

  You can set up a managed instance resource to collect the following diagnostic telemetry:

  ResourceUsageStats contains vCores count, average CPU percentage, IO requests, bytes read/written, reserved storage space, and used storage space.

  To configure streaming of diagnostic telemetry for managed instance and instance databases, you will need to separately configure each:

  Enable streaming of diagnostic telemetry for managed instance

  Enable streaming of diagnostic telemetry for each instance database

  The managed instance container has its own telemetry separate from each instance database's telemetry.

  To enable streaming of diagnostic telemetry for a managed instance resource, follow these steps:

  1.

  Go to the managed instance resource in Azure portal.

  2.

  Select Diagnostics settings.

  3.

  Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.

  4.

  Enter a setting name for your own reference.

  5.

  Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6.

  For log analytics, select Configure and create a new workspace by selecting +Create New Workspace, or use an existing workspace.

  7.

  Select the check box for instance diagnostic telemetry: ResourceUsageStats.

  8.

  Select Save.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure

• Question 55:

  You have an Azure subscription that contains a SQL Server on Azure Virtual Machines instance named SQLVM1. SQLVM1 hosts a database named DB1.

  You need to retrieve query plans from the Query Store on DB1.

  What should you do first?

  A. From Microsoft SQLServer Management Studio, modify the properties of DB1.

  B. From Microsoft SQL Server Management Studio, modify the properties of the SQL Server instance.

  C. On SQLVM1, install the SQL Server IaaS Agent extension.

  D. On SQLVM1, install the Azure Monitor agent for Windows.

  Correct Answer: A

  Enable the Query Store Use the Query Store page in SQL Server Management Studio

  1.

  In Object Explorer, right-click a database, and then select Properties.

  2.

  In the Database Properties dialog box, select the Query Store page.

  3.

  In the Operation Mode (Requested) box, select Read Write.

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

• Question 56:

  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 plan to migrate db1 to MI1 by using the backup and restore process.

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

  1.

  Use block blob storage.

  2.

  Maximize security.

  What should you do on storage1?

  A. Generate a shared access signature (SAS)

  B. Enable infrastructure encryption.

  C. Create an access policy.

  D. Rotate the storage keys

  Correct Answer: A

• Question 57:

  You manage 100 Azure SQL managed instances located across 10 Azure regions.

  You need to receive voice message notifications when a maintenance event affects any of the 10 regions.

  The solution must minimize administrative effort.

  What should you do?

  A. From the Azure portal, create a service health alert.

  B. From the Azure portal, create an Azure Advisor operational excellence alert.

  C. From Microsoft SQL Server Management Studio (SSMS), configure a SQL Server agent job.

  D. From the Azure portal, configure an activity log alert.

  Correct Answer: C

• Question 58:

  You have an Azure SQL database named DBI that contains a nonclustered index named index1.

  End users report slow queries when they use index1.

  You need to identify the operations that are being performed on the index.

  Which dynamic management view should you use?

  A. Sys.dm_exec_query_plan_stats

  B. Sys.dm_db_index_physical_stats

  C. Sys.dm_db_index_operational_stats

  D. Sys.dm_db_index_useage_stats

  Correct Answer: D

  Sys.dm_db_index_useage_stats

  Returns counts of different types of index operations and the time each type of operation was last performed.

  Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-

  submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

  Incorrect:

  * Sys.dm_db_index_operational_stats

  Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

  Reference:

  https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

• Question 59:

  You have An Azure SQL managed instance.

  You need to configure the SQL Server Agent service to email job notifications.

  Which statement should you execute?

  A. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sysadmin_dbmail_profile’;

  B. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘application_dbmail_profile’;

  C. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘AzureManagedInstance_dbmail_profile’;

  D. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘sys_dbmail_profile’;

  Correct Answer: C

  -- Create a Database Mail profile

  EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AzureManagedInstance_dbmail_profile', @description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

• Question 60:

  You have an Azure subscription that contains two Azure SQL managed instances named SQLMI1 and SQLMI2.

  SQLM2 contains a database named DB1 and a user named User1.

  User1 drops DB1.

  You need to perform a point-in-time restore of DB1 to SQLMI2.

  A. Azure CLI

  B. Transact-SQL

  C. The Azure portal

  D. Azure PowerShell

  Correct Answer: C