DP-300 Practice Test Questions

You have SQL Server on Azure virtual machines in an availability groupYou have a database named DB1 that is NOT in the availability group.
You create a full database backup of DB1.
You need to add DB1 to the availability group.
Which restore option should you use on the secondary replica?

A. Restore with Recovery

B. Restore with Norecovery

C. Restore with Standby

B. Restore with Norecovery

Explanation:

D18912E1457D5D1DDCBD40AB3BF70D5D
Prepare a secondary database for an Always On availability group requires two steps:
1. Restore a recent database backup of the primary database and subsequent log backups onto each server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY
2. Join the restored database to the availability group.

Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availabilitygroups/ windows/manually-prepare-asecondarydatabase- for-an-availability-group-sql-server

You are building an Azure virtual machine.
You allocate two 1-TiB, P30 premium storage disks to the virtual machine. Each disk provides 5,000 IOPS.
You plan to migrate an on-premises instance of Microsoft SQL Server to the virtual machine. The instance has a database that contains a 1.2-TiB data file. The database requires 10,000 IOPS You need to configure storage for the virtual machine to support the database.
Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order

You have an Azure SQL database named DB1. The automatic tuning options for DB1 are
configured as shown in the following exhibit.

You have an Always On availability group deployed to Azure virtual machines. The availability group contains a database named DB1 and has two nodes named SQL1 and SQL2. SQL1 is the primary replica.
You need to initiate a full backup of DB1 on SQL2.
Which statement should you run?

A. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);

B. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);

C. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);

D. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);

B. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);

Explanation:

BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap.

Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/activesecondaries- backup-on-secondary-replicas-always-on-availability-groups

You have an Azure SQL Database instance named DatabaseA on a server named Server1.
You plan to add a new user named App1 to DatabaseA and grant App1 db_datacenter permissions. App1 will use SQL Server Authentication.
You need to create App1. The solution must ensure that App1 can be given access to other
databases by using the same credentials.
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.

You plan to move two 100-GB databases to Azure.
You need to dynamically scale resources consumption based on workloads. The solution must minimize downtime during scaling operations.
What should you use?

A. An Azure SQL Database elastic pool

B. SQL Server on Azure virtual machines

C. an Azure SQL Database managed instance

D. Azure SQL databases

A. An Azure SQL Database elastic pool

Explanation:

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price.

Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview

You have SQL Server on an Azure virtual machine that contains a database named DB1.
DB1 is 30 TB and has a 1-GB daily rate of change.
You back up the database by using a Microsoft SQL Server Agent job that runs Transact-SQL commands. You perform a weekly full backup on Sunday, daily differential backups at 01:00, and transaction log backups every five minutes.
The database fails on Wednesday at 10:00.
Which three backups should you restore in sequence? To answer, move the appropriate backups from the list of backups to the answer area and arrange them in the correct order.

You have an Azure SQL managed instance named SQLMI1 that hosts 10 databases.
You need to implement alerts by using Azure Monitor. The solution must meet the following requirements:
Minimize costs.
Aggregate Intelligent Insights telemetry from each database.
What should you do?

A. A. From the Diagnostic settings of each database, select Send to Log Analytics.

B. From the Diagnostic settings of each database, select Stream to an event hub.

C. From the Diagnostic settings of SQLMI1. select Send to Log Analytics.

D. From the Diagnostic settings of SQLMI1. select Stream to an event hub.

A company plans to use Apache Spark analytics to analyze intrusion detection data.
You need to recommend a solution to analyze network and system activity data for malicious activities and policy violations. The solution must minimize administrative efforts.
What should you recommend?

A. Azure Data Lake Storage

B. Azure Databricks

C. Azure HDInsight

D. Azure Data Factory

C. Azure HDInsight

Explanation:

Azure HDInsight offers pre-made, monitoring dashboards in the form of solutions that can be used to monitor the workloads running on your clusters. There are solutions for Apache Spark, Hadoop, Apache Kafka, live long and process (LLAP), Apache HBase, and Apache Storm available in the Azure Marketplace.
Note: With Azure HDInsight you can set up Azure Monitor alerts that will trigger when the value of a metric or the results of a query meet certain conditions. You can condition on a query returning a record with a value that is greater than or less than a certain threshold, or even on the number of results returned by a query. For example, you could create an alert to send an email if a Spark job fails or if a Kafka disk usage becomes over 90 percent full.

Reference:
https://azure.microsoft.com/en-us/blog/monitoring-on-azure-hdinsight-part-4-workloadmetrics- and-logs/

You have an Azure SQL database named db1 on a server named server1.
You need to modify the MAXDOP settings for db1. What should you do?

A. Connect to db1 and run the sp_configure command.

B. Connect to the master database of server1 and run the sp_configure command.

C. Configure the extended properties of db1.

D. Modify the database scoped configuration of db1.

You plan to perform batch processing in Azure Databricks once daily.
Which type of Databricks cluster should you use?

A. automated

B. interactive

C. High Concurrency

A. automated

You have an Azure subscription that contains a server named Server1. Server1 hosts two Azure SQL databases named DB1 and DB2.
You plan to deploy a Windows app named App1 that will authenticate to DB2 by using SQL authentication.
You need to ensure that App1 can access DB2. The solution must meet the following requirements: What should you create?