Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 251:

  You have a server named Server1 that has 2 processors. You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.

  You need to recommend a method to allocate processor time to each database.

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

  A. Resource Governor
  B. Max Degree of Parallelism
  C. Windows System Resource Manager (WSRM)
  D. Processor affinity
  A. Resource Governor

• Question 252:

  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.

  The performance of the SPUpdate2 stored procedure needs to be improved.

  Your solution must meet the design requirements.

  What should your solution include?

  A. A common table expression.
  B. A derived table.
  C. A Cursor.
  D. A table variable.
  A. A common table expression.

• Question 253:

  DRAG DROP

  Background Corporate Information Fabrikam, Inc. is a retailer that sells electronics products on the Internet. The company has a headquarters site and one satellite sales office. You have been hired as the database administrator, and the company wants you to change the architecture of the Fabrikam ecommerce site to optimize performance and reduce downtime while keeping capital expenditures to a minimum. To help with the solution, Fabrikam has decided to use cloud resources as well as on-premise servers.

  Physical Locations All of the corporate executives, product managers, and support staff are stationed at the headquarters office. Half of the sales force works at this location. There is also a satellite sales office. The other half of the sales force works at the satellite office in order to have sales people closer to clients in that area. Only sales people work at the satellite location.

  Problem Statement To be successful, Fabrikam needs a website that is fast and has a high degree of system uptime. The current system operates on a single server and the company is not happy with the single point of failure this presents. The current nightly backups have been failing due to insufficient space on the available drives and manual drive cleanup often needing to happen to get past the errors. Additional space will not be made available for backups on the HQ or satellite servers. During your investigation, you discover that the sales force reports are causing significant contention. Configuration Windows Logins The network administrators have set up Windows groups to make it easier to manage security. Users may belong to more than one group depending on their role. The groups have been set up as shown in the following table:

  

  Server Configuration The IT department has configured two physical servers with Microsoft Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition and one Windows Azure Server. There are two tiers of storage available for use by database files only a fast tier and a slower tier. Currently the data and log files are stored on the fast tier of storage only. If a possible use case exists, management would like to utilize the slower tier storage for data files. The servers are configured as shown in the following table:

  

  Database

  Currently all information is stored in a single database called ProdDB, created with the following script:

  

  The Product table is in the Production schema owned by the ProductionStaff Windows group. It is the main table in the system so access to information in the Product table should be as fast as possible. The columns in the Product table are defined as shown in the following table:

  

  The SalesOrderDetail table holds the details about each sale. It is in the Sales schema owned by the SalesStaff Windows group. This table is constantly being updated, inserted into, and read. The columns in the SalesOrderDetail table are defined as shown in the following table:

  

  Database Issues

  The current database does not perform well. Additionally, a recent disk problem caused the system to go down, resulting in lost sales revenue. In reviewing the current system, you found that there are no automated maintenance procedures.

  The database is severely fragmented, and everyone has read and write access.

  Requirements

  Database

  The database should be configured to maximize uptime and to ensure that very little data is lost in the event of a server failure. To help with performance, the database needs to be modified so that it can support in-memory data, specifically

  for the Product table, which the CIO has indicated should be a memory optimized table. The auto-update statistics option is set off on this database. Only product managers are allowed to add products or to make

  changes to the name, description, price, cost, and supplier. The changes are made in an internal database and pushed to the Product table in ProdDB during system maintenance time. Product managers and others working at the

  headquarters location also should be able to generate reports that include supplier and cost information.

  Customer data access

  Customers access the company's website to order products, so they must be able to read product information such as name, description, and price from the Product table. When customers place orders, stored procedures called by the

  website update product quantity on-hand values. This means the product table is constantly updated at random times.

  Customer support data access

  Customer support representatives need to be able to view and not update or change product information. Management does not want the customer support representatives to be able to see the product cost or any supplier information.

  Sales force data access

  Sales people at both the headquarters office and the satellite office must generate reports that read from the Product and SalesOrderDetail tables. No updates or inserts are ever made by sales people. These reports are run at random times

  and there can be no reporting downtime to refresh the data set except during the monthly maintenance window.

  The reports that run from the satellite office are process intensive queries with large data sets. Regardless of which office runs a sales force report, the SalesOrderDetail table should only return valid, committed order data; any orders not yet

  committed should be ignored.

  Historical Data

  The system should keep historical information about customers who access the site so that sales people can see how frequently customers log in and how long they stay on the site.

  The information should be stored in a table called Customer Access. Supporting this requirement should have minimal impact on production website performance.

  Backups

  The recovery strategy for Fabrikam needs to include the ability to do point in time restores and minimize the risk of data loss by performing transaction log backups every 15 minutes.

  Database Maintenance

  The company has defined a maintenance window every month when the server can be unavailable. Any maintenance functions that require exclusive access should be accomplished during that window.

  Project milestones completed

  Revoked all existing read and write access to the database, leaving the schema ownership in place.

  Configured an Azure storage container secured with the storage account name MyStorageAccount with the primary access key StorageAccountKey on the cloud file server.

  SQL Server 2014 has been configured on the satellite server and is ready for use.

  On each database server, the fast storage has been assigned to drive letter F:,

  and the slow storage has been assigned to drive letter D:.

  You need to distribute functionality across the three servers. Which function should you assign to each server? To answer, drag the appropriate functions to the correct servers.

  Each function 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:

  

  

• Question 254:

  HOTSPOT

  You are configuring log shipping for a Microsoft SQL Server database named salesOrders. You run the following Transact-SQL script:

  

  You need to determine the changes that the script has on the environment.

  How does the script affect the environment? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: is

  The dedicated backup file share is \\localhost\Backup

  Box 2: does not run

  The only thing with a name related to ADATM-SQL11 is the schedule name.

  Box 3: 72 hours

  4320 minutes equals 72 hours.

  Note: @backup_retention_period= ] backup_retention_period

  Isthe length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_period is int, with no default, and cannot be NULL.

  Box 4: 15 minutes.

  [ @freq_subday_type = ] freq_subday_type

  Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.

  Here it is 4, which means minutes.

  [ @freq_subday_interval = ] freq_subday_interval

  The number of freq_subday_type periods to occur between eachexecution of a job.

  freq_subday_intervalis int, with a default of 0.

  Note: Interval should be longer than 10 seconds. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1.

  Here it is 15.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/spadd-schedule-transact-sql

  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/spadd-log-shipping-primary-database-transact-sql

• Question 255:

  DRAG DROP

  You manage a database named DB1 that uses the following filegroups:

  

  The database is configured to use full recovery model. Transaction logs are backed up to a backup set named TLogBackup.

  The PRIMARY and FG2 for DB1 are damaged. FG1 and FG3 are intact.

  You need to design a piecemeal restore plan that meets all the above requirements. You need to bring critical filegroups online as soon as possible while minimizing restoration time. All damaged filegroups must be online after the restore

  operation completes.

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

  Select and Place:

  

  

  ---

  Step 1: Perform a differential database backup…

  Step 2: Perform a tail-log backup…

  A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up

  the tail of its transaction log.

  The tail-log backup will be the last backup of interest in the recovery plan for the database.

  Step 3:

  The PRIMARY and FG2 for DB1 are damaged. FG1 and FG3 are intact.

  Step 4:

  Transaction logs are backed up to a backup set named TLogBackup.

  Step 5:

  The PRIMARY and FG2 for DB1 are damaged.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/backuprestore/restore-files-and-filegroups-sql-server?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backupssql-server?view=sql-server-2017

• Question 256:

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

  Uses report that some Microsoft SQL Server Reporting Service (SSRS) queries take 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 Database Read-Only property to True.
  B. Run the system stored procedure sp_updatestats.
  C. Set the value of the Auto Create Incremental Statistics property to True.
  D. Run the UPDATE STATISTICS command with the COLUMNS option.
  D. Run the UPDATE STATISTICS command with the COLUMNS option.

• Question 257:

  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 offices in Vancouver, Tokyo, and Paris. The company uses Microsoft SQL Server 2016 Standard edition. You must design a reporting solution that uses data from a point of sale (POS) application's transactional database.

  After data is entered into the POS system, users must be able to run reports within 24 hours. Latency must be minimized.

  You need to implemented a solution that minimizes licensing costs. What should you implement?

  A. a Microsoft Azure Stretch Database
  B. log shipping
  C. an Always On Availability Group with all replicas in synchronous-commit mode
  D. a file share witness
  E. a Microsoft SQL Server failover cluster instance (FCI)
  F. a Windows cluster with a shared-nothing architecture
  G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
  A. a Microsoft Azure Stretch Database

• Question 258:

  HOTSPOT

  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.

  Start of repeated scenario.

  You are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several

  customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition.

  The customer environments are shown in the following table.

  

  End of repeated scenario.

  You need to configure auditing for the AdventureWorks environment. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 259:

  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 a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.

  You need to ensure that you can perform auditing at the database level for DB1.

  Solution: You migrate DB1 to a named instance on a server that runs Microsoft SQL Server 2016 Enterprise edition.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 260:

  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 feature to support your backup solution. What should you include in the recommendation?

  A. Transparent Data Encryption (TDE)
  B. Column-level encryption
  C. An NTFS file permission
  D. A Secure Sockets Layer (SSL)
  A. Transparent Data Encryption (TDE)