70-442 Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Practice Exam

Demo Question 1.

Bilco Engineering, Scenario BACKGROUND. Company overview Bilco Engineering is a government owned west wing military project which is responsible for creating, reverse engineering and modifying advanced weapon systems and is the primary source for weapons systems in locations throughout North America. Bilco Engineering has recently started contracting for weapons with three ammunition weapon stores named National Weapon Trade, Real-Weapon Technology and Weapon Skills Learning who each have their own administrative and instructional staffs and manages its own systems. Bilco Engineering knows that the all the stores offer traditional weapons training and Real-Weapon Technology with Weapon Skills Learning additionally offer online training and short-duration satellite demonstration classes in non-traditional locations. The employees at the three stores all come from different backgrounds, but the majority of the employees are adults improving job skills or looking to make career changes. TECHNICAL ENVIRONMENT: Bilco Engineering is currently consisting of only 20 distribution centers with each location having its own internal network that includes a database server running SQL Server 2005. Bilco Engineering has the internal network isolated from the Internet by a perimeter network whilst the Web servers are hosted in the main office in Atlanta. The Bilco Engineering Inc main network infrastructure is shown below: Bilco Engineering has additionally ensured that the Web servers are the only servers located in a private perimeter network whilst each location maintains is own inventory, with the database servers configured as federated database servers. Bilco Engineering has recently ensured that the Web servers host local copies of all offices' inventories in a custom designed database in a local SQL Server instance and civilian registration information which is also written to the local database servers. Bilco Engineering has configured the Atlanta office to supports the company's operations servers and two distribution warehouse servers. Bilco Engineering makes use of the main office for maintaining information about all weapons and defense systems information and a master registered civilian list. Bilco Engineering has identified the registered civilians as either visitors or testers-only. The Bilco Engineering military left wing project currently has one database server used as a weapons archival records server at the main office. The SQL Server used as the weapons archival records server is also used as a data source for data transform and load to a business intelligence server. Bilco Engineering Inc ahs made sure that all the internal network database servers are deployed as Active Directory member servers and that the servers in the Atlanta main office are configured for Windows authentication only. Bilco Engineering has decided to have their Web servers configured as stand-alone servers. Bilco Engineering has additionally configured the SQL server on the Web servers are configured for mixed mode authentication. TECHNICAL REQUIREMENTS: Bilco Engineering wants to have their left wing government military project company to be identifying the requirements for local office Web servers which should take place after local deployment to ensure each Web server maintains a current copy of the company's inventory without cost information. Bilco Engineering has also decided to configure using the simple recovery model is enough protection for the Web server database server instances. Bilco Engineering wants to ensure that all the deployed servers to any local network must make use of Active Directory user accounts in order to access to the server. Bilco Engineering also wants to have the user credentials to be protected against unauthorized disclosure. All of the left wing government military project company Bilco Engineering database servers are configured with the hard disk configuration shown below: 1. 80 GB IDE hard disk as drive C. 2. 80 GB IDE hard disk as drive D. 3. 270 GB SCSI RAID 5 storage unit as drive E. Bilco Engineering has recently announced that they require having all the company local database servers to automatically report serious errors to the Atlanta main office and immediately paging all IT staff members is imperative if and when errors occur. Bilco Engineering has also expressed concern that the IT staff requires periodic information about selected resource use. Bilco Engineering has decided that they will make use of replication on the local database servers for provide inventory updates to the Web servers whilst ensuring that any orders generated on the Web servers are submitted to the local database servers using the locally deployed Web services configured at each local network. Bilco Engineering has planned to have all the Web servers moved to the local networks as soon as possible. Bilco Engineering will have the Web servers then moved to the local offices after which the local offices will use the same network configuration currently used in Atlanta. Bilco Engineering has the database servers shown below at the Atlanta main office: 1. BILCO-SR01 2. BILCO-SR02 3. BILCO-SR03 4. BILCO-SR04 Bilco Engineering makes use of BILCO-SR02 to contain the information about the weapons systems records, secure employee records, civilian credit information, and other sensitive data. Bilco Engineering additionally makes use of BILCO-SR01 which maintains the summary civilian records, purchase order records, consolidated civilian orders, and other information needed for daily operations. Bilco Engineering makes use of BILCO-SR03 and BILCO-SR04 as distribution centre servers which are configured like the servers for the other local office database servers. Bilco Engineering has recently announced that the server BILCO-SR01 will have one database named WeaponTech. BILCO-SR01 is configured with each filegroup containing a single read-write file. Bilco Engineering has decided that the WeaponTech database shall be configured for the full recovery model and backups are run as shown bellow: 1. Bilco Engineering runs a full database backup is run on the first Saturday of each month. 2. Bilco Engineering runs a full backup of the Primary filegroup is run on Sunday night. 3. Bilco Engineering runs a full backup of the Secondary filegroup is run on Wednesday night. 4. Bilco Engineering runs a differential backup of each filegroup is run before start of business each morning. Bilco Engineering has recently revealed that the Atlanta main office database servers are required to be available at all times whilst potential single points of failure should be minimized. BUSINESS REQUIREMENTS: 1. Bilco Engineering has planned to have the detailed civilian information is maintained locally by each office whilst the summary civilian information and sensitive information such as credit card information is maintained at the Atlanta main office. Bilco Engineering has recently stated that each civilian should be registered with and appears in only one local database. Bilco Engineering requires having the users be able to access combined detailed civilian information through a distributed view named Sales.v_All_Civil. 1. Bilco Engineering always makes use of the Web Server which generates orders making use of a stored procedure. Bilco Engineering has additionally stated that all the orders be posted to the database server in each office's internal network from the Web servers. Bilco Engineering will also be responsible for sending orders to Atlanta for consolidation whilst making it possible to easily identify the source office for each order. Bilco Engineering has recently expressed concern about orders for the current quarter being kept separate from orders for previous quarters. 1. Bilco Engineering has also recently stated that their distribution center inventory is maintained through just-in-time ordering with most inventories coming from local vendors. Bilco Engineering has insured that their purchase order stocking points are determined automatically, and vendor purchase orders are placed from the Atlanta main office. The local branch offices are authorized by Bilco Engineering to order to fill spot shortages and for specialty items that require pick-up from the vendor. 1. Bilco Engineering has also recently stated that their company policy specifies that purchase orders can be kept locally for up to 10 days, but must be kept until the order is either received (and given a status of Closed) or canceled. Bilco Engineering wants to ensure that any order not received within 10 days should be canceled whilst the archival purchase order records are used to update BILCO-SR02 SQL server. 1. Bilco Engineering wants to have the replication administered locally from the archival server whilst requiring the local offices to have control over specific aging requirements for order archives. The Bilco Engineering local offices sometimes require changing the length of time that they hold purchase orders in cases when there is a dispute with a vendor over charges. 1. Bilco Engineering has also recently announced that all periodic reporting requirements rely on data maintained in the Atlanta office. 1. Bilco Engineering has recently decided to deploy local Web servers at each location and is considered a priority project. Bilco Engineering wants to ensure when this happens that each office will be responsible for posting its own orders. Bilco Engineering additionally wants to have the orders delivered to Atlanta nightly when this happens. Topic 3, Bilco Engineering (13 Questions) You work as the network database administrator at Bilco Engineering. The Bilco Engineering network consists of a single Active Directory domain named Bilco Engineering. All servers on the Bilco Engineering network run Windows Server 2003 and all client computers run Windows XP Professional. You have recently received instruction from the Bilco Engineering.com network CIO to start designing performance tuning a database and database application. You will be responsible optimizing the indexing strategies for Bilco Engineering. You are currently in the process of optimizing application performance for applications that reference the Weapons database. You have decided to restart the stop and restart the SQL Server service after hours. Bilco Engineering has asked you to collect index statistics from selected dynamic management counters after the normal day's operations. You decided to make use of the information to create new nonclustered indexes for the tables accessed by applications. You are required to collect information about missing equality columns and missing include columns whilst selecting which dynamic management views provide the information required. What should you do? (Choose TWO.)

A. You should make use of the sys.dm_db_missing_index_details.
B. You should make use of the sys.dm_db_missing_index_columns.
C. You should make use of the sys.dm_db_missing_index_group_stats.
D. You should make use of the sys.dm_db_index_usage_stats.
E. You should make use of the sys.dm_db_index_operational_stats.
F. You should make use of the sys.dm_db_missing_index_groups.


Display Answer

Answer: A, B

Explanation: You should remember in the scenario that when we create a new index, the CREATE INDEX statement list equality columns first then inequality columns and that any columns that are listed as included columns should be added as included columns. 1. Bilco Engineering always makes use of the Web Server which generates orders making use of a stored procedure. Bilco Engineering has additionally stated that all the orders be posted to the database server in each office's internal network from the Web servers. Incorrect