SQL Server 2000 Consolidation: a business case

Servers in the same group are good candidates for consolidating on a single SQL Server instance,

and similar consolidated groups could then be stacked on the same 64-bit server. Having profiled the

applications and server groups, we now have enough environment-related information to design the

consolidated servers. The next phase objective is to architect the consolidated server solution and

provide for the hardware specifications of the consolidated servers.

Phase 2—designing the consolidated servers

The consolidated server design should take into account every aspect of the new environment,

including administration and operations (together with monitoring), performance, backup and

recovery, chargeback (if necessary), disaster recovery, high availability, connectivity (crossing

domain and DMZ boundaries, linked servers, etc.) and security. Do not assume that the elements in

place for the current system can be used in the new environment; the proposed new environment will

be very different from the current one. With more databases and SQL Server instances per server in

the new consolidated environment, different operational rules and different management options will

need to be reconciled. This phase concentrates on instance planning and specifying the new

hardware.

Planning for instances

In this phase, a primary consideration is where to implement a single instance, and where to

implement multiple instances: Are databases to be consolidated into a single instance of SQL Server,

or will multiple instances of SQL Server exist, each with its own set of databases? High-availability

instances are mandated by the availability requirements of the applications. To effectively determine

the best type of instance for each group identified by the consolidation matrix in phase 1, it’s

necessary to look into the pros and the cons of each instance type. These are listed below:

• Single instance—Single-instance implementations generally require less maintenance than other

implementations, but issues such as downtime impact and resource contention will need to be

evaluated. Because a large number of databases are consolidated into a single SQL Server

instance, a large amount of downtime is impractical as it impacts too many users. If the single

instance is running on a cluster, failover time is increased as well. In a single instance, the

maintenance window is limited to the time window of the most available database.

Resource contention may pose a larger problem; one bad query in one database can impact every

user on the server. Also, if one database contains a large number of stored procedures, the

procedure cache will fill up, affecting the performance of other applications.

• Multiple instances—Considering the alternative of multiple instances, each multiple-instance sizing

for SQL Server is based on peak memory and CPU needs. The multiple-instance model is going to

be far from ideal, except in very rare cases. However, application compatibility, workload

isolation, availability requirements, and security requirements can be addressed using multiple

instances. One of the major concerns with multiple instances is per-instance resource allocation due

to heterogeneous workload. SQL Server’s sp_configure can be used to set various tuning options to

determine optimal settings for each instance. These options include:

– Affinity mask

– affinity64 mask

– IO_Affinity_Mask

– Cost threshold for parallelism

– Max degree of parallelism

– Max server memory

– Min server memory

– Set Working set size