Microsoft DP-300 Cert Guide PDF 100% Cover Real Exam Questions
Pass DP-300 Exam - Real Questions & Answers
Planning and Implementing a High Availability and Disaster Recovery (HADR) Setup
- Testing and configuring HADR
- The use of failover to test DR and HA
- Utilizing database, OS, and platform tools to perform the configuration of replication
- Designing an Availability Group and integrating databases into it
- The configuration of AG listener as well as quorum options
- Recommending an HADR strategy
- Recommending the appropriate HADR strategy on the basis of RTO/RPO needs
- Evaluating HADR for hybrid deployments
- Evaluating Azure specifics for HADR solutions
- Defining resources for HADR
- Performing backup and restoration of data using database tools
- Performing database backup with options
- Performing database restoration with options and to specific points in time
- Configuring long-term solutions for backup retention
NEW QUESTION 16
You have an Azure SQL database named sqldb1.
You need to minimize the possibility of Query Store transitioning to a read-only state.
What should you do?
- A. Double the value of Statistics Collection Interval
- B. Decrease by half the value of Statistics Collection interval
- C. Double the value of Data Flush interval
- D. Decrease by half the value of Data Flush Interval
Answer: D
Explanation:
Section: [none]
Explanation:
The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode.
Incorrect Answers:
C: Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
NEW QUESTION 17
You have an Always On availability group deployed to Azure virtual machines. The availability group contains a
database named DB1 and has two nodes named SQL1 and SQL2. SQL1 is the primary replica.
You need to initiate a full backup of DB1 on SQL2.
Which statement should you run?
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/
- A. mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ - B. mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);
- C. mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ - D. mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/
Answer: C
Explanation:
Section: [none]
Explanation:
BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed
on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap.
Incorrect Answers:
A: Differential backups are not supported on secondary replicas. The software displays this error because the
secondary replicas support copy-only database backups.
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-
on-secondary-replicas-always-on-availability-groups
NEW QUESTION 18
HOTSPOT
You are building an Azure Stream Analytics job to retrieve game data.
You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game.
How should you complete the Stream Analytics query? 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: TopOne() OVER(PARTITION BY Game ORDER BY Score Desc)
TopOne returns the top-rank record, where rank defines the ranking position of the event in the window according to the specified ordering. Ordering/ranking is based on event columns and can be specified in ORDER BY clause.
Analytic Function Syntax:
TopOne() OVER ([<PARTITION BY clause>] ORDER BY (<column name> [ASC |DESC])+ <LIMIT DURATION clause> [<WHEN clause>]) Box 2: Tumbling(minute 5) 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.
Reference:
https://docs.microsoft.com/en-us/stream-analytics-query/topone-azure-stream-analytics
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window- functions.md
NEW QUESTION 19
HOTSPOT
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.
Hot Area:
Answer:
Explanation:
Explanation/Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the- query-store
NEW QUESTION 20
You have an Azure SQL Database instance named DatabaseA on a server named Server1.
You plan to add a new user named App1 to DatabaseA and grant App1 db_datacenter permissions. App1 will use SQL Server Authentication.
You need to create App1. The solution must ensure that App1 can be given access to other databases by using the same credentials.
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.
Answer:
Explanation:
Explanation:
Step 1: On the master database, run CREATE LOGIN [App1] WITH PASSWORD = 'p@aaW0rd!' Logins are server wide login and password pairs, where the login has the same password across all databases. Here is some sample Transact-SQL that creates a login:
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command.
Step 2: On DatabaseA, run CREATE USER [App1] FROM LOGIN [App1]
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
Step 3: On DatabaseA run ALTER ROLE db_datareader ADD Member [App1]
Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the readonlyuser is given read only permissions to the database via the db_datareader role.
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
Reference:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/
NEW QUESTION 21
You plan to deploy an app that includes an Azure SQL database and an Azure web app. The app has the following requirements:
The web app must be hosted on an Azure virtual network.
The Azure SQL database must be assigned a private IP address.
The Azure SQL database must allow connections only from the virtual network.
You need to recommend a solution that meets the requirements.
What should you include in the recommendation?
- A. a network security group (NSG)
- B. Azure Private Link
- C. a server-level firewall
- D. a database-level firewall
Answer: B
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview
NEW QUESTION 22
Your company uses Azure Stream Analytics to monitor devices.
The company plans to double the number of devices that are monitored.
You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?
- A. Early Input Events
- B. Late Input Events
- C. Input Deserialization Errors
- D. Watermark delay
Answer: D
Explanation:
Section: [none]
Explanation:
The Watermark delay metric is computed as the wall clock time of the processing node minus the largest watermark it has seen so far.
The watermark delay metric can rise due to:
1. Not enough processing resources in Stream Analytics to handle the volume of input events.
2. Not enough throughput within the input event brokers, so they are throttled.
3. Output sinks are not provisioned with enough capacity, so they are throttled.
Reference:
https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling
NEW QUESTION 23
You have a SQL pool in Azure Synapse that contains a table named dbo.Customers. The table contains a column name Email.
You need to prevent nonadministrative users from seeing the full email addresses in the Email column. The users must see values in a format of [email protected] instead.
What should you do?
- A. From the Azure portal, set a sensitivity classification of Confidential for the Email column.
- B. From the Azure portal, set a mask on the Email column.
- C. From Microsoft SQL Server Management Studio, grant the SELECT permission to the users for all the columns in the dbo.Customers table except Email.
- D. From Microsoft SQL Server Management Studio, set an email mask on the Email column.
Answer: A
Explanation:
The Email masking method, which exposes the first letter and replaces the domain with XXX.com using a constant string prefix in the form of an email address.
Example: [email protected]
NEW QUESTION 24
Based on the PaaS prototype, which Azure SQL Database compute tier should you use?
- A. Serverless
- B. Hyperscale
- C. Business Critical 4-vCore
- D. General Purpose v-vCore
Answer: C
NEW QUESTION 25
You have an Azure SQL Database managed instance.
The instance starts experiencing performance issues.
You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.
What should you use?
- A. the Azure portal
- B. Extended Events
- C. Query Store
- D. dynamic management views
Answer: C
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
NEW QUESTION 26
HOTSPOT
You are performing exploratory analysis of bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool.
You execute the Transact-SQL query shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
Hot Area:
Answer:
Explanation:
Section: [none]
Explanation:
Box 1: CSV files that have file named beginning with "tripdata_2020"
Box 2: a header
FIRSTROW = 'first_row'
Specifies the number of the first row to load. The default is 1 and indicates the first row in the specified data file.
The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset
NEW QUESTION 27
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_free_space
- B. DBCC SHRINKDATABASE
- C. DBCC SHRINKFILE
- D. sp_clean_db_file_free_space
Answer: B
Explanation:
Section: [none]
Explanation:
DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database.
Incorrect Answers:
D: To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql
NEW QUESTION 28
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 Synapse Analytics dedicated SQL pool that contains a table named Table1.
You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.
You plan to insert data from the files into Table1 and transform the dat a. Each row of data in the files will produce one row in the serving layer of Table1.
You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.
Solution: You use a dedicated SQL pool to create an external table that has an additional DateTime column.
Does this meet the goal?
- A. No
- B. Yes
Answer: A
Explanation:
Instead use a serverless SQL pool to create an external table with the extra column.
Note: In dedicated SQL pools you can only use Parquet native external tables. Native external tables are generally available in serverless SQL pools.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables
NEW QUESTION 29
HOTSPOT
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.
Hot Area:
Answer:
Explanation:
Section: [none]
Explanation:
Box 1: 0
Custom String : Masking method that exposes the first and last letters and adds a custom padding string in
the middle. prefix,[padding],suffix
Box 2: xxx-xxx
Box 3: 5
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
NEW QUESTION 30
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. a GUID column
- B. an IDENTITY column
- C. a sequence object
Answer: B
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
NEW QUESTION 31
......
Get information about the topics of Microsoft DP-300 Exam
Candidates must know the exam topics before they start of preparation. Because it will really help them in hitting the core. Our Microsoft DP-300 dumps will include the following topics:
- Perform administration by using T-SQL (10-15%)
- Implement a secure environment (15-20%)
- Plan and implement a High Availability and Disaster Recovery (HADR) environment (15-20%)
- Monitor and optimize operational resources (15-20%)
- Optimize query performance (5-10%)
- Perform automation of tasks (10-15%)
- Plan and implement data platform resources (15-20%)
Monitoring and Optimizing Operational Resources
- Resource configuration for optimal performance
- Infrastructure and storage resources
- Server and service account settings
- Configuration of Resource Governance for performance
- Configuring a user database
- Implementation of database-scoped configuration
- Configuring compute resources
- Intelligent Query Processing (IQP) configuration
- Identifying performance-related issues
- Configuration of Query Store
- Identifying sessions that can lead to blocking
- Assessing fragmentation of logs and databases
- Assessing configuration parameters related to database performance
- Implementation of maintenance tasks that are performance-related
- Implementing index maintenance and statistics maintenance tasks
- Database auto-tuning configuration
- Storage capacity management
- Performing automation for database maintenance tasks
- Monitoring performance and activity
- Preparation of a working performance baseline
- Determining performance metrics' sources
- Interpretation of performance metrics
- Assessing database performance using Intelligent Insights for Managed Instances and Azure SQL Database
- Monitoring and configuring performance and activity at the server, infrastructure, service and database levels
100% Free DP-300 Daily Practice Exam With 178 Questions: https://www.validvce.com/DP-300-exam-collection.html
Pass DP-300 Review Guide, Reliable DP-300 Test Engine: https://drive.google.com/open?id=1Rgpgf_ZUNaorltiAYeFFmtEHEZreFtge
