Virtualization vs Consolidation on a server platform

There will be a common question in the newsgroups asking about number of instances that are supported by SQL Server (2000 & 2005). The answer is do you really want to run most of the instances that are important to you business on one server, not really on high availability aspects.

As you are aware SQL Server 2000 can support upto 16 instances (default & named) and SQL Server 2005 can support upto 50 instances. I'm elevating the Cluster layer in this regard as it will be a different number when it comes to Cluster, the maximum number of instances is 25 and it will be very hard to manage due to restrictions on a Clustering environment.

As per the SQL documentation the maximum number of instances are good to know and in the real world to the maximum recommended number of instances, that is going to depend on the resources available on your server and the resources that each instance requires. So to manage the various physcial servers & instances it is possible to sum the individual resource usage and take count of CPU, memory and I/O for a consolidation. In simple terms, server consolidation is the process of condensing multiple physical servers, applications, and workloads to a smaller number of physical servers.

Consolidating multiple SQL Servers offers various benefits to an organization. Consolidation involves analyzing SQL Servers in your enterprise and grouping databases onto a smaller number of servers without compromising the performance of any applications using the SQL Servers. This tip looks at the pros and cons of SQL Server consolidation. The benefits may include a reduction in licensing and hardware costs as well as eased administrative burdens.

How you can define the under-utilized database servers?

Not a simple maths to decide, without a proper benchmarks and performance analysis there is no easy way to define or confirm that particular server is being under-utilized for a consolidation consideration.

Using SYSMON (performance monitor) and server-side trace (profiler) will give you first hand information to get into further assessment. If the assessed numbers do not exist, you will need to perform this analysis; otherwise consolidation of a seemingly underutilized server may be the wrong thing to do. In my experience if the server is having enough CPU (4) memory (8GB) and disk space (512GB) then you can get about four to six instances comfortabily that can be ascertained as a medium-usage consumption of server resources.

But you have to constantly monitor the growth and usage of data and server resources in this case, otherwise you may lose all the instances on the server when you need to perform workaround resolution.

SQL Server 2005 changes things a bit with mount point support, but the thought process involved isn't different: if you get down to one node, one plus one still must equal two. If one plus one equals three, then your totaled-up instances have greater capacity than the node itself. Generally speaking, one SQL Server instance will outperform two or more instances on the same hardware because there is some overhead for the instances themselves.

This means that if your first instance is not hitting a performance bottleneck, having a second instance will always reduce the resources available to both instances because the second instance will maintain both the second copy of SQL Server itself and its own copies of the query plans for its data. The various organizations within a company may use different processes to administer their systems. This might include varying monitoring software, standards for system availability, security processes, and more.

This creates the potential for confusion and chaos when one business unit needs to work with another. In small and large corporations, standards need to be determined at the top of the business hierarchy and flow down. With a more centralized approach to server deployment and utilization, stronger and more regulated controls are typically put in place to ensure that the company is dealing with matters in a single unified way.

The Consolidation process is not an overnight job, the impact on your customer or end user is the most important aspect to think about from a business perspective. As the goal should be to improve or maintain satisfaction, because consolidation should not affect the end user if at all possible and the process must be seamless. There are vairous white papers and articles available on Microsoft website and Google if you need more information and get to know the stories behind the consolidation process.

This link refers about best practices on consolidation process and the product is Polyserve.