High value 70-765 Braindumps 2021

NEW QUESTION 1
You administer two Microsoft SQL Server 2014 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers. What should you do?

• A. Configure the SQL Server service account to use Network Service.
• B. Use a server certificate.
• C. Use a database certificate.
• D. Configure the SQL Server service account to use Local System.

Answer: B

Explanation: To enable certificate authentication for database mirroring on a given server instance, the system administrator must configure each server instance to use certificates on both outbound and inbound connections.
References:
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirrorin

NEW QUESTION 2
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine. You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?

• A. Manually specify the column master key.
• B. Modify the connection string for applications.
• C. Auto-generate a column master key.
• D. Use theWindows certificate store.

Answer: D

Explanation: Always Encrypted supports multiple key stores for storing Always Encrypted column master keys. A column master key can be a certificate stored in Windows Certificate Store.
References:https://msdn.microsoft.com/en-us/library/mt723359.aspx

NEW QUESTION 3
You are tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores.
The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.
Several reports use the PIVOT statement and access more than 100 million rows in table1. You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the
tempdb database.
You need to prevent the PAGELATCH_IO waits from occurring.
Solution: You rewrite the queries to use aggregates instead of PIVOT statements. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Instead you can add more files to the database.
References: https://www.mssqltips.com/sqlservertip/3088/Explanation:-of-sql-server-io-and-latches/

NEW QUESTION 4
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the QUERY_OPTIMIZER_HOTFIXES option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY } enables or disables query optimization hotfixes regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 5
You plan to install Microsoft SQL Server 2014 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?

• A. Express Edition with Tools
• B. Standard Edition
• C. Web Edition
• D. Express Edition with Advanced Services

Answer: B

NEW QUESTION 6
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You suspect that the current SQL Server indexes cause queries to execute slowly.
You need to identify which indexes must be created to reduce the query execution time.
Which three 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_db_index_physical_stats
• B. sys.dm_db_missing_index_group_stats
• C. sys.indexes
• D. sys.dm_db_index_usage_stats
• E. sys.dm_db_missing_index_groups
• F. sys.dm_db_index_operational_stats
• G. sys.dm_db_missing_index_details
• H. sys.sysindexkeys

Answer: BEG

Explanation: The missing indexes feature consists of the following components:
A set of dynamic management objects that can be queried to return information about missing indexes.
The Missing Indexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.
Dynamic Management Objects
After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.
sys.dm_db_missing_index_group_stats
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers ofall missing indexes that are contained in that group.
sys.dm_db_missing_index_details
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns
Returns information about the database table columns that are missing an index. References: https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx

NEW QUESTION 7
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server
role in Server01.
What should you create?

• A. A Database Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: G

Explanation: The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events.
Audits can have the following categories of actions:
Server-level. These actions include server operations, such as management changes, such as in this question, and logon and logoff operations.
Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.
Audit-level. These actions include actions in the auditing process.
References:
http://technet.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

NEW QUESTION 8
You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSOSQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

• A. Restart the SQL Server Agent Service.
• B. Disable snapshot isolation.
• C. Restart the SQL Server Service.
• D. Add the CONTOSOSQLService account to the Perform Volume Maintenance Tasks local security policy.
• E. Add the CONTOSOSQLService account to the Server Operators fixed server role.
• F. Enable snapshot isolation.

Answer: CD

Explanation: How To Enable Instant File Initialization References:
http://msdn.microsoft.com/en-us/library/ms175935.aspx

NEW QUESTION 9
You are a database developer for an application hosted on a Microsoft SQL Server 2014 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC

Answer: A

Explanation: Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount). Finally a WHERE close is needed: WHERE o.Rnk = 1

NEW QUESTION 10
You administer a Microsoft SQL Server 2014 environment. One of the SQL Server 2014 instances contains a database named Sales.
You plan to migrate Sales to Windows Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set database containment to AZURE.
• B. Enable server property contained database authentication.
• C. Disable server property cross db ownership chaining.
• D. Set database containment to PARTIAL.
• E. Disable server property contained database authentication.
• F. database containment to FULL.

Answer: BD

Explanation: A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
B: In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database.
SQL Database and SQL Data Warehouse support Azure Active Directory identities as contained database users.
D: The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

NEW QUESTION 11
HOTSPOT
You need to resolve the identified issues.
Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.

Answer:

Explanation: From exhibit we see:
Cost Threshold of Parallelism: 5 Optimize for Ad Hoc Workloads: false
Max Degree of Parallelism: 0 (This is the default setting, which enables the server to determine the maximum degree of parallelism. It is fine.)
Locks: 0
Query Wait: -1
Box 1: Optimize for Ad Hoc Workload
Change the Optimize for Ad Hoc Workload setting from false to 1/True.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

NEW QUESTION 12
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account.
You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes.
Solution: You run the Get-Counter cmdlet and specify the –counter ‘physicaldisk:disk write/sec’ parameter. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 13
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You create a SQL Server Agent job by using the following statement.

You need to send an email message if the job fails. Which stored procedure should you use?

• A. msd
• B. db
• C. sp_updace_alerc
• D. msdb.dbo.sp_add_jobstep
• E. msdb.dbo.sp_add_notification
• F. msdb.dbo.sp_help_alert

Answer: C

Explanation: To notify an operator of job status through Transact-SQL.
In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query.
-- adds an e-mail notification for the specified alert (Test Alert).
-- This example assumes that Test Alert already exists
-- and that François Ajenstat is a valid operator name. USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N'Test Alert',
@operator_name = N'François Ajenstat',
@notification_method = 1 ; GO
References:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-2021

NEW QUESTION 14
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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space.
The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has one container. You create multiple VHDs in the container. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References: https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/

NEW QUESTION 15
You administer a SQL Server 2014 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?

• A. A domain user
• B. The BUILTINSYSTEM account
• C. A local user with Run as Service permissions
• D. The SQLBrowser account

Answer: A

Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-s

NEW QUESTION 16
You have a database named DB1 that contains a table named Table1. Table1 has 1 billion rows.
You import 10 million rows of data into Table1.After the import, users report that queries take longer than
usual to execute.
You need to identify whether an out-of-date execution plan is causing the performance issue. Which dynamic management view should you use?

• A. sys.dm_xtp_transaction_stats
• B. sys.dm_exec_input_buffer
• C. sys.dm_db_index_operational_stats
• D. sys.dm_db_stats_properties

Answer: C

Explanation: sys.dm_db_index_operational_stats dynamic management function provides us the current low-level I/O, locking, latching, and access method for each partition of the table. This information is really useful to troubleshoot SQL Server performance issues.
Reference:
https://basitaalishan.com/2013/03/19/using-sys-dm_db_index_operational_stats-to-analyse-howindexes-are-utili

NEW QUESTION 17
You have an on-premises server that runs Windows Server 2012 R2. The server has a Microsoft SQL Server 2021 instance that has one user database. The database is 2 TB.
Your company has a Win32 application installed on 1,000 computers. The application connects to the database by using a network name of server1.contoso.local.
You need to migrate the database to SQL Server 2021 on a Microsoft Azure virtual machine that runs Windows Server 2021. The solution must minimize outages to the application.
What should you do?

• A. Copy the database files and update the records in DNS.
• B. Implement an availability group and update the records in DNS.
• C. Implement database mirroring and update the records in DNS.
• D. Implement database mirroring and change the connection string.

Answer: B

Explanation: SQL Server high availability and disaster recovery (HADR) technologies that are supported in Azure include: References: