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

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

Question No.121

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.

You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

  1. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.

  2. Modify the stored procedures to update tables in the same order for all of the stored procedures.

  3. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.

  4. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.

Correct Answer: B

Explanation:

Concurrency Requirements

You must reduce the likelihood of deadlocks occurring when Sales.Proc1 and Sales.Proc2 execute.

Question No.122

You are a database administrator for a Microsoft SQL Server 2016 instance.

You need to ensure that data can be replicated from a production server to two reporting servers in real time.

You also need to ensure that data on the reporting server is always accessible. Which solution should you use?

  1. Availability Groups

  2. Extended Events

  3. Snapshot Replication

  4. Policy Based Management

Correct Answer: A

Question No.123

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

You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

What should you create?

  1. an Extended Event session

  2. a Policy

  3. a Database Audit Specification

  4. an Alert

  5. a Server Audit Specification

  6. a SQL Profiler Trace

  7. a Resource Pool

Correct Answer: F

Explanation:

http://msdn.microsoft.com/en-us/library/ms188246.aspx

Question No.124

You want to reproduce the same SQL Server 2016 installation configuration across five servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal?

  1. Configuration.xml

  2. Setup.ini

  3. Setup.xml

  4. ConfigurationFile.ini

Correct Answer: D

Question No.125

You are designing a database named DB1.

Changes will be deployed to DB1 every Wednesday night.

You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:

image

The strategy must not disrupt backup operations.

image

image

DB1 must be unavailable to users while the changes are deployed. You must be able to undo quickly the entire operation.

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

  1. Perform a copy-only database backup before the changes are deployed.If the deployment fails, restore the database to another server and recover the original.Objects from the restored database.

  2. Create a database snapshot.If the deployment fails, recover the objects from the database snapshot.

  3. Create a database snapshot.If the deployment fails, revert the database to the database snapshot.

  4. Perform a full database backup before the changes are deployed.If the deployment fails, restore the database to another server and recover the original objects from the restored database.

Correct Answer: C

Question No.126

Overview

You are a database administrator for a company named Litware, Inc.

Litware is a book publishing house. Litware has a main office and a branch office.

You are designing the database infrastructure to support a new web-based application that is being developed.

The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.

You have an existing desktop application that uses a SQL Server 2008 database named App1_DB.

App1_DB will remain in production.

Requirements Planned Changes

You plan to deploy a SQL Server 2014 instance that will contain two databases named Database1 and Database2.

All database files will be stored in a highly available SAN.

Database1 will contain two tables named Orders and OrderDetails.

The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes.

The rows returned from the first query must be returned on the second query unchanged along with any rows added to the table between the two read operations.

Database1 will contain several queries that access data in the Database2 tables. Database2 will contain a table named Inventory.

Inventory will contain over 100 GB of data.

The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index.

The column that is used as the primary key will use the identity property.

Database2 wilt contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

Business Requirements

You have the following requirements:

image

Costs for new licenses must be minimized.

image

image

Private information that is accessed by Application must be stored in a secure format. Development effort must be minimized whenever possible.

image

The storage requirements for databases must be minimized.

image

image

System administrators must be able to run real-time reports on disk usage. The databases must be available if the SQL Server service fails.

image

Database administrators must receive a detailed report that contains allocation errors and data corruption.

image

Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

You must encrypt the backup files to meet regulatory compliance requirements.

The encryption strategy must minimize changes to the databases and to the applications.

You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the

business requirements. What should you include in the recommendation?

  1. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.

  2. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.

  3. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.

  4. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.

Correct Answer: C

Explanation:

Scenario: The databases must be available if the SQL Server service fails.

Question No.127

DRAG DROP

You are designing an authentication strategy for a new server that has SQL Server 2014 installed. The strategy must meet the following business requirements:

image

The account used to generate reports must be allowed to make a connection during certain hours only.

image

Failed authentication requests must be logged.

You need to recommend a technology that meets each business requirement. The solution must minimize the amount of events that are logged.

Which technologies should you recommend?

To answer, drag the appropriate solution to the correct business requirement in the answer area.

image

Correct Answer:

image

Question No.128

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. You need to recommend a solution that addresses the backup issue.

The solution must minimize the amount of development effort. What should you include in the recommendation?

  1. Indexed views

  2. Filegroups

  3. Table partitioning

  4. Indexes

Correct Answer: B Explanation: Backup Issues

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

For very large databases (and by that, I mean, at least 500gb, but more like 5-10tb or more), it can become too expensive to regularly run a straight full backup.

So, where needed, you can choose to backup smaller pieces of the database by choosing to back up one of the files or file groups that make up a database.

Question No.129

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media.

You plan to synchronize SQLDev and SQLProd.

You need to recommend a solution that meets the following requirements:

image

image

image

The database schemas must be synchronized from SQLDev to SQLProd. The database on SQLDev must be deployed to SQLProd by using a package. The package must support being deployed to SQL Azure.

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

  1. A database snapshot

  2. A data-tier application

  3. Change data capture

  4. SQL Server Integration Services (SSIS)

Correct Answer: B

Explanation:

SIS supports connections to SQL Database by using the ADO.NET provider. OLEDB is not supported at this time. You can build the SSIS package connecting to SQL Database and create the data flow tasks the same way as you would against a typical on premise SQL Server. http://technet.microsoft.com/en-us/library/ee210546.aspx

Question No.130

Your company has offices in Seattle and Montreal.

The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. Server1 is located in the Seattle office. Server2 is located in the Montreal office. The latency of the WAN link between the Montreal office and the Seattle office is more than 200 ms.

You plan to implement an AlwaysOn availability group on both servers.

You need to recommend a failover type for the availability group. What should you recommend?

  1. Synchronous manual failover

  2. Synchronous automatic failover

  3. Asynchronous automatic failover

  4. Asynchronous manual failover

Correct Answer: D

Get Full Version of the Exam
70-764 Dumps
70-764 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>