70-029 Designing and Implementing Databases with Microsoft SQL Server 7.0 Practice Exam

Demo Question 1.

You are the database manager for a manufacturing company. You are reviewing the mixture of Online Transaction Processing (OLTP) and Decision Support System (DSS) activities that access the same information in a new production monitoring application. The application is designed to capture production information from the shop floor in real time. The application must report production results, trends, and variances in a timely fashion. What should you do to maximize the overall performance of the production monitoring application?

A. Add additional indexes to support both OLTP and DSS requirements.
B. Create an OLTP SQL Server and a DSS SQL Server. Replicate tables from the OLTP SQL Server to a DSS SQL Server with the distribution database on the DSS Server. Index the tables on each SQL Server to support their respective activities.
C. Create an OLTP SQL Server and a DSS SQL Server. On the OLTP SQL Server, index the tables to support the OLTP requirements. Every 15 minutes, back up the transaction log from the OLTP SQL Server and restore it on the DSS SQL Server. Reindex the tables on the DSS SQL Server to support the DSS activities.
D. Create an OLTP database and a DSS database on the same SQL Server. In the OLTP database, create triggers that automatically update tables in the DSS database.


Display Answer

Answer: B.

Explanation: OLTP and DSS applications place very different demands on the SQL Server (see note below). It is therefore, for performance reasons, best to split the OLTP and the DSS applications to two different servers. Then use replication to replicate current information from the OLTP SQL Server to the DSS SQL Server. Note: Many applications fall into two main categories of database applications: • Decision Support Systems (DSS). • Online transaction processing (OLTP). DSS: To make business decisions, users need to be able to determine trends in sales quickly by querying the data based on various criteria. However, they do not need to change this data. The tables in a decision-support database are heavily indexed, and the raw data is often preprocessed and organized to support the various types of queries to be used. Characterization: • Read-intensive Should promote: • Denormalization of the database. Introduce preaggregated or summarized data to satisfy common query requirements and improve query response times. • Heavy indexing. OLTP: Transaction processing database applications are optimal for managing changing data, and usually have a large number of users who will be simultaneously performing transactions that change real-time data. Although individual requests by users for data tend to reference few records, many of these requests are being made at the same time. Common examples of these types of databases are airline ticketing systems and banking transaction systems. Characterization: • Relatively high volumes of transactions that modify data. • Short transactions tend and usually don't query large amounts of data. Should promote: • High normalization of the database. • Careful use of indexes. Incorrect answers: A. OLTP and DSS don't have the same indexing requirements. It would be impossible to meet both their requirements at the same time. C. You can't move data between two different SQL Servers by copying the transaction log. D. Using different databases on the same server could improve performance, but still both applications would use the same hardware. Therefore it is better to split the OLTP and the DSS applications to two different servers.