ID Number: Q81864
1.10 1.11 4.20
OS/2
Summary:
This article applies to SQL Server versions 1.x and 4.2.
Several technological factors have made it possible to place large
databases on Microsoft SQL Server. As these trends continue, a
question often arises concerning the maximum database size Microsoft
SQL Server can support. This article addresses some of the factors
that govern this question.
More Information:
The most important point to remember is: There is no single number
that defines a SQL Server database maximum size limit that, if not
exceeded, will ensure a successful information system. There are
physical limits to the maximum database size SQL Server can support,
but these limits are so large that other practical factors come into
play long before these constraints.
SQL Server versions earlier than 4.2 support up to 10 physical
devices. Assuming the maximum available size of current-technology
5.25-inch hard disk drives, this equates to about 16 GB (10 x 1.6 GB).
Formatted capacity may be slightly less.
Feasibility questions concerning SQL Server database capacity should
include a number of areas besides these physical limits. The following
areas should be given special consideration:
- Backup: The highest-performance backup medium in common use is 8 mm
helical tape. These drives typically hold about 2 GB per tape, and
have a backup rate of about 130K per second. Backup of a 5 GB
database requires 11 hours and two tape changes. The database must
be shut down for this period unless the online backup facility is
used. If online backup is used, an additional 5 GB of intermediate
disk or network file storage will be required. SQL Server 4.2 can
perform online backup directly to tape, eliminating the
intermediate storage requirement.
- Transactional throughput: An estimate should be made of the
maximum transactional throughput rate required to support the
prospective applications. Without this, database size requirements
can be satisfied, but not response time requirements. SQL Server has
been benchmarked at roughly 10 TP1 transactions per second on a
386/33 machine. Higher performance hardware will be proportionately
faster.
- Sorting rate: The number and type of queries involving sorting
should be evaluated. Some applications require frequent sorting of
large tables. It is possible to satisfy database size and
transactional requirements, and still be unable to provide the I/O
bandwidth necessary for sort-intensive applications. Test runs
involving typical queries should be made to empirically determine
expected performance. As a rough rule of thumb, SQL Server has been
observed to sort about 700 rows per second on a 486/33 machine,
with a table consisting of 1 int, 2 char(15), and 1 image(16)
columns.
- Scanning rate: The number and type of queries involving non-indexed
accesses should be evaluated. As table size increases, the variance
between indexed versus non-indexed access will increase. For
example, on a 10,000 row table, this could be .5 second versus 4
seconds. For a 2,000,000 row table, this will be about 1 second
versus 12 minutes.
- Recovery: The maximum acceptable down time that you specify should
be evaluated. When a fatal hardware disk error forces recovery from
backup, the down time necessary to restore a large database should
be considered. Restoration of a 5 GB database could take about 15
hours.
Microsoft SQL Server is capable of handling large databases that once
required a mainframe. However, reliance on database size alone as an
indicator of SQL Server's ability to support a large database is not
wise. A number of factors should be carefully and thoughtfully
evaluated.