Server Consolidation - What to measure to determine if you should

Question

Server Consolidation - What to measure to determine if you should

Jack Corbett

SSC Guru

Points: 184393
More actions
July 31, 2009 at 12:45 pm

#212767

Just got an email from a former employer asking for my review of a server they are looking at buying to consolidate several (3-5) older sql servers on one box. The older sql servers are 7 and 2000. I have obviously given some advice already about checking load on existing servers, what would you recommend be checked?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Load and space would be the main things.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

Ah, yes, but do you have any specific counters or methods you would use for checking load? Here's what I gave them to start:

Memory\Available MBytes
Memory\Pages/sec
PhysicalDisk\% Disk Time
PhysicalDisk\Current Disk Queue Length
PhysicalDisk\Disk Bytes/sec
PhysicalDisk\Disk Transfers/sec
Processor(_Total)\%Privileged Time
Processor(_Total)\%Processor Time
SQLServer:AccessMethods\FreeSpace Scans/sec
SQLServer:AccessMethods\Full Scans/sec
SQLServer:Buffer Manager\Buffer Cache Hit ratio
SQLServer:Buffer Manager\Free Pages
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
System\Context Switches/sec
System\Processor Queue Length

Most of these I've gleaned from different baselining articles and chapters like in Grant's 2008 book. Wondering what I'm missing?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

By consolidate do you mean with VM's or instances or just tossing all the db's on one instance of one machine. I'm thinking besides current baselines, what Disk and network IO issues may come up, as this would apply mostly to VM's but a physical box as well. Also there's the whole bit about the security needs of the various applications using those servers. Can that be done with database roles/permissions, with instances, or will other such boundaries need to be put in place.

Let's not forget about DR plans and such. probably the old plan had provisions for moving databases from one server to others in the event of a hardware failure... since they're looking at putting all the eggs in one basket what additional DR protections are they looking into?

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

The only other thing I'd look closely at is the backup strategy. Does the consolidation introduce any conflicts with backup configurations? Will the larger number of backups require a different disk configuration or another NIC?

And then again, I might be wrong ...
David Webb

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Have you read these blog posts?

http://tomlarock.com/2009/07/sql-server-consolidation/

http://tomlarock.com/2009/07/sql-server-consolidation-part-deux/

http://tomlarock.com/2009/07/sql-server-consolidation-part-trois/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184393 More actions · Answer 6

This is why I asked. I didn't even think about it from a DR/Backups perspective. Backups would definitely have to be local and then copied at completion to a another location then to tape. When I was last there most of the backups completed in less than 20 minutes so they could probably and that was before they got RedGate Backup and began compressing the backups which I know reduced the time.

Great questions Luke:

Looking at multiple DB's on a single instance at this point with no virtualization. I haven't been employed there for over 2 years so I can't say exactly how many DB's or how big exactly, but I can make an educated guess. I'd say 20-30 db's with 3 fairly active OLTP and the rest mostly reporting with a 3 that have regular large data transfers (not exactly datawarehouses). The rest are mostly small support databases with configuration type information. None would be classified as VLDB's, but I'd say in the 5-10GB range, but that's a guess. I honestly think with the sizes and usage they could get away with it because the existing SQL Servers aren't configured as well as this would be, so the extra activity will likely be offset by a better configuration.

Network could be an issue. You also need to know that most of these are still Windows 2000 Server as well, so there are likely some counters missing that you'd like to use.

Security would not be a big deal. HR application is outsourced and the rest could easily be handled with roles. Most of the applications have web apps and have security included in the application and all the homegrown stuff uses stored procedures with no direct table access as do the other main applications.

Gail,

I was just going back and re-reading Tom's posts. I was trying to see if they were simple enough for the guy I am dealing with to understand so I could just send him the links. Yes, that is part of the problem, I am dealing with an under-qualified (to be polite) system/network administrator. Let's just say that I am limited in my knowledge about hardware and RAID/disk systems, but I probably know more than the person I am dealing with.

I certainly appreciate the comments and advice.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

Jack Corbett (7/31/2009)
Just got an email from a former employer asking for my review of a server they are looking at buying to consolidate several (3-5) older sql servers on one box. The older sql servers are 7 and 2000. I have obviously given some advice already about checking load on existing servers, what would you recommend be checked?

Ummmm... just a couple of thoughts from someone who has seen people really screw up by touching stable systems...

Why would anyone want to mess with...

1. Something that's already bought and paid for?

2. Something that's been working for (apparently) many years?

3. Probably has stable code , data, and backups.

4. Something that probably operates better on separate machines than a consolidated machine.

Unless the applications on these systems really, really need the features provided by an upgrade to another version of SQL Server, I'd leave these hardworking, faithful dogs alone.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 8

Thanks Jeff. Those are actually reasons why the SQL 7 application is still out there. I didn't want to mess with it while I was there and I was asked to!

Issue is that the hardware is all beyond expected end-of-life, almost 10 years old, and what hasn't already failed is beginning to fail. Since they need to move to new hardware they want to consolidate and upgrade too.

Most of the stuff is home grown and should have no issues moving to a newer version of SQL Server. To be honest the biggest issue will be around email as we used XP_SMTP_SENDMAIL extensively so we'd have to move that to sp_send_dbmail.

For the 2 main purchased applications the vendors would have to be brought in as part of the upgrade.

It's going to cost them a bit as they don't have the expertise in house anymore they downsized the IT staff to a barely qualified system administrator and 1 help desk person. All the former staff are in the system as vendors now, making more money with less headaches.

I actually have someone I'm going to recommend to them instead of going up myself, if they ask.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question