SQL Consolidation Assistance

Question

SQL Consolidation Assistance

gareth.thompson

SSC Enthusiast

Points: 123
More actions
September 10, 2010 at 6:55 am

#229211

Hey Guys / Girls, I know I always seem to only be asking questions here. 🙂 But hey I have no studying behind me here.
Anyways, I wanna know if anyone has some info for me.
I Need to consolidate some servers, but I want to make sure the Hardware is correctly spect first.
As far as HDD space goes I'm good, Total DB and Size plus 1/3 free space, and considerations based on Trends for growth on Yearly basis.
BUT
Is there a basic consideration when it comes to CPU and Memory based on the Database sizes and user connections?
Thanks for any and all assist.

Viewing 5 posts - 1 through 4 (of 4 total)

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

lisa.hale SSC Enthusiast Points: 174 More actions · Answer 1

I know this answer sounds lame, but it depends.

What type of application is it, how many concurrent sessions are you expecting? Is it Reporting intensive or are you doing any replication? Ask yourself what all the bottlenecks are.. then figure out what causes them. You'll then know what questions to ask to get the details necessary to scale the servers to support your application requirements.

gareth.thompson SSC Enthusiast Points: 123 More actions · Answer 2

Hey Lisa,

I was looking more at a base idea, but not a issue, most the applications are customized applets.

Been reading around like a mad man, think im going to have to do a few trends to view disk IO and memory access and have to jusge from there I think.

If anyone has any Suggestions I am open to them 🙂 though I suspect I would have to go through everything like you suggest.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

I wrote about this for a 2008 exam book and found very few references at the time. Here's a few I did find

http://www.mssqltips.com/tip.asp?tip=1320

http://msdn.microsoft.com/en-us/library/ee819082%28SQL.100%29.aspx

http://www.simple-talk.com/sql/database-administration/sql-server-consolidation/

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Having consolidated many servers, I can safely say..no, there is no way to predict exactly what kind of resources you will need.

You can get a ballpark though by looking at your current hardware, usage trends, and what your expected growth will be.

For example, if you have two servers now, each with 2x Quad CPUs (8 logical) with 32gb ram and CPU usage is around 50-60% at peak times on both..then obviously you can't move them to a single 8 core box. You'd need to go with a quad-quad with probably 96-128gb ram to be safe. Also remember that the more processes you have running on a machine, the higher the need to find the perfect maxdop setting. If you leave maxdop at 0 and have a runaway process on one database, then it will grind everything to a halt, and your consolidation plan will appear to have been a bad idea. Same goes for memory..if you're consolidating onto one server but will maintain separate instances (depends on the situation...I try to avoid this), you need to figure out how much memory to allocate to each instance, especially if it's on a cluster. This will take trial and error.

The bottom line is unless your current servers are over-spec'd and your CPU is sitting at < 25% at all times, even peak..it's a good rule of thumb to try to build a new box with specs equal to or greater than the sum of your current ones.

Then again..if your current servers are old, then newer servers have faster hardware (DDR3 vs DDR2, more efficient/faster CPUs) which could mean that a modern dual-quad box could handle the same workload of two separate older dual-quad machines from 4 years ago.