Much has been written about large scale database deployments, and the later
versions of SQL Server – including the upcoming version of SQL Server 2005 –
fit well into VLDB (very large database) configurations. However, for every
VLDB setup there are dozens if not hundreds of small (under 500 megabytes) to
medium (500 megabytes to 10 gigabytes) database deployments. This is an often
overlooked element of the SQL Server community; there are many fine books and
other publications that focus on VLDBs but few if any that are geared toward
those who support much smaller databases. To that end, I have assembled a
broad view of using Microsoft SQL Server to deploy less-than-enterprise level
databases.
Availability and Disaster Recovery
Key elements to any SQL Server deployment include performance,
availability, and disaster recovery. The architect of the database solution
must ask the following questions:
- How much performance do I need?
- How much downtime is considered acceptable?
- How much data can we afford to lose?
The answer to these three questions will be the driving force behind the
architecture decisions. It has been my experience that many smaller database
implementations do not require the level of availability or performance as
larger systems. For example, I work daily with an internal trouble ticket
tracking system that is available throughout the day, but is rarely if
ever accessed outside of normal weekday business hours. Another ticket tracking
system I developed is accessible from the Internet via a web application and is
continuously available, but has a minimal number of concurrent users.
Neither of these systems requires high availability, and neither has
performance demands to necessitate high-end hardware or software; since they
are not core to our operations, it would be an unnecessary drain on our very
small budget to pour money into enterprise software or expensive metal for
these applications. By the way, both systems have been running well with
excellent performance and very little downtime.
Choosing the Right Software
Critical to a successful small implementation of SQL Server is determining
which version of the software your deployment will require. SQL Server 2005
comes available in three different flavors for production use: Enterprise,
Standard, and Workgroup. The Enterprise version is the all-inclusive release,
and includes the full suite of features including high availability, business
intelligence, and a rich set of client tools. The Standard version includes
many of the same features, but lacks several of the enterprise-level elements
such as database partitioning, online indexing and restore, and other advanced
BI and high availability features, and has a limit of 4 CPUs. The Workgroup
edition is limited to 2 CPUs and 3GB of RAM, and does not include Notification
Services, Analysis Services, or Integration Services, all of which are
extremely useful but may be of little benefit on smaller implementations.
Fortunately, the core database engine is identical on all versions,
allowing for maximum throughput regardless of the version you choose. If your
organization’s policy or budget requires the use of SQL Server 2000, it is
available in Enterprise and Standard edition, which has similar limitations
between the two versions as its successor. Microsoft's website can
provide more detailed information about product features by version for SQL
Server
2005 and 2000
.
In choosing the correct version of SQL Server for your small implementation,
don’t forget to consider SQL Server Express, the free version of SQL Server
2005. Although it lacks the high-availability features and the rich GUI client
of its more glamorous cousins, the database engine in the Express version is
identical to that of the costlier flavors. The predecessor to this
product was MSDE (Microsoft SQL Desktop Engine), which had a throughput
throttle of five simultaneous queries; fortunately, no such limitation exists
on SQL Server Express. The Express edition also has a scaled-down
administrative tool called Express Manager, a welcome addition since MSDE did
not include any GUI tools. In addition, like its predecessor, SQL Server
Express may be freely redistributed to end users and clients after proper
registration with Microsoft. For many small to medium sized database
deployments that do not require high availability, the new SQL Server Express
may be the perfect low-cost solution.
Choosing the Right Hardware
Extreme databases require extreme hardware, and I've had my share of both.
But many database implementations have far less stringent requirements.
The last two new databases I deployed were on less-than-enterprise hardware; in
fact, one was set up on a converted desktop machine. The hardware
requirements for installing SQL Server 2005 are well within reach of any one or
two year old desktop machine. For the record, all commercial versions of SQL
Server 2005 require at least a P3/600 processor and 512MB of RAM, while
the Express edition requires only 192MB of RAM. Even when operating at or
just above the
minimum hardware requirements , I have found that query responses are
more than adequate in low- to moderate-volume systems. SCSI drives are
great, but a set of good 7200RPM IDE drives will yield good results in less
demanding systems. Further, it is possible to configure a software RAID
setup on IDE drives within Windows 2000 or XP (see
Darrell Brown's how-to on this) for increased disk performance and
fault tolerance. In fact, you could configure a fully capable small- to
medium-sized database server with just the spare parts you find lying around
your shop.
Backup
Many organizations choose to use third-party tools to back up VLDBs, and with
good reason: an online backup operation on a large database can take several
hours using the integrated Microsoft SQL backup utility. However, for
small to moderately sized databases, the backup tools within SQL Server
generally work fine. I am able to back up the aforementioned ticket
tracking system, currently somewhere around 500MB in size, in under 30
seconds. Further, one can configure a remote network location - for
example, a share folder on a desktop machine or small backup server - as a
backup storage target to avoid using costly and troublesome tape systems.
Conclusion
There are many cases in which a high-end server and a full-blown Enterprise
copy of SQL Server are essential. But for many small companies, or even
large companies needing a small database deployment, Microsoft has
provided some scaled-down options - including one that is free - for
implementing SQL Server, which may be deployed on relatively minimal
hardware. There is a correct tool for every job, and for many smaller
tasks, these tips could provide just the right solution.
