[Free] 2019(Nov) EnsurePass Microsoft 70-764 Dumps with VCE and PDF 1-10

Get Full Version of the Exam
http://www.EnsurePass.com/70-764.html

Question No.1

You administer a Microsoft SQL Server 2016 database named Orders.

Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.

You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.

What should you do?

  1. Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.

  2. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.

  3. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.

  4. Use System Monitor to catch the Lock Waits/sec event.

Correct Answer: B

Question No.2

You administer several Microsoft SQL Server 2016 database servers.

Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types.

Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

  1. Set the Merge agent on the problem subscribers to use the slow link agent profile.

  2. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.

  3. Change the Merge agent on the problem subscribers to run continuously.

  4. Set the Remote Connection Timeout on the Publisher to 0.

Correct Answer: A

Explanation:

You might have different profiles for different instances of an agent. For example, a Merge Agent that connects to the Publisher and Distributor over a dialup connection could use a set of parameters that are better suited to the slower communications link by using the slow link profile.

Note:

When replication is configured, a set of agent profiles is installed on the Distributor. An agent profile contains a set of parameters that are used each time an agent runs: each agent logs in to the Distributor during its startup process and queries for the parameters in its profile.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-agent- profiles

Question No.3

You administer a Microsoft SQL Server 2016 instance that has several SQL Server Agent jobs configured.

SQL Server Agent jobs fail, the error messages returned by the job steps are truncated. The following error message is an example of the truncated error message:

quot;Executed as user CONTOSO\ServiceAccount. …0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript… The package execution fa… The step failed.quot;

You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.

What should you do?

  1. Expand agent logging to include information from all events.

  2. Disable the Limit size of job history log feature.

  3. Configure event forwarding.

  4. Configure output files.

Correct Answer: D

Explanation:

When you have a multiple-step job, then log all steps against a single file. Check the #39;Append output to existing file#39; checkbox for all steps in the job that execute after the initial step. This results in a log file with all of the job steps from the last job execution. Each time the first step executes (each time the job is kicked-off) the file will be overwritten, so we have a record of the last set of output.

References:

https://www.mssqltips.com/sqlservertip/1411/verbose-sql-server-agent-logging/

Question No.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 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.

A company has a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.

You need to ensure that you can perform auditing at the database level for DB1.

Solution: You migrate DB1 to a named instance on a server than runs Microsoft SQL Server 2016 Standard edition.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit- database-engine

Question No.5

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.

You have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted.

You need to grant the correct permission to the service account that backs up the HRDB database.

Which permission should you grant?

  1. DDLAdmin

  2. db_datawriter

  3. dbcreator

  4. dbo

  5. View Database State

  6. View Server State

  7. View Definition

  8. sysadmin

Correct Answer: G

Explanation:

Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption

Question No.6

You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails. You add two Windows groups as logins for the server:

image

image

CORP\Employees – All company employees CORP\HRAdmins – HR administrators only

image

HR Administrators are also company employees.

You need to grant users access according to the following requirements:

image

CORP\Employees should have SELECT access to the Employees table.

image

image

Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table. Logins are based only on Windows security groups.

What should you do?

  1. A.Create a database role called Employees.Add CORP\Employees to the db_datareader role.Add all company employees except HR administrators to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.

  2. Create a database role called HRAdmins.Add all company employees except HR administrators to the db_datareader role, Add all HR administrators to the HRAdmins role.Grant SELECT access to the SalaryDetails table to the HRAdmins role.Deny SELECT access to the SalaryDetails table to the db_datareader role.

  3. Create two database roles: Employees and HRAdmins.Add all company employees to the Employees role.Add HR administrators to the HRAdmins role.Grant SELECT access to all tables except SalaryDetails to the Employees role.Grant SELECT access to the SalaryDetails table to the HRAdmins role. Deny SELECT access to the SalaryDetails table to the Employees role.

  4. Create a database role called Employees.Add all HR administrators to the db_datareader role.Add all company employees to the Employees role.Grant SELECT access to all tables except the SalaryDetails table to the Employees role.Deny SELECT access to the SalaryDetails table to the Employees role.

Correct Answer: D

Question No.7

You have a database that stores information for a shipping company. You create a table named Customers by running the following Transact-SQL statement. (Line numbers are included for reference only.)

image

You need to ensure that salespeople can view data only for the customers that are assigned to them.

Which Transact-SQL segment should you insert at line 07?

  1. RETURNS varchar(20)WITH Schemabinding

  2. RETURNS dbo.CustomersORDER BY @salesPerson

  3. RETURNS tableORDER BY @salesPerson

  4. RETURNS tableWITH Schemabinding

Correct Answer: D

Explanation:

The return value can either be a scalar (single) value or a table.

SELECT 1 just selects a 1 for every row, of course. What it#39;s used for in this case is testing whether any rows exist that match the criteria: if a row exists that matches the WHERE clause, then it returns 1, otherwise it returns nothing.

Specify the WITH SCHEMABINDING clause when you are creating the function. This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

Question No.8

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You manage a Microsoft SQL Server environment. You implement Transparent Data Encryption (TDE).

A user will assist in managing TDE.

You need to ensure that the user can view the TDE metadata while following the principle of lease privilege.

Which permission should you grant?

  1. DDLAdmin

  2. db_datawriter

  3. dbcreator

  4. dbo

  5. View Database State

  6. View Server State

  7. View Definition

  8. sysadmin

Correct Answer: G

Explanation:

Viewing the metadata involved with TDE requires the VIEW DEFINITION permissionon the certificate.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data- encryption-tde

Question No.9

DRAG DROP

You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID column. New rows are inserted into the table every day.

Performance of queries that filter the Product ID column have degraded significantly.

You need to improve the performance of the queries. 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.

image

Correct Answer:

image

Question No.10

You administer a Microsoft SQL Server 2016 default instance. The instance is hosted by a server that has a local firewall configured.

The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server.

You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections.

What should you do?

  1. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.

  2. Execute the following Transact-SQL command: sp_configure #39;remote admin connections#39;,

  3. Execute the Reconfigure command.

  4. Execute the following Transact-SQL command: sp_configure #39;remote access#39;, 1

  5. Restart the SQL Server Agent Service.

  6. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.

Correct Answer: ABC

Explanation:

SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434

The following example enables the DAC from a remote computer. sp_configure #39;remote admin connections#39;, 1;

GO RECONFIGURE; GO

References:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/remote-admin- connections-server-configuration-option

Get Full Version of the Exam
70-764 Dumps
70-764 VCE and PDF

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.