Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 171:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

  

  You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

  

  Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the

  total database size.

  Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

  Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

  You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.

  You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

  All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the

  space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

  

  Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

  You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

  Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data.

  The queries must be load balanced over variable read-only replicas.

  Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database. Queries from the

  operations system will perform both DDL and DML operations.

  The wait statistics monitoring requirements for the instances are described in the following table.

  

  You need to reduce the amount of time it takes to backup OperationsMain. What should you do?

  A. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.
  B. Modify the backup script to use the keyword SKIP in the WITH statement
  C. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
  D. Modify the full database backups script to stripe the backup across multiple backup files.
  D. Modify the full database backups script to stripe the backup across multiple backup files.

• Question 172:

  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.

  A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

  One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.

  You need to optimize query performance.

  Solution: You run the DBCC CHECKDB command. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 173:

  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.

  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 contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does

  not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes

  occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

  Business Requirements

  You have the following requirements:

  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 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
  A. A table variable

• Question 174:

  DRAG DROP

  You manage a Microsoft SQL Server instance with data collection configured. You create a Management Data Warehouse instance in a different instance.

  Currently all the System Data collection sets are stopped.

  You need to configure the System Data collection sets to perform historical and trend analyses by using System Data collection reports.

  Which System Data collection sets should you use? To answer, drag the appropriate System Data collection sets to the correct targets. Each System Data collection set 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

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

• Question 175:

  You have a database that stores information for a shipping company. You create a table named Customers by running the following Transact-SQL statement. (Line numbers are included for reference only.)

  

  You need to ensure that salespeople can view data only for the customers that are assigned to them. Which Transact-SQL segment should you insert at line 07?

  A. RETURNS varchar(20)WITH Schemabinding
  B. RETURNS dbo.CustomersORDER BY @salesPerson
  C. RETURNS tableORDER BY @salesPerson
  D. RETURNS tableWITH Schemabinding
  D. RETURNS tableWITH Schemabinding

• Question 176:

  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.

  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 contains a stored procedure named usp_UpdateInventory. usp_UpdateInventory will manipulate a table that contains a self-join that has an unlimited number of hierarchies. All data in Database2 is recreated each day ad does

  not change until the next data creation process. Data from Database2 will be accessed periodically by an external application named Application1. The data from Database2 will be sent to a database named Appl_Dbl as soon as changes

  occur to the data in Database2. Litware plans to use offsite storage for all SQL Server 2014 backups.

  Business Requirements

  You have the following requirements:

  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. Read committed
  B. Repeatable read
  C. Read uncommitted
  D. Serializable
  B. Repeatable read

• Question 177:

  DRAG DROP

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

  

  You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

  

  Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the

  total database size.

  Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

  Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

  You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction.

  Changes made must not interrupt the log backup chain.

  You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

  All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the

  space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

  

  Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

  You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

  Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data.

  The queries must be load balanced over variable read-only replicas.

  Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles.

  The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.

  The wait statistics monitoring requirements for the instances are described in the following table.

  

  You need to analyze the wait type and statistics for specific instanced in the environment.

  Which object should you use to gather information about each instance? To answer, drag the appropriate objects to the correct instances. Each object 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.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Instance 1: sys.dm_exec_query_stats

  From Scenario: Instance1 requirement: Aggregate statistics since last server restart.

  sys.dm_exec_query_stats returns aggregate performance statistics for cachedquery plans in SQL Server.

  Instance 4: sys.dm_os_wait_stats

  sys.dm_os_wait_statsreturns information about all the waits encountered by threads that executed.

  From Scenario: Instance4 requirement: Identify the most prominent wait types.

  

  Instance 5:sys.dm_exec_session_wait_stats

  From Scenario: Instance5 requirement: Identify all wait types for queries currently running on the server.

  sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session.

• Question 178:

  DRAG DROP

  You manage a Microsoft SQL Server that has a database named salesOrders. Users connect to the database by using a client application.

  Users report that the application cannot connect to the database. You observe that the database storage has experienced a failure.

  You need to repair the database and ensure that applications can connect to the database.

  Which three action should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  The specified database must be in single-user mode to use one of the following repair options.

  REPAIR_REBUILD

  Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

• Question 179:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You observe that several indexes are fragmented.

  You need to rebuild the indexes.

  What should you use?

  A. Activity Monitor
  B. sp_who3 stored procedure
  C. Object Explorer in the SQL Server Management Studio (SSMS)
  D. SQL Server Data Collector
  E. SQL Server Data Tools (SSDT)
  F. SQL Server Configuration Manager
  C. Object Explorer in the SQL Server Management Studio (SSMS)

• Question 180:

  DRAG DROP

  You use SQL Server 2014.

  You need to create a single object that inserts a provided value into Table1, and then returns a count of the records in Table1.

  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: