70-465 Exam - Designing Database Solutions for Microsoft SQL Server 2012

certleader.com

Q1. 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 full recovery model, full backups, and differential backups 

C. The full recovery model, full backups, and transaction log backups 

D. The bulk-logged recovery mode and full backups 

Answer: D

Q2. You need to recommend a feature to support your backup solution. 

What should you include in the recommendation? 

A. Column-level encryption 

B. A Secure Sockets Layer (SSL) 

C. An NTFS file permission 

D. Transparent Data Encryption (TDE) 

Answer: D

Q3. You have a SQL Server 2012 environment that contains 20 servers. 

The corporate security policy states that all SQL Server 2012 instances must meet specific security standards. 

You need to recommend a management strategy for the SQL Server 2012 servers. 

What should you include in the recommendation? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Multi server jobs 

B. Maintenance plans 

C. Policy-Based Management 

D. Common criteria compliance 

Answer: C

Q4. You need to recommend a feature to support your backup solution. 

What should you include in the recommendation? 

A. Column-level encryption 

B. Transparent Data Encryption (TDE) 

C. An NTFS file permission 

D. A Secure Sockets Layer (SSL) 

Answer: B

Q5. You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements. 

What should you recommend? 

A. Create stored procedures that use EXECUTE AS clauses. 

B. Create a user-defined database role and add users to the role. 

C. Create functions that use EXECUTE AS clauses. 

D. Create a Policy-Based Management Policy. 

Answer: A

Topic 2, 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 2012. 

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: 

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.Prod and Sales.Proc2 execute. 

Q6. You are designing a monitoring application for a new SQL Server 2012 instance. 

You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance. 

What should you include in the recommendation? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. The sys.dm_os_wait_stats dynamic management view 

B. The DBCC SQLPERF(WAITSTATS) command 

C. The SQL Server error log 

D. SQL Server Profiler 

Answer: A

Q7. 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 cursor 

B. a subquery 

C. a table variable 

D. a common table expression 

Answer: C

Q8. 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. 

A. SQL Server 2012 Standard 

B. SQL Azure 

C. SQL Server 2012 Express with Advanced Services 

D. SQL Server 2012 Enterprise 

Answer: B

Q9. DRAG DROP 

You plan to deploy SQL Server 2012. 

You must create two tables named Table 1 and Table 2 that will have the following specifications: -Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. -Table2 will contain a column named Column2 that is the product of two other columns in Table2. Both Table1 and Table2 will contain more than 1 million rows. 

You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. 

Which options should you recommend? 

To answer, drag the appropriate options to the correct column in the answer area. 

Answer:  

Q10. DRAG DROP 

You need to design a stored procedure to update rows. 

The stored procedure must meet the following requirements: 

-If more than one row is updated, an error must be raised to the application and the update must be discarded. -The stored procedure must be designed to maximize concurrency. 

What should you include in the design? 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 

Answer: