SQL Server A (The Base Level)
This server is a good candidate for a small-enterprise production server or a
development server for a small to medium MIS department. Frequently such
departments have grown into a need for client-server databases. This type of
machine would also handle replication distribution tasks very well for a
medium-sized enterprise.
One other point to consider for this type of server is using it in conjunction
with the Distributed Transaction Coordinator (DTC). With Microsoft's
increasing focus on the distributed server environment, you should consider
smaller servers in your plans. These servers can become valuable in supporting
your needs. Also, by separating processes across multiple servers, you can
include some legacy systems and save money.
With a little planning, even these smaller servers can be very useful in a
production environment. From an audit trail or tracking standpoint to a
centralized error-handling and task management scenario, these servers are
becoming a part of many enterprises. Learning how to integrate them into your
plans will make you look very good in the eyes of any management group
trying to resolve growth issues at lower costs.
Note:
You may want to look for a network task server application to help
manage your network resources and extend the life of your legacy hardware.
Base-level server configurations would typically involve an i486-based server,
possibly with 48 to 96MB of RAM. Even a smaller amount of RAM should
not pose a real problem for most basic environments. As long as the target
system meets the minimum RAM requirements for Microsoft SQL Server, you
will be fine. Keep in mind, however, that adding RAM to one of these servers
can have a great impact on performance and should be one of the first things
you consider. Also included in this group are slower Pentium machines. These
lower-speed Pentium servers will surely become commonplace in smaller
enterprises.
When specifying a server for your needs, always consider the environment and
the clients your machine will be servicing. Most desktops and workstations
being purchased today are Pentium 100MHz machines with 32MB RAM. If
you have a dozen of these Pentium workstations picking on a poor little
486/66MHz server, your SQL server might not be able to keep up with the
requests for data during peak periods.
When choosing a smaller server, you should keep the amount of available
RAM in the server as high as possible. Keep the additional tasks of file, print,
and gateway services off these types of servers. You may notice a slower
connection time with these lighter-weight servers. However, once you have
connected to one of these servers, you should get acceptable query response
times under a light load. This is usually exactly the machine a small office or
satellite location needs from a data server.
Example Configuration For Server A
Our example server in this class will be an Intel-based 486/100MHz processor
with 48MB RAM. Since this is an entry-level server, we will use a single hard
drive with 740MB of free space left after installing Windows NT. This server
will run Windows NT Server 4.0 (version 3.51 would do fine as well). The file
system for this server will be NTFS and not FAT for file-level security
reasons. This server will be running on a TCP/IP network with a PCI network
interface card and will run as a standalone server in a Microsoft Windows NT
domain. Take special care during installation of the operating system to make
the correct installation choices for the network. Accidentally installing NT
servers as domain controllers is easy to do and should be avoided.
Note:
Avoid putting any additional overhead on this type of server. Starting
an application or service can place a burden on the functionality of the
operating system or Microsoft SQL Server and bring your data services to a
crawl.
SQL Server B (Middle Of The Road)
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.
For more information visit www.nueva-design.com
