Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 351:

  You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database.

  You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.

  Which type of user should you create?

  A. User mapped to a certificate
  B. SQL user without login
  C. Domain user
  D. SQL user with login
  C. Domain user

• Question 352:

  You administer a Microsoft SQL Server 2016 instance that has multiple databases.

  You have a two-node SQL Server failover cluster.

  The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.

  You need to reduce the I/O workload on the SAN at a minimal cost.

  What should you do?

  A. Move user databases to a local disk.
  B. Expand the tempdb data and log files.
  C. Modify application code to use table variables.
  D. Move the tempdb files to a local disk.
  D. Move the tempdb files to a local disk.

• Question 353:

  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 synchronize Database2 to App1_Db1. What should you recommend?

  A. Change data capture
  B. Snapshot replication
  C. Master Data Services
  D. Transactional replication
  D. Transactional replication

• Question 354:

  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 configure a synchronization solution to copy data from the Current_Inventory database the DesABCopAppDB database.

  What should you configure?

  A. Transactional Replication.
  B. Database Mirroring.
  C. Snapshot Replication.
  D. Incremental Backups
  A. Transactional Replication.

• Question 355:

  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 is deploying a Microsoft SQL Server environment in Microsoft Azure.

  Backups need to be performed and managed automatically.

  You need to configure the SQL Server backup.

  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 356:

  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 reorganize all indexes.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 357:

  DRAG DROP

  You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

  

  The backup strategies for each database are described in the following table.

  

  Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

  

  SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:

  database name

  impacted file id

  impacted file physical name

  impacted page id

  event type that identifies the error type

  error count

  Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:

  Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).

  Capture queries based on resource consumption.

  Use a stale query threshold value of 60 days.

  The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were

  automatically created and have not been updated for 30 days. The update should be based on all data in the table.

  You need to view the information about the corrupted pages on SalesDb3.

  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: msdb.dbo.suspect_pages

  suspect_pages contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is

  updated in the event_type column.

  The suspect_pages table resides in the msdb database.

  SalesDb3 has pages with checksum errors.

  Box 2: msdb.sys.database_files

  We want to identify these pages and which database they are in, this is easy enough to do when we join out to sys.databases and sys.master_files, as seen here:

  SELECT d.name AS databaseName,

  mf.name AS logicalFileName,

  mf.physical_name AS physicalFileName,

  sp.page_id,

  case sp.event_type

  when 1 then N'823 or 824 error'

  when 2 then N'Bad Checksum'

  when 3 then N'Torn Page'

  when 4 then N'Restored'

  when 5 then N'Repaired'

  when 7 then N'Deallocated'

  end AS eventType,

  sp.error_count,

  sp.last_update_date

  from msdb.dbo.suspect_pages as sp

  join sys.databases as d ON sp.database_id = d.database_id

  join sys.master_files as mf on sp.[file_id] = mf.[file_id]

  and d.database_id = mf.database_id;

  The result of this query will give you a high level view of where you have potential corruption in your databases, from here it is important to use tools such as DBCC CHECKDB and your backups to recover from in line with your RPO and RTO.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-server

  https://blogs.sentryone.com/johnmartin/monitoring-for-suspect-pages/

• Question 358:

  You administer a Microsoft SQL Server 2016 database.

  Users report that a billing application becomes unresponsive during busy times of the day.

  While investigating, you notice large number of processes taking or waiting for table locks.

  You suspect that SQL Server is assigning stronger locks to queries.

  You start a SQL Profiler trace.

  Which event should you select?

  A. Deadlock graph
  B. Lock: Escalation
  C. Lock: Timeout
  D. Lock: Deadlock
  B. Lock: Escalation

• Question 359:

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

  A customer that uses an on-premises instance reports that queries take a long time to complete.

  You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.

  Which Transact-SQL segment should you use?

  A. sp_autostats
  B. AUTO_UPDATE_STATISTICS_ASYNC
  C. SET AUTO_UPDATE_STATISTICS ON
  D. CREATE STATISTICS
  C. SET AUTO_UPDATE_STATISTICS ON

• Question 360:

  HOTSPOT

  You manage a Microsoft SQL Server environment with a database named DB1. You notice that queries take longer to complete than they did a year ago.

  You increase the file size for the database named tempdb, but it does not resolve the problem.

  You need to capture runtime statistics for all queries.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Use the ALTER DATABASE statement to enable the query store. For example:

  ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

  The SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store