SQL Server 2000 Consolidation: a business case

Note: These options are classified as advanced options by Microsoft, which states that “Advanced
options are those that should be changed only by an experienced system administrator or certified
SQL Server technician.” These options should only be changed based on hard performance data
and thorough testing.
• High-availability instances—Certain mission-critical applications, such as the inventory application
for ConsolidateMe.com, require 24x7 uptime. These mission-critical applications will require
planning for high availability. When considering clustered solutions, carefully consider whether a
single node can handle its own load plus the load of additional databases planned for failover from
other nodes. Just because Windows 2003 may support up to eight nodes, this does not necessarily
mean it would be a good idea to implement a potential high-load solution without careful attention
to availability factors (such as crowding in MemToLeave memory space on 32-bit servers).
Considering the respective pros and cons of the single instance and of multiple instances, groups with
similar workloads, availability requirements, and security requirements are candidates for single-
instance consolidation. The primary reasons for considering multiple instances are workload isolation,
security, and accommodating multiple application compatibility. Once the instance type has been
determined for each group, similar instances can be considered for stacking onto the same SQL
Server instance. Once the number of servers and the SQL Server instances for each server have been
determined, there is enough information to determine the hardware specifications for each server.
Determining hardware specifications
Moving forward with the consolidation effort, it is necessary to determine the best hardware
configurations for the consolidated servers. This involves determining the servers to be consolidated
and forecasting expected performance and business growth. The performance measurement data
collected during phase 1 provides for server sizing, capacity planning, and optimal server
configuration. Server sizing and capacity planning studies are conducted on a couple of hardware
models. The outcome of this phase is the optimal and most robust hardware specification for the
consolidation effort.
A summarized example of a data sheet from this phase is as follows:
Table 9. SQL Server database consolidation value proposition comparison sheet
Hardware
configuration
Software configuration
Number
and size
of DBs
Number
of users
Batch
requests
per
second
Transactions
per
second
Response
time
gains (%)
Total
system
cost
TCO
gains
(%)
8 CPUs,
32 GB RAM
Windows Server 2003
Enterprise Edition 64-bit,
SQL Server 2000 Enterprise
Edition 64-bit
100 DBs,
2.5 TB
2500+
16 CPUs,
32 GB RAM
Windows Server 2003
Datacenter Edition 64-bit,
SQL Server 2000 Enterprise
Edition 64-bit
500 DBs,
5.6 TB
5000+
32 CPUs,
32 GB RAM
Windows Server 2003
Datacenter Edition 64-bit,
SQL Server Enterprise
Edition 64-bit
2,000
DBs
10 TB
10,000+
22