February 2006 - Posts
One of the challenges in writing about a new version of SQL Server is
finding out information, especially for the less than detailed
technical topics. Consolidation is an interesting topic because it
seems simple in theory, but the details of how to proceed and what to
do are harder to divine from public knowledge. I've consolidated some
servers and to a large extent it was using my experience as a guide to
make the sizing decisions or determine if a new instance would overload
existing hardware.
But for SQL Server 2005, there is relatively little information here.
In fact, very few people have even deployed 2005 into their production
environments. There are a couple of interesting white papers on 2000
consolidation and one great one on 2005 performance, but not much
between.
So I've struggled to get concrete backing for my writing. And things like these don't help:
- When
you perform your analysis, it is crucial that you understand the impact
of consolidation. It is also critical to understand the effects on CPU
utilization in the other server processes. The server processes on a
single physical server designated as the consolidation server will be
affected by applications and server processes such as SQL Server
consolidated from other servers onto the consolidation server.
- You can promote success by performing proper capacity
planning and applying solid performance metrics.
Nowhere in the details of looking through the white papers and research
in performance books do I see concrete examples of say add up your
average CPU usage and do not exceed 70%. Nowhere do I actually find
guidelines that specifically make recommendations. I see lots of
information about configuring AWE/PAE, and setting CPU affinity, but no
detailed guidelines. Just lots of "test, don't overload things, watch
for high values, etc" without specifically marking that is "high".
Maybe it's just me, but I think that part of developing the product and
providing the support is that MS needs to provide detailed guidelines
on what performance levels people should shoot for. There is a great
white paper for 2005 on
Performance Problems that goes into the type of details I wish I had seen from past versions.
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 :)
I haven't had time to research the extent, but someone reported plagiarism and the author admitted the code, while "from memory" was tkaen from a book. So I pulled the article.
Great.
I'd started an article (again) on this topic for wed.
I left at lunch to hit the gym and then meet a friend. Afterwards I stopped by Starbucks to do some writing with a latte.
I'm not sure what it is, and it's certainly not the quiet, but I think that ambient noise really helps me concentrate. I've gotten some great writing, including most of my two books that I'm currently writing, done at Starbucks. For some reason I can just get concentrated time there.
Part of it may be the lack of Internet distractions. I had a problem with my T-Mobile account and they paused my account. Since I was in the middle of a move, I didn't worry about it and I have yet to renew it, instead spending good time there working. In about two hours today I got about 10 pages written of pretty good stuff.
At least I think it's good.
This is very interesting.
I've often thought that blogs were a great source of information, but it is more unvetted than normal. Not that an article is necessarily better written or more correct, but in general someone spends more time on it and usually at least two eyes see it.
With a blog it's more off the cuff, quick notes on something. While I enjoy long posts like I see on some blogs, I don't want to spend a lot of time working on them. If I'm going to spend that much time, then it should be an article.
Which is what my comments on that above might be.
Or an editorial :)