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: Dec 03 ,2024
Page 1 out of 14. Viewing questions 1-15 out of 197

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:

Mark Question:
Answer:


Explanation:
Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-
server-ver15

Discussions
0 / 1000

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:

Mark Question:
Answer:


Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/aad-security-configure-tutorial

Discussions
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000

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
Mark Question:
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

User Votes:
A
50%
B
50%
C
50%
Discussions
vote your answer:
A
B
C
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000

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
Mark Question:
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/

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000

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
Mark Question:
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

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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
Mark Question:
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

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

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:

Mark Question:
Answer:


Explanation:
Reference:
https://sqlserverguides.com/read-only-replica-azure-sql/

Discussions
0 / 1000

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:

Mark Question:
Answer:


Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

Discussions
0 / 1000

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:

Mark Question:
Answer:


Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=azuresqldb-mi-current&preserve-
view=true

Discussions
0 / 1000

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:

Mark Question:
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

Discussions
0 / 1000
To page 2