Read an Excerpt
Chapter 1: Server Hardware
Systems administrators face many challenges today in setting up a new server, regardless of the operating system (OS), application, and hardware manufacturer. An often difficult task is to adapt the installation to the needs of the environment. Administrators should look at as many factors as possible prior to setup to ensure that the machine is configured to the requirements of the users and the application load placed on the server. This approach will help spare administrators from having to upgrade the equipment almost immediately when their performance expectations are not met. Understanding the basic components of server hardware is important for planning your first installation, reinstallation, or upgrade. The following pages will help clear the way and help you choose or configure your server hardware.
Recommended System Configurations
There are many aspects of a system configuration that should be considered. You need to think about how much of a load the server needs to be able to handle. You also need to think about what part of the system will most likely be your bottleneck. And unfortunately, you'll probably need to keep an eye on the budget as well.Microsoft's recommendations for your system and what most administrators have found to be a more realistic configuration for your server are quite different. Keep in mind that Microsoft's recommended minimum configuration is just that, the bare minimum that SQL Server will install on. Microsoft's recommendations should be implemented with care. Each environment is unique and is rarely a fit for minimums or guidelines. Likewise, other recommendations should also not be followed blindly. Recommendations are intended to give you an idea of where to start and should not be considered the end all solution for choosing your system or platform.
The system requirements for installing Microsoft SQL server are actually very easy to meet, and often lead the administrator into a false sense of security with regard to how well the server will perform. Table 1.1 contains Microsoft's minimum system requirements. Remember, these are the minimum system requirements.
Table 1.1 Microsoft's minimum system configuration....
...The minimum CPU recommendation of an Intel-based Pentium 166 is usually a poor choice for a production machine. This kind of machine should only be considered for a development-type environment. Even then, it should only be used for very light loads. The preference is to use a Pentium II 400MHz or equivalent, at minimum. But depending on the application, processor speed will probably be much less important than memory or disk subsystems.
That's not to say that you should discard your current machine or scrap your plans for a cheaper alternative. Budgets and real-world requirements often do not allow a top-of-the-line machine for every project. The idea is to put your best performing machine as well as your money where it will do the most good. If you are using existing hardware for your data server, take a good inventory of what makes the target machine tick. Know the specifics of the disk access time and memory configuration. Benchmark the machine wherever possible to get an idea of how well it is performing against others in the same class. You might find a less expensive alternative to the planned configuration. Sometimes, a simple memory upgrade is good enough to get a server performing reasonably well.
The minimum RAM recommendation of 32MB for Standard Edition and 64MB for Enterprise Edition is also usually a poor choice for a production machine or even a development machine. Development servers with a very light load should have a minimum of 128MB of RAM, especially if you are running Windows 2000. Actually, the minimum amount of memory in any machine needs to be 128MB for Window NT 4 and 192MB for Windows 2000. You would be hard-pressed to even try to install Windows 9x on a system with 32MB of RAM. RAM is a very performance-sensitive item that can make a significant difference and is relatively cheap and easy to add to a system. Most newly installed production servers today are configured with 256MB to 2GB of RAM with the occasional 4GB machine. Current hardware and specific operating systems can even handle up to 16GB, whereas Windows 2000 DataCenter Server will recognize up to 64GB of RAM. DataCenter is not likely to be a widespread solution due to cost, but rather a hosting solution for only the larger installs.
Always specify more RAM than you think you will need for the server. Have at least 256MB of RAM for a production server. This amount of memory gives you plenty of space for Microsoft SQL Server and a good amount of data cache and procedure cache for performance. You cannot go wrong by adding RAM. Even on an old Pentium Pro processor, giving it additional RAM will allow it to run very well.
Adding RAM to a server is almost always the best starting point in improving performance. However, don't forget about multiprocessor machines. Machines that can hold four or more processors normally have a higher I/O bandwidth as well as improved upgrade paths. As important as RAM is-and it is very important-you also need to take a good look at your drive subsystem. Databases are by nature very I/O-intensive, and your drives can quickly become the bottleneck of the system.
With constant changes in hardware, any server purchase you make today will likely be outdated by the time you finally get your system up and running. This can be very frustrating. Consequently, you should buy servers with good expandability and lots of options. The potential expansion allows you to keep up with changes in the industry and react to changes in software requirements over time. You may want to purchase brand-name servers so that you don't invest money in machines that have poor technical support or that might not be supported the following year. Always check the Windows Hardware Compatibility List (HCL). This is a must. Check each component, from CPU to disk controller, when needed. This ensures that you will not have an operating system problem with the server you are configuring.
NOTE: You can check the current HCL online at www.microsoft.com/hcl/default.asp to get up-to-date information on available options.
You may want to configure your servers with a RAID disk subsystem for your data, which will be covered in detail later in the chapter. When reliable access to data is critical, you should require some sort of RAID configuration for the data to reside on. With the capability of Microsoft Windows NT to implement RAID at the operating system level, this is easily accomplished with even a limited budget. But keep in mind that a hardware solution is always going to perform much better than a software solution.
You may also want to try to keep the operating system and program files separate from the data and log files. Place these files on a separate disk and controller from the data files as well as the log files and mirror the disk when the budget allows. This provides the maximum amount of protection from hard drive failures while keeping performance at the highest possible levels. The number of disks in the RAID 5 array can be as few as three and as many as the disk subsystem can support.
Not everyone can afford this type of configuration for his or her hardware. Nevertheless, it is highly recommended for a fault-tolerant data server. If your budget is tight, cut the hardware mirror and RAID controller out of the plans for the operating system drives and transaction log drives. The data drive RAID subsystem should be the last system you relinquish. Use the built-in, software-driven, RAID option on Microsoft Windows NT servers only as a last resort. You should use this feature and set up your own fault-tolerant disk system for storing data only if no other option is available.
Given the many ways Microsoft SQL Server can write backups of databases to shared drives on other machines, a tape drive for backups is not required on the data server as well. However, this can be a nice feature if you run around-the-clock operations and need to keep performance at high levels 24 hours a day. Moving the backup software and hardware load to another machine is in keeping with the distributed-load concept, which is becoming popular in many enterprises today. You will find techniques in Chapter 5 that will allow you to keep the tape drives off your database server. If you must put your tape drive in the database server, remember not to attach it to the same controller as the data and log devices. (This could slow down performance unnecessarily.)
Invest in a good network interface card-a card with as much bus speed and bandwidth as possible. If you are setting up a cluster, it is recommended that you put two network cards in the server: one for user connections and the other for cluster communications. Standard 100BaseTx Bus Mastering network cards are considered a standard part of servers. Some administrators like to connect to their servers using a switch instead of a hub. Packet switches for heavily used servers are a must. They allow you to connect at full duplex as well as limit the bottleneck of slow networks. If you are setting up a large server environment, you may even want to consider Gigabit Ethernet or some other high bandwidth option for your backplane.
Most of the time you won't need to go overboard on the CD-ROM because you may rarely use it for production purposes. Try to use whichever speed comes with the server. You definitely want to watch where you connect your CD-ROM drive. Most brand-name servers are shipped with IDE CD-ROMs. This keeps the CD-ROM off the SCSI data channels. Placing the CD-ROM or other slow devices, like tape drives, on the same channel as the data slows the entire bus to a crawl. So, if you are going to install a tape backup drive directly on your server, put it on a separate bus from the data.
There is no need for expensive monitors, video cards, or sound cards. This is where you can save your money and buy more RAM. Keep it simple. Video memory is not an issue on a data server. And there is no reason for a sound card in a data server unless you want to hear the server say, "I am slowing down now. Please let your users know. Never mind, they already know." If you follow the recommendations in this book, your server will be just a data server, which is best for your users.
Verify that you have full functionality on the server before installing Microsoft SQL Server. One of the hardest things to do is troubleshoot problems when you have no idea what is working and what is not. Assume nothing. Always use the break-it-down-into-the-simplest-form approach in troubleshooting. If you cannot get out on the network, no one will be able to connect to your server for data.
Consider using a redundant power supply for your unit. Keeping the data available should be any systems administrator's primary focus. Use an uninterruptible power supply (UPS) that is reliable, and test it occasionally. An untested backup strategy is just that: untested. If you think you're under pressure now, wait until the backup won't restore and the system your boss has invested thousands of dollars in does not work. See Table 1.2 for an example of what is considered a minimum system configuration in today's production environment....