DP-300 Administering Relational Databases on Microsoft Azure exam is a hot Microsoft certification exam, Exam4Training offers you the latest free online DP-300 dumps to practice. You can get online training in the following questions, all these questions are verified by Microsoft experts. If this exam changed, we will share new update questions.
You have an Azure SQL database named DB1 that contains a private certificate named Sales. The private key for Sales is encrypted with a password. You need to change the password for the private key.
Which Transact-SQL statement should you run?
A)
B)
C)
D)
A . Option A
B. Option B
C. Option C
D. Option D
Answer: C
You have an on-premises Microsoft SQL Server 2019 instance named SQL1 that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named MI1 and an Azure Storage account named storage1.
You need to ensure that you can back up db1 to storage1.
The solution must meet the following requirements:
* Use block blob storage.
* Maximize security.
A . Generate a shared access signature (SAS)
B. Enable infrastructure encryption.
C. Create an access policy.
D. Rotate the storage keys
Answer: B
You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.
You need to create a surrogate key for the table. The solution must provide the fastest query performance.
What should you use for the surrogate key?
A . an IDENTITY column
B. a GUID column
C. a sequence object
Answer: A
Explanation:
Dedicated SQL pool supports many, but not all, of the table features offered by other databases.
Surrogate keys are not supported. Implement it with an Identity column.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tablesoverview
Topic 3, ADatum Corporation
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
Overview
ADatum Corporation is a retailer that sells products through two sales channels: retail stores and a website.
Existing Environment
ADatum has one database server that has Microsoft SQL Server 2016 installed. The server hosts three mission-critical databases named SALESDB, DOCDB, and REPORTINGDB.
SALESDB collects data from the stores and the website.
DOCDB stores documents that connect to the sales data in SALESDB. The documents are stored in two different JSON formats based on the sales channel.
REPORTINGDB stores reporting data and contains several columnstore indexes. A daily process creates reporting data in REPORTINGDB from the data in SALESDB. The process is implemented as a SQL Server Integration Services (SSIS) package that runs a stored procedure from SALESDB.
Requirements
Planned Changes
ADatum plans to move the current data infrastructure to Azure.
The new infrastructure has the following requirements:
✑ Migrate SALESDB and REPORTINGDB to an Azure SQL database.
✑ Migrate DOCDB to Azure Cosmos DB.
✑ The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.
✑ As they arrive, all the sales documents in JSON format must be transformed into one consistent format.
✑ Azure Data Factory will replace the SSIS process of copying the data from SALESDB to REPORTINGDB.
Technical Requirements
The new Azure data infrastructure must meet the following technical requirements:
✑ Data in SALESDB must encrypted by using Transparent Data Encryption (TDE). The encryption must use your own key.
✑ SALESDB must be restorable to any given minute within the past three weeks.
✑ Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.
✑ Missing indexes must be created automatically for REPORTINGDB.
✑ Disk IO, CPU, and memory usage must be monitored for SALESDB.
Which windowing function should you use to perform the streaming aggregation of the sales data?
A . Sliding
B. Hopping
C. Session
D. Tumbling
Answer: D
Explanation:
Scenario: The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.
Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.
Timeline
Description automatically generated
Reference: https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md
DRAG DROP
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:
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.
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 Data Lake Storage account that contains a staging zone.
You need to design a daily process to ingest incremental data from the staging zone, transform the data by executing an R script, and then insert the transformed data into a data warehouse in Azure Synapse Analytics.
Solution: You use an Azure Data Factory schedule trigger to execute a pipeline that executes an Azure Databricks notebook, and then inserts the data into the data warehouse.
Does this meet the goal?
A . Yes
B. No
Answer: B
HOTSPOT
You have an Azure SQL database named that contains a table named Table1.
You run a query to bad data into Table1.
The performance Of Table1 during the load operation are shown in exhibit.
Answer: 
Topic 5, Misc. Questions
You have an Azure SQL database named DB1.
You need to encrypt DB1.
The solution must meet the following requirements:
• Encrypt data in motion.
• Support comparison operators.
• Provide randomized encryption.
What should you include in the solution?
A . Always Encrypted
B. column-level encryption
C. Transparent Data Encryption (TDE)
D. Always Encrypted with secure enclaves
Answer: A
Topic 4, Contoso Ltd Clothing Store
You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements.
What should you create?
A . a table that has a FOREIGN KEY constraint
B. a table the has an IDENTITY property
C. a user-defined SEQUENCE object
D. a system-versioned temporal table
Answer: B
Explanation:
Scenario: Contoso requirements for the sales transaction dataset include:
Implement a surrogate key to account for changes to the retail store addresses.
A surrogate key on a table is a column with a unique identifier for each row. The key is not generated from the table data. Data modelers like to create surrogate keys on their tables when they design data warehouse models. You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.
Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tablesidentity
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.
Answer: 
Explanation:
Graphical user interface, text, application, email
Description automatically generated