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.