Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 401:

  General Overview

  You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.

  Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is

  currently using. The current application will remain in use while the users are trained to use the new webbased application.

  You need to design the SQL Server and database infrastructure for the web-based application.

  Databases

  You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting.

  The Sales database contains a table named OrderTotals and a table named SalesInfo.

  A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.

  The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table. The Current_Inventory database contains a large table named Inv_Current. The Inv_Current

  table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property. The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed

  by multiple queries in the Sales database. Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2. An external application

  named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo. A stored procedure named SPUPdateGenInfo combines

  data from multiple databases and generates millions of rows of data in the GenInfo table.

  The GenInfo table is used for reports.

  When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data. The GenInfo table is deleted after the reporting process completes. The

  Products database contains tables named ProductNames and ProductTypes.

  Current System

  The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB. This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the

  Current_Inventory database.

  SQL Servers

  A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.

  Design Requirements

  Your SQL Server infrastructure and database design must meet the following requirements:

  Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.

  Direct access to database tables by developers or applications must be denied.

  The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.

  Deadlocks must be analyzed with the use of Deadlock Graphs.

  In the event of a SQL Server failure, the databases must remain available.

  Software licensing and database storage costs must be minimized.

  Development effort must be minimized.

  The Tempdb databases must be monitored for insufficient free space.

  Failed authentication requests must be logged.

  Every time a new row is added to the ProductTypes table in the Products database, a user defined function that validates the row must be called before the row is added to the table.

  When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.

  You need to ensure that the account used to generate reports can only connect during certain hours.

  What should you configure?

  A. A CHECK constraint.
  B. Windows Server Resource Manager (WSRM).
  C. Logon Triggers.
  D. Login Auditing.
  C. Logon Triggers.

• Question 402:

  You have an application that queries a database.

  Users report that the application is slower than expected.

  You discover that several server process identifiers (SPIDs) have PAGELATCH_UP and PAGELATCH_EX waits. The resource descriptions of the SPIDs contains 2:1:1.

  You need to resolve the issue.

  What should you do?

  A. Reduce the number of table variables used in the application.
  B. Use identity integers as primary key fields in the tables.
  C. Reduce the fill factor of all the clustered indexes.
  D. Move the database files to SSD storage.
  D. Move the database files to SSD storage.

• Question 403:

  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 next of the scenario is exactly the same in each question in this series.

  Start of repeated scenario

  Contoso, Ltd. has Microsoft SQL Server databases that support a custom application. The current SQL Server environment consists of two servers: ContosoSQL1 and ContosoSQL2. These two servers participate in an Always On Availability Group named ContosoAG1 that is configured to use synchronous-commit with automatic failover. The secondary replica is not configured for read-only access. The application performs both transactional processing and historical data retrieval in a database named ContosoDB. The application includes an inventory management module. The inventory management module and database have experienced performance issues.

  Users report that a query named InventoryQuery1 takes a long time to complete. The query is shown as follows:

  SELECT ProductNumber, Name, ProductLine FROM Production.Product WHERE ProductNumber = N''

  The query plan used by SQL Server for this query is shown in the exhibit. (Click the Exhibit tab.) Various performance issues, including frequent long-term blocking episodes, prevent business users from completing their daily tasks. You suspect the tempdb database resources could be responsible. You must create Blocking reports for the ContosoDB database to identify issues.

  Exhibit.

  

  You plan to use Extended Events to review all Transact-SQL statements that are run against the ContosoSQL1 instance. The output from the Extended Events session must contain both start and stop events and must be written to a file. You

  must configure the Extended Events session to minimize possible data loss and reduce the effect on server performance.

  You plan to deploy an additional secondary replica named ContosoSQL3 to ContosoAG1. Read-only traffic must be load-balanced between the two secondary replicas, regardless of which instance is the primary replica. Contoso plans to add

  an additional dedicated reporting system that will rely on real-time data from the transactional databases.

  The company plans to improve their high availability/disaster recovery (HA/DR) solution. As part of the planned improvements, you will back up all databases from ContosoSQL1 directly to an off-site location.

  End of repeated scenario

  You need to identify the issue with tempdb.

  What should you do?

  A. Review results from the sys.dm_db_file_space_usage Dynamic Management View (DMV).
  B. Monitor for high or higher-than-normal I/O wait times on tempdb data files.
  C. Use query Store to observe query performance within tempdb.
  D. Monitor for excessive blocking on the instance.
  A. Review results from the sys.dm_db_file_space_usage Dynamic Management View (DMV).

• Question 404:

  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 that meets the data recovery requirement. What should you include in the recommendation?

  A. A database snapshot
  B. A transaction log backup
  C. Snapshot isolation
  D. A differential backup
  D. A differential backup

• Question 405:

  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
  A. Yes

• Question 406:

  General Overview

  You are the Senior Database Administrator (DBA) for a software development company named Leafield Solutions. The company develops software applications custom designed to meet customer requirements.

  Requirements Leafield Solutions has been asked by a customer to develop a web-based Enterprise Resource Planning and Management application. The new application will eventually replace a desktop application that the customer is

  currently using. The current application will remain in use while the users are trained to use the new webbased application.

  You need to design the SQL Server and database infrastructure for the web-based application.

  Databases

  You plan to implement databases named Customers, Sales, Products, Current_Inventory, and TempReporting.

  The Sales database contains a table named OrderTotals and a table named SalesInfo.

  A stored procedure named SPUpdateSalesInfo reads data in the OrderTotals table and modifies data in the SalesInfo table.

  The stored procedure then reads data in the OrderTotals table a second time and makes further changes to the information in the SalesInfo table. The Current_Inventory database contains a large table named Inv_Current. The Inv_Current

  table has a clustered index for the primary key and a nonclustered index. The primary key column uses the identity property. The data in the Inv_Current table is over 120GB in size. The tables in the Current_Inventory database are accessed

  by multiple queries in the Sales database. Another table in the Current_Inventory database contains a self-join with an unlimited number of hierarchies. This table is modified by a stored procedure named SPUpdate2. An external application

  named ExternalApp1 will periodically query the Current_Inventory database to generate statistical information. The TempReporting database contains a single table named GenInfo. A stored procedure named SPUPdateGenInfo combines

  data from multiple databases and generates millions of rows of data in the GenInfo table.

  The GenInfo table is used for reports.

  When the information in GenInfo is generated, a reporting process reads data from the Inv_Current table and queries information in the GenInfo table based on that data. The GenInfo table is deleted after the reporting process completes. The

  Products database contains tables named ProductNames and ProductTypes.

  Current System

  The current desktop application uses data stored in a SQL Server 2005 database named DesABCopAppDB. This database will remain online and data from the Current_Inventory database will be copied to it as soon as data is changed in the

  Current_Inventory database.

  SQL Servers

  A new SQL Server 2012 instance will be deployed to host the databases for the new system. The databases will be hosted on a Storage Area Network (SAN) that provides highly available storage.

  Design Requirements

  Your SQL Server infrastructure and database design must meet the following requirements:

  Confidential information in the Current_ Inventory database that is accessed by ExternalApp1 must be securely stored.

  Direct access to database tables by developers or applications must be denied.

  The account used to generate reports must have restrictions on the hours when it is allowed to make a connection.

  Deadlocks must be analyzed with the use of Deadlock Graphs.

  In the event of a SQL Server failure, the databases must remain available.

  Software licensing and database storage costs must be minimized.

  Development effort must be minimized.

  The Tempdb databases must be monitored for insufficient free space.

  Failed authentication requests must be logged.

  Every time a new row is added to the ProductTypes table in the Products database, a user defined function that validates the row must be called before the row is added to the table.

  When SPUpdateSalesInfo queries data in the OrderTotals table the first time, the same rows must be returned along with any newly added rows when SPUpdateSalesInfo queries data in the OrderTotals table the second time.

  You need to recommend a solution to meet the design requirement of logging all failed authentication requests.

  What should you recommend?

  A. Object Access Auditing
  B. C2 Audit Mode
  C. Logon Triggers.
  D. Login Auditing.
  D. Login Auditing.

• Question 407:

  You need to ensure that a stored procedure fails if an INSERT statement within the stored procedure fails. What action should you take?

  A. THROW 51000, 'Abort!'
  B. SET XACT_ABORT OFF
  C. SET XACT_ABORT ON
  D. TRY....CATCH
  C. SET XACT_ABORT ON

• Question 408:

  You administer all the deployments of Microsoft SQL Server 2016 in your company.

  You need to ensure that data changes are sent to a non-SQL Server database server in near real time.

  You also need to ensure that data on the primary server is unaffected.

  Which configuration should you use?

  A. SQL Server that includes an application database configured to perform transactional replication
  B. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  C. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  D. SQL Server that includes an application database configured to perform snapshot replication
  E. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
  F. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  G. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  H. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
  A. SQL Server that includes an application database configured to perform transactional replication

• Question 409:

  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.

  You have a data warehouse that stores sales data. One fact table has 100 million rows.

  You must reduce storage needs for the data warehouse.

  You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.

  Solution: You implement a columnstore index on the fact table.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 410:

  HOTSPOT

  You are a database administrator for an organization. You manage a mission-critical database named DB1 that is hosted on a server named LON-SQL1.

  An audit record for transactions that occur in DB1 are written to a file every five seconds. If an audit record cannot be recorded, the transaction must be terminated. You create a shared folder named Audits on a server named LON-SVR1.

  You need to implement the process for recording audit records.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segment in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: Server Audit The CREATE SERVER AUDIT command creates a server audit object using SQL Server Audit. Box 2: FILE(FILEPATH='\\LON-SVR1\Audits\') An audit record for transactions that occur in DB1 are written to a file every five seconds. You create a shared folder named Audits on a server named LON-SVR1. Box 3: QUEUE_DELAY=5000 QUEUE_DELAY =integer Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. Box 4: ON_FAILURE= FAIL_OPERATION FAIL_OPERATION Database actions fail if they cause audited events. Actions, which do not cause audited events can continue, but no audited events can occur. The audit continues to attempt to log events and resumes if the failure condition is resolved. Use this option when maintaining a complete audit is more important than full access to the Database Engine.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql