I've been working on the "Designing a Database Consolidation Strategy" chapter of my book and it's been quite an effort. Around the kids' ice skating lessons, short school weeks, and broken water pipe, I've been researching and digging around the web for information. That's when I wasn't digging a 6ft deep hole to fix my water.
There is relatively little information on SQL Server consolidation, mostly I'm guessing because there was a proliferation of servers with SQL Server 2000 rather than a reduction. There are two good papers, one from MS and one from Dell on consolidation as well as an x64 consolidation. With the almost limitless headroom with 64-bit computing, I expect to see more in this area soon.
But it means that I've had to read what's out there and use experience and common sense to build the chapter. I hope it comes out alright as I'm trying to balance the little info from MS with what I've learned and think is important. A hard task since every consolidation is different and so many things are judgement calls,
It's almost like a spiral development model. Choose servers that appear to be underloaded and make some guesses about which ones to consolidate based on performance metrics. Then go look at multiple instances v 64 bit v single instance, consider clustering, design new hardware, do some testing, go back and look again at your plan. I can see why most people don't want to do it; it's a ton of work!
Add in there that any performance problems will be blamed on you and it's no wonder that most people overbuy hardware and then underutilize it.
I went through this a little at JD Edwards. That was the first time I had literally hundreds of servers and was tired of looking through reports on logs, jobs, etc. Even summarized it was a ton of data. I started to consolidate servers that I thought were very underutilized and fought through all the political and departmental battles. So many people were worried the one time they wanted a fast query that they'd be overloaded. My arguements were mostly along the line of it's rare and deal with it. It's not cost effective for a company to pay literally thousands more per server just for some non-critical department application that tracks whose turn it is to bring in donuts for the staff meeting.
I especially loved finding a database that hadn't been logged into for over a month. Those were the easy consolidations 🙂