Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 391:

  You manage database servers in a high security environment. Your company has the following auditing requirements:

  SQL Server auditing must be enabled on all server instances.

  Auditing results must be logged in the Windows Security even log.

  A routine review shows that a SQL Server is writing auditing entries to Windows Application event log. You change the SQL Server audit target to Windows Security event long. SQL Server auditing stops working on the server.

  You need to ensure that the server meets the auditing requirements.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Grant the manage auditing and security log permission to the SQL Server service account.
  B. Grant the generate security audits permission on the SQL Server service account.
  C. Update Windows security policy to audit object access.
  D. Restart the SQL Server Agent service.
  B. Grant the generate security audits permission on the SQL Server service account.
  C. Update Windows security policy to audit object access.

• Question 392:

  You have an SQL Server 2014 server named SQL1. You are designing a performance monitoring solution. You need to monitor the following events on SQL1: A deadlock graph Missing column statistics CPU performance statistics A batch of completed Transact-SQL statements

  Which two tools should you use? Each correct answer presents a complete solution.

  A. dynamic management views
  B. Database Engine Tuning Advisor
  C. SQL Server Profiler
  D. Activity Monitor
  E. Data Profile Viewer
  B. Database Engine Tuning Advisor
  C. SQL Server Profiler

• Question 393:

  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 allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements.

  What should you recommend?

  A. Create stored procedures that use EXECUTE AS clauses.
  B. Create a user-defined database role and add users to the role.
  C. Create functions that use EXECUTE AS clauses.
  D. Create a Policy-Based Management Policy.
  A. Create stored procedures that use EXECUTE AS clauses.

• Question 394:

  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.

  A company has an on-premises Microsoft SQL Server environment.

  SQL Server backups should be stored as Microsoft Azure blob pages. The connection process from the SQL Server instances to Azure should be encrypted.

  You need to store backups as Azure blob pages. Which option should you use?

  A. backup compression
  B. backup encryption
  C. file snapshot backup
  D. mirrored backup media sets
  E. SQL Server backup to URL
  F. SQL Server Managed Backup to Azure
  G. tail-log backup
  H. back up and truncate the transaction log
  F. SQL Server Managed Backup to Azure

• Question 395:

  You are planning on deploying a server that will be dedicated for ETL (Extraction, Transformation, and Loading) processes.

  You want to ensure that SSIS (SQL Server Integration Services) packages will run on this dedicated ETL server and not on any other server on which they were started.

  Which of the following features must you install on the ETL server in addition to SSIS to accomplish this goal?

  A. Database Engine
  B. SQL Server Reporting Services
  C. SQL Server Analysis Services
  D. Client Tools SDK
  A. Database Engine

• Question 396:

  HOTSPOT

  You have a database named DB1.

  Users report that queries that use a specific table take a long time to complete. You suspect an issue with how often checkpoints are issued.

  You need to create an Extended Events session to monitor the database checkpoint activity on DB1.

  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:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server

• Question 397:

  Which feature should you enable and configure so session requests addressed to a specific instance can be allocated different processor resources based on session request properties?

  A. Resource Governor
  B. Windows System Resource Manager
  C. Processor affinity
  D. I/O affinity
  A. Resource Governor

• Question 398:

  You are a database administrator for a Microsoft SQL Server 2016 environment.

  You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.

  You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.

  Which feature should you use?

  A. Database Mirroring
  B. Peer-to-Peer Replication
  C. Log Shipping
  D. Availability Groups
  B. Peer-to-Peer Replication

• Question 399:

  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 disk monitoring solution that meets the business requirements.

  What should you include in the recommendation?

  A. a SQL Server Agent alert
  B. a dynamic management view
  C. a maintenance plan
  D. an audit
  B. a dynamic management view

• Question 400:

  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 configure the backup process for ContosoSQL1.

  What should you do?

  A. Set the recovery model to Simple.
  B. Perform mirrored backups to a DR datacenter.
  C. Create a new backup set.
  D. Perform a backup to a tape device.
  B. Perform mirrored backups to a DR datacenter.