Topic 6: Misc. Questions

You have SQL Server on an Azure virtual machine.
You review the query plan shown in the following exhibit

You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The
instance contains a 4-TB database named DB1.
You plan to migrate DB1 to an Azure SQL Database managed instance.
What should you use to minimize downtime and data loss during the migration?
D18912E1457D5D1DDCBD40AB3BF70D5D

A. distributed availability groups

B. database mirroring

C. log shipping

D. Database Migration Assistant

D.   Database Migration Assistant

Explanation:
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
Note: SQL Managed Instance supports the following database migration options (currently these are the only supported migration methods):
Azure Database Migration Service - migration with near-zero downtime.
Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime.

Reference:
https://docs.microsoft.com/en-us/sql/dma/dma-overview

You have SQL Server on an Azure virtual machine named SQL1.
SQL1 has an agent job to back up all databases.
You add a user named dbadmin1 as a SQL Server Agent operator.
You need to ensure that dbadmin1 receives an email alert if a job fails.
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

Note: This question is part of a series of questions that present the same scenario.
Each question in the series contains a unique solution that might meet the stated
goals. Some question sets might have more than one correct solution, while others
might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a
result, these questions will not appear in the review screen.
You have an Azure SQL database named Sales.
You need to implement disaster recovery for Sales to meet the following requirements:
During normal operations, provide at least two readable copies of Sales.
Ensure that Sales remains available if a datacenter fails.
Solution: You deploy an Azure SQL database that uses the General Purpose service tier
and geo-replication.
Does this meet the goal?

A. Yes

B. No

B.   No

Explanation:

Instead deploy an Azure SQL database that uses the Business Critical service tier and
Availability Zones.
Note: Premium and Business Critical service tiers leverage the Premium availability model,
which integrates compute resources (sqlservr.exe process) and storage (locally attached
SSD) on a single node. High availability is achieved by replicating both compute and
storage to additional nodes creating a three to four-node cluster.
By default, the cluster of nodes for the premium availability model is created in the same
datacenter. With the introduction of Azure Availability Zones, SQL Database can place
different replicas of the Business Critical database to different availability zones in the
same region. To eliminate a single point of failure, the control ring is also duplicated across
multiple zones as three gateway rings (GW).
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

Note: This question is part of a series of questions that present the same scenario.
Each question in the series contains a unique solution that might meet the stated
goals. Some question sets might have more than one correct solution, while others
might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a
result, these questions will not appear in the review screen.
You have two Azure SQL Database servers named Server1 and Server2. Each server
contains an Azure SQL database named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the
existing Database1 on Server2.
Solution: You restore Database1 from Server1 to the Server2 by using the RESTORE
Transact-SQL command and the REPLACE option.
Does this meet the goal?

A. Yes

B. No

A.   Yes

Explanation:

The REPLACE option overrides several important safety checks that restore normally
performs. The overridden checks are as follows:
Restoring over an existing database with a backup taken of another database.
With the REPLACE option, restore allows you to overwrite an existing database with
whatever database is in the backup set, even if the specified database name differs from
the database name recorded in the backup set. This can result in accidentally overwriting a
database by a different database.

Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

You have an Azure SQL Database managed instance named SQLMI1. A Microsoft SQL Server Agent job runs on SQLMI1.
You need to ensure that an automatic email notification is sent once the job completes What should you include in the solution?

A. From SQL Server Configuration Manager (SSMS), enable SQL Server Agent

B. From SQL Server Management Studio (SSMS), run sp_set_sqlagent_properties

C. From SQL Server Management Studio (SSMS), create a Database Mail profile

D. From the Azure portal, create an Azure Monitor action group that has an Email/SMS/Push/Voice action

C.   From SQL Server Management Studio (SSMS), create a Database Mail profile

You have an Azure SQL Database managed instance named sqldbmi1 that contains a
database name Sales
You need to initiate a backup of Sales.
How should you complete the Transact-SQL statement? To answer, select the appropriate
options in the answer area.
NOTE: Each correct selection is worth one point

You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.
You run the select * from sys.database_scoped_configurations Transact-SQL command
and receive the results shown in the following table
You need relieve the memory pressure.
What should you configure?

A. LEGACY_CARDINALITY_ESTIMATION

B. QUERY_OPTIMIZER_HOTFIXES

C. OPTIMIZE_FOR_AD_HOC_WORKLOADS

D. ACCELERATED_PLAN_FORCING

C.   OPTIMIZE_FOR_AD_HOC_WORKLOADS

Explanation:

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
Enables or disables a compiled plan stub to be stored in cache when a batch is compiled
for the first time. The default is OFF. Once the database scoped configuration
OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub
will be stored in cache when a batch is compiled for the first time. Plan stubs have a
smaller memory footprint compared to the size of the full compiled plan.

Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configurationtransact-
sql

You are planning disaster recovery for the failover group of an Azure SQL Database
managed instance.
Your company’s SLA requires that the database in the failover group become available as
quickly as possible if a major outage occurs.
You set the Read/Write failover policy to Automatic.
What are two results of the configuration? Each correct answer presents a complete
solution.
NOTE: Each correct selection is worth one point.

A. In the event of a datacenter or Azure regional outage, the databases will fail over automatically.

B. In the event of an outage, the databases in the primary instance will fail over immediately.

C. In the event of an outage, you can selectively fail over individual databases.

D. In the event of an outage, you can set a different grace period to fail over each database.

E. In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour.

A.   In the event of a datacenter or Azure regional outage, the databases will fail over automatically.
E.   In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour.

Explanation:

A: Auto-failover groups allow you to manage replication and failover of a group of databases on a server or all databases in a managed instance to another region.
E: Because verification of the scale of the outage and how quickly it can be mitigated involves human actions
by the operations team, the grace period cannot be set below one hour. This limitation applies to all databases in the failover group regardless of their data synchronization state.

Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview

You are building a database backup solution for a SQL Server database hosted on an Azure virtual machine.
In the event of an Azure regional outage, you need to be able to restore the database backups. The solution must minimize costs.
Which type of storage accounts should you use for the backups?

A. locally-redundant storage (LRS)

B. read-access geo-redundant storage (RA-GRS)

C. zone-redundant storage (ZRS)

D. geo-redundant storage

B.   read-access geo-redundant storage (RA-GRS)

Explanation:

Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read if the primary region becomes unavailable. For read access to the secondary region, enable read-access geo-redundant storage (RA-GRS) or read-access geo-zone-redundant storage (RA-GZRS).

Reference:
https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

Page 3 out of 34 Pages
DP-300 Practice Test Previous

Are You Truly Prepared?

Don't risk your exam fee on uncertainty. Take this definitive practice test to validate your readiness for the Microsoft DP-300 exam.

Enroll Now