microsoft dp-300 practice test
Administering Relational Databases on Microsoft Azure
Note: Test Case questions are at the end of the exam
Question 1 Topic 8, Mixed Questions
HOTSPOT
You have a 50-TB Microsoft SQL Server database named DB1.
You need to reduce the time it takes to perform database consistency checks of DB1.
Which Transact-SQL 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/database-console-commands/dbcc-checkdb-transact-sql?view=sql-
server-ver15
Question 2 Topic 8, Mixed Questions
DRAG DROP
You have an Azure Active Directory (Azure AD) tenant named contoso.com that contains a user named [email protected]
and an Azure SQL managed instance named SQLMI1.
You need to ensure that [email protected] can create logins in SQLMI1 that map to Azure AD service principals.
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.
Select and Place:
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/aad-security-configure-tutorial
Question 3 Topic 8, Mixed Questions
HOTSPOT
You have two Azure virtual machines named VM1 and VM2 that run Windows Server 2019. VM1 and VM2 each host a
default Microsoft SQL Server 2019 instance. VM1 contains a database named DB1 that is backed up to a file named
D:\DB1.bak.
You plan to deploy an Always On availability group that will have the following configurations:
VM1 will host the primary replica of DB1.
VM2 will host a secondary replica of DB1.
You need to prepare the secondary database on VM2 for the availability group.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
Hot Area:
Answer:
Explanation:
Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-
secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15
Question 4 Topic 8, Mixed Questions
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
Answer:
A
Explanation:
Azure Databricks makes a distinction between all-purpose clusters and job clusters. You use all-purpose clusters to analyze
data collaboratively using interactive notebooks. You use job clusters to run fast and robust automated jobs.
The Azure Databricks job scheduler creates a job cluster when you run a job on a new job cluster and terminates the cluster
when the job is complete.
Reference: https://docs.microsoft.com/en-us/azure/databricks/clusters
Question 5 Topic 8, Mixed Questions
HOTSPOT
You have an Azure Data Lake Storage Gen2 container.
Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after
that. Users can read files in the container but cannot modify the files.
You need to design a data archiving solution that meets the following requirements:
New data is accessed frequently and must be available as quickly as possible.
Data that is older than five years is accessed infrequently but must be available within one second when requested.
Data that us older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost
possible.
Costs must be minimized while maintaining the required availability.
How should you manage the data? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Box 1: Move to cool storage
The cool access tier has lower storage costs and higher access costs compared to hot storage. This tier is intended for data
that will remain in the cool tier for at least 30 days. Example usage scenarios for the cool access tier include:
Short-term backup and disaster recovery
Older data not used frequently but expected to be available immediately when accessed Large data sets that need to be
stored cost effectively, while more data is being gathered for future processing
Note: Hot - Optimized for storing data that is accessed frequently.
Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days. Archive - Optimized for storing
data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.
Box 2: Move to archive storage
Example usage scenarios for the archive access tier include:
Long-term backup, secondary backup, and archival datasets
Original (raw) data that must be preserved, even after it has been processed into final usable form Compliance and archival
data that needs to be stored for a long time and is hardly ever accessed
Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers
Question 6 Topic 8, Mixed Questions
HOTSPOT
You have an Azure Data Lake Storage Gen2 account named account1 that stores logs as shown in the following table.
You do not expect that the logs will be accessed during the retention periods.
You need to recommend a solution for account1 that meets the following requirements:
Automatically deletes the logs at the end of each retention period Minimizes storage costs
What should you include in the recommendation? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Box 1: Store the infrastructure logs in the Cool access tier the application logs in the Archive access tier
Hot - Optimized for storing data that is accessed frequently.
Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days. Archive - Optimized for storing
data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.
Box 2: Azure Blob storage lifecycle management rules
Blob storage lifecycle management offers a rich, rule-based policy that you can use to transition your data to the best access
tier and to expire data at the end of its lifecycle.
Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers
Question 7 Topic 8, Mixed Questions
You have the following Transact-SQL query.
Which column returned by the query represents the free space in each file?
- A. ColumnA
- B. ColumnB
- C. ColumnC
- D. ColumnD
Answer:
C
Explanation:
Example:
Free space for the file in the below query result set will be returned by the FreeSpaceMB column.
SELECT DB_NAME() AS DbName, name AS FileName, type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
Reference: https://www.sqlshack.com/how-to-determine-free-space-and-file-size-for-sql-server-databases/
Question 8 Topic 8, Mixed Questions
HOTSPOT
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.
Hot Area:
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
Reference: https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-
instance/ba-p/386154
Question 9 Topic 8, Mixed Questions
HOTSPOT
You have SQL Server on an Azure virtual machine that contains a database named DB1.
The database reports a CHECKSUM error.
You need to recover the database.
How should you complete the statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:
Explanation:
Box 1: SINGLE_USER The specified database must be in single-user mode to use one of the following repair options.
Box 2: REPAIR_ALLOW_DATA_LOSS REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can
cause some data loss.
Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing
a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data
loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.
Incorrect Answers:
REPAIR_FAST Maintains syntax for backward compatibility only. No repair actions are performed.
Box 3: MULTI_USER MULTI_USER All users that have the appropriate permissions to connect to the database are allowed.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
Question 10 Topic 8, Mixed Questions
You have an Azure SQL database named DB3.
You need to provide a user named DevUser with the ability to view the properties of DB3 from Microsoft SQL Server
Management Studio (SSMS) as shown in the exhibit. (Click the Exhibit tab.)
Which Transact-SQL command should you run?
- A. GRANT SHOWPLAN TO DevUser
- B. GRANT VIEW DEFINITION TO DevUser
- C. GRANT VIEW DATABASE STATE TO DevUser
- D. GRANT SELECT TO DevUser
Answer:
C
Explanation:
The exhibits displays Database [State] properties.
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or
VIEW DATABASE STATE permission.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-options-page
Question 11 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 12 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 13 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 14 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 15 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