[Jan-2025] Latest Microsoft DP-300 Certification Practice Test Questions
Verified DP-300 Dumps Q&As - 1 Year Free & Quickly Updates
Passing the DP-300 exam requires a deep understanding of Azure SQL databases and the ability to perform various administrative tasks. The Microsoft Certified: Azure Database Administrator Associate certification is highly valued in the industry and can help professionals advance their careers in database administration. Overall, the DP-300 exam is an excellent opportunity for professionals to validate their skills and knowledge in administering relational databases on Microsoft Azure.
Microsoft DP-300 certification exam is designed for professionals who want to administer relational databases on Microsoft Azure. Administering Relational Databases on Microsoft Azure certification is suitable for database administrators, developers, and architects who want to learn how to manage and maintain databases on the cloud. DP-300 exam covers various topics, including deploying and configuring SQL Server on Azure, managing databases and instances, implementing security, and optimizing performance.
NEW QUESTION # 36
You have an Azure SQL database named sqldb1.
You need to minimize the amount of space by the data and log files of sqldb1.
What should you run?
- A. sp_clean_db_file_free_space
- B. DBCC SHRINKDATABASE
- C. DBCC SHRINKFILE
- D. sp_clean_db_free_space
Answer: C
Explanation:
DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql
NEW QUESTION # 37
You have a resource group named App1Dev that contains an Azure SQL Database server named DevServer1.
DevServer1 contains an Azure SQL database named DB1. The schema and permissions for DB1 are saved in a Microsoft SQL Server Data Tools (SSDT) database project.
You need to populate a new resource group named App1Test with the DB1 database and an Azure SQL Server named TestServer1. The resources in App1Test must have the same configurations as the resources in App1Dev.
Which four 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.
Answer:
Explanation:
Explanation:
NEW QUESTION # 38
Hotspot Question
You have an Azure SQL database named DB1 that contains two tables named Table1 and Table2. Both tables contain a column named a Column1. Column1 is used for joins by an application named App1.
You need to protect the contents of Column1 at rest, in transit, and in use.
How should you protect the contents of Column1? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
Box 1: Column encryption Key
Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys.
Incorrect Answers:
TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK).
Box 2: Deterministic
Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server).
Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption. Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns.
Incorrect Answers:
Randomized encryption uses a method that encrypts data in a less predictable manner.
Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
NEW QUESTION # 39
You have an Azure Data Factory instance named ADF1 and two Azure Synapse Analytics workspaces named WS1 and WS2.
ADF1 contains the following pipelines:
P1:Uses a copy activity to copy data from a nonpartitioned table in a dedicated SQL pool of WS1 to an Azure Data Lake Storage Gen2 account P2:Uses a copy activity to copy data from text-delimited files in an Azure Data Lake Storage Gen2 account to a nonpartitioned table in a dedicated SQL pool of WS2 You need to configure P1 and P2 to maximize parallelism and performance.
Which dataset settings should you configure for the copy activity of each pipeline? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse
https://docs.microsoft.com/en-us/azure/data-factory/load-azure-sql-data-warehouse
NEW QUESTION # 40
You have SQL Server on an Azure virtual machine.
You review the query plan shown in the following exhibit.
For each of the following statements, select yes if the statement is true. Otherwise, select no.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
NEW QUESTION # 41
You plan to create a table in an Azure Synapse Analytics dedicated SQL pool.
Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted.
You need to ensure that the data is distributed evenly across partitions. The solutions must minimize the amount of time required to delete old data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all.
You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Graphical user interface, text, application Description automatically generated
Box 1: HASH
Box 2: OrderDateKey
In most cases, table partitions are created on a date column.
A way to eliminate rollbacks is to use Metadata Only operations like partition switching for data management.
For example, rather than execute a DELETE statement to delete all rows in a table where the order_date was in October of 2001, you could partition your data early. Then you can switch out the partition with data for an empty partition from another table.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool
NEW QUESTION # 42
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.
Answer:
Explanation:
Reference:
https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-instance/ba-p/386154
NEW QUESTION # 43
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:
Section: [none]
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
NEW QUESTION # 44
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.
Answer:
Explanation:
Explanation
Follow these same steps to create striped virtual disk:
Create Log Storage Pool.
Create Virtual Disk
Create Volume
Box 1: a storage pool
Box 2: a virtual disk that uses stripe layout
Disk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput limit. The combined limit per VM should be higher than the combined limits of attached premium disks.
Box 3: a volume
Reference:
https://hanu.com/hanu-how-to-striping-of-disks-for-azure-sql-server/
NEW QUESTION # 45
You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.
You need to ensure that User1 can perform the following tasks on DB1:
* Create jobs.
* View all jobs.
* Modify, delete, and disable the jobs the user created.
The solution must use the principle of least privilege.
Which built-in database role should you assign to User1, and where is the role defined? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server- ver16#sqlagentreaderrole-permissions
NEW QUESTION # 46
You have an Azure SQL database that contains a table named Customer. Customer has the columns shown in the following table.
You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements:
The first six numerals of each customer's phone number must be masked.
The last four digits of each customer's phone number must be visible.
Hyphens must be preserved and displayed.
How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
NEW QUESTION # 47
You have an Azure subscription that is linked to an Azure AD tenant named contoso.com. The subscription contains an Azure SQL database named SQL 1 and an Azure web named app1. App1 has the managed identity feature enabled.
You need to create a new database user for app1.
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.
Answer:
Explanation:
Explanation:
https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Ce
NEW QUESTION # 48
DRAG DROP
You are creating a managed data warehouse solution on Microsoft Azure.
You must use PolyBase to retrieve data from Azure Blob storage that resides in parquet format and load the data into a large table called FactSalesOrderDetails.
You need to configure Azure Synapse Analytics to receive the data.
Which four 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:
Section: [none]
Explanation:
To query the data in your Hadoop data source, you must define an external table to use in Transact-SQL queries. The following steps describe how to configure the external table.
Step 1: Create a master key on database.
1. Create a master key on the database. The master key is required to encrypt the credential secret.
(Create a database scoped credential for Azure blob storage.)
Step 2: Create an external data source for Azure Blob storage.
2. Create an external data source with CREATE EXTERNAL DATA SOURCE..
Step 3: Create an external file format to map the parquet files.
3. Create an external file format with CREATE EXTERNAL FILE FORMAT.
Step 4. Create an external table FactSalesOrderDetails
4. Create an external table pointing to data stored in Azure storage with CREATE EXTERNAL TABLE.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-azure-blob-storage
NEW QUESTION # 49
You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines. The virtual machine hosts a database named DB1. You need to monitor DB1 by using Extended Events. The solution must meet the following requirements:
* Capture raw event data and store the data in Azure Storage.
* Minimize the performance impact of capturing extended events.
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.
Answer:
Explanation:
Explanation:
NEW QUESTION # 50
What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?
- A. Update the users in SalesSQLDb1.
- B. Configure SalesSQLDb1 as writable.
- C. Update the firewall rules of SalesSQLDb1.
- D. Update the connection strings of SalesSQLDb1App1.
Answer: C
Explanation:
Scenario: SalesSQLDb1 uses database firewall rules and contained database users.
Plan and Implement a High Availability and Disaster Recovery (HADR) Environment Question Set 2
NEW QUESTION # 51
You are planning disaster recovery for the failover group of an Azure SQL Database managed instance.
Your company's SLA requires that the database in the failover group become available as quickly as possible if
a major outage occurs.
You set the Read/Write failover policy to Automatic.
What are two results of the configuration? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
- A. In the event of an outage, you can selectively fail over individual databases.
- B. In the event of a datacenter or Azure regional outage, the databases will fail over automatically.
- C. In the event of an outage, you can set a different grace period to fail over each database.
- D. In the event of an outage, the databases in the primary instance will fail over immediately.
- E. In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be
one hour.
Answer: B,E
Explanation:
Section: [none]
Explanation:
A: Auto-failover groups allow you to manage replication and failover of a group of databases on a server or all
databases in a managed instance to another region.
E: Because verification of the scale of the outage and how quickly it can be mitigated involves human actions
by the operations team, the grace period cannot be set below one hour. This limitation applies to all databases
in the failover group regardless of their data synchronization state.
Incorrect Answers:
C: individual SQL Managed Instance databases cannot be added to or removed from a failover group.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview
NEW QUESTION # 52
You have a on-premises Microsoft SQL Server named SQL1 that hosts five databases.
You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss.
What should you use?
- A. log shipping
- B. Always On availability groups
- C. Backup and Restore
- D. Database Migration Assistant
Answer: D
NEW QUESTION # 53
......
The Microsoft DP-300 exam consists of various topics such as deploying Azure SQL Database, configuring data access and security, monitoring and optimizing databases, and implementing high availability and disaster recovery solutions. DP-300 exam is designed to test the ability of candidates in performing critical database administration tasks such as backup and restore operations, performance tuning, and troubleshooting. Passing the Microsoft DP-300 exam is an excellent way to demonstrate skills in administering and deploying relational databases on Microsoft Azure, which is a highly demanded skill in today's job market.
Latest 2025 Realistic Verified DP-300 Dumps - 100% Free DP-300 Exam Dumps: https://www.itcertmagic.com/Microsoft/real-DP-300-exam-prep-dumps.html
Get 2025 Updated Free Microsoft DP-300 Exam Questions and Answer: https://drive.google.com/open?id=1VsqKdqSY-jnQeKnFr0TqZapZ-Rv_FrIh