70-441 Designing Database Solutions by Using Microsoft SQL Server 2005 Practice Exams

Demo Question 2.

TestLabs, Inc. OVERVIEW: TestLabs, Inc. is the leading manufacturer which produces specially brewed high octane racing fuels and prototype high performance accessories for several companies and customers in the racing industries. TestLabs, Inc. mission is to provide the best racing fuels which meet precise manufacturing standards. TestLabs, Inc. distributes the racing fuels all across the world. TestLabs, Inc. has most of their customers located in the United States. TestLabs, Inc. makes use of fully automated manufacturing procedures which is used to create the prototype high performance accessories and high octane racing fuels. TestLabs, Inc. has all their manufacturing equipment controlled by proprietary systems which can be programmed manually through a control interface program or automatically by uploading a process file to the control computer. TestLabs, Inc. has the capability to switch production lines between components by loading a different XML process file. TestLabs, Inc. has four manufacturing lines which are responsible for operating at any time, each making a single component. TestLabs, Inc. has recently decided to stop the production line for an hour each day for inspection, cleaning, and calibration. TestLabs, Inc. makes use of a rolling schedule for taking down the production lines meaning no more than one production line is down for scheduled maintenance at a time. TestLabs, Inc. shuts down each production line at the same time each day. TestLabs, Inc. takes one day in a month to shutdown all production lines for major maintenance activities. TestLabs, Inc. has the manufacturing line running 24 × 7 except for the shut down. TestLabs, Inc. has recently decided to have all manufacturing tolerances for each component specified by the manufacturing contract. TestLabs, Inc. uses a unique serial number that includes an embedded batch number for identifying each component. TestLabs, Inc. decided to create the serial number as each component is started and applied during the manufacturing process. TestLabs, Inc. has the batch numbers updated daily based on the year and Julian date. All serial numbers used by TestLabs, Inc. conforms to a standard format including a series of numbers and letters. TestLabs, Inc. has decided to sell their prototype high performance accessories and high octane racing fuels only to contracting customer. The customers of TestLabs, Inc. want to have the inventory on hand to minimize for the accessories to be scheduled for just-in-time delivery. TestLabs, Inc. requires that the customers provide at least 48 hours warning when requesting a change in delivery schedule. TestLabs, Inc. has all the raw materials that they receive based on just-in-time delivery schedules. TestLabs, Inc. also generates to the vendor purchasing the accessories 24-hours before expected delivery. TestLabs, Inc. additionally generates daily internal-mail to the warehouse management listing orders expected for receipt that day. TestLabs, Inc. has recently decided to take the next step in the company evolution by automating most of its quality control (QC. procedures. TestLabs, Inc.'s currently makes use of a proprietary network-based collection program named KingQuality with a limited reporting component. TestLabs, Inc. knows that the proprietary network-based collection programs reporting component has the capability to generate a raw dump of sensor data to a comma-delimited file. TestLabs, Inc. makes use of the latest sensors which are used to measure the exact weigh and diameter of the components verifying they are within manufacturing tolerance. TestLabs, Inc. has the information reported to a specific IP address. TECHNICAL ENVIRONMENT: TestLabs, Inc. currently makes use of a computer running Microsoft Windows Server 2003 Standard Edition for storing the XML process files. TestLabs, Inc. has the Microsoft Windows Server 2003 Standard Edition computer running an XML Web service to support the process control computers. TestLabs, Inc. has loaded the Process files into the control systems as XML files containing the manufacturing steps and specifications. TestLabs, Inc. has configured the process computer to submit an identifying code to the Web service when reset. TestLabs, Inc. has configured the Web service to return the XML process file contains a seed value for the component serial number which is used as the starting serial number for the day's batch. TestLabs, Inc. was deployed originally with only three Microsoft SQL Server 2005 Enterprise Edition computers running Windows Server 2003 Enterprise Edition. TestLabs, Inc. has ensured that their deployment plan allows for more instances of SQL to be installed in the future if required to meet business or technical requirements. TestLabs, Inc. makes use of the SQL Server named TL-SR01 to maintain accounting and personnel records. The second SQL Server named TL-SR02 is used to maintain the manufacturing line production, inventory, and shipping records. TestLabs, Inc. has planned to have TL-SR02 host the XML process files whilst maintaining both current and historic process files. The third SQL Server named TL-SR03 will be used to collect and monitor quality control information when the automated quality control system goes live. TestLabs, Inc. will deploy more servers in the future if it is required. BUSINESS REQUIREMENTS: TestLabs, Inc. knows that this type of business has several time-sensitive reporting requirements. TestLabs, Inc. requires having the Managers have daily reports on current inventory, daily output, and projected shipments. TestLabs, Inc. wants to have all the reports e-mailed automatically to managers. The Managers have recently requested that they be able to recall previous day's reports. The Managers of TestLabs, Inc. additionally requires the daily QC summary reports describing any variations from optimum tolerance and when they occurred. TestLabs, Inc. considers it very critical that reporting should not be allowed to interfere with operational requirements. 1. TestLabs, Inc. has identified other reporting requirements which include general production and sales reports, business-related expenses due to problems such as down time or raw materials delays, and general accounting reports. 2. TestLabs, Inc. maintains all their purchase and sales records in US dollars. The Orders are generated automatically at TestLabs, Inc. when the components are shipped and the sale posted at that time. TestLabs, Inc. also generates invoices for customers of other countries in their local currency. 3. TestLabs, Inc. requires having the customers receive inventory and shipment schedule information by e-mail only. TestLabs, Inc. wants to have their customers able to request shipment records for components already shipped throughout the life of the manufacturing contract. TestLabs, Inc. wants to have the Managers to be able to request projected shipments for the next 30 days. TestLabs, Inc. wants to have their maintenance personnel generate detailed ad hoc QC tracking reports in chart and table report formats. The TestLabs, Inc. maintenance staff should be able to filter the report by manufacturing line, specific QC sensors, and time of day range. TestLabs, Inc. requires having the maintenance team to be able to generate these reports at any time accurate to the latest five minutes. TECHNICAL REQUIREMENTS: TestLabs, Inc. makes use of the QC sensors to provide real-time data updated for each component manufactured. TestLabs, Inc. has configured each component with a separate QC tracking table. TestLabs, Inc. wants to have a new tracking table created before the start of each new batch run. TestLabs, Inc. has the date encoded in the table name. A list of data to be tracked with each entry is shown in the table below: TestLabs, Inc. wants to have each sensor return a single value for each component. TestLabs, Inc. considers it to be imperative that all QC data is collected and reported accurately. 1. TestLabs, Inc. does not want the data to be changed manually, and any attempts to change the data must be tracked and reported. TestLabs, Inc. wants to have the QC data maintained online for a minimum of one year. TestLabs, Inc. wants to have all the older QC data archived to offline storage which should be retrievable for reference. 2. TestLabs, Inc. wants to have the QC records monitored on a real-time basis as any time QC values exceeding the tolerances specified on the contract are detected; the manufacturing line must be shut down immediately. 3. TestLabs, Inc. wants to have this initiated automatically by sending a coded message through the network. TestLabs, Inc. will configure the manufacturing control system to monitor a specified TCP port for the message. TestLabs, Inc. wants to be able to configure the port value individually for each control system through its internal maintenance application. 4. The initial testing of TestLabs, Inc. has shown that QC data collection and monitoring will be resource intensive, with disk resources a special area of concern. 5. TestLabs, Inc. has one key concern for any development relating to QC collection, monitoring, or reporting which should not be allowed to interfere with data collection and monitoring and extraneous activity on TL-SR03 must be kept to a minimum. Topic 2, TestLab Inc (14 Questions) You work as the network database administrator at TestLabs, Inc. The TestLabs, Inc. network consists of a single Active Directory domain named TestLabs, Inc. All servers on the TestLabs, Inc. network run Windows Server 2003 and all client computers run Windows XP Professional. You have recently received instruction from the TestLabs.com network CIO to design an application solution for SQL Server 2005. You have recently started designing Reporting Services applications to meet the reporting requirements of TestLabs, Inc. The application you are busy designing will be used by the maintenance personnel to generate QC reports. During the course of the day you decided to use a database snapshot from a separate instance as the data source for reporting. You are required to identify the most appropriate source for creating the database snapshot. The solution you design should require minimum administration effort to maintain. What should you do? (Choose TWO.)

A. The new server should be configured as a Windows cluster with QC.
B. A new named instance of SQL Sever should be deployed on Q
C. A new default instance should be configured for database mirroring with Q
D. A new named default instance of SQL Server should be deployed on the new server.
E. The new server should be configured for log shipping with QC.


Display Answer

Answer: C, D

Explanation: In the scenario you should try and avoid interfering with QC operations by creating the new SQL Server instance on a new computer rather than a named instance. By making use of database mirroring you ensure that the currency requirement for ad hoc reporting are met and places a minimal load on the primary server TL-SR03. 1. TestLabs, Inc. wants to have their maintenance personnel generate detailed ad hoc QC tracking reports in chart and table report formats 2. TestLabs, Inc. has one key concern for any development relating to QC collection, monitoring, or reporting which should not be allowed to interfere with data collection and monitoring and extraneous activity on TL-SR03 must be kept to a minimum Incorrect