Mobile Menu

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

by admin, November 6, 2019

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

Question No.191

You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly available. After the event, the database will be deleted. You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  1. Max Degree of Parallelism

  2. Resource Governor

  3. Windows System Resource Manager (WSRM)

  4. Processor affinity

Correct Answer: D

Question No.192

You administer a Microsoft SQL Server 2016 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:

image

No more than 5 minutes worth of transactions are lost.

image

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

Explanation:

If there are only three options, the CDE (exclude differential backup), is the best answer.

Question No.193

You are the lead database administrator (DBA) of a Microsoft SQL Server 2016 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group.

You grant DOMAIN\JrDBAs access to the SQL Server.

You need to create a server role named SpecialDBARole that can perform the following functions:

image

image

View all databases. View the server state.

image

Assign GRANT, DENY, and REVOKE permissions on logins. You need to add DOMAIN\JrDBAs to the server role.

You also need to provide the least level of privileges necessary.

Which SQL statement or statements should you use? Choose all that apply.

  1. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;

  2. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];

  3. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;

  4. GRANT VIEW DEFINITION TO [SpecialDBARole];

  5. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;

  6. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];

Correct Answer: BCF

Question No.194

You plan to create a database.

The database will be used by a Microsoft .NET application for a special event that will last for two days.

During the event, data must be highly available. After the event, the database will be deleted.

You need to recommend a solution to implement the database while minimizing costs.

The solution must not affect any existing applications.

What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  1. SQL Server 2014 Enterprise

  2. SQL Server 2014 Standard

  3. SQL Azure

  4. SQL Server 2014 Express with Advanced Services

Correct Answer: B

Explanation:

Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL) supported by Standard and Enterprise editions only. References: Features Supported by the Editions of SQL Server 2014.

Question No.195

Overview

Application Overview

Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.

Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.

The new version will use SQL Server 2014.

The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily.

You receive several support calls reporting unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.

Tables

The current database schema contains a table named OrderDetails.

The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order.

The product price is stored in a table named Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.

A column named ProductName was created by using the varchar data type. The database contains a table named Orders.

Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.

The previous version of the ERP application relied on table-level security.

Stored Procedures

The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

image

Customer Problems Installation Issues

The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.

Index Fragmentation Issues

Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the clustered index key:

image

Backup Issues

Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

Search Issues

Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

Missing Data Issues

Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

Query Performance Issues

Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database administrators receive many supports call from

users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.

Design Requirements

File Storage Requirements

The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

Data Recovery Requirements

If the import process fails, the database must be returned to its prior state immediately.

Security Requirements

You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

Concurrency Requirements

You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute. What should you recommend for the updates to Sales.TransactionHistory?

  1. a REPEATABLE READ isolation level

  2. implicit transactions

  3. query hints

  4. a SNAPSHOT isolation level

Correct Answer: A

Question No.196

You administer a Microsoft SQL Server 2016 database named Contoso that contains a single user-defined database role namedBillingUsers.

All objects in Contoso are in the dbo schema.

You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?

  1. EXEC sp_addrolemember#39;db_procexecutor#39;, #39;BillingUsers#39;

  2. CREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller

  3. GRANT EXECUTE ON Schema::dbo TO BillingUsers

  4. GRANT EXECUTE ON Contoso::dbo TOBillingUsers

Correct Answer: D

Question No.197

You administer a Microsoft SQL Server 2016 database.

You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

  1. Execute sp_configure #39;max log size#39;, 2G.

  2. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.

  3. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.

  4. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

Correct Answer: D

Question No.198

You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database.

What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  1. One-way encryption

  2. B.Transparent Data Encryption (TDE)

  3. Encrypting File System (EFS)

  4. Reversible encryption

Correct Answer: B

Explanation:

Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts an entire database using that symmetric key called the database encryption key. The database encryption key is protected by other keys or certificates which are protected either by the database master key or by an asymmetric key stored in an EKM module.

SQL Server provides the following mechanisms for encryption:

image

image

image

image

Transact-SQL functions Asymmetric keys Symmetric keys Certificates

image

Transparent Data Encryption

Question No.199

You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization#39;s SQL Server 2016 deployment. Which of the following tools would you use to accomplish this goal?

  1. SQLIO

  2. SQLIOSim

  3. SQLIOStress

  4. chkdsk

Correct Answer: B

Explanation:

The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server.

References:

https://support.microsoft.com/en-us/help/231619/how-to-use-the-sqliosim-utility-to-simulate-sql- server-activity-on-a-d

Question No.200

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.

The Sales database is configured as shown in the following table.

image

You discover that all files except Sales_2.ndf are corrupt.

You need to recover the corrupted data in the minimum amount of time. What should you do?

  1. Perform a restore from a full backup.

  2. Perform a transaction log restore.

  3. Perform a file restore.

  4. Perform a filegroup restore.

Correct Answer: A

Explanation:

http://technet.microsoft.com/en-us/library/ms187048.aspx

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

Categories