Click here to monitor SSC
Av rating:
Total votes: 26
Total comments: 5


Thomas LaRock
SQL Server Consolidation
04 September 2009

In the crusade to cut expenses, Data Centres (and IT Infrastructure) are now coming under scrutiny as a possible source of savings. Half-informed executives now rally behind the cry of "Virtualization!", but virtualization is only part of the story - The tip of the consolidation iceberg - and it might not actually save you as much as you think. Thomas takes us deeper.

As more and more companies are looking to trim their operating expenses, many are looking at their data centers as a place where they can find some savings. For far too long the data center has been 'hands off' when it comes to reducing expenses, as it was just a vast unknown. Nobody wanted to touch things for fear of disrupting the systems that were running just fine 'as-is'. Yet as virtualization technology becomes commonplace, more and more executives are now turning a hungry eye to their infrastructure without necessarily grasping the full spectrum of consolidation possibilities, which is what I'm about to explain.

This tremendous pressure to operate your data centers as efficiently as possible often means converting as many physical servers as possible to virtual servers, but virtualization alone is not the end of the story! There are other ways to reduce your costs as part of a broader server consolidation strategy.

This article will look at some of the business concerns driving server consolidation efforts, as well as some of the decision factors you should consider when thinking about what your future environment will look like. I've also included a sample process flow to help you understand and plan your own server consolidation project, so you should have everything you need.

Business Concerns

Cost Reductions

There's no doubt that this is the biggest reason cited for want to do a consolidation project. You will see mention of a variety of expenses that will be reduced. Licensing is usually the biggest driver, followed by the fewer resources needed to monitor the reduced number of systems. But you will also see mention of other costs, such as reduced power consumption, which also ties closely to a reduction in cooling expenses, and so on.

I can't go around
not buying diamonds
and tell everyone I
saved my household
a few million dollars
in expenses last year.

Also along the lines of cost reduction would be what is called cost avoidance, i.e. avoiding spending money on future purchases. For example, say you have a five year old physical server that is no longer under warranty, and is scheduled to be replaced with an identical physical server. If you were to virtualize that server, you'd be able to avoid spending money on the new hardware. This is a classic example of cost avoidance, as opposed to actual cost savings. Yes, there is a difference between saving money you have been spending, versus not spending money that is still in your pocket. I can't go around not buying diamonds and tell everyone I saved my household a few million dollars in expenses last year.

Another example of cost avoidance in a server consolidation project could be the reduction in rack space. Perhaps going virtual will mean you won't need to expand your data center in the near future. Plus, with fewer physical servers you have fewer cables (usually), fewer routers, switches, and so on. In other words, when you start factoring in every piece of hardware, along with each person required to maintain that hardware (accounting for their time, that is), you can see the costs adding up quickly. It is these associated costs that quickly become a significant driving factor in any consolidation or virtualization project.

Inefficient Use of Resources

No company wants to be operating inefficiently. Yet at the same time I see cases where a company will buy a vendor product, the vendor will recommend using dedicated hardware, and that hardware will go mostly unused. A company is told to buy a server with a specific amount of memory, or a specific minimum of processors, and neither of those items are ever taxed. Oh, sure, I can hear it now, "But that's why we told you to buy so much extra, so you wouldn't have any problems!"

With consolidation you
[...] have a chance to
standardize your
environment; you
might be able to ditch
those old NT servers!

Let's take an example of a server that has eight processors, but it only uses two of those processors during even peak usage. You are essentially wasting six processors, right? Would it not be in your best interest to see that those processors get used? After all, you paid for them!

But there is more than just hardware efficiencies in play here. There are also software efficiencies. With consolidation you start reducing the number of operating systems in your environment and, as such, you also have a chance to standardize your environment; you might be able to ditch those old NT servers! You could have centralized database management, too. You could even start segmenting your servers in such a way that you could have (for example) certain team members responsible for replication, others for the BI stack and SSRS servers, and still others for performance tuning.

High Availability and Disaster Recovery

After cost reduction and efficient use of resources, there is a third common motivator I find. The use of tools focused on high availability and disaster recovery can often play a part in leading a company to consider a consolidation project. When you begin to virtualize your servers, you start finding it easier to move things around when necessary, and that is going to dramatically improve your ability to maintain good up-time.

I've also seen cases where virtualization is driven by efforts to have better disaster recovery plans. Again, this boils down to the fact that it is easier to move virtual servers than physical ones. Companies located in zones that have a certain frequency of natural events (hurricanes, earthquakes, Hanna Montana concerts) are always looking to ensure they have a plan in place to avert going out of business altogether.

Decision Factors

Costs

It takes time to
consolidate systems
... they don't just
migrate themselves.

No surprise here, right? Not only is cost one of the business factors it is also the biggest decision factor. Costs associated with everything will need to be considered. While the idea of logically consolidating three servers into one looks great on paper, there are lots of hidden costs that people often fail to consider. First up would be people's time - It takes time to consolidate systems, they don't just migrate themselves.

Next up is new hardware. Chances are you don't have the right hardware already in place to serve as a virtual host, so you are going to need to make some initial purchase. And then some more once you realize that you want little things like disaster recovery and/or high availability. And while you are adding in all of those costs you do need to also consider the cost avoidance of things.

For example, if you know it will take a year to consolidate three servers into one, and cost $500k to get the job done, but the end result will be a cost avoidance of only $100k over the next three years, you will probably want to reconsider your project. However, if the end result means less overall support, increased up-time, and a more stable environment, then you may consider it to be $400k well spent in light of other options.

Stability

Some business-owned
application just loaded
500GB of files and filled
up your drive?
No problem!

We all want our environments to be stable. We want to limit access to servers and systems, only letting the few hands that are necessary to be in there, touching things. If you have server sprawl, you have way too many operating systems to maintain, making patching more difficult. I'm not even going to go into the security implications. With consolidation, you get back some of those operating systems, you get back your time, and in exchange your users get back a more stable environment.

What's that? Some business-owned application just loaded 500GB of files and filled up your drive? No problem, we can expand the drive quickly, and even change that RAID5 to a RAID10 while the server is running. You say a developer claims the box is out of memory? No worries, I can allocate more memory to that guest right now, without leaving my desk. Did you say the hardware lease on that host has expired? Well, let's just migrate those guests off that host and onto a newer one, without ever interrupting the existing connections. It really is this easy.

Ease of Implementation

What tools will you be using, should you start building virtual hosts and guests? How will you monitor this environment? What virtualization software will you be using? Will it scale enough for your needs? Most importantly, what can we implement today, given the current configuration of our shop?

This is similar to asking yourself if you know what you want versus what you can afford. Sure, we all want the most expensive hardware possible, but what would it matter if you had no place to put it, or the right people to run it? In addition to basic cost decisions, and related to stability decisions, you need to consider what solutions offer the most ease for implementation and integration in your current environment. What good is the perfect solution if it will take you ten years to build, and be unmanageable by anyone currently on staff? Your business will need to put the pieces into place over time anyway, so you might as well find the easiest pieces first.

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 meetings or discussions around any server consolidation project. If you're not, different people will think one definition or the other, but rarely both.

Physical Consolidation

In fact, when you
include the host,
you have actually
increased the number
of operating systems
you need to administer.

A physical consolidation typically means you want to take, say, ten physical servers and consolidate them onto one virtual 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, the fact remains that while you have only one physical server, you still have the same number of operating systems to administer. In fact, when you include the host you have actually increased the number of operating systems you need to administer.

Logical Consolidation

Enter logical consolidation. 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's a reason you had two servers to begin with. Perhaps the systems are owned by different business units that don't 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 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 wasn't 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?

Perhaps in an ideal world
I'd be able to just plug a
software tool into my
infrastructure [and] have
it spit out a nicely
structured consolidation
plan. There's just one
small problem...

So, 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?

Perhaps in an ideal world I'd be able to just plug a software tool into my infrastructure, have it monitor what's going on in there for a little while, and then spit out a nicely structured consolidation plan. There's just one small problem; no such tool exists.

Oh sure, I've seen some tools offered that do their best to track utilization of systems, in an effort to consolidate them. I've also seen tools offered that do their best to measure capacity in an effort to logically consolidate servers. However, I have yet to find a tool that does both very well while also taking into account business knowledge of the systems, and making sound suggestions about how to consolidate the environment. And I have not even heard a rumor about a tool that can do all that and also map out the project plan ("this server first, then this one...", and so on). You're stuck doing this the old-fashioned way.

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. Ideally you would have a process map for the server level as well as one for the application or system level.

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 the answer is 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 physical consolidation or virtualization. After you have eliminated systems no longer in use, and done 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 new host. If so, then spend some time calculating the costs associated and look to report on any actual cost savings or cost avoidance.

I have included the process summary at the end of the article to help better explain my thought process.

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. 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. You simply cannot over-communicate the details when discussing the project with your peers.

Once you agree upon the business needs, your main decision factors, and the process map you will be in great shape to get started on your server consolidation project.

*Process Narrative

P1. Identify Server.

Identify an instance of MS SQL Server running in your environment.

D1. Determin if the server is a dedicated server.

If yes, determine the expected lifespan for the system. If no then we will inventory the current databases and see which ones can be eliminated.

P2. Elimination of databases
no longer in use.

Define a date for the elimination of databases from the environment.

D2. Does the server have an end-of-life less than 12
months?

Define the end-of-life for the server; if it is less than or equal to twelve (12) months then it will (most likely) be easiest to leave the server alone until it can be powered off. If it is longer than twelve (12) months then we go to P2 and start to identify any databases no longer in use.

D3. Identify any remaining
databases.

After the elimination phase, identify the remaining databases.

D4. Upgrade remaining
databases to SQL 2008.

For the databases that remain, evaluate if they can be upgraded to SQL 2008. If they cannot be upgraded, then we go to P3 and look to consolidate if possible. If they can be upgraded then we go to D7.

P3. Logical consolidation of
remaining databases.

For databases that cannot be upgraded to SQL 2008 (whether vendor or not), then we look to perform a logical consolidation where possible. It may be the case that no consolidation is possible, in either case we go to D5.

D5. Physical to Virtual
migration of consolidated
server.

At this point we look to virtualize the server; the server could be one that has been logically consolidated or it could be one that only has a few remaining databases. If it can be placed as a guest then we go to P4. If not, then we will leave it alone.

P4. Migrate to SQL200/5
guest.

Perform the physical to virtual migration of the server. If possible, take the opportunity to upgrade to SQL 2008 and place onto a SQL 2008 guest.

D6. Reuse of physical
hardware.

After the migration to a guest, examine the old hardware to determine if it can be upgraded to serve as a new host.

P5. Estimate cost avoidance
and savings.

If the server cannot be reused then we will turn it off and estimate the cost avoidance (i.e., if the server was out of warranty and we would have purchased new physical hardware).

P6. Purchase upgrades.

If the server can be upgraded, then purchase the necessary hardware, perform the upgrade, and redeploy as a new VM host.

D7. Does the vendor prohibit the use of a Virtual Machine?

If the vendor prohibits their application from running on a VM, then we go to D8; otherwise we go to P7.

D8. Logical consolidation.

If the vendor prohibits their application from being on a VM then we look to logically consolidate where it makes financial sense to do so. If no consolidation can take place then we will leave the server alone.



This article has been viewed 7719 times.
Thomas LaRock

Author profile: Thomas LaRock

Thomas LaRock is a seasoned IT professional with over a decade of technical and management experience. Currently serving as a Senior Database Administrator manager for Confio Software, Thomas has progressed through several roles including programmer, analyst, and DBA. Prior to that, he worked at several software and consulting companies, working at customer sites in the United States and abroad. Thomas holds a MS degree in Mathematics from Washington State University and is a member of the Usability Professional’s Association. Thomas is also a member of Quest Software’s Association of SQL Server Experts, currently serves on the Board of Directors for the Professional Association for SQL Server (PASS), and is a SQL Server MVP. Thomas can also be found blogging at http://thomaslarock.com and is the author of DBA Survivor: Become a Rock Star DBA (http://dbasurvivor.com).

Search for other articles by Thomas LaRock

Rate this article:   Avg rating: from a total of 26 votes.


Poor

OK

Good

Great

Must read
 
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: How rare!
Posted by: Rob (view profile)
Posted on: Thursday, September 17, 2009 at 8:19 AM
Message: Thanks for the article - it seems that in everyone's rush to virtualise everything as a silver bullet, people forget that, heaven forbid, you can run more than one service on an operating system!

Subject: Order of implementation
Posted by: puzsol (view profile)
Posted on: Tuesday, September 22, 2009 at 12:44 AM
Message: I might be a little nieve here (having never done it), but I would have thought that a virtual environment is best suited to development. That way if a developer does something stupid and wrecks the environment like they sometimes do... then you can just restore it from a copy... after all that's where I have used VM machines in the past - for testing one application on different OS/browser versions.

If it was me, I would be tempted to start with the dev/test environment, which you hope would more likely match what your future production environment will need. That way you have a chance to get it right without hurting the main business... Or is that too difficult to sell to the shareholders?

Subject: There's virtualization and then there's database virtualization
Posted by: hamp0002 (view profile)
Posted on: Tuesday, September 22, 2009 at 3:40 AM
Message: Excellent article - it reflects the sort of business pressures I am seeing at work, and I like the suggested 'review' approach.

I like some of the things virtualization offers - I have a VM that runs SQL Enterprise Manager to manage some old SQL 7 servers. But I resist it for database servers, on the grounds that databases are I/O-dependent, and I think that the RDBMS will make better use of dedicated and managed storage than shared and allocated space; though I'm ready to accept that a really hot (and expensive) SAN might do the job.

Subject: Use case senarios and performance
Posted by: Anonymous (not signed in)
Posted on: Tuesday, September 22, 2009 at 10:15 AM
Message: I'd like to see some examples of how much savings happens in the real world. Also, I've used a number of VMs, and helped others utilize them, and the first thing everyone notices is the performance hit. I haven't seen any examples such as, "I had 5 servers with these specs (cpu, memory, cost, storage, etc) averaging 40% CPU usage and combined them into 1 server with these specs without any perforance hit." I don't think you'll see many because the numbers don't add up and things come out as you suggested....5 servers costing $100K were replaced by one server costing $500K, and it's slower than before.

Subject: Virtualisation and consolidation
Posted by: tasdavid (view profile)
Posted on: Tuesday, October 20, 2009 at 5:52 PM
Message: Enjoyed the article and its strengthened my resolve to pursue further consolidation. We had a bunch of servers which had reached end of life so it was almost a no-brainer to replace them with a couple of host servers and a SAN. We even virtualised our CRM (SQL Server based) and by following best practice spreading the load over different LUNs we have had no performance hit. In fact our users say the system is quicker now. What I would like to do next is consolidate more of our database sprawl and do some more logical consolidation of server VMs.

 










Phil Factor
Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL Server... Read more...



 View the blog
Converting String Data to XML and XML to String Data
 We all appreciate that, in general, XML documents or fragments are held in strings as text markup. In... Read more...

SQL Source Control: The Development Story
 Often, there is a huge difference between software being easy to use, and easy to develop. When your... Read more...

How to Import Data from HTML pages
 It turns out that there are plenty of ways to get data into SQL Server from websites, whether the data... Read more...

SQL Scripts Manager: An Appreciation
 SQL Scripts Manager is Simple-Talk's present to its readers. William Brewer was an enthusiastic... Read more...

Hosted Team Foundation Server 2010 Review
 Team Foundation Server (TFS) has expanded its remit to support the whole software development process,... Read more...

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 Database design and implementation is the cornerstone of any data centric project (read 99.9% of... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Beginning SQL Server 2005 Reporting Services Part 2
 Continuing his in-depth tour of SQL Server 2005 Reporting Services, Steve Joubert demonstrates the most... Read more...

Creating CSV Files Using BCP and Stored Procedures
 Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing... Read more...

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.

Join Simple Talk