Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 271:

  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 is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.

  A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT

  operations or dynamic SQL commands.

  The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.

  You need to create the required security principals and grant the appropriate permissions.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 272:

  You plan to create a database.

  The database will be used by a Microsoft .NET application for a special event that will last for two days.

  During the event, data must be highly available.

  After the event, the database will be deleted.

  You need to recommend a solution to implement the database while minimizing costs.

  The solution must not affect any existing applications.

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

  A. SQL Server 2014 Enterprise
  B. SQL Server 2014 Standard
  C. SQL Azure
  D. SQL Server 2014 Express with Advanced Services
  B. SQL Server 2014 Standard

• Question 273:

  DRAG DROP

  You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID

  column. New rows are inserted into the table every day.

  Performance of queries that filter the Product ID column have degraded significantly.

  You need to improve the performance of the queries.

  Which three actions should you perform in sequence? To answer, 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: Drop the clustered columnstore index Step 2: Create a clustered rowstore index on ProductID. Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans. Step 3: Create a nonclustered index on ProductID

• Question 274:

  HOTSPOT

  You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: TableA and TableB.

  TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millions of rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATE STATISTICS for TableA

  takes longer to complete than the allotted maintenance window.

  Thousands of operations are performed against TableB each minute. You observe a large number of Auto Update Statistics events for TableB.

  You need to address the performance issues with each table.

  In the table below, identify the action that will resolve the issues for each table.

  NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  Table A: Auto_update statistics off Table A does not change much. There is no need to update the statistics on this table. Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON You can set the database to update statistics asynchronously: ALTER DATABASE YourDBName SET AUTO_UPDATE_STATISTICS_ASYNC ON If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments

  References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-autocreate-statistics-options/

• Question 275:

  Overview

  Application Overview

  Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.

  Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.

  The new version will use SQL Server 2014.

  The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily. You receive several support calls reporting

  unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.

  Tables

  The current database schema contains a table named OrderDetails.

  The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order. The product price is stored in a table named

  Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.

  A column named ProductName was created by using the varchar data type. The database contains a table named Orders.

  Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.

  The previous version of the ERP application relied on table-level security.

  Stored Procedures

  The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

  

  Customer Problems

  Installation Issues

  The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.

  Index Fragmentation Issues

  Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the

  clustered index key:

  

  Backup Issues

  Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

  Search Issues

  Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

  Missing Data Issues

  Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

  Query Performance Issues

  Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database

  administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.

  Design Requirements

  File Storage Requirements

  The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

  Data Recovery Requirements

  If the import process fails, the database must be returned to its prior state immediately.

  Security Requirements

  You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

  Concurrency Requirements

  You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.

  You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

  A. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
  B. Modify the stored procedures to update tables in the same order for all of the stored procedures.
  C. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
  D. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
  B. Modify the stored procedures to update tables in the same order for all of the stored procedures.

• Question 276:

  DRAG DROP

  You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed.

  SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.

  You need to upgrade the database to SQL Server 2014. The solution must minimize downtime. Which upgrade steps should you recommend? To answer, move the appropriate actions from the list of actions to the answer area and arrange

  them in the correct order.

  Select and Place:

  

  

  ---

  References:https://docs.microsoft.com/en-us/sql/database-engine/databasemirroring/upgrading-mirrored-instances?view=sql-server-2017

• Question 277:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company uses a Microsoft SQL Server 2012 infrastructure.

  The sales and marketing departments contain a total of 60 users. Each user uses a custom application that stores data in a SQL Server database.

  Each user has a separate database.

  A server named ABC-SQL1 hosts the 60 databases.

  You need to configure a backup solution for all the databases.

  The solution must ensure that any new databases configured on ABC-SQL1 are automatically added to the backup schedule. The backup schedule consists of a full backup every night, a differential backup every hour and a transaction log backup every 15 minutes.

  How should you configure the backup solution?

  A. You should configure SQL Server Agent jobs.
  B. You should configure Change Data Capture.
  C. You should configure Policy-Based Management.
  D. You should configure a Maintenance Plan.
  D. You should configure a Maintenance Plan.

• Question 278:

  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 memoryoptimized 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 asname, description, and price from the Product table. When customers place orders, stored procedures calledby the website

  update product quantityon-hand values. This means the product table is constantly updated at randomtimes.

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

  What should you create in Azure to support the creation of the backups for DB1?

  A. an Azure Content Delivery Network (CDN) endpoint
  B. a Service Bus namespace
  C. a storage account
  D. a cloud service
  C. a storage account

• Question 279:

  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 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. Run the system stored procedure sp_updatestats.
  B. Reorganize the index.
  C. Set the value of the Database Read-Only property to True.
  D. Set the value of the Auto Create Incremental Statistics property to True.
  A. Run the system stored procedure sp_updatestats.

• Question 280:

  Your network contains an Active Directory domain that has two groups named Group1 and Group2.

  The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.

  The SQL Server instances have a database that contains a table named Table1. Table1 contains a column named Column1.

  The value for Column1 can be either Value1 or Value2.

  You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.

  What should you recommend?

  A. A dynamic management view
  B. Filegroups
  C. Snapshot isolation
  D. User-defined views
  D. User-defined views