Even the best Capacity Planners, the people who specialise in doing capacity planning as their paid job for many years, reckon any estimate they give is at best accurate to +- 25%. As part of the work you do, you need to be realistic on how accurate your estimates might be.

How much staff time do you think you and your employer should devote to an estimate that may be +- 40% away from what is actually needed, assuming you think you can get to that level of confidence.

My approach has been to try getting management to have the right expectations. They need to be aware of likely accuracy, the impact if the figure is out by 40% (too much or too little kit purchased, etc), the risks involved and the mitigation plan.

For SQL consolidation, I try to get three sets of figures (CPU, Memory, IO) to produce an initial sizing estimate. I would then move workload to the new box, one server or even one DB at a time, and monitor performance. One great advantage of doing this in a virtual environment is you only need a small amount of swing kit to start with, and as you move workload off old SQL instances you can reallocate CPU and memory to the new instance.

To get the CPU figures I get the peak and average CPU usage on each of the old servers, over at least a 14-day period, and rationalise this to a GHz requirement. eg for a 4-CPU server running 2.1GHz with 75% peak and 37% average this gives 6.3 GHz peak and 3.4 GHz average. These figures should be adjusted to account for virtual to real CPU loading if you are not running SQL at 1:1. You then add these figures for all the servers to be consolidated to get your new server GHz requirement. Finally, work out your target CPU loading % and calculate the number of cores your new server may need (adjusting for virtual to real usage).

Do the same for memory and IO. For memory took for actual and target bufferpool size, plus any memory used by other processes that will be moved to the new server. IO can be the hardest to deal with, as the allocation of storage to the new server may use a different hardware path in your storage subsystem to the old servers, but if anybody thinks a server consolidation can be done without heavy involvement of the storage administrator they are missing a vital part of risk mitigation for the project.

You also need to plan how to do the consolidation with minimum disruption. My preferred approach is to set up DNS Vanity Names (aka DNS Aliases) to abstract the actual server environment from the applications, and then change all application connection strings to use the vanity names. This allows a DB to be moved to a new server and at the same time repoint the relevant vanity name so there is no actual change made at the application level. This also allows for fast fallback (reset the vanity name) if your regression testing has a show-stopper problem. Expect to take more time both in planning and implementation in dealing with minimising disruption than in sizing and building the new servers.

This process should give you a relatively quick way to size your consolidated servers, but as I said above you only need a small amount of swing kit to start the actual consolidation. Make sure the new server has more than the expected CPU and memory for the application you plan to move, then do the move and check performance. Before you move the next application, reallocate CPU and memory no longer needed on the old server to the new server. Over time the old servers get decommissioned and your new server gets all their resources. If you find your capacity planning was 40% above actual needs then you return capacity to the pool for other uses. If your planning asked for less than is needed, you can pause the consolidation until more kit becomes available - this aspect is an important part of risk mitigation.

At the end of the day you have to choose the right approach for your organisation. Trying to test everything before you commit to a change may sound good, but only if the change you test and the change you make are identical. My advice is to accept that any sizing you calculate is unlikely to be better than +- 40% of what is needed, and plan the project to cope with what actually happens rather than with what you expect to happen.