Prev Next Question 57/256 - 70-764.v2019-07-01

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:.
You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort. Which code should you use?

LEAVE A REPLY

Your email address will not be published. Required fields are marked *

Question List (256q)
Question 1: You maintain a database named DB1 that has a nonpartitioned ...
Question 2: You plan to create a database. The database will be used by ...
Question 3: General Overview You are the Senior Database Administrator (...
Question 4: General Overview You are the Senior Database Administrator (...
Question 5: You have two databases named DB1 and DB2 that are located on...
Question 6: You have a SQL Server 2014 environment That includes four se...
Question 7: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 8: Note: This question is part of a series of questions that us...
Question 9: Overview General Overview ADatum Corporation has offices in ...
Question 10: You work as a Database Administrator (DBA) for a company nam...
Question 11: Note: This question is part of a series of questions that us...
Question 12: Note: This question is part of a series of questions that pr...
Question 13: You administer a Microsoft SQL Server 2016 database named Co...
Question 14: Note: This question is part of a series of questions that us...
Question 15: Overview You are a database administrator for a company name...
Question 16: Overview Application Overview Contoso, Ltd., is the develope...
Question 17: Note: This question is part of a series of questions that pr...
Question 18: You have the following Microsoft SQL Server instances: (Exhi...
Question 19: Your company has a SQL Azure subscription. You implement a d...
Question 20: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 21: You install a Microsoft SQL Server 2016 instance. The instan...
Question 22: You administer a Microsoft SQL Server 2016 database instance...
Question 23: Note: This question is part of a series of questions that pr...
Question 24: You create a stored procedure that retrieves all of the rows...
Question 25: You work as a Database Administrator (DBA) at ABC.com. All d...
Question 26: Note: This question is part of a series of questions that us...
Question 27: You manage a Microsoft SQL Server environment. You plan to e...
Question 28: Overview Application Overview Contoso, Ltd., is the develope...
Question 29: You administer a Microsoft SQL Server 2016 database. Users r...
Question 30: Note: This question is part of a series of questions that pr...
Question 31: You are the administrator of a Microsoft SQL Server 2016 ser...
Question 32: You maintain a database named DB1 that has a nonpartitioned ...
Question 33: Overview General Overview ADatum Corporation has offices in ...
Question 34: Note: This question is part of a series of questions that pr...
Question 35: Overview General Overview ADatum Corporation has offices in ...
Question 36: You have a database named DB1 that is configured to use the ...
Question 37: General Overview You are the Senior Database Administrator (...
Question 38: Note: This question is part of a series of questions that pr...
Question 39: You have a database named DB1. Users report that a database ...
Question 40: You administer a Microsoft SQL Server 2016 database named Co...
Question 41: You deploy a database by using SQL Server 2014. The database...
Question 42: You are implementing a SQL Server 2016 five-node failover cl...
Question 43: You have the following servers: (Exhibit) SQL1 hosts a datab...
Question 44: Note: This question is part of a series of questions that pr...
Question 45: You are designing a SQL Server database for an order fulfill...
Question 46: Overview Application Overview Contoso, Ltd., is the develope...
Question 47: You are designing a Windows Azure SQL Database for an order ...
Question 48: Which task should you perform before you can create a utilit...
Question 49: Overview You are a database administrator for a company name...
Question 50: You manage a SQL Server 2014 instance that contains a databa...
Question 51: Note: This question is part of a series of questions that pr...
Question 52: Note: This question is part of a series of questions that pr...
Question 53: Note: This question is part of a series of questions that us...
Question 54: You administer two instances of Microsoft SQL Server 2016. Y...
Question 55: You administer a Microsoft SQL Server 2016 server that has S...
Question 56: Note: This question is part of a series of questions that pr...
Question 57: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 58: You have a customer who has several SQL Server 2012 database...
Question 59: You work as a Developer at ABC.com. All databases are hosted...
Question 60: You work as a Database Administrator (DBA) for a company nam...
Question 61: You are the new database administrator for a SQL Server 2014...
Question 62: You plan to implement a fault tolerance solution for a Micro...
Question 63: Overview Application Overview Contoso, Ltd., is the develope...
Question 64: You deploy a database by using SQL Server 2012. The database...
Question 65: Which feature should you enable and configure so session req...
Question 66: A company has an on-premises Microsoft SQL Server environmen...
Question 67: You administer two Microsoft SQL Server 2012 servers. Each s...
Question 68: Note: This question is part of a series of questions that pr...
Question 69: Note: This question is part of a series of questions that pr...
Question 70: Note: This question is part of a series of questions that us...
Question 71: Note: This question is part of a series of questions that pr...
Question 72: Your company stored customer data, including credit card num...
Question 73: Note: This question is part of a series of questions that pr...
Question 74: Note: This question is part of a series of questions that pr...
Question 75: You administer a Microsoft SQL Server 2016 instance. The ins...
Question 76: Note: This question is part of a series of questions that pr...
Question 77: You have a database hosted on SQL Server 2012 R2. The databa...
Question 78: Note: This question is part of a series of questions that us...
Question 79: General Overview You are the Senior Database Administrator (...
Question 80: You administer a Microsoft SQL Server 2012 instance. You nee...
Question 81: Overview Application Overview Contoso, Ltd., is the develope...
Question 82: You have a table that has grown in the past six months. A us...
Question 83: You administer a Microsoft SQL Server 2016 server. The MSSQL...
Question 84: General Overview You are the Senior Database Administrator (...
Question 85: You configure log shipping on two servers named SQL1 and SQL...
Question 86: Note: This question is part of a series of questions that us...
Question 87: You administer a SQL Server 2016 database instance. You need...
Question 88: Note: This question is part of a series of questions that pr...
Question 89: You are planning on deploying a server that will be dedicate...
Question 90: Note: This question is part of a series of questions that pr...
Question 91: Note: This question is part of a series of questions that pr...
Question 92: You administer all the deployments of Microsoft SQL Server 2...
Question 93: Note: This question is part of a series of questions that pr...
Question 94: You administer two Microsoft SQL Server 2016 servers named P...
Question 95: You use Microsoft SQL Server 2016 to write code for a transa...
Question 96: You want to simulate read, write, checkpoint, backup, sort, ...
Question 97: Note: This question is part of a series of questions that pr...
Question 98: You administer a single server that contains a Microsoft SQL...
Question 99: Which feature should you enable and configure so session req...
Question 100: Overview General Overview ADatum Corporation has offices in ...
Question 101: Your company has offices in Seattle and Montreal. The networ...
Question 102: You administer a SQL Server 2014 instance. Users report that...
Question 103: Overview Application Overview Contoso, Ltd., is the develope...
Question 104: Note: This question is part of a series of questions that us...
Question 105: You use SQL Server 2014 to maintain the data used by applica...
Question 106: Note: This question is part of a series of questions that us...
Question 107: Note: This question is part of a series of questions that pr...
Question 108: Note: This question is part of a series of questions that pr...
Question 109: You administer a Microsoft SQL Server 2016 server that hosts...
Question 110: You have a database named DB1 that contains two tables. You ...
Question 111: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 112: You are troubleshooting an application that runs a query. Th...
Question 113: Note: This question is part of a series of questions that pr...
Question 114: You work as a Database Administrator (DBA) for a company nam...
Question 115: Note: This question is part of a series of questions that pr...
Question 116: You administer a Microsoft SQL Server 2016 database that inc...
Question 117: You are a database administrator for an organization. You mu...
Question 118: Note: This question is part of a series of questions that pr...
Question 119: You administer a Microsoft SQL Server 2016 server that hosts...
Question 120: You administer a Microsoft SQL Server 2016 database that con...
Question 121: Note: This question is part of a series of questions that pr...
Question 122: You administer a Microsoft SQL Server 2016 database. You hav...
Question 123: Overview General Overview ADatum Corporation has offices in ...
Question 124: You have two SQL Server 2012 instances named SQLDev and SQLP...
Question 125: You have a database named Saleshistory that records sales tr...
Question 126: You administer a Microsoft SQL Server database named Sales. ...
Question 127: You need to address the Sales Director's requirements regard...
Question 128: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 129: You administer a Windows Azure SQL Database database named I...
Question 130: You are a database administrator for Contoso, Ltd. You confi...
Question 131: Note: This question is part of a series of questions that us...
Question 132: You administer a Microsoft SQL Server 2016 database. You pro...
Question 133: You work as a Database Administrator (DBA) for a company nam...
Question 134: Note: This question is part of a series of questions that pr...
Question 135: Overview General Overview ADatum Corporation has offices in ...
Question 136: You administer a Microsoft SQL Server 2016 server. One of th...
Question 137: Note: This question is part of a series of questions that pr...
Question 138: You administer a Microsoft SQL Server 2016 instance. You dis...
Question 139: Note: This question is part of a series of questions that us...
Question 140: You have five servers that run Microsoft SQL Server. Each se...
Question 141: You maintain a database named DB1 that has a nonpartitioned ...
Question 142: You have two SQL Server instances named SQLDev and SQLProd t...
Question 143: Note: This question is part of a series of questions that pr...
Question 144: You maintain three datacenters in different geographical reg...
Question 145: Overview Application Overview Contoso, Ltd., is the develope...
Question 146: You administer a Microsoft SQL Server 2016 database that has...
Question 147: You work as a Developer at ABC.com. All databases are hosted...
Question 148: You create an availability group that has replicas named HA/...
Question 149: You have a database that stores information for a shipping c...
Question 150: You administer all the deployments of Microsoft SQL Server 2...
Question 151: You administer a Microsoft SQL Server 2016 database. You wan...
Question 152: Overview You are a database administrator for a company name...
Question 153: You have four databases that are accessed by using an Online...
Question 154: You need to validate rows before they are added to a table e...
Question 155: You administer a Microsoft SQL Server 2016 server. When tran...
Question 156: Overview General Overview ADatum Corporation has offices in ...
Question 157: You have configured Resource Governor with three resource po...
Question 158: You need to address the requirements for disc usage monitori...
Question 159: You are creating a database that will store usernames and pa...
Question 160: Overview General Overview ADatum Corporation has offices in ...
Question 161: Overview Application Overview Contoso, Ltd., is the develope...
Question 162: You administer a Microsoft SQL Server 2016 instance. After a...
Question 163: You have an SQL Server 2014 server named SQL1. You are desig...
Question 164: You create an availability group named HaContoso that has re...
Question 165: You administer a Microsoft SQL Server 2016 database named Co...
Question 166: You administer a Microsoft SQL Server 2016 database named Co...
Question 167: You have a server that has SQL Server 2014 installed. The se...
Question 168: Note: This question is part of a series of questions that pr...
Question 169: You are using dynamic management views to monitor an SQL Ser...
Question 170: Note: This question is part of a series of questions that us...
Question 171: You administer all the deployments of Microsoft SQL Server 2...
Question 172: Note: This question is part of a series of questions that us...
Question 173: Note: This question is part of a series of questions that us...
Question 174: Note: This question is part of a series of questions that us...
Question 175: Note: This question is part of a series of questions that pr...
Question 176: Note: This question is part of a series of questions that pr...
Question 177: You are creating a database that will store usernames and cr...
Question 178: Overview General Overview ADatum Corporation has offices in ...
Question 179: Note: This question is part of a series of questions that us...
Question 180: Note: This question is part of a series of questions that us...
Question 181: You have a SQL Server 2012 database named DB1. You have a ba...
Question 182: You administer a Microsoft SQL Server 2016 database named Co...
Question 183: Note: This question is part of a series of questions that pr...
Question 184: You are troubleshooting an application that runs a query. Th...
Question 185: You administer a Windows Azure SQL Database database named H...
Question 186: Note: This question is part of a series of questions that us...
Question 187: You use SQL Azure to store data used by an e-commerce applic...
Question 188: Note: This question is part of a series of questions that pr...
Question 189: You configure the Microsoft SQL Server Agent service to star...
Question 190: You plan to integrate an on-premises Microsoft SQL Server en...
Question 191: You administer a Microsoft SQL Server 2016 instance. You nee...
Question 192: You plan to integrate an on-premises Microsoft SQL Server en...
Question 193: You have a server named Server1 that has 2 processors. You p...
Question 194: You have a Microsoft SQL Server instance that has a database...
Question 195: You are designing a database named DB1. Changes will be depl...
Question 196: You need to provide a group of users from the IT and Manufac...
Question 197: Note: This question is part of a series of questions that us...
Question 198: Note: This question is part of a series of questions that us...
Question 199: A company has an on-premises Microsoft SQL Server environmen...
Question 200: You administer a Microsoft SQL Server 2016 instance that has...
Question 201: You have a database named DB1 that stores more than 700 giga...
Question 202: Note: This question is part of a series of questions that us...
Question 203: You need to grant access to an OLTP database regardless of t...
Question 204: Note: This question is part of a series of questions that us...
Question 205: You administer a Microsoft SQL Server 2016 Enterprise Editio...
Question 206: You have a database named DB1. You plan to create a stored p...
Question 207: A Microsoft SQL Server database named DB1 has two filegroups...
Question 208: Note: This question is part of a series of questions that us...
Question 209: Overview Application Overview Contoso, Ltd., is the develope...
Question 210: Overview You are a database administrator for a company name...
Question 211: You administer a Microsoft SQL Server 2016 instance that con...
Question 212: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 213: You work as a Database Administrator (DBA) for a company nam...
Question 214: You have an application that queries a database. Users repor...
Question 215: You have a server named Server1 that has 16 processors. You ...
Question 216: You administer a Microsoft SQL Server 2016 server. You plan ...
Question 217: Overview Application Overview Contoso, Ltd., is the develope...
Question 218: Note: This question is part of a series of questions that pr...
Question 219: You are a database administrator for a Microsoft SQL Server ...
Question 220: Note: This question is part of a series of questions that pr...
Question 221: You have a SQL Server 2014 environment That contains 20 serv...
Question 222: You are building a stored procedure for a SQL Azure database...
Question 223: You run the sp_whosystem stored procedure. A process is bloc...
Question 224: Note: This question is part of a series of questions that us...
Question 225: Note: This question is part of a series of questions that us...
Question 226: Note: This question is part of a series of questions that pr...
Question 227: You are a database administrator at Contoso, Ltd. You are pr...
Question 228: General Overview You are the Senior Database Administrator (...
Question 229: You administer a Microsoft SQL Server 2016 database named Or...
Question 230: You administer a single server that contains a Microsoft SQL...
Question 231: You administer a Microsoft SQL Server 2016 database. You nee...
Question 232: You administer a Microsoft SQL Server 2016 database named Co...
Question 233: You administer several Microsoft SQL Server 2016 database se...
Question 234: You create a Management Data Warehouse instance and enable d...
Question 235: Overview Application Overview Contoso, Ltd., is the develope...
Question 236: You work as a Database Administrator (DBA) for a company nam...
Question 237: General Overview You are the Senior Database Administrator (...
Question 238: Note: This question is part of a series of questions that us...
Question 239: You install Microsoft SQL Server 2016 on a new server. After...
Question 240: You have a SQL Server 2012 database named DB1. You plan to i...
Question 241: Note: This question is part of a series of questions that pr...
Question 242: Note: This question is part of a series of questions that pr...
Question 243: General Overview You are the Senior Database Administrator (...
Question 244: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 245: You have a Microsoft SQL Server database named DB1. Nested a...
Question 246: You have a query that is used by a reporting dashboard. User...
Question 247: You work as a Database Administrator (DBA) for a company nam...
Question 248: Overview You are a database administrator for a company name...
Question 249: You are a database administrator for a Microsoft SQL Server ...
Question 250: Note: This question is part of a series of questions that pr...
Question 251: Overview You are a database administrator for a company name...
Question 252: Background Corporate Information Fabrikam, Inc. is a retaile...
Question 253: You deploy a SQL Server instance named SQLProd that uses SQL...
Question 254: You administer a Microsoft SQL Server 2016 failover cluster....
Question 255: You are migrating a database named Orders to a new server th...
Question 256: You administer a Microsoft SQL Server 2016 database instance...