Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 431:

  DRAG DROP

  You plan to deploy SQL Server 2014. Your company identifies the following monitoring requirements:

  Tempdb must be monitored for insufficient free space.

  Deadlocks must be analyzed by using Deadlock graphs.

  You need to identify which feature meets each monitoring requirement.

  Which features should you identify? To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.

  Select and Place:

  

  

  ---

  You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space. Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. SQL Server Profiler and SQL Server Management Studio use a deadlock wait-for graph to describe a deadlock. The deadlock wait-for graph contains process nodes, resource nodes, and edges representing the relationships between the processes and the resources. References: Troubleshooting Insufficient Disk Space in tempdb

  References: Analyze Deadlocks with SQL Server Profiler

• Question 432:

  You administer a Microsoft SQL Server 2016 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day.

  The reporting database is only updated from the transactional database.

  The recovery model and backup schedule are configured as shown in the following table:

  

  At 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are corrupted on the reporting database.

  You need to ensure that the databases are restored.

  You also need to ensure that data loss is minimal. What should you do?

  A. Perform a partial restore.
  B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  C. Restore the latest full backup.
  D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  E. Perform a page restore.
  F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  G. Perform a point-in-time restore.
  H. Restore the latest full backup. Then, restore the latest differential backup.
  H. Restore the latest full backup. Then, restore the latest differential backup.

• Question 433:

  Overview

  General Overview

  ADatum 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 5-ms latency. A. Datum standardizes its database platform by using SQL Server 2014 Enterprise

  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 updated frequently.

  The database is often used for reporting.

  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 Table1 sequentially several times. After the process is complete, Table1 is deleted. A stored procedure named USP_2 is used to generate a product list. The product list contains the names of products

  grouped by category. USP_2 takes several minutes to run due to locks on the tables the procedure accesses. The locks are caused by USP_1 and USP_3. 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 fails. 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.

  All nested stored procedures handle errors by using structured exception handling. A stored procedure named USP_5 calls several stored procedures in the same database. Security checks are performed each time USP_5 calls a stored

  procedure.

  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 Time Objective (RTO) of 5 minutes.

  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 manage security for 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

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

  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.
  A. Enable SQL Server Audit.

• Question 434:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company has a Windows Azure subscription.

  The company uses a cloud based SQL Server environment hosted on SQL Azure.

  Developers in your company are creating an ecommerce website.

  You are designing a database for the website.

  The database will be hosted on SQL Azure.

  The database will store and reuse web site login details and customer credit card numbers.

  You need to ensure the username, passwords and credit card details are securely stored in the database.

  Which of the following would be the most suitable secure storage solution?

  A. Secure Sockets Layer (SSL)
  B. IPSec
  C. Data encryption
  D. Transparent Data Encryption (TDE)
  E. Encrypting File System (EFS)
  C. Data encryption

• Question 435:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company uses a Microsoft SQL Server 2012 infrastructure.

  You are configuring a highly-available database solution using an AlwaysOn availability group on two servers running SQL Server 2012. The two servers are in separate datacenters.

  The two datacenters are connected by a WAN link with a network latency of more than 200ms.

  Which of the following failover types should you configure for the availability group?

  A. You should configure the asynchronous manual failover failover type.
  B. You should configure the synchronous manual failover failover type.
  C. You should configure the synchronous automatic failover failover type.
  D. You should configure the Asynchronous automatic failover failover type.
  A. You should configure the asynchronous manual failover failover type.

• Question 436:

  You administer a Microsoft SQL Server 2016 database.

  You need to ensure that the size of the transaction log file does not exceed 2 GB.

  What should you do?

  A. Execute sp_configure 'max log size', 2G.
  B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
  C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
  D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
  D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

• Question 437:

  Your company has a SQL Azure subscription.

  You implement a database named Database1. Database1 has two tables named Table1 and Table2.

  You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2.

  A user named User1 informs you that he is unable to run sp1.

  You verify that User1 has the SELECT permission on Table1 and Table2.

  You need to ensure that User1 can run sp1.

  The solution must minimize the number of permissions assigned to User1.

  What should you do?

  A. Grant User1 the INSERT permission on Table2.
  B. Add User1 to the db_datawriter role.
  C. Change sp1 to run as the sa user.
  D. Grant User1 the EXECUTE permission on sp1.
  D. Grant User1 the EXECUTE permission on sp1.

• Question 438:

  HOTSPOT

  You have a database named DB1.

  A user named User1 reports that a specific query takes a long time to run. You suspect that User1 is blocked by a session from User2. The session ID for User1 is 54. The session ID for User2 is 62.

  You need to use Activity Monitor to identify the session that is causing the blocking activity.

  What values in each column should you look for? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 439:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  Your company has several Microsoft SQL Server instances. Each instance hosts many databases.

  You observe I/O corruption on some of the instances.

  You need to perform the following actions:

  Identify databases where the PAGE VERIFY option is not set.

  Configure full page protection for the identified databases.

  Solution: You run the following Transact-SQL statement:

  

  For each database that you identify, you run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 440:

  You have a SQL Server 2014 environment That contains 20 servers.

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

  You need to recommend a management strategy for the SQL Server 2014 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. Policy-Based Management
  C. Common criteria compliance
  D. Maintenance plans
  B. Policy-Based Management