Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 291:

  You administer several Microsoft SQL Server 2016 database servers.

  Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types.

  Occasionally, merge replication fails due to timeout errors.

  You need to reduce the occurrence of these timeout errors.

  Which of following action should you do?

  A. Set the Remote Connection Timeout on the Publisher to 0.
  B. Change the Merge agent on the problem subscribers to run continuously.
  C. Set the Merge agent on the problem subscribers to use the slow link agent profile.
  D. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
  C. Set the Merge agent on the problem subscribers to use the slow link agent profile.

• Question 292:

  HOTSPOT

  Your organization is developing a web application. The application will access data from a Microsoft SQL Server database. You must implement a security solution that meets the following requirements:

  All user logins must be associated with an Active Directory

  Service accounts are not permitted.

  Constrained database are not permitted.

  Users must not be able to log on to SQL Server as the web application and access the database.

  The web application must be permitted to display records to the and add the database.

  You need 10 implement the required security and permitted structure for the web application while the principle of least privilege.

  Which settings should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 293:

  You administer a Microsoft SQL Server 2016 instance.

  After a routine shutdown, the drive that contains tempdb fails.

  You need to be able to start the SQL Server.

  What should you do?

  A. Modify tempdb location in startup parameters.
  B. Start SQL Server in minimal configuration mode.
  C. Start SQL Server in single-user mode.
  D. Configure SQL Server to bypass Windows application logging.
  B. Start SQL Server in minimal configuration mode.

• Question 294:

  General Overview You are the Senior Database Administrator (DBA) for a manufacturing company named Fairstone Manufacturing. Fairstone Manufacturing is based in the New York area. The company has two offices: a main office in the city and a branch office just outside the city. The company has four factories where their products are manufactured. Two factories are in the New York area and the other two factories are in Washington. Network Connectivity

  The two offices are connected by a 10 Mbps dedicated WAN link.

  SQL Server Environment

  The main office has four SQL Server 2012 Standard Edition servers named MainDB1, MainDB2, MainDB3 and MainDB4. The branch office has two SQL Server 2012 Standard Edition servers named BranchDB1 and BranchDB2. The main

  office has a Development department. All databases used by the Development department are hosted on MainDB3 and MainDB4. MainDB1 and MainDB2 host the following databases:

  Products

  Manufacturing

  Sales

  HR

  Customers

  DailyReportsTemp

  BranchDB1 and BranchDB2 host the same databases as MainDB1 and MainDB2. The DailyReportsTemp database is a temporary database that is recreated every day and used for reporting purposes. One of the tables in the Customer

  database lists all the customers. Another table linked to the customers table contains a list of classifications for the customers. The classifications are Hot, Warm and Cold based on the number of orders placed by the customers in the last

  year. The customers are classified according to the following criteria:

  Hot - Over 100 orders placed in a year.

  Warm - Between 50 and 100 orders placed in a year.

  Cold - Under 50 orders placed in a year.

  Stored Procedures

  Three tables in the Manufacturing database are modified by a stored procedure named ManProc1.

  A segment of code from ManProc1 is as follows:

  

  The same three tables are also modified by a stored procedure named ManProc2. A segment of code from ManProc2 is as follows:

  

  A product list in the Products database is updated using information from tables in the Manufacturing database by a stored procedure named ProductUpdateProc. Locks on tables in the Manufacturing database often cause

  ProductUpdateProc to take a long time to complete.

  A list of manufacturing processes required to create each product is stored in tables in the Manufacturing database and updated by a stored procedure named ProcessUpdateProc. The ProcessUpdateProc stored procedure contains several

  UPDATE statements. The UPDATE statements are configured to be called in a specific order. The ProcessUpdateProc stored procedure continues to run in the event of a failure of one of the UPDATE statements.

  This can cause inaccurate results in the manufacturing process list.

  Sales Director Statement

  The Sales Director has made the following observations about the current database design:

  The current customer classification system needs to be changed.

  Currently the customers are classified by the number of orders placed in the last year.

  This information is an unreliable guide as it does not take in to account the size of the orders.

  I would suggest a trial run of a classification system based on the revenue generated by the orders placed in the last year.

  We may add more than the current three classification types in future.

  We should have a method of recording changes to the classifications.

  IT Manager Statement

  The IT Manager has listed the following requirements for the SQL Server and database environment:

  We need to provide a group of users from the IT and Manufacturing departments the minimum administrative rights to view database information and server state for the Manufacturing database on MainDB1.

  The Sales database takes too long to back up due to the large amount of historical sales order data in the database. We need to reduce the backup time for this database.

  The DailyReportsTemp database takes four hours to back up. We need to be able to recover the DailyReportsTemp database in less than one hour if the database storage hardware fails.

  We need to be able to immediately return the Manufacturing database to its previous state if the ProcessUpdateProc stored procedure fails to update the process information correctly.

  I also want the ProcessUpdateProc stored procedure to stop running in the event of a failure of one of the UPDATE statements.

  IT Administrators need to be able to monitor the disk space used on the SQL Servers by running real-time reports on the disk usage.

  The Developers would like to install second instances of SQL Server on MainDB3 and MainDB4.

  They would like to assign each instance to specific processors on the SQL Servers.

  You need to enable the Developers to assign SQL Server instances on MainDB3 and MainDB4 to specific processors on the servers. What should you configure?

  A. Windows System Resource Manager (WSRM)
  B. Resource Governor
  C. A Maintenance Plan
  D. Processor Affinity
  D. Processor Affinity

• Question 295:

  DRAG DROP

  You have a test server that contains a database named DB1. Backups of the database are written to a single backup device. The backup device has a full, differential, and transaction log backup.

  You discover that the database is damaged. You restore the database to the point at which the differential backup was taken.

  You need to rebuild the database with data stored in the latest transaction logs.

  How should you complete the Transact-SQL statement? To answer. drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.

  Select and Place:

  

  

  ---

  Box 1: RESTORE Box 2: RECOVERY The RESTORE ... WITH RECOVERY option puts the database into a useable state, so users can access a restored database.

  References: https://www.mssqltips.com/sqlservertutorial/112/recovering-a-database-that-isin-the-restoring-state/

• Question 296:

  You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistics updates are disabled.

  Users report that some Microsoft SQL Server Reporting Service (SSRS) queries lake a long time to run.

  You determine that the issue is caused by stale statistics for Index1.

  You need to update the statistics for the index.

  What should you do?

  A. Set the value of the AUTO_CREATE_STATISTICS option to ON.
  B. Reorganize the index.
  C. Set the value of the Auto Create Incremental Statistics property to True.
  D. Set the value of the STATISTICS_NORECOMPUTE option to OFF and rebuild the index.
  D. Set the value of the STATISTICS_NORECOMPUTE option to OFF and rebuild the index.

• Question 297:

  HOTSPOT

  You manage a Microsoft SQL Server environment. A server fails and writes the following event to the application event log:

  MSG_AUDIT_FORCED_SHUTDOWN

  You configure the SQL Server startup parameters as shown in the following graphic:

  

  Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: single-user The startup option -m starts an instance of SQL Server in single-user mode.

  Box 2: sysadmin Starting SQL Server in single-user mode enables anymember of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

  References: https://docs.microsoft.com/en-us/sql/database-engine/configurewindows/database-engine-service-startup-options

• Question 298:

  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 are examining information about users, sessions, and processes in an on-premises Microsoft SQL Server 2016 Standard Edition server.

  You need to identify waits for resources and return only the following information: a list of all databases on the SQL Server instance, along with information about the database files, their paths, and names a list of the queries recently executed that use most of memory, disk, and network resources

  What should you use?

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

• Question 299:

  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:

  

  The differential backup of the reporting database fails.

  Then, the reporting database fails at 14:00 hours.

  You need to ensure that the reporting database is restored.

  You also need to ensure that data loss is minimal.

  What should you do?

  A. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  B. Perform a point-in-time restore.
  C. Restore the latest full backup.
  D. 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.
  E. Restore the latest full backup. Then, restore the latest differential backup.
  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 page restore.
  H. Perform a partial restore.
  C. Restore the latest full backup.

• Question 300:

  You have a server that has SQL Server 2014 installed.

  The server contains 100 user databases.

  You need to recommend a backup solution for the user databases.

  The solution must meet the following requirements:

  Perform a transaction log backup every hour.

  Perform a full backup of each database every week.

  Perform a differential backup of each database every day.

  Ensure that new user databases are added automatically to the backup solution.

  What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  A. A maintenance plan
  B. SQL Server Agent jobs
  C. Policy-Based Management
  D. A Data Definition Language (DDL) trigger
  A. A maintenance plan