microsoft dp-300 practice test
Administering Relational Databases on Microsoft Azure
Note: Test Case questions are at the end of the exam
Last exam update: Jul 20 ,2024
Question 1 Topic 8, Mixed Questions
You need to implement authentication for ResearchDB1. The solution must meet the security and compliance requirements.
What should you run as part of the implementation?
- A. CREATE LOGIN and the FROM WINDOWS clause
- B. CREATE USER and the FROM CERTIFICATE clause
- C. CREATE USER and the FROM LOGIN clause
- D. CREATE USER and the ASYMMETRIC KEY clause
- E. CREATE USER and the FROM EXTERNAL PROVIDER clause
Answer:
E
Explanation:
Scenario: Authenticate database users by using Active Directory credentials.
(Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01.)
Authenticate the user in SQL Database or SQL Data Warehouse based on an Azure Active Directory user: CREATE USER
[[email protected]] FROM EXTERNAL PROVIDER;
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
Question 2 Topic 8, Mixed Questions
HOTSPOT
You have an Azure subscription that contains the resources shown in the following table.
You need to create a read-only replica of DB1 and configure the App1 instances to use the replica.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Reference:
https://sqlserverguides.com/read-only-replica-azure-sql/
Question 3 Topic 8, Mixed Questions
DRAG DROP
You have an Azure subscription that contains the resources shown in the following table.
You need to back up db1 to mysqlbackups, and then restore the backup to a new database named db2 that is hosted on
SQL1. The solution must ensure that db1 is backed up to a stripe set.
Which three Transact-SQL statements should you execute in sequence? To answer, move the appropriate statements from
the list of statements to the answer area and arrange them in the correct order.
Select and Place:
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15
Question 4 Topic 8, Mixed Questions
HOTSPOT
You have an Azure SQL managed instance.
You need to restore a database named DB1 by using Transact-SQL.
Which command should you run? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=azuresqldb-mi-current&preserve-
view=true
Question 5 Topic 8, Mixed Questions
HOTSPOT
You have an Azure SQL database.
You run the following PowerShell script.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Reference: https://docs.microsoft.com/en-us/powershell/module/az.sql/set-
azsqldatabasebackupshorttermretentionpolicy?view=azps-7.2.0 https://docs.microsoft.com/en-
us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy?view=azps-7.2.0
Question 6 Topic 8, Mixed Questions
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.
Answer:
B C
Explanation:
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 7 Topic 8, Mixed Questions
HOTSPOT
You configure a long-term retention policy for an Azure SQL database as shown in the exhibit. (Click the Exhibit tab.)
The first weekly backup occurred on January 4, 2020. The dates for the first 10 weekly backups are:
January 4, 2020
January 11, 2020
January 18, 2020
January 25, 2020
February 1, 2020
February 8, 2020
February 15, 2020
February 22, 2020
February 29, 2020 March 7, 2020
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in
the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Question 8 Topic 8, Mixed Questions
HOTSPOT
You plan to migrate on-premises Microsoft SQL Server databases to Azure.
You need to identify which deployment and resiliency options meet the following requirements:
Support user-initiated backups.
Support multiple automatically replicated instances across Azure regions.
Minimize administrative effort to implement and maintain business continuity.
What should you identify? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Box 1: SQL Server on Azure VMs
SQL Server on Azure Virtual Machines can take advantage of Automated Backup, which regularly creates backups of your
database to blob storage. You can also manually use this technique.
Box 2: Active geo-replication
Geo-replication for services such as Azure SQL Database and Cosmos DB will create secondary replicas of your data
across multiple regions. While both services will automatically replicate data within the same region, geo-replication protects
you against a regional outage by enabling you to fail over to a secondary region.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview
https://docs.microsoft.com/en-us/dotnet/architecture/cloud-native/infrastructure-resiliency-azure
Question 9 Topic 8, Mixed Questions
You have an on-premises multi-tier application named App1 that includes a web tier, an application tier, and a Microsoft SQL
Server tier. All the tiers run on Hyper-V virtual machines.
Your new disaster recovery plan requires that all business-critical applications can be recovered to Azure.
You need to recommend a solution to fail over the database tier of App1 to Azure. The solution must provide the ability to
test failover to Azure without affecting the current environment.
What should you include in the recommendation?
- A. Azure Backup
- B. Azure Information Protection
- C. Windows Server Failover Cluster
- D. Azure Site Recovery
Answer:
D
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-test-failover-to-azure
Question 10 Topic 8, Mixed Questions
You have an Azure subscription that uses a domain named contoso.com.
You have two Azure VMs named DBServer1 and DBServer2. Each of them hosts a default SQL Server instance. DBServer1
- A. Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Asynchronous.
- B. Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Synchronous.
- C. Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Asynchronous.
- D. Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Synchronous.
Answer:
C
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-
groups?view=sql-server-ver15
Question 11 Topic 8, Mixed Questions
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: From Microsoft SQL Server Management Studio (SSMS), you rename Database1 on Server2 as Database2. From
the Azure portal, you create a new database on Server2 by restoring the backup of Database1 from Server1, and then you
delete Database2.
Does this meet the goal?
- A. Yes
- B. No
Answer:
B
Explanation:
Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE
option.
Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally
overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the
current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the
database is not restored. This is an important safeguard.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
Question 12 Topic 8, Mixed Questions
You have an on-premises app named App1 that stores data in an on-premises Microsoft SQL Server 2019 database named
DB1.
You plan to deploy additional instances of App1 to separate Azure regions. Each region will have a separate instance of
App1 and DB1. The separate instances of DB1 will sync by using Azure SQL Data Sync.
You need to recommend a database service for the deployment. The solution must minimize administrative effort.
What should you include in the recommendation?
- A. Azure SQL Managed instance
- B. Azure SQL Database single database
- C. Azure Database for PostgreSQL
- D. SQL Server on Azure virtual machines
Answer:
B
Explanation:
Azure SQL Database single database supports Data Sync.
Incorrect Answers:
A: Azure SQL Managed instance does not support Data Sync.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison
Question 13 Topic 8, Mixed Questions
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. two Azure SQL Databases in an elastic pool
- B. two databases hosted in SQL Server on an Azure virtual machine
- C. two databases in an Azure SQL Managed instance
- D. two single Azure SQL databases
Answer:
A
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
Question 14 Topic 8, Mixed Questions
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 failover groups.
Does this meet the goal?
- A. Yes
- B. No
Answer:
B
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
Question 15 Topic 8, Mixed Questions
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 Business Critical service tier and Availability Zones.
Does this meet the goal?
- A. Yes
- B. No
Answer:
A
Explanation:
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