[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 11-20

Get Full Version of the Exam

Question No.11

You are a database administrator for a Microsoft SQL Server 2014 environment.

You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.

You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.

Which feature should you use?

  1. Database Mirroring

  2. Peer-to-Peer Replication

  3. Log Shipping

  4. Availability Groups

Correct Answer: B


Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.


https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer- transactional-replication

Question No.12

You use Microsoft SQL Server 2014 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. What should you do?

  1. Reference a user-defined function within the computed column.

  2. Create a BEFORE trigger that maintains the state of the computed column.

  3. Add a default constraint to the computed column that implements hard-coded values.

  4. Add a default constraint to the computed column that implements hard-coded CASE statements.

Correct Answer: A


A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.


https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column-defined-with-a-user- defined-function-might-impact-query-performance/

Question No.13

You administer two instances of Microsoft SQL Server 2014. You deploy an application that uses a database on the named instance.

The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

  1. Configure the application as data-tiered.

  2. Open port 1433 on the Windows firewall on the server.

  3. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.

  4. Start the SQL Server Browser Service.

Correct Answer: D


The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL

Server instances installed on the computer.



Question No.14

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.

You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.

What should you create?

  1. An Alert

  2. A Resource Pool

  3. An Extended Event session

  4. A Server Audit Specification

  5. A SQL Profiler Trace

  6. A Database Audit Specification

  7. A Policy

  8. A Data Collector Set

Correct Answer: F


To audit users in a role use a Database Audit Specification.


https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification- transact-sql

Question No.15

You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.

You discover performance issues when large amounts of data are written to tables under heavy system load.

You need to limit the number of cores that handle I/O. What should you configure?

  1. Processor affinity

  2. Lightweight pooling

  3. Max worker threads

  4. I/O affinity

Correct Answer: D


The affinity Input-Output (I/O) mask Server Configuration Option. To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association

between a thread and a processor is called processor affinity.



Question No.16

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?

  1. A domain user

  2. The BUILTIN\SYSTEM account

  3. A local user with Run as Service permissions

  4. The SQLBrowser account

Correct Answer: A


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

Question No.17

You are a database developer of a Microsoft SQL Server 2014 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.


You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

  1. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);

  2. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);

  3. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);

  4. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID,


Correct Answer: D

Question No.18

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.

You need to prevent users from disabling server audits in Server01. What should you create?

  1. A Database Audit Specification

  2. A Policy

  3. An Alert

  4. A SQL Profiler Trace

  5. A Resource Pool

  6. An Extended Event session

  7. A Server Audit Specification

Correct Answer: B


Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and NetworkService are part of this policy. This setting can be configured by using the security policy snap-in (secpol.msc). Additionally, the Audit object access security policy must be enabled for both Success and Failure.


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

Question No.19

You administer a Microsoft SQL Server 2014 instance.

The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.

You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:

No more than 5 minutes worth of transactions are lost. Data can be recovered by using the minimum amount of administrative effort.

What should you do? Choose all that apply.

  1. Configure the database to use the SIMPLE recovery model.

  2. Create a DIFFERENTIAL database backup every 4 hours.

  3. Create a LOG backup every 5 minutes.

  4. Configure the database to use the FULL recovery model.

  5. Create a FULL database backup every 24 hours.

  6. Create a DIFFERENTIAL database backup every 24 hours.

Correct Answer: BCDE


The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.



Question No.20

You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)


You write the following Transact-SQL query:


You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.

You need to improve the performance of the query. What should you do?

  1. Use a FORCESCAN hint in the query.

  2. Add a clustered index on SalesOrderId in SalesOrderHeader.

  3. Use a FORCESEEK hint in the query.

  4. Update statistics on SalesOrderId on both tables.

Correct Answer: D


New statistics would be useful.

The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.



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