Mar 2016 updated: Examcollection Microsoft 70-465 testing engine 1-19

Exam Code: 70-465 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Designing Database Solutions for Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-465 Exam.

2016 Mar 70-465 Study Guide Questions:

Q1. 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? 

A. Synchronous manual failover 

B. Synchronous automatic failover 

C. Asynchronous automatic failover 

D. Asynchronous manual failover 

Answer: D


Q2. You need to recommend a solution for Application1 that meets the security requirements. 

What should you include in the recommendation? 

A. Encrypted columns 

B. Certificate Authentication 

C. Secure Socket Layer (SSL) 

D. Signed stored procedures 

Answer: B


Q3. 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 subquery 

B. a cursor 

C. a common table expression 

D. a table variable 

Answer: C


Q4. DRAG DROP 

You are designing a database for a university. 

The database will contain two tables named Classes and Enrollment that have the following specifications: 

-Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths. 

-Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments. 

You need to identify which SQL Server technology meets the specifications of each table. 

Which technologies should you identify? 

To answer, drag the appropriate technology to the correct table in the answer area. 


Answer:



Q5. You need to recommend a solution to minimize the amount of time it takes to execute sp1. With what should you recommend replacing Table1? 

A. a temporary table 

B. a view 

C. a table variable 

D. a function 

Answer: B


Q6. You manage a SQL Server 2012 instance that contains a database named DB1. 

Users report that some queries to DB1 take longer than expected. Although most queries run in less than one second, some queries take up to 20 seconds to run. 

You need to view all of the performance statistics for each database file. Which method should you use? 

A. Examine the Data File I/O pane in Activity Monitor. 

B. Query the sys.dm_io_virtual_file_stats dynamic management function. 

C. Query the sys.dm_os_performance_counters dynamic management view. 

D. Query the sys.dm_os_tasks dynamic management view. 

Answer: B


70-465 exam prep

Latest 70-465 exam engine:

Q7. DRAG DROP 

You have two servers named SQL1 and SQL2 that have SQL Server 2008 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month. 

You need to upgrade the database to SQL Server 2012. The solution must minimize downtime. 

Which upgrade steps should you recommend? 

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


Answer: 



Q8. You need to recommend a disaster recovery strategy for the Inventory database. 

What should you include in the recommendation? 

A. Log shipping 

B. AlwaysOn availability groups 

C. Peer-to-peer replication 

D. SQL Server Failover Clustering 

Answer: A


Q9. 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. Implement change data capture. 

B. Add a table to track any changes made to the classification of each customer. 

C. Add columns for each classification to the Customers table. 

D. Add a row to the Customers table each time a classification changes. 

E. Add a column to the Classifications table to track the status of each classification. 

Answer: AC


Topic 4, Litware, Inc Case B 

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 2005 database named App1_DB. App1_Db will remain in production. 

Requirements 

Planned Changes 

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

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. Each query against the Orders table must return the same data. All statements in the stored procedure run in a single transaction. 

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 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 several small data sets of up to 100 rows each. Each data set will be used multiple times. 

Data from Database2 will be accessed periodically by an external application named Application!. 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 2012 backups. 

Business Requirements 

You have the following requirements: 

-Costs for new equipment must be minimized. 

-External access to the databases must be encrypted. 

-Development effort must be minimized whenever possible. 

-System administrators must be notified when disk space falls below a certain threshold. 

-The databases must be available if the SQL Server service or the storage solution 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. 


Q10. 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 maintenance plan 

C. a policy 

D. a SQL Server Agent alert 

Answer: D


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


Q12. DRAG DROP 

You plan to deploy three highly available SQL Server environments that will use SQL Server 2012. You identify the following specifications for each environment as shown following table. 


You need to recommend which high-availability feature is required for each environment. 

Which features should you identify? 

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


Answer:



70-465 real exam

Refined 70-465 dumps:

Q13. You need to recommend a solution to minimize the amount of time it takes to execute sp5. What should you include in the recommendation? 

A. Use the EXECUTE AS clause in sp5. 

B. Enable cross-database chaining. 

C. Use a server role to group all logins. 

D. Copy sp5 to each database. 

Answer: B


Q14. DRAG DROP 

You have a SQL Azure database named Database1. 

You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 will contain the following information for each row: 


The solution must minimize the amount of space used to store each row. 

Which data types should you recommend for each column? 

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


Answer: 



Q15. nbsp;Topic 1, Litware, Inc Case A 

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 2005 database named App1_DB. App1_DB will remain in production. 

Requirements 

Planned Changes 

You plan to deploy a SQL Server 2012 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. Database1 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 wilt 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 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 2012 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. 

You need to recommend an isolation level for usp_UpdateOrderDetails. Which isolation level should recommend? 

A. Repeatable read 

B. Read uncommitted 

C. Serializable 

D. Read committed 

Answer: A


Q16. You have two SQL Server 2012 instances named SQLDev and SQLProd. 

You plan to create a new database on SQLProd that will use SQL Server Authentication. 

You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev if they do not have a login on the SQLDev instance. 

What should you use? 

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

A. SQL Server Integration Services (SSIS) scripts 

B. Extended Events 

C. Triggers 

D. SQL Server Analysis Services (SSAS) scripts 

E. Contained database 

Answer: E


Q17. 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. Filegroups 

B. Table partitioning 

C. Indexed views 

D. Indexes 

Answer: A


Q18. DRAG DROP 

You plan to deploy a database by using SQL Server 2012. 

Your company identifies the following requirements for the database: 

-The name of all stored procedures must start with "usp_"always. 

-All distribution statistics must be updated daily. 

You need to identify which feature must be used to meet each database requirement. 

Which features should you identify? 

To answer, drag the appropriate feature to the correct database requirement in the answer area. 


Answer: 



Q19. You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2012 installed. 

You plan to deploy an additional server that has SQL Server 2012 installed. 

You need to design a high-availability solution for the databases that meets the following requirements: 

-If SQL1 fails, the databases must be available. 

-Users must be able to run reports against a secondary copy of the databases. 

What should you include in the design? 

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

A. Database mirroring 

B. Log shipping 

C. Failover Clustering 

D. AlwaysOn availability groups 

Answer: D