One of the main presentations that I saw last year (2005), actually at two different shows, was on the scalability of SQL Server 2005 and it's ability to handle very large workloads. The demo showed a load generated by some testing software and the load on SQL Server 2000 and then the same load on SQL Server 2005. The number of clients was dramatically different, and the supposed transaction load was much higher. I don't know how accurate the demo was and I'm assuming it was a real test and the display was an accurate representation of the relative loads.

It stuck in my head and in writing for a book on the ability to consolidate and how to go about it, I had to stop and really think why do we not consolidate and how should you go about looking to combine your servers. I wrote a lot in the book and thought a short consolidation, he he, pun intended, of my work would be good here.

Pros and Cons

Consolidation sounds like a great selling point. Hey, buy this new version (either SS2K in 2001/2202 timeframe or SS2K5 now) and you can combine a few applications onto one server and save licensing, hardware, support, spare parts, etc. It makes logical sense if you think about it.

Suppose that I have 3 applications, each of them something relatively small for a department. Not the large ERP installation of SAP or something for 1000 employees, but something like a web site backend or a tracking app of some sort that a group in your company, 10-100 people, use. Each of those servers, assuming you're buying mid-tier boxes, will cost you $3,000 or so after RAID, rack mount, 2GB RAM with space for 2GB more, etc. Add in the cost of Windows Server ($700 or so) and SQL Server ($900 for SS2K). These are guesstimates with some people paying more, some less. There are CALs as well, but we'll ignore them since you need them in either case. That means you've invested:

Well, not you, but your company. But your manager, director, someone will see these numbers as a rough investment of $13,800. Now suppose it's two years down the road and you are looking at adding in two new applications of similar size. And you've got either lease changes, support contracts, etc. on the old servers to consider in the next year. If you extrapolate, you're really looking at a new investment of $8,200 more for the new applications, but also perhaps $6k or so in box changes for the old apps, assuming 2 of the 3 might be replaced. That may be bad, but hey, it's my example :). There's also the chance Windows might need an upgrade, or some other cost on the old systems.

Now the alternative from the manager's point of view is that you could buy one new big box. Let's say you look at a 4 way box, but buy 2CPUs, 8GB of RAM, etc. and get a box for around $10k. I know it's possible because I priced one out on DELL at $9200 or so. That gives you room to expand (2 more CPUs, 8more GB of RAM). If you say you've now got one installation of Windows and SQL, then you are either spending $3k or so on the new versions or $0 for the existing ones.

And you've got spare hardware for print servers, file servers, development, etc. That seems like a good deal for the manager. If he knows that the 3 servers have been rolling along at 10-15% CPU on average, then he's thinking they have been underutilized. You could redeploy hardware and software for other uses or even save for future uses (a SQL 2000 license can be saved and used on another upgrade later), and get more use out of the $$ you are spending on your equipment.

The downside is that the box can be overloaded a little easier and downtime affects all your apps not just one, but from the manager's perspective this hasn't been an issue over a few years and the existing hardware is barely used.

Crinkles in the DBA's Forehead

Face it, we're all getting older each day and none of us needs help with growing gray hair or adding wrinkles to our visage. And every time a manager comes up with a "good idea", that's what happens to us. So how do you feel about consolidation?

If you are like most DBAs, you don't like one thing. And that's your phone ringing. And adding a load to your servers increases the likelihood of that. Either from overload or because any patches, downtime, etc, mean there are that many more people likely to call. It's easy mathematics. Friday night downtime for one application means 1 or 2 or 10 might be working and call. Five applications could mean that 10 or 20 or 100 could call and complain.

And complain to their managers.

And use this as an excuse for something not being done or being late.

Face it, consolidation just increases the hassles you deal with on a daily basis. Plus you get a number of other benefits. As if you were not busy enough rewriting queries, checking performance, etc., now you get to spend some of that spare time you had squirreled away for frivolous things like going to the rest room or filling your coffee cup, on analyzing the performance of the applications and actually trying to determine if one big server can support the 5 applications.

And after your analysis of the 4 or 5 sources you can find, you will still have some concerns that this will actually work. After all, your analysis will be detailed and careful, but it will be an extrapolation, a guess, your best effort, but still an uncertain result. And we know what uncertainty means...

Crinkles, wrinkles, and gray hairs.

The Best Practice

It's not that we're lazy. Actually it is to some extent. We in IT are usually lazy in that we want to have things automated, running smoothly, and spend time on improving operations. We want the computers to do the work and when they're overloaded, we end up spending time babysitting them.

Consolidation sounds good and I think it can be done well and help the company. But it isn't for everyone and it isn't for all applications. In my various positions, I have always looked to consolidate databases onto fewer instances. It makes financial sense, means less logs to check and less servers to patch. But I also have examined each instance with a careful eye to the political consequences of the application.

In other words, a judgement as to the amount of flak I'll get from my boss, executives, etc.

Consolidation is something to be driven by the DBA, not a manager. So you should be watching for places that you can consolidate instances wherever possible. The things you want to be aware of are low resource utilization (transactions and CPU) and a relatively low impact to the business. Low impact being both in terms of revenue and how much of a complaint it will generate if issues arise. This is business dependent and you will have to gauge this for yourself. A 1 user application for tracking football scores may be more important if the one user is the boss than a 10 user application for project tracking if the projects are not mission critical.

Each upgrade or new deployment should be examined as to whether it can be placed on an existing server or even if you can move existing applications to this server. Your examination should be from the technical viewpoint to see if the applications can co-exist with a minimum of issues.

Once you find applications for consolidation, you want to gain acceptance from other sources. There are two sources that I have spent time with in the past. One is my manager or director to show that I am pro-actively looking to make the business run more efficiently. The other is the business liaison or application owner to be sure they understand the potential impacts as well the efficiency gains for the company. This is a sales job to the business leaders because you are asking them to give up some independence and performance abilities for a more efficient operation. They might not ever need that level of performance that the server can deliver, but do not lead them astray. They need to be aware that there is the potential for the two applications to overload the box, however small it may be.

In my experience, this has presented various degrees of difficulty, but if you have done some careful analysis and are comfortable with it, you can usually gain their blessing. The decision on who to approach first really depends on where you think resistance will come from. I usually choose to least resistant individual first and gain some support before approaching the other. It also pays to check with the financial people and gain their approval of any expected cost savings as well.

The New DBA

If you are new to a job, especially in a large environment, or maybe you get a new boss, a large scale consolidation may be considered. This is a way to show some huge savings, at least on paper, and gain some kudos for your record with a successful consolidation.

A large scale undertaking should involve a committee of at least two. One technical person and one business person at the minimum to examine the various applications and come to an agreement on the order of consolidation, the importance of each application and other factors. Since you are sacrificing some of the benefits of separate servers for financial savings, you want to be sure that there is a consensus from all affected groups.

Conclusion

This article should give you some things to think about with consolidation. It is not something that I see often done, but it can pay some benefits both to the company and your career. Anytime you save money for the company, or increase the amount of applications you can handle without requiring more staff, you will make yourself a more valuable employee.

I have not delved into the technical side of consolidation and I'll look to get another article out that examines some of the considerations that you should use from the technical side.

As always, your opinions and thoughts on this are appreciated by me as well as others. I certainly don't have all the answers and my experience is limited compared with the sum of the readers. So use the "Your Opinion" button below and share your thoughts.

©2006 dkranch.net

Steve Jones