Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

This server is a good medium-duty server. Usually at this point, many companies begin to consider multiple-processor machines--even if they don't yet actually purchase them. At this stage in a company's growth, purchasing machines that support multiple processors as expansion options is a smart move.

Take the time to ensure you can expand your server down the road. By definition, databases will always grow, and there are few things that you can do to prevent a good system from slowing down over time with increased use. Making good purchasing decisions is as important as hiring good people to fill critical positions in your company.

In addition, spending money on as current a processor architecture as possible at this level is a good idea. Take the time to look at what machine will supply the most processing bang for your buck.

Check the results of independent tests for different machines. You'll find that many show that Intel-based servers are closing the performance gap with other architectures--and are less expensive and cheaper to support. I have had very good luck going with the established name brands. I also insist on good technical support.

The CD-ROM accompanying this book includes a few excellent white papers from Compaq and other sources. These white papers can be found on Compaq's Web site at www.compaq.com or on any of the appropriate sites listed in the documents themselves.

Regardless of the make of your server, these white papers do a very thorough job of helping you configure it to run well in a production environment. (In Chapter 12, you'll find more on using online sources to answer your configuration questions.) There is a wealth of information out there for you to use--some of it is good and some is junk. You should take the time to learn what is and is not valid advice.

Note:
Configuring data servers can be a difficult task. Adding one nonconforming task or application can throw your performance into a downward spiral. Always begin by breaking down the problem into simplest-possible form. Then look for the obvious problem. Never assume that something is working fine.

When I am asked to recommend a server, I try to get a good feel for how the server is going to be used in the future. With this middle-of-the-road class of server, you must start to look at fine-tuning your hardware choices for maximum performance. It is common to have multiple drives in these servers; this should be considered, since multiple drives will increase your system's fault tolerance (an important goal of any database administrator).

If you cannot afford to purchase all of the components for a fault-tolerant server at one time, you should plan their addition as budgets permit. In many scenarios in this range, it is a good practice to propose during the bid process some sort of plan to upgrade or migrate the server hardware as the use and load of the server matures.

Management does not view this approach as overspending but as good business planning. In most of the system consulting I have done over the last few years, I have found that including estimated costs for migrating and improving the server over time actually helps sell the idea of purchasing hardware and software to prospective clients. These estimates add value to your recommendations, both short- and long-term, and they give decision makers the realistic information they need to plan and budget IT resources.

A Word On RAID

A middle-of-the-road server should incorporate the ability to add disk space as painlessly as possible. Be leery of using the Microsoft Windows NT Server software implementation of RAID as a solution for your disk subsystem needs. Although this is better than not having a fault-tolerant disk system at all, a hardware-based solution is preferable.

Using a hardware-based RAID system relieves your server from the burden of disk I/O while decreasing the amount of CPU processor time spent handling disk activities. Some of the better RAID< systems have some very impressive throughput with high capacity and built-in caching. Some even support hot swap disks that, in the event of a failure, allow you to replace the bad drive in your system with an off-the-shelf disk without ever having to power down your server. Not all servers justify this kind of expense, however. Use your best judgment and choose a disk subsystem with great care.

If you must use the software-level implementation of RAID, I suggest starting with a mirrored disk configuration. By choosing the mirror configuration, you will keep performance up as much as possible while maintaining some kind of tolerance. For more information on how to configure Microsoft Windows NT for the software implementation of RAID, see your Windows NT documentation.

Example Configuration For Server B

Let's take a moment and look at an example server configuration in this class. Typically these servers have up to 128MB RAM and are configured with RAID disk subsystems. For our example, we'll use a Pentium Pro 200MHz single-processor server with 128MB RAM. We will again be using a good PCI network card for maximum throughput and two disk drives. The first drive will be for installing the operating system and program files; the second will be for data. Due to costs, we are not going to go with a RAID system. Instead, I will use a 1.2GB hard drive for the C: drive and a 4.3GB hard drive for the D: drive. I will use two separate controllers for the hard drives for maximum throughput of data.

This system will be a great candidate for adding a mirrored disk down the road as funding becomes available for adding fault tolerance. Choose a good disk controller that supports multiple disks or a system that will support the addition of a disk and controller for establishing a mirror--or, in the case of adding the extra controller, for duplexing the data drive.

 SQL Server C (The High-End Beast)

Because of the prohibitive cost of a quad-processor or higher monster server, most small- to medium-sized companies do not purchase a high-end server. These machines, of course, do provide some incredible numbers when it comes to throughput and horsepower. Having recently rolled out a major system utilizing a Compaq Proliant 200MHz dual-processor Pentium Pro system at the heart of the data services, I am impressed with the performance of both Microsoft SQL Server and Windows NT on this platform. Despite their high cost, multiple-processor machines are of great interest to the majority of students in my classes. Given the cost difference between these high-end Pentium machines and the mainframe/RISC machines, as well as the performance of Microsoft SQL Server, the multiple-processor machine is quickly becoming a hot topic. Many companies have purchased expandable machines and are looking for answers on how to take advantage of this architecture. As we explore topics later in this book, I will describe how efficiently these monster machines can run your queries.

Example Configuration For Server C

Although Microsoft SQL Server runs on other hardware (Alpha machines, for example), it must run on the Microsoft Windows NT operating system. I am focusing on Intel platforms because of their cost-to-performance advantages over other hardware platforms. These machines should be running with between 128MB and 256MB of RAM. Our example server in this class will use 256MB RAM. In addition, RAID level 5 subsystems are usually the order of the day for these machines. These servers usually have between 8 and 32GB of hard drive to work with. Redundant power supplies and a replacement drive on the shelf are musts when these systems go online.

These high-end servers are not something that the average programmer should jump into. Great care should be taken to configure these machines to suit their final environment.