Microsoft Administering Relational Databases on Microsoft Azure - DP-300 Exam Questions
QUESTION NO: 1
You have an Azure SQL managed instance named SQL1.
You need to configure a backup solution for SQL1. The solution must ensure that database backups can be retained for up to 10 years.
Which two resources can you include in the solution? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
You have an Azure SQL managed instance named SQL1.
You need to configure a backup solution for SQL1. The solution must ensure that database backups can be retained for up to 10 years.
Which two resources can you include in the solution? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
Correct Answer: A,D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 2
Hotspot Question
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.
Hotspot Question
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.
Correct Answer:
Explanation:
Box 1: TO URL = 'https://storage1.blob.core.windows.net/blob1/Sales.bak' Native database backup in Azure SQL Managed Instance.
You can backup any database using standard BACKUP T-SQL command:
BACKUP DATABASE tpcc2501
TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak' WITH COPY_ONLY Box 2: WITH COPY_ONLY SQLMI has "managed" everything, includes backup chain, so user can not break it's chain by using different method than COPY_ONLY.
Reference:
https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-instance/ba-p/386154
QUESTION NO: 3
You have an Azure SQL managed instance that hosts multiple databases.
You need to configure alerts for each database based on the diagnostics telemetry of the database.
What should you use?
You have an Azure SQL managed instance that hosts multiple databases.
You need to configure alerts for each database based on the diagnostics telemetry of the database.
What should you use?
Correct Answer: D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 4
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?
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?
Correct Answer: C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 5
Hotspot Question
You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.
You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service.
You need to create a backup of DB1 that is accessible to the Azure Database Migration Service.
What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.
You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service.
You need to create a backup of DB1 that is accessible to the Azure Database Migration Service.
What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: ..with CHECKSUM option
Azure Database Migration Service does not initiate any backups, and instead uses existing backups, which you may already have as part of your disaster recovery plan, for the migration. Be sure that you take backups using the WITH CHECKSUM option.
Box 2: An SMB share
For online migrations from SQL Server to SQL Managed Instance using Azure Database Migration Service, you must provide the full database backup and subsequent log backups in the SMB network share that the service can use to migrate your databases.
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online
QUESTION NO: 6
You have an instance of SQL Server on Azure Virtual Machines.
You need to ensure that a user named User1 can configure proxy accounts for SQL Server Agent jobs. The solution must use the principle of least privilege.
Which role should you assign to User1?
You have an instance of SQL Server on Azure Virtual Machines.
You need to ensure that a user named User1 can configure proxy accounts for SQL Server Agent jobs. The solution must use the principle of least privilege.
Which role should you assign to User1?
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 7
Case Study 5 - ADatum Corporation
Overview
ADatum Corporation is a financial services company that has a main office in New York City.
Existing Environment
Licensing Agreement
ADatum has a Microsoft Volume Licensing agreement that includes Software Assurance.
Network Infrastructure
ADatum has an on-premises datacenter and an Azure subscription named Sub1.
Sub1 contains a virtual network named Network1 in the East US Azure region.
The datacenter is connected to Network1 by using a Site-to-Site (S2S) VPN.
Identity Environment
The on-premises network contains an Active Directory Domain Services (AD DS) forest.
The forest contains a single domain named corp.adatum.com.
The corp.adatum.com domain syncs with a Microsoft Entra tenant named adatum.com.
Database Environment
The datacenter contains the servers shown in the following table.
DB1 and DB2 are used for transactional and analytical workloads by an application named App1.
App1 runs on Microsoft Entra hybrid joined servers that run Windows Server 2022. App1 uses Kerberos authentication.
DB3 stores compliance data used by two applications named App2 and App3.
DB3 performance is monitored by using Extended Events sessions, with the event_file target set to a file share on a local disk of SVR3.
Resource allocation for DB3 is managed by using Resource Governor.
Requirements
Planned Changes
ADatum plans to implement the following changes:
- Deploy an Azure SQL managed instance named Instance1 to Network1.
- Migrate DB1 and DB2 to Instance1.
- Migrate DB3 to Azure SQL Database.
- Following the migration of DB1 and DB2, hand over database development to remote developers who use Microsoft Entra joined Windows 11 devices.
- Following the migration of DB3, configure the database to be part of an auto-failover group.
Availability Requirements
ADatum identifies the following post-migration availability requirements:
- For DB1 and DB2, offload analytical workloads to a read-only database replica in the same Azure region.
- Ensure that if a regional disaster occurs, DB1 and DB2 can be recovered from backups.
- After the migration, App1 must maintain access to DB1 and DB2.
- For DB3, manage potential performance issues caused by resource demand changes by App2 and App3.
- Ensure that DB3 will still be accessible following a planned failover.
- Ensure that DB3 can be restored if the logical server is deleted.
- Minimize downtime during the migration of DB1 and DB2.
Security Requirements
ADatum identifies the following security requirements for after the migration:
- Ensure that only designated developers who use Microsoft Entra joined Windows 11 devices can access DB1 and DB2 remotely.
- Ensure that all changes to DB3, including ones within individual transactions, are audited and recorded.
Management Requirements
ADatum identifies the following post-migration management requirements:
- Continue using Extended Events to monitor DB3.
- In Azure SQL Database, automate the management of DB3 by using elastic jobs that have database-scoped credentials.
Business Requirements
ADatum identifies the following business requirements:
- Minimize costs whenever possible, without affecting other requirements.
- Minimize administrative effort.
You need to recommend a solution to ensure that the performance of DB3 is optimized after the migration to Azure SQL Database. The solution must meet availability requirements.
What should you include in the recommendation?
Case Study 5 - ADatum Corporation
Overview
ADatum Corporation is a financial services company that has a main office in New York City.
Existing Environment
Licensing Agreement
ADatum has a Microsoft Volume Licensing agreement that includes Software Assurance.
Network Infrastructure
ADatum has an on-premises datacenter and an Azure subscription named Sub1.
Sub1 contains a virtual network named Network1 in the East US Azure region.
The datacenter is connected to Network1 by using a Site-to-Site (S2S) VPN.
Identity Environment
The on-premises network contains an Active Directory Domain Services (AD DS) forest.
The forest contains a single domain named corp.adatum.com.
The corp.adatum.com domain syncs with a Microsoft Entra tenant named adatum.com.
Database Environment
The datacenter contains the servers shown in the following table.
DB1 and DB2 are used for transactional and analytical workloads by an application named App1.
App1 runs on Microsoft Entra hybrid joined servers that run Windows Server 2022. App1 uses Kerberos authentication.
DB3 stores compliance data used by two applications named App2 and App3.
DB3 performance is monitored by using Extended Events sessions, with the event_file target set to a file share on a local disk of SVR3.
Resource allocation for DB3 is managed by using Resource Governor.
Requirements
Planned Changes
ADatum plans to implement the following changes:
- Deploy an Azure SQL managed instance named Instance1 to Network1.
- Migrate DB1 and DB2 to Instance1.
- Migrate DB3 to Azure SQL Database.
- Following the migration of DB1 and DB2, hand over database development to remote developers who use Microsoft Entra joined Windows 11 devices.
- Following the migration of DB3, configure the database to be part of an auto-failover group.
Availability Requirements
ADatum identifies the following post-migration availability requirements:
- For DB1 and DB2, offload analytical workloads to a read-only database replica in the same Azure region.
- Ensure that if a regional disaster occurs, DB1 and DB2 can be recovered from backups.
- After the migration, App1 must maintain access to DB1 and DB2.
- For DB3, manage potential performance issues caused by resource demand changes by App2 and App3.
- Ensure that DB3 will still be accessible following a planned failover.
- Ensure that DB3 can be restored if the logical server is deleted.
- Minimize downtime during the migration of DB1 and DB2.
Security Requirements
ADatum identifies the following security requirements for after the migration:
- Ensure that only designated developers who use Microsoft Entra joined Windows 11 devices can access DB1 and DB2 remotely.
- Ensure that all changes to DB3, including ones within individual transactions, are audited and recorded.
Management Requirements
ADatum identifies the following post-migration management requirements:
- Continue using Extended Events to monitor DB3.
- In Azure SQL Database, automate the management of DB3 by using elastic jobs that have database-scoped credentials.
Business Requirements
ADatum identifies the following business requirements:
- Minimize costs whenever possible, without affecting other requirements.
- Minimize administrative effort.
You need to recommend a solution to ensure that the performance of DB3 is optimized after the migration to Azure SQL Database. The solution must meet availability requirements.
What should you include in the recommendation?
Correct Answer: A
QUESTION NO: 8
Drag and Drop Question
You have a database named db1.
The log for db1 contains the following entry.
You need to ensure that db1 can process transactions.
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.
Drag and Drop Question
You have a database named db1.
The log for db1 contains the following entry.
You need to ensure that db1 can process transactions.
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.
Correct Answer:
QUESTION NO: 9
You have an instance of SQL Server on Azure Virtual Machines named SQL1.
SQL1 contains an Extended Events session named session1 that captures Microsoft SQL Server events.
You need to correlate the session events with events captured by Event Tracing for Windows (ETW).
What should you do for session1?
You have an instance of SQL Server on Azure Virtual Machines named SQL1.
SQL1 contains an Extended Events session named session1 that captures Microsoft SQL Server events.
You need to correlate the session events with events captured by Event Tracing for Windows (ETW).
What should you do for session1?
Correct Answer: A
QUESTION NO: 10
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails.
The solution must minimize costs.
Which deployment option and pricing tier should you configure?
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails.
The solution must minimize costs.
Which deployment option and pricing tier should you configure?
Correct Answer: C
QUESTION NO: 11
You create five Azure SQL Database instances on the same logical server.
In each database, you create a user for an Azure Active Directory (Azure AD) user named User1.
User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error.
You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases.
What should you do?
You create five Azure SQL Database instances on the same logical server.
In each database, you create a user for an Azure Active Directory (Azure AD) user named User1.
User1 attempts to connect to the logical server by using Azure Data Studio and receives a login error.
You need to ensure that when User1 connects to the logical server by using Azure Data Studio, User1 can see all the databases.
What should you do?
Correct Answer: D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 12
Hotspot Question
You have an Azure subscription.
You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.
How should you complete the ARM template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an Azure subscription.
You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.
How should you complete the ARM template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: Microsoft.Sql/managedInstances
There is a need to support cross database queries.
Box 2: parameters('virtualNetworkName')
The Managed Instance depends on the Virtual Network.
{
"type": "Microsoft.Sql/managedInstances",
"apiVersion": "2020-02-02-preview",
"name": "[parameters('managedInstanceName')]",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks', parameters('virtualNetworkName'))]"
]
}
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/create-template-quickstart?tabs=azure-powershell
QUESTION NO: 13
Hotspot Question
You have an Azure subscription that contains a storage account named databasebackups.
You have an Azure SQL managed instance named DB1.
You need to back up DB1 to databasebackups.
How should you complete the commands? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an Azure subscription that contains a storage account named databasebackups.
You have an Azure SQL managed instance named DB1.
You need to back up DB1 to databasebackups.
How should you complete the commands? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
