Flydumps has timely updated the Microsoft 70-465 exam questions. With all the new questions and answers, you will pass the Microsoft 70-465 exam easily. If you want to get more Microsoft 70-465 exam dumps, you can free download the new version VCE test engine from Flydumps. All Microsoft 70-465 dumps are new updated and cover all aspect of the examination.
Testlet 1
Litware, Inc
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. Databasel will also contain a stored procedure named usp_UpdateOrderDetails. 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 will contain 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 and 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 App1_Db1 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:
Costs for new licenses must be minimized.
Private information that is accessed by Application must be stored in a secure format.
Development effort must be minimized whenever possible.
The storage requirements for databases must be minimized.
System administrators must be able to run real-time reports on disk usage.
The databases must be available if the SQL Server service fails.
Database administrators must receive a detailed report that contains allocation errors and data corruption.
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.
QUESTION 1
During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. What should you recommend?
A. Partition the nonclustered index.
B. Partition the Inventory table.snapshot replication
C. Create a column store index.Master Data Services
D. Drop the clustered index.change data capture
Correct Answer: A Explanation
Explanation/Reference:
Scenario: 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.
QUESTION 2
You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.
What should you recommend?
A. Create a Policy-Based Management Policy.
B. Create a user-defined database role and add users to the role.
C. Create stored procedures that use EXECUTE AS clauses.
D. Create functions that use EXECUTE AS clauses.
Correct Answer: D Explanation
Explanation/Reference:
* EXECUTE AS Clause (Transact-SQL)
In SQL Server you can define the execution context of the following user-defined modules:
functions (except inline table-valued functions), procedures, queues, and triggers.
QUESTION 3
You need to recommend a feature to support your backup solution.
What should you include in the recommendation?
A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)
Correct Answer: A Explanation
Explanation/Reference:
*
Scenario: · You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
*
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.
QUESTION 4
You need to recommend a solution for Application1 that meets the security requirements.
What should you include in the recommendation?
A. Signed stored procedures
B. Certificate Authentication
C. Encrypted columns
D. Secure Socket Layer (SSL)
Correct Answer: A Explanation
Explanation/Reference:
* Scenario:
/ Data from Database2 will be accessed periodically by an external application named Application1
/ Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.
QUESTION 5
You need to recommend a disk monitoring solution that meets the business requirements.
What should you include in the recommendation?
A. An audit
B. A dynamic management view
C. A maintenance plan
D. A SQL Server Agent alert
Correct Answer: B Explanation
Explanation/Reference:
QUESTION 6
You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort. What should you include in the recommendation?
A. A table variable
B. A common table expression
C. A subquery
D. A cursor
Correct Answer: A Explanation
Explanation/Reference:
*Scenario: Database2 will contain a stored procedure named usp_UpdateInventory. Usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies.
*
A table variable can be very useful to store temporary data and return the data in the table format.
*
Example: The following example uses a self-join to find the products that are supplied by more than one vendor. Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement: USE AdventureWorks2008R2; GO SELECT DISTINCT pv1.ProductID, pv1.VendorID FROM Purchasing.ProductVendor pv1 INNER JOIN Purchasing.ProductVendor pv2 ON pv1.ProductID = pv2.ProductID AND pv1.VendorID <> pv2.VendorID ORDER BY pv1.ProductID
Incorrect:
Not B: Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple,
logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
QUESTION 7
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?
A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.
Correct Answer: C Explanation
Explanation/Reference:
Scenario: The databases must be available if the SQL Server service fails.
QUESTION 8
You need to recommend a solution to synchronize Database2 to App1_Db1.
What should you recommend?
A. Change data capture
B. Snapshot replication
C. Master Data Services
D. Transactional replication
Correct Answer: D Explanation
Explanation/Reference:
Scenario:
*
Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named App1_Db1 as soon as changes occur to the data in Database2.
*
All data in Database2 is recreated each day and does not change until the next data creation process.
QUESTION 9
You need to recommend a database reporting solution that meets the business requirements.
What should you include in the recommendation?
A. Data collection
B. Performance Monitor
C. A maintenance plan
D. A dynamic management view
Correct Answer: A Explanation
Explanation/Reference:
*
Scenario: System administrators must be able to run real-time reports on disk usage.
*
The data collector provides an historical report for each of the System Data collection sets. Each of the following reports use data that is stored in the management data warehouse: Disk Usage Summary Query Statistics History Server Activity History You can use these reports to obtain information for monitoring system capacity and troubleshooting system performance.
QUESTION 10
You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should recommend?
A. Read committed
B. Repeatable read
C. Read uncommitted
D. Serializable
Correct Answer: B Explanation
Explanation/Reference:
*
Scenario: Databasel will also contain a stored procedure named usp_UpdateOrderDetails. 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.
*
REPEATABLE READ Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Testlet 1
Contoso Ltd 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_CPl_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.
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:
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:
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.Proc1 and Sales.Proc2 execute.
QUESTION 1
You need to recommend a solution that resolves the missing data issue. The solution must minimize the amount of development effort.
What should you recommend?
A. Denormalize the Products table.
B. Denormalize the OrderDetails table.
C. Normalize the OrderDetails table.
D. Normalize the Products table.
Correct Answer: D Explanation
Explanation/Reference:
* Scenario:
/ 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.
/ 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.
QUESTION 2
You need to recommend a solution that addresses the index fragmentation and index width issue. What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
A. Change the data type of the lastModified column to smalldatetime.
B. Remove the lastModified column from the clustered index.
C. Change the data type of the modifiedBy column to tinyint.
D. Change the data type of the id column to bigint.
E. Remove the modifiedBy column from the clustered index.
F. Remove the id column from the clustered index.
Correct Answer: BE Explanation
Explanation/Reference:
Scenario: 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:
QUESTION 3
You need to recommend a solution that reduces the time it takes to import the supplier data. What should you include in the recommendation?
A. Enable instant file initialization.
B. Reorganize the indexes.
C. Disable Resource Governor.
D. Enable Auto Update Statistics.
Correct Answer: C Explanation Explanation/Reference:
* 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.
QUESTION 4
You need to recommend a solution that meets the data recovery requirement. What should you include in the recommendation?
A. A differential backup
B. A transaction log backup
C. Snapshot isolation
D. A database snapshot
Correct Answer: D Explanation Explanation/Reference: QUESTION 5
You need to recommend a solution that addresses the file storage requirements.
What should you include in the recommendation?
A. FileStream
B. FileTable
C. The varbinary data type
D. The image data type
Correct Answer: B Explanation
Explanation/Reference:
*
Scenario: 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.
*
FileTables remove a significant barrier to the use of SQL Server for the storage and management of unstructured data that is currently residing as files on file servers. Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.
QUESTION 6
You need to recommend a solution that addresses the installation issues.
What should you include in the recommendation?
A. Windows logins
B. Server roles
C. Contained users
D. Database roles
Correct Answer: C Explanation
Explanation/Reference:
*
Scenario: 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.
*
Creating contained users enables the user to connect directly to the contained database. This is a very significant feature in high availability and disaster recovery scenarios such as in an AlwaysOn solution. If the users are contained users, in case of failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This provides an immediate benefit.
QUESTION 7
You need to recommend a solution that addresses the security requirement.
What should you recommend?
A. Revoke user permissions on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.
B. Grant the users the SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
C. Deny the users SELECT permission on the tables. Create views that retrieve data from the tables. Grant the users the SELECT permission on the views.
D. Deny the users the SELECT permission on the tables. Create stored procedures that manipulate data. Grant the users the EXECUTE permission on the stored procedures.
Correct Answer: C Explanation
Explanation/Reference:
* Security Requirements
You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.
QUESTION 8
You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
A. The collation of the Products table
B. The index on the ProductName column
C. The collation of the ProductName column
D. The data type of the ProductName column
Correct Answer: C Explanation
Explanation/Reference:
QUESTION 9
You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?
A. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
B. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
C. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D. Modify the stored procedures to update tables in the same order for all of the stored procedures.
Correct Answer: D Explanation
Explanation/Reference:
* Concurrency Requirements
You must reduce the likelihood of deadlocks occurring when Sales.Proc1 and Sales.Proc2 execute.
QUESTION 10
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?
A. Indexed views
B. Filegroups
C. Table partitioning
D. Indexes
Correct Answer: B Explanation
Explanation/Reference:
*
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.
Testlet 1 Fabrikam, Inc
Background
Corporate Information
Fabrikam, Inc. is a retailer that sells electronics products on the Internet. The company has a headquarters site and one satellite sales office.
You have been hired as the database administrator, and the company wants you to change the architecture of the Fabrikam ecommerce site to optimize performance and reduce downtime while keeping capital expenditures to a minimum. To help with the solution, Fabrikam has decided to use cloud resources as well as on-premise servers.
Physical Locations
All of the corporate executives, product managers, and support staff are stationed at the headquarters office. Half of the sales force works at this location.
There is also a satellite sales office. The other half of the sales force works at the satellite office in order to have sales people closer to clients in that area. Only sales people work at the satellite location.
Problem Statement
To be successful, Fabrikam needs a website that is fast and has a high degree of system uptime. The current system operates on a single server and the company is not happy with the single point of failure this presents. The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers. During your investigation, you discover that the sales force reports are causing significant contention.
Configuration
Windows Logins
The network administrators have set up Windows groups to make it easier to manage security.
Users may belong to more than one group depending on their role.
The groups have been set up as shown in the following table:
Server Configuration
The IT department has configured two physical servers with Microsoft Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition and one Windows Azure Server. There are two tiers of storage available for use by database files only a fast tier and a slower tier. Currently the data and log files are stored on the fast tier of storage only. If a possible use case exists, management would like to utilize the slower tier storage for data files.
The servers are configured as shown in the following table:
Database
Currently all information is stored in a single database called ProdDB, created with the following script:
The Product table is in the Production schema owned by the ProductionStaff Windows group. It is the main table in the system so access to information in the Product table should be as fast as possible.
The columns in the Product table are defined as shown in the following table:
The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read.
The columns in the SalesOrderDetail table are defined as shown in the following table:
Database Issues
The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the current system, you found that there are no automated maintenance procedures. The database is severely fragmented, and everyone has read and write access.
Requirements
Database
The database should be configured to maximize uptime and to ensure that very little data is lost in the event of a server failure. To help with performance, the database needs to be modified so that it can support in-memory data, specifically for the Product table, which the CIO has indicated should be a memory-optimized table. The auto-update statistics option is set off on this database. Only product managers are allowed to add products or to make changes to the name, description, price, cost, and supplier. The changes are made in an internal database and pushed to the Product table in ProdDB during system maintenance time. Product managers and others working at the headquarters location also should be able to generate reports that include supplier and cost information.
Customer data access
Customers access the company’s website to order products, so they must be able to read product information such asname, description, and price from the Product table. When customers place orders, stored procedures calledby the website update product quantity-on-hand values. This means the product table is constantly updated at randomtimes.
Customer support data access
Customer support representatives need to be able to view and not update or change product information. Management does not want the customer support representatives to be able to see the product cost or any supplier information.
Sales force data access
Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times and there can be no reporting downtime to refresh the data set except during the monthly maintenance window. The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
Historical Data
The system should keep historical information about customers who access the site so that sales people can see how frequently customers log in and how long they stay on the site. The information should be stored in a table called Customer Access. Supporting this requirement should have minimal impact on production website performance.
Backups
The recovery strategy for Fabrikam needs to include the ability to do point in time restores and minimize the risk of data loss by performing transaction log backups every 15 minutes.
Database Maintenance
The company has defined a maintenance window every month when the server can be unavailable. Any maintenance functions that require exclusive access should be accomplished during that window.
Project milestones completed
Revoked all existing read and write access to the database, leaving the schema ownership in place.
Configured an Azure storage container secured with the storage account name MyStorageAccount with the primary access key StorageAccountKey on the cloud file server.
SQL Server 2014 has been configured on the satellite server and is ready for use.
On each database server, the fast storage has been assigned to drive letter F:, and the slow storage has been assigned to drive letter D:.
QUESTION 1
You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort.
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: A Explanation
Explanation/Reference:
* Scenario: / During your investigation, you discover that the sales force reports are causing significant contention. / Sales force data access Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times and there can be no reporting downtime to refresh the data set except during the monthly maintenance window. The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed should be ignored.
QUESTION 2
You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?
A. DBCC INDEXDEFRAG (ProdDB, SalesOrderDetail, SODIndex)
B. ALTER INDEX SODIndex ON SalesOrderDetail REORGANIZE
C. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD
D. ALTER INDEX SODIndex ON SalesOrderDetail REBUILD WITH (ONLINE * ON)
Correct Answer: D Explanation
Explanation/Reference:
*
Scenario: Database Issues
The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the
current system, you found that there are no automated maintenance procedures. The database is severely fragmented, and everyone has read and write access.
*
After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.
avg_fragmentation_in_percent value
/ > 5% and < = 30%
then use
ALTER INDEX REORGANIZE
/ > 30%
then use
ALTER INDEX REBUILD WITH (ONLINE = ON)
QUESTION 3
You need to implement a backup strategy to support the requirements.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a credential called MyCredential on SQL Server by using a Windows domain account and password.
B. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO DISK…
C. Create a share on your Windows Azure site by using your Windows Azure storage account information, and grant permission to the SQL Server service login.
D. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO URL …
WTTH CREDENTIAL=N’MyCredential’
E. Create a share on the hot standby site and grant permission to the SQL Server service login.
F. Create a credential called MyCredential on SQL Server, using MyStorageAccount for the storage account name and StorageAccountKey for the access key.
G. Schedule a full backup by using the command BACKUP DATABASE ProdDB TO SHARE … WITH CREDENTIAL=N’ MyCredential’
Correct Answer: CD Explanation
Explanation/Reference:
*
Scenario: The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers.
*
Need to store files in the cloud.
*
Manage your backups to Windows Azure: Using the same methods used to backup to DISK and TAPE, you can now back up to Windows Azure storage by Specifying URL as the backup destination. You can use this feature to manually backup or configure your own backup strategy like you would for a local storage or other off-site options. This feature is also referred to as SQL Server Backup to URL.
QUESTION 4
You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution. Choose three.
A. Use (SNAPSHOT] hints in the report queries
B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
C. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
E. Use (TABLOCK) hints in the report queries
F. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
G. ALTER DATABASE [ProdDB] SET ALLOW.SNAPSHOT ISOLATION ON
H. Use (SNAPSHOT] hints in the update statements
Correct Answer: ABF Explanation
Explanation/Reference:
*
Scenario:
The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group.
This table is constantly being updated, inserted into, and read.
*
Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet committed
should be ignored.
*
READ_COMMITTED_SNAPSHOT { ON | OFF }
ON
Enables Read-Committed Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction
uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a
transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.
OFF
Turns off Read-Committed Snapshot option at the database level. Transactions specifying the READ COMMITTED isolation level use locking.
QUESTION 5
You need to change the ProdDB database.
Which two database options should you change to meet the requirements? Each correct answer presents part of the solution. Choose two.
A. CONTAINS FILESTREAM
B. Change recovery model to FULL
C. CONTAINMENT = PARTIAL
D. Change recovery model to BULK_LOGGED
E. COLLATE IN.MEMORY
F. CONTAINS MEMORY OPTIMIZED DATA
Correct Answer: EF Explanation
Explanation/Reference:
* Scenario: To help with performance, the database needs to be modified so that it can support in-memory data, specifically for the Product table, which the CIO has indicated should be a memory-optimized table.
QUESTION 6
You need to configure security on the Product table for customer support representatives. Which two actions should you perform? Each correct answer presents
part of the solution.
Choose two.
A. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID.
B. GRANT ALL on CustProduct TO OurDomain\CustomerSupport
C. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.
D. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice.
E. GRANT SELECT on CustProduct TO OurDomain\CustomerSupport.
F. GRANT SELECT on CustProduct TO public.
Correct Answer: AE Explanation
Explanation/Reference:
Give access to CustomerSupport through a view. The view must include all these columns (refer to scenario).
QUESTION 7
You need to distribute functionality across the three servers.
Which function should you assign to each server? To answer, drag the appropriate functions to the correct servers. Each function may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Correct Answer:
Explanation
Explanation/Reference:
Note:
* Scenario: The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers.
QUESTION 8
The business requires a satellite office to have a local copy of the data to report against.
You want to implement a solution to support the requirements. You need to establish a new Availability Group between the two servers.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:
Correct Answer:
Explanation Explanation/Reference:
Note:
* The following table lists the basic tasks involved in creating and configuring an availability group and indicates which Transact-SQL statements to use for these tasks. The AlwaysOn Availability Groups tasks must be performed in the sequence in which they are presented in the table.
(step 2) Create database mirroring endpoint (once per SQL Server instance) CREATE ENDPOINT endpointName … FOR DATABASE_MIRRORING (step 3) Create availability group CREATE AVAILABILITY GROUP (step 4) Join secondary replica to availability group ALTER AVAILABILITY GROUP group_name JOIN (step 5-6) Prepare the secondary database BACKUP and RESTORE. Create backups on the server instance that hosts the primary replica. Restore backups on each server instance that hosts a secondary replica, using RESTORE WITH NORECOVERY. (step 7) Start data synchronization by joining each secondary database to availability group ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name
Reference: Create an Availability Group (Transact-SQL)
QUESTION 9
You need to create a job to automate some database maintenance tasks.
Which code fragment should you use in each location in the command to complete one of the commands you will need to include in the job? To answer, drag the appropriate lines of code to the correct locations in the command. Each line of code may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Correct Answer:
Explanation
Explanation/Reference:
Note:
UPDATE STATISTICS WITH FULLSCAN NORECOMPUTE
Update statistics by using FULLSCAN and NORECOMPUTE
The following example updates the Products statistics in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics for
the Products statistics.
USE AdventureWorks2012;
GO
UPDATE STATISTICS Production.Product(Products)
WITH FULLSCAN, NORECOMPUTE;
GO
QUESTION 10
You need to create the CustomerAccess table to support the reporting and performance requirements.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Select and Place:
Correct Answer: Explanation
Explanation/Reference:
Note:
*
Scenario: During your investigation, you discover that the sales force reports are causing significant contention.
*
Step 1: add filegroup
*
Step 2: add file
*
Step 3: create table on filegroup
/ CREATE TABLE ON { <partition_scheme> | filegroup | “default” }
Specifies the partition scheme or filegroup on which the table is stored.
Testlet 1
A.Datum Overview
General Overview
A. Datum Corporation has offices in Miami and Montreal.
The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has a 5-ms latency.
A. Datum standardizes its database platform by using SQL Server 2014 Standard edition.
Databases
Each office contains databases named Sales. Inventory, Customers, Products, Personnel, and Dev.
Servers and databases are managed by a team of database administrators. Currently, all of the database administrators have the same level of permissions on
all of the servers and all of the databases.
The Customers database contains two tables named Customers and Classifications. The following graphic shows the relevant portions of the tables:
The following table shows the current data in the Classifications table:
The Inventory database is used mainly for reports. The database is recreated every day. A full backup of the database currently takes three hours to complete.
Stored Procedures
A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and
Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from a table in the Products database and searches for information in Table1 based on input from the
Products table. After the process is complete, Table1 is deleted.
A stored procedure named USP_2 is used to generate a product list. USP_2 takes several minutes to run due to locks on the tables the procedure accesses.
A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction.
Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables from the
Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
A stored procedure named USP_5 changes data in multiple databases. Security checks are performed each time USP_5 accesses a database.
You suspect that the security checks are slowing down the performance of USP_5.
All stored procedures accessed by user applications call nested stored procedures. The nested stored procedures are never called directly.
Design Requirements Data Recovery
You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour. You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.
Classification Changes
You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future.
Management requests that historical data be maintained for the previous classifications.
Security
A group of junior database administrators must be able to view the server state of the SQL Server instance that hosts the Sales database. The junior database administrators will not have any other administrative rights.
A. Datum wants to track which users run each stored procedure.
Storage
A. Datum has limited storage. Whenever possible, all storage space should be minimized for all databases and all backups.
Error Handling
There is currently no error handling code in any stored procedure. You plan to log errors in called stored procedures and nested stored procedures. Nested stored procedures are never called directly.
QUESTION 1
You need to recommend a solution to minimize the amount of time it takes to execute USP_2. What should you recommend?
A. A database snapshot
B. A table variable
C. A temporary table
D. Snapshot isolation
Correct Answer: C Explanation Explanation/Reference:
Scenario: A stored procedure named USP_2 is used to generate a product list. USP_2 takes several minutes to run due to locks on the tables the procedure accesses.
QUESTION 2
You need to recommend a solution for the error handling of USP_3. The solution must minimize the amount of custom code required. What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use a TRY CATCH block in the called stored procedures.
C. Use the @@ERROR variable in the called stored procedures.
D. Use the RAISERROR command in the nested stored procedures.
Correct Answer: B Explanation Explanation/Reference:
*
Must catch and handle the error.
*
Scenario: A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
QUESTION 3
You need to recommend a solution to minimize the amount of time it takes to execute USP_1. With what should you recommend replacing Table1?
A. An indexed view
B. A function
C. A table variable
D. A temporary table
Correct Answer: D Explanation Explanation/Reference:
Scenario: A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from a table in the Products database and searches for information in Table1 based on input from the Products table. After the process is complete, Table1 is deleted.
QUESTION 4
You need to recommend a disaster recovery solution for the Dev database. What should you include in the recommendation?
A. The simple recovery model and full backups
B. The bulk-logged recovery model and full backups
C. The full recovery model, full backups, and differential backups
D. The full recovery model, full backups, and transaction log backups
Correct Answer: A Explanation Explanation/Reference:
*
Scenario: You must be able to recover data from the Dev database if data is lost accidentally.
You have a Recovery Point Objective (RPO) of one day.
*
The simple recovery model provides the simplest form of backup and restore. This recovery model supports both database backups and file backups, but does
not support log backups. Transaction log data is backed up only with the associated user data. The absence of log backups simplifies managing backup and
restore. . However, a database can be restored only to the end of the most recent backup.
Incorrect:
Not B: The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-
scale bulk operations, such as bulk imports of large amounts of data.
QUESTION 5
You need to recommend a disaster recovery strategy for the Inventory database. What should you include in the recommendation?
A. Log shipping
B. SQL Server Failover Clustering
C. AlwaysOn availability groups
D. Peer-to-peer replication
Correct Answer: A Explanation
Explanation/Reference:
*
Scenario:
/ You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour.
/ A. Datum Corporation has offices in Miami and Montreal.
*
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more
secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually.
QUESTION 6
You need to recommend a solution to meet the security requirements of the junior database administrators.
What should you include in the recommendation?
A. A server role
B. A database role
C. A credential
D. A shared login
Correct Answer: C Explanation
Explanation/Reference:
*
Scenario: A group of junior database administrators must be able to view the server state of the SQL Server instance that hosts the Sales database. The junior database administrators will not have any other administrative rights.
*
Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.
QUESTION 7
You need to recommend a solution to ensure that USP_4 adheres to the security requirements.
What should you include in the recommendation?
A. Enable SQL Server Audit.
B. Enable trace flags.
C. Configure data manipulation language (DML) triggers.
D. Enable C2 audit tracing.
Correct Answer: A Explanation
Explanation/Reference:
*
Scenario: A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
*
Beginning in SQL Server 2008 Enterprise, you can set up automatic auditing by using SQL Server Audit.
QUESTION 8
You need to recommend a solution to minimize the amount of time it takes to execute USP_5.
What should you include in the recommendation?
A. Enable cross-database chaining.
B. Use a server role to group all logins.
C. Use the EXECUTE AS clause in USP_5.
D. Copy USP.5 to each database.
Correct Answer: A Explanation
Explanation/Reference:
*
scenario: A stored procedure named USP_5 changes data in multiple databases. Security checks are performed each time USP_5 accesses a database.
*
Cross-database ownership chaining occurs when a procedure in one database depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining within a single database, except that an unbroken ownership chain requires that all the object owners are mapped to the same login account. If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
QUESTION 9
You need to recommend a solution for the planned changes to the customer classifications.
What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add a row to the Customers table each time a classification changes.
B. Add columns for each classification to the Customers table.
C. Add a table to track any changes made to the classification of each customer.
D. Add a column to the Classifications table to track the status of each classification.
E. Implement change data capture.
Correct Answer: CD Explanation
Explanation/Reference:
* scenario: You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future.
Incorrect:
not E: Change data capture provides information about DML changes on a table and a database. By using change data capture, you eliminate expensive
techniques such as user triggers, timestamp columns, and join queries.
QUESTION 10
You need to recommend a change to USP_3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?
A. Set the XACT_ABORT option to off
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.
Correct Answer: B Explanation Explanation/Reference:
*
Scenario: A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
*
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
QUESTION 11
You need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails. Which change should you recommend?
A. Set the XACT_ABORT option to off.
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.
Correct Answer: A Explanation Explanation/Reference:
*
Scenario: A stored procedure named USP_3 is used to update prices. USP_3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
*
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
QUESTION 12
You need to recommend a solution for the error handling of USP_4. The solution must handle errors for nested stored procedures in the code for USP_4. What should you recommend?
A. Use the @@ERROR variable in the nested stored procedures.
B. Use the @@ERROR variable in USP_4.
C. Use the RAISERROR command in the nested stored procedures.
D. Use the RAISERROR command in USP_4.
Correct Answer: C Explanation Explanation/Reference:
* A stored procedure named USP_4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables from the Sales, Customers, and Inventory databases. USP_4 uses an EXECUTE AS clause.
QUESTION 13
You need to recommend a solution to minimize the amount of time it takes to execute USP_1. With what should you recommend replacing Table1?
A. A view
B. A temporary table
C. A table variable
D. A function
Correct Answer: A Explanation Explanation/Reference:
* A stored procedure named USP_1 generates millions of rows of data for multiple reports. USP_1 combines data from five different tables from the Sales and Customers databases in a table named Table1.
Question Set 1 QUESTION 1
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.
Each order is tracked by using one of the following statuses:
Fulfilled
Shipped
Ordered
Received
You need to design the database to ensure that that you can retrieve the following information:
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.
Correct Answer: B Explanation
Explanation/Reference:
QUESTION 2
You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
A. Snapshot isolation
B. A database snapshot
C. Filegroups
D. Indexes
Correct Answer: A Explanation
Explanation/Reference:
Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.
QUESTION 3
You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy
Correct Answer: C Explanation
Explanation/Reference:
Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.
QUESTION 4
You use SQL Server 2014 to maintain the data used by applications at your company.
You want to execute two statements.
You need to guarantee that either both statements succeed, or both statements fail together as a batch.
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Correct Answer: D Explanation
Explanation/Reference:
Structure should be: BEGIN TRY BEGIN TRANSACTION .. COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH.
QUESTION 5
You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.
You need to recommend a solution to monitor the transactions that are running currently against SQLProd. The solution must minimize the amount of custom code required.
What should you recommend?
A. Statistics
B. A dynamic management view
C. A trigger
D. User-defined views
Correct Answer: B Explanation
Explanation/Reference:
Acquiring Microsoft 70-465 certifications are becoming a huge task in the field of I.T. More over these exams like Microsoft 70-465 exam are now continuously updating and accepting this challenge is itself a task.This Microsoft 70-465 practice test is an important part of Microsoft certifications and at Microsoft 70-465 Certification Flydumps we have the resources to prepare you for this.The Microsoft 70-465 exam is essential and core part of Microsoft 70-465 certifications and once you clear the exam you will be able to solve the real time problems yourself.
