System Requirements

3 4

To determine the requirements for an entire system, you must look at the function of the system. Once you have an idea of the purpose of a system, you can start looking at ways of using one or more computers to serve these requirements. You might find that one computer is sufficient or that you need many computers to achieve your goals. This chapter looks at those goals in general. Chapters 5 and 6 will more specifically describe how to design and configure the hardware.

In this chapter, we will look at system functionality and at what configuration decisions can be made based on that functionality. We will begin by looking at the types of applications that a system might be serving before looking at how the service level requirements can be defined.

System Application

What good is the SQL Server database without an application? Well, unless everyone in your company is an expert in SQL and enjoys putting together complex queries and viewing data in tabular form, it is not of much use. The application facilitates the use of SQL Server. Some applications make it clear that you are accessing a database, and other applications completely hide the fact that there is a database at all. In any case, it is important to design the application to easily provide users the service they desire, in a timely manner. In many cases, a slow, inefficient application causes frustration for end users. Eventually, if an application doesn't meet the customer's needs, the customer might find another company to deal with that provides better service.

Application types vary based on the function that they are serving. In general, there are three main functions: online transaction processing (OLTP) systems, decision support systems (DSSs), and batch processing systems. These functions have different requirements and might use drastically different types of applications.

OLTP

OLTP systems are characterized by many users accessing online data simultaneously. In addition, and probably of more importance, these users are waiting for a response from the system. OLTP systems take a variety of forms, such as the following:

• Online purchasing These applications are widely used because Internet commerce is growing rapidly. When purchasing products over the Internet, users often experience a delay while data is transmitted, as well as while it is retrieved and processed. By minimizing the database access time, you improve the entire transaction time.
• In-store purchasing When the cashier at a store swipes your credit card, that transaction is accessing a database. This transaction might pass through many systems before it accesses a database.
• Business systems Every company has some sort of application that is accessing databases. It might be your accounts payable system, your purchasing system, your human resources system, your inventory control system, or a variety of other systems. These might be written as intranet applications, in a language such as C++ or Microsoft Visual Basic, or using a fourth-generation language (4GL) tool. In any case, the data eventually comes from a database.

All OLTP systems have one thing in common. The user is waiting on a response from them. It is your job to design a system that can adequately service user requests in the specified response time.

DSS

Decision support systems assist the user in making important business decisions by providing a specific result based on a business question. Here are a few examples of business questions that might be answered by a decision support system:

• Who are the top salespeople in each district; what are their best-selling products?
• What time of year does each product sell best?
• What was the result of lowering the price of an item?
• What is the average commission for salespeople by district?

Decision support systems are different from OLTP in that the user of a decision support system expects the result of a complex query to take a significant amount of time to be returned. Decision support queries can take anywhere from seconds to minutes to hours to complete. This does not mean that response time is not important, but some compromises can be made between throughput (performance for all users) and response time (performance for each user).

Batch Processing Systems

Batch processing systems process offline jobs that do not have any end-user component. The following tasks are typical jobs for this kind of system:

• Daily refresh of data Some decision support systems require data to be reloaded every night, and batch processing systems often automate this task.
• Data transformation This task is similar to data refresh, but the data is transformed.
• Data cleansing This task accomplishes things like removing duplicate accounts from the database.
• Offline billing This task could consist of performing nightly billing of customers.

Batch processing systems typically have no users waiting for their jobs to finish, but they also typically have a certain time frame in which they must conclude the tasks. For example, overnight loads of data cannot overlap morning logins.

Requirements

As you can see, it is important to get an idea of the types of applications that the system will need to support before you design the database server. Without knowing the requirements, you can't effectively design the system. As you will see later in this chapter and throughout the book, each system has its own requirements and properties. These requirements will prompt certain design decisions such as how many computers to use and whether you need distributed transaction processing.

Service Level Requirements

Among the most important factors going into the design of your system are the service level requirements. The service level requirements are usually specified in a service level agreement (SLA). The SLA is made between the supplier (the CIO) and the customer (the users). Whether a formal SLA is created really depends on who the customer is and how the services are provided.

For example, if you are contracting with an outside firm for database services, you will almost certainly have an SLA. In fact, this SLA is most likely a legally binding contract. If you are providing services to other in-house departments, you might not have a legally binding agreement. In any case, an SLA will spell out the level of service that is guaranteed to the customer. It might also include penalties if these service levels are not met. SLAs are described in more detail in Chapter 6.

When a level of service is guaranteed, you must design your system to handle that level of performance. A few areas that influence that design are discussed here: performance, capacity, and uptime.

Performance

One of the most important aspects of an SLA is the specification of the minimum acceptable system performance. A typical SLA will include a chart of various transactions that an application supports, a minimal service time for 100 percent of the transactions, and an optional, more stringent minimal service time for 95 or 90 percent of the transactions. For example, the SLA might specify that 90 percent of "add new customer" transactions must finish within 2 seconds and that all of them must finish within 3 seconds.

The provider of the services must maintain this level of performance, or a penalty might be involved. As a DBA, you are responsible for administering the system so that these service levels are maintained. In addition, you must anticipate when problems might begin to occur and add capacity to the system as necessary. You meet these responsibilities by constantly monitoring the system and periodically performing capacity-planning exercises.

Capacity

In addition to containing performance requirements, the SLA might also contain capacity requirements. Capacity of the system falls into several categories, such as disk space capacity, user capacity, network connection capacity, and so forth. It is your job to maintain the system so that it does not run out of capacity.

You maintain the capacity of the system by continually monitoring the system and taking action if it appears to be nearing a threshold. As you will see in Chapter 6, a certain amount of disk space should remain available at all times for workload spikes. If this buffer begins to be used frequently, additional resources should be added to the system. This is also true of CPU usage.

Uptime

In addition to specifications for performance and capacity, specifications for system uptime are usually included in an SLA. Requirements for system uptime demand that the system be available for users during specified time intervals. The SLA might specify 99.9 percent uptime seven days a week, 52 weeks a year, or it might specify uptime for eight hours a day, five days a week, depending on your company's needs. You are responsible for developing a plan for routine backup and recovery, as well as a disaster recovery plan, in order to maintain the required uptime.