Get New 2021 Microsoft exam DP-300 Dumps Bundle On flat Updated Dumps!
Full DP-300 Practice Test and 178 unique questions with explanations waiting just for you, get it now!
NEW QUESTION 82
You receive numerous alerts from Azure Monitor for an Azure SQL database.
You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Set Threshold Sensitivity to High
- B. Set Force Plan to On
- C. Set the Alert logic threshold to Dynamic
- D. Set the Alert logic threshold to Static
- E. Set Threshold Sensitivity to Low
Answer: C,E
Explanation:
B: Dynamic Thresholds continuously learns the data of the metric series and tries to model it using a set of algorithms and methods. It detects patterns in the data such as seasonality (Hourly / Daily / Weekly), and is able to handle noisy metrics (such as machine CPU or memory) as well as metrics with low dispersion (such as availability and error rate).
D: Alert threshold sensitivity is a high-level concept that controls the amount of deviation from metric behavior required to trigger an alert.
Low - The thresholds will be loose with more distance from metric series pattern. An alert rule will only trigger on large deviations, resulting in fewer alerts.
Incorrect Answers:
A: High - The thresholds will be tight and close to the metric series pattern. An alert rule will be triggered on the smallest deviation, resulting in more alerts.
Reference:
https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresholds
NEW QUESTION 83
HOTSPOT
You need to recommend a configuration for ManufacturingSQLDb1 after the migration to Azure. The solution
must meet the business requirements.
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:
Section: [none]
Explanation:
Box 1: Node majority with witness
As a general rule when you configure a quorum, the voting elements in the cluster should be an odd number.
Therefore, if the cluster contains an even number of voting nodes, you should configure a disk witness or a file
share witness.
Note: Mode: Node majority with witness (disk or file share)
Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes
in the active cluster membership plus a witness vote. A quorum witness can be a designated disk witness or a
designated file share witness.
Box 2: Azure Standard Load Balancer
Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more
Healthy Virtual Machine Instances, will be available 99.99% of the time.
Scenario: Business Requirements
Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.
Incorrect Aswers:
Basic Balancer: No SLA is provided for Basic Load Balancer.
Note: There are two main options for setting up your listener: external (public) or internal. The external (public)
listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible
over the internet. An internal listener uses an internal load balancer and only supports clients within the same
Virtual Network.
Reference:
https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness
https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/
NEW QUESTION 84
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:
Section: [none]
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
NEW QUESTION 85
Which audit log destination should you use to meet the monitoring requirements?
- A. Azure Storage
- B. Azure Log Analytics
- C. Azure Event Hubs
Answer: B
Explanation:
Section: [none]
Explanation:
Scenario: Use a single dashboard to review security and audit data for all the PaaS databases.
With dashboards can bring together operational data that is most important to IT across all your Azure
resources, including telemetry from Azure Log Analytics.
Note: Auditing for Azure SQL Database and Azure Synapse Analytics tracks database events and writes them
to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.
Reference:
https://docs.microsoft.com/en-us/azure/azure-monitor/visualize/tutorial-logs-dashboards
Question Set 3
NEW QUESTION 86
You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.
You run the select * from sys.database_scoped_configurationsTransact-SQL command and receive the results shown in the following table.
You need relieve the memory pressure.
What should you configure?
- A. QUERY_OPTIMIZER_HOTFIXES
- B. LEGACY_CARDINALITY_ESTIMATION
- C. ACCELERATED_PLAN_FORCING
- D. OPTIMIZE_FOR_AD_HOC_WORKLOADS
Answer: D
Explanation:
Section: [none]
Explanation:
OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }
Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. The default is OFF. Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint compared to the size of the full compiled plan.
Incorrect Answers:
A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.
B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }
Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
NEW QUESTION 87
You have an Azure SQL database that contains a table named Employees. Employees contains a column named Salary.
You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the Salary column and must provide the most secure encryption.
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: Create a column master key
Create a column master key metadata entry before you create a column encryption key metadata entry in the database and before any column in the database can be encrypted using Always Encrypted.
Step 2: Create a column encryption key.
Step 3: Encrypt the Salary column by using the randomized encryption type.
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.
Note: A column encryption key metadata object contains one or two encrypted values of a column encryption key that is used to encrypt data in a column. Each value is encrypted using a column master key.
Incorrect Answers:
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. However, it may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there's a small set of possible encrypted values, such as True/False, or North/South/East/West region.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
NEW QUESTION 88
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
- A. Azure SQL Database Standard
- B. Azure SQL Database serverless
- C. Azure SQL Database Premium
- D. Azure SQL Database managed instance Business Critical
Answer: C
Explanation:
By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager (ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business Critical databases or pools to the zone redundant configuration.
Incorrect Answers:
C. This feature is not available in SQL Managed Instance.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla
NEW QUESTION 89
You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.
You plan to migrate DB1 to an Azure SQL Database managed instance.
What should you use to minimize downtime and data loss during the migration?
D18912E1457D5D1DDCBD40AB3BF70D5D
- A. database mirroring
- B. distributed availability groups
- C. log shipping
- D. Database Migration Assistant
Answer: B
Explanation:
The Data Migration Assistant (DMA) helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.
Note: SQL Managed Instance supports the following database migration options (currently these are the only supported migration methods):
Azure Database Migration Service - migration with near-zero downtime.
Native RESTORE DATABASE FROM URL - uses native backups from SQL Server and requires some downtime.
Reference:
https://docs.microsoft.com/en-us/sql/dma/dma-overview
NEW QUESTION 90
You need to identify the cause of the performance issues on SalesSQLDb1.
Which two dynamic management views should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. sys.dm_cdc_errors
- B. sys.dm_exec_compute_node_errors
- C. sys.dm_exec_requests
- D. sys.dm_tran_locks
- E. sys.dm_pdw_nodes_os_wait_stats
- F. sys.dm_pdw_nodes_tran_locks
Answer: E,F
Explanation:
Section: [none]
Explanation:
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
A: Use sys.dm_pdw_nodes_tran_locks instead of sys.dm_tran_locks from Azure Synapse Analytics (SQL Data Warehouse) or Parallel Data Warehouse.
E: Example:
The following query will show blocking information.
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
Note: Depending on the system you're working with you can access these wait statistics from one of three locations:
sys.dm_os_wait_stats: for SQL Server
sys.dm_db_wait_stats: for Azure SQL Database
sys.dm_pdw_nodes_os_wait_stats: for Azure SQL Data Warehouse
Incorrect Answers:
F: sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2019 (15.x). Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
Instead use sys.dm_pdw_nodes_tran_locks.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran- locks-transact-sql Question Set 2
NEW QUESTION 91
You need to recommend an availability strategy for an Azure SQL database. The strategy must meet the following requirements:
* Support failovers that do not require client applications to change their connection strings.
* Replicate the database to a secondary Azure region.
* Support failover to the secondary region.
What should you include in the recommendation?
- A. failover groups
- B. transactional replication
- C. Availability Zones
- D. geo-replication
Answer: D
Explanation:
Section: [none]
Explanation:
Active geo-replication is an Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a server in the same or different data center (region).
Incorrect Answers:
C: Availability Zones are unique physical locations within a region. Each zone is made up of one or more datacenters equipped with independent power, cooling, and networking.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
NEW QUESTION 92
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:
NEW QUESTION 93
You have several Azure SQL databases on the same Azure SQL Database server in a resource group named
ResourceGroup1.
You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any
additional databases that are created on the Azure SQL server.
Which resource type should you use to create the alert?
- A. SQL Virtual Machines
- B. SQL Servers
- C. SQL Databases
- D. Resource Groups
Answer: C
Explanation:
Section: [none]
Explanation:
There are resource types related to application code, compute infrastructure, networking, storage + databases.
You can deploy up to 800 instances of a resource type in each resource group.
Some resources can exist outside of a resource group. These resources are deployed to the subscription,
management group, or tenant. Only specific resource types are supported at these scopes.
Reference:
https://docs.microsoft.com/en-us/azure/azure-resource-manager/management/resource-providers-and-types
NEW QUESTION 94
You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as 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:
Explanation:
Box 1: Yes
We see: Tuning option: Create index ON
CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
Box 2: No
Box 3: Yes
FORCE LAST GOOD PLAN (automatic plan correction) - Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.
NEW QUESTION 95
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 set a different grace period to fail over each database.
- B. In the event of an outage, you can selectively fail over individual databases.
- C. In the event of a datacenter or Azure regional outage, the databases will fail over automatically.
- 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: C,E
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 96
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 8 vCPUSs and 64 GB of memory.
You need to provide the lowest latency for tempdb.
What is the total number of data files that tempdb should contain?
- A. 0
- B. 1
- C. 2
- D. 3
Answer: C
Explanation:
Section: [none]
Explanation:
The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
NEW QUESTION 97
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 DATABASE STATE TO DevUser
- C. GRANT SELECT TO DevUser
- D. GRANT VIEW DEFINITION TO DevUser
Answer: B
Explanation:
Section: [none]
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
NEW QUESTION 98
You have SQL Server on an Azure virtual machine that contains a database named DB1.
You have an application that queries DB1 to generate a sales report.
You need to see the parameter values from the last time the query was executed.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Enable Lightweight_Query_Profiling in the master database
- B. Enable Last_Query_Plan_Stats in DB1
- C. Enable Last_Query_Plan_Stats in the master database
- D. Enable Lightweight_Query_Profiling in DB1
- E. Enable PARAMETER_SNIFFING in DB1
Answer: B,C
Explanation:
Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.
Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.
Incorrect Answers:
E: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.
Parameter values are sniffed during compilation or recompilation for the following types of batches:
* Stored procedures
* Queries submitted via sp_executesql
* Prepared queries
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
NEW QUESTION 99
You need to apply 20 built-in Azure Policy definitions to all new and existing Azure SQL Database deployments in an Azure subscription. The solution must minimize administrative effort.
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: Create an Azure Policy Initiative
The first step in enforcing compliance with Azure Policy is to assign a policy definition. A policy definition defines under what condition a policy is enforced and what effect to take.
With an initiative definition, you can group several policy definitions to achieve one overarching goal. An initiative evaluates resources within scope of the assignment for compliance to the included policies.
Step 2: Create an Azure Policy Initiative assignment
Assign the initiative definition you created in the previous step.
Step 3: Run Azure Policy remediation tasks
To apply the Policy Initiative to the existing SQL databases.
Reference:
https://docs.microsoft.com/en-us/azure/governance/policy/tutorials/create-and-manage
NEW QUESTION 100
You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:
* Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.
* Restrict network connectivity to SqlSrv1.
What should you create on VNet1?
- A. a service endpoint
- B. an ExpressRoute gateway
- C. a VPN gateway
- D. a private link
Answer: D
Explanation:
Section: [none]
Explanation:
Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network.
Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
NEW QUESTION 101
You have SQL Server on an Azure virtual machine that contains a database named DB1.
You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit:
What should you do to ensure that the query uses the execution plan which executes in the least amount of time?
- A. Force the query execution plan for plan 1221065.
- B. Disable parameter sniffing.
- C. Force the query execution plan for plan 1220917.
- D. Run the DBCC FREEPROCCACHE command.
Answer: C
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
NEW QUESTION 102
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 DATABASE STATE TO DevUser
- C. GRANT SELECT TO DevUser
- D. GRANT VIEW DEFINITION TO DevUser
Answer: B
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
NEW QUESTION 103
......
[Oct-2021] Pass Microsoft DP-300 Exam in First Attempt Guaranteed: https://drive.google.com/open?id=1kMeY9qqA1DowoVSzFXrl_8-nHcvPYbJu
Reduce Your Chance of Failure in DP-300 Exam: https://www.pass4sures.top/Microsoft-Azure/DP-300-testking-braindumps.html