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

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

Question No.21

You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

image

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

image

Which Transact-SQL query should you use?

  1. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW

  2. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS

  3. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO

  4. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS

  5. SELECT Name, Country, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO

  6. SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS

  7. SELECT Name AS `@Name#39;, Country AS `@Country#39;, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH (`Customers#39;)

  8. SELECT Name AS `Customers/Name#39;, CountryAS `Customers/Country#39;, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers#39;)

Correct Answer: G

Question No.22

You administer a Microsoft SQL Server 2014 database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.

Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.

You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.

Which type of solution should you use?

  1. Partitioned tables

  2. Online index rebuild

  3. Change data capture

  4. Change tracking

Correct Answer: A

Explanation:

Partitioning large tables or indexes can have manageability and performance benefits including: You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and- indexes

Question No.23

You administer a Microsoft SQL Server 2014 database.

You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.

You need to ensure that the job step can run using a different user account. What should you use?

  1. a schedule

  2. an alert

  3. an operator

  4. a proxy

Correct Answer: D

Explanation:

A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

References:

https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

Question No.24

You administer a Microsoft SQL Server 2014 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.

You need to reduce the I/O workload on the SAN at a minimal cost. What should you do?

  1. Move user databases to a local disk.

  2. Expand the tempdb data and log files

  3. Modify application code to use table variables

  4. Move the tempdb files to a local disk

Correct Answer: D

Explanation:

The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks.

References:

https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

Question No.25

You use Microsoft SQL Server 2014 to develop a database application. You need to create an object that meets the following requirements:

Which object should you use?

  1. Scalar-valued function

  2. Inline function

  3. User-defined data type

  4. Stored procedure

Correct Answer: D

Explanation:

Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures- database-engine

Question No.26

You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

You need to ensure that the backup size is as small as possible. Which backup should you perform every two hours?

  1. NORECOVERY

  2. FULL

  3. NO_CHECKSUM

  4. CHECKSUM

  5. Differential

  6. BULK_LOGGED

  7. STANDBY

  8. RESTART

  9. SKIP

  10. Transaction log

  11. DBO ONLY

  12. COPY_ONLY

  13. SIMPLE

  14. CONTINUE AFTER ERROR

Correct Answer: J

Explanation:

Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/transaction-log- backups-sql-server

Question No.27

You administer a Microsoft SQL Server 2014 instance.

You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

  1. Disable FILESTREAM on the Database.

  2. Enable FILESTREAM on the Server Instance.

  3. Configure the Database for Partial Containment.

  4. Create a non-empty FILESTREAM file group.

  5. Enable Contained Databases on the Server Instance.

  6. Set the FILESTREAM directory name on the Database.

Correct Answer: BDF

Explanation:

B: FileTables extend the capabilities of the FILESTREAM feature of SQL Server. Therefore you have to enable FILESTREAM for file I/O access at the Windows level and on the instance of SQL Server before you can create and use FileTables.

D: Before you can create FileTables in a database, the database must have a FILESTREAM filegroup.

F: Specifying a Directory for FileTables at the Database Level When you enable non- transactional access to files at the database level, you can optionally provide a directory name at the same time by using the DIRECTORY_NAME option. If you do not provide a directory name when you enable non-transactional access, then you have to provide it later before you can create FileTables in the database.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for- filetable

Question No.28

You administer two Microsoft SQL Server 2014 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services.

You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.

The distribution agent on ProdSrv2 regularly fails, displaying the following error message: quot;Cannot access the file. Operating system error code 5 (Access is denied.).quot;

You need to configure the distribution agent by granting only the minimum required access to all accounts.

What should you do?

  1. Configure the Subscriber to use the Local System account.

  2. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.

  3. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.

  4. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.

Correct Answer: D

Explanation:

Confirm that distribution agent has read privileges, full control access is not required, to the folder in question.

References:

http://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5- access-is-denied

Question No.29

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?

  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: 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

Question No.30

You use a contained database named ContosoDb within a domain.

You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

Which type of user should you create?

  1. SQL user without login

  2. User mapped to an asymmetric key

  3. Domain user

  4. login mapped to a virtual account

Correct Answer: C

Explanation:

If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account.

References:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows- service-accounts-and-permissions

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>