SQL Server Consolidation, Part Trois

I wrote a couple of posts previously on SQL Server consolidation. The first post tried to give some insight on some of the problems and associated motivating factors that most companies have for consolidation efforts. The second post was an attempt to cover some of the decision factors that could drive a company to decide to consolidate or virtualize. This final post will explain some of the differences the word “consolidation” could mean to different people as well as cover a process mapping I feel helps to organize the servers in your shop as you get ready for your own consolidation efforts.

What Consolidation Means

The very first thing you need to be aware of is that there is a BIG difference between logical consolidation and physical consolidation. You need to be mindful of this when you have any meeting or discussion around any server consolidation project, otherwise different people will think one definition or the other, but rarely both.

A physicalconsolidation typically means you want to take, say, ten physical servers and virtualize them onto one host. So, you would go from having ten servers to having only one. You save space, power, cooling, and gain all the benefits of having a virtual environment that we discussed previously. As good as all that may sound to some, the fact remains that while you have only one physical server you still have the same number of operating systems to administer. Actually, when you include the host you have actually increased the number of operating systems you need to administer.

Enter logicalconsolidation. This is when you take two systems, merge them together onto one, and shut down the other. You reduce the number of operating systems as well as the number of physical servers. What’s not to love? Well, for one thing, the time and effort it takes to get the job done. Chances are there is a reason you had two servers to begin with; perhaps the systems are owned by different business unit that do not want to share, or the systems can be over utilized at specific times of the day and everyone thinks their system should be the priority. Whatever the reason the bottom line is that it takes a lot of time to coordinate all the groups necessary in order to perform a logical consolidation. Which is my way of saying it could take a long time to get anything done.

What I Haven’t Told You Yet

There’s more, lots more. As if the whole logical versus physical consolidation mess was not enough for you to fight your way through, I have a handful of other terms you should be familiar with already that come in handy from time to time. The first is upgrade, as in “let’s take this opportunity to upgrade from SQL 2000 to SQL 2008.” And that could be an in-place upgrade or it could be a migration to another server, which could be a part of a logical consolidation which ultimately could be a part of a physical consolidation. Confused yet?

The last term you need to factor into your project is my favorite: elimination. Identify systems that are no longer being used, or are slated to be turned off as a result of some other projects and get those systems removed from your environment as soon as possible. So, you could end up with some system being logically consolidated, which might take so long that they would then be simply eliminated at which point it would be easy to physically consolidate.

Simple, right?

Where Do You Begin?

Great question. Any other questions?

Seriously, where do you get started? How do you know what systems to start with? Most people may look at you and say “just start with your quick wins, your low-hanging fruit, right?” After slapping them for using the term “low-hanging fruit” because no one picks their own fruit anymore, you can then ask them “and which ones, exactly, would be a quick win?” Who decides what is “quick”? Quick for me is powering down a server, which could really cause issues for people using it, so how do I know where to begin?

I have seen some tools offered that do their best to track utilization of systems in an effort to virtualize systems. I have also seen tools offered that do their best to measure capacity in an effort to logically consolidate servers. I have yet, however, found a tool that does both very well and also takes into account business knowledge of the systems in order to make sound decisions about how to consolidate the environment. I have not even heard a rumor about a system that can do all that and also map out the project plan (this server first, then this one, and so on).

Sample Process Flow

I decided that the best way to go about organizing any effort is to figure out how it should work in a process flow (and yes, this would be some of my Six Sigma training rearing its ugly head). By being able to map it out you can keep everything in focus and remove any outside emotion. I decided to design a process flow at the server level but you could choose to design one at the database or even application level. I suppose the right thing to do is to have all three process flows mapped out but for now my focus is at the server level so that is why I drew this process map:

So, identify any physical server in your environment right now. The very first question I ask is “is this a server that is dedicated to a vendor or in-house system?” If yes, and that system has a defined end of life that is short enough for your liking, then you should just leave that server alone. Don’t spend one moment of time or money trying to consolidate, upgrade, migrate, or anything. Just let it sit there until the day comes that you can power it off.

Now, if that server is not dedicated, you need to go through all the databases and ask if they are still needed. My advice is to ask these questions for production servers first, that way you can ensure the corresponding test, QA, and development databases are left alone as well. Anything not identified becomes a candidate for elimination, which I grouped and denoted above.

After the elimination phase you move into the logical consolidation phase. Here you have to ask a handful of questions about each database. If necessary, can it be upgraded? Does the vendor prohibit the database from residing on a VM for some (odd) reason? You also need to examine the costs associated with a logical consolidation. If it will take years and cost millions of dollars just to merge two systems together in order to have one less operating system then you may just want to leave things alone.

The final piece is virtualization. After you have eliminated systems no longer in use, and did your best to logically consolidate those that remained, now you can safely look to go virtual whenever and wherever possible. I also included a section on the reuse of existing hardware; in some cases you may be able to upgrade some existing servers into a possible new host. If so, then spend some time calculating the costs associated and look to report on any actual cost savings or cost avoidance.

Summary

Any server consolidation project is going to have its share of bumps in the road. With a little planning and some decent research up front you can avoid a lot of headaches and miscommunication later on. I spent three blog posts on this topic and truthfully I could spend three more just talking about tools, vendor products, and what resource utilization measurements make the most sense. In other words, this is definitely one of those projects that really depends a lot upon how your particular shop either operates or intends to operate once things are built. All I can stress right now is that you simply cannot over-communicate the details when discussing the project with your peers.