March 19, 2008 at 4:13 am
Hi Everybody,
I'm a newby and was asked to make a report stating the exact server and storage specifications required to run current databases, e.g 6 73GB 15K disks in a RAID 5
array with 2 servers with 4GB RAM, 2 2Ghz CPU's etc. I'm struggling a little. Please can anybody help
They want to upgrade from sql 2000 to sql 2005 cluster. At the moment they have 3 servers -
All storage is local (DAS) to the servers.
SQL Server 1
Server Specification
2 * Intel PIV 2.4Ghz 1GB Memory
2 * 36GB SCSI Drives in RAID 1 config - System drive
2 * 36GB SCSI Drives in RAID 5 config – Database files
Windows 2000
SQL Server 2000
100Mb Network Cards
15 Databases
SQL Server 2
Server Specification
2 * Dual Core Opteron Processors 2.2Ghz
4GB Memory
2 * 72GB SCSI Drives in RAID 1 config - System drive and Database files
Windows 2000
SQL Server 2000
1Gb Network Cards
13 Databases
SQL Server 3
Server Specification
2 * Intel PIV 2.8Ghz
2GB Memory
2 * 36GB SCSI Drives in RAID 1 config - System drive and Database files
Windows 2000
SQL Server 2000
100Mb Network Cards
2 Databases
I know i'm asking a lot but your help will be highly appreciated.
March 19, 2008 at 4:49 am
Hi. This is a virtually impossible question to answer. There are so many factors that go into planning the hardware architecture required for any system
Questions like performance, how the databases are used (i.e. is it oltp or olap, number of users, amount of data in tables, indexes, the list goes on). Then you've got to look at things like redundancy.
Personally, I would never spec a system without planning for using seperate disks for my log files. The reason for this is for redundancy and performance. On an oltp system in particular placing the log files on seperate disks is important for performance alone. Raid 1 is preferable to raid 5 because the log files are almost always written to (sequentially).
But like I said, it's not that easy to just say "use this configuration" every system is different and has different priorities and requirements so it's impossible to say what you'd need based on just the information you've given. It involves careful planning, which would take a while to plan for.
Sorry I couldn't be of any more help.
March 19, 2008 at 7:02 am
Hi SQZ,
I appreciate your prompt reply and i understand you need more info.
The databases are police dbs and no of users is about 150 and will continue to increase, performance is ok but is bound to slow as more users come on board and dbs increase. i just wondered if you can possibly suggest minimum requirements for the 3 servers that are going to be upgraded to cluster sql 2005. Many thanks.
March 19, 2008 at 8:32 am
adelan (3/19/2008)
Hi SQZ,I appreciate your prompt reply and i understand you need more info.
The databases are police dbs and no of users is about 150 and will continue to increase, performance is ok but is bound to slow as more users come on board and dbs increase. i just wondered if you can possibly suggest minimum requirements for the 3 servers that are going to be upgraded to cluster sql 2005. Many thanks.
I wish I could provide you with a decent reply but unfortunately this will involve loads of time and investigation.
You're looking to put 30 databases on a server, so that is a concern initially. By doing so, you've got to think about possibly splitting the databases onto different physical disks so that they don't contend with eachother for disk i/o. Then again, the i/o impact might be negligible, in which case splitting the databases onto different disks isn't necessary.
If I was doing this from scratch my first step would be to interview the manager(s) that depend on or are responsible for each of those 30 databases. I'd need to understand the function of each of those databases from a business point of view. I'd need to understand how the business sees those functions changing over time.
After getting a feel for the business roles that each of these databases service I'd look into the physical database. Starting with the database design. How big are the database? Which tables are hit the most. How are they hit (select statements, inserts, updates?). I'd run traces and performance monitor to get a feel for the number of disk i/o's that are incurred during peak usage.
When you've figured the number of i/o's that will be occurring on each database you can then get at a figure for the number of i/o's that all databases would require collectively. Once you've factored in future growth only then can you say how many disks you'd need to put in raid 5 array in order to service the required number of i/o requests. Assuming you opt for raid 5.
You'd also have to figure out if it's worth putting indexes on their seperate disk.
Up to this point I've only looked at disks but there's memory, cpu so, as you can see it involves a fair amount of work.
Without doing any of this you might as well just pluck a figure out of thin air and take a guess.
As an absolute minimum I personally would start off with something like the following disk sub-system:
raid 1 for os system drive
raid 5 for data drive
raid 1 for log drive
How many spindles you put on your raid 5 array will have to come down to your analysis based on the i/o load that the system will be put under. There's just no point me taking a guess.
Beyond that, you'll also need to determine if it will be better to place databases on different physical disks. Should you go with raid 10 instead of raid 1 for the log drive? Should you split logs onto seperate disks? Given the number of databases you'll seriously need to consider whether tempdb should be on its seperate disk (I'd probably say it would be necessary, but again, I don't want to guess at this).
I probably wouldn't consider less than 8 GB of memory (considering you already use 8 GB between all 3 servers).
I'd go for 64 bit, Windows and SQL Server. I imagine you're upgrading to Windows Server 2003 (or 2008)?
Beyond that I really couldn't say any more - it would just be pure guess work.
The biggest problem you'll face is justifying the cost to management. They'll want to know why you need x, y and z. If you do all of the analysis work then not only are you designing a system based on facts and figures but you also happen to be justifying everything as a side-effect.
The worst thing you could do is spec a system, go out and buy it and then realise that it under-performs. Then you'll be trying to justify additional costs for system upgrades. The second worst thing is to spec a system that is over-specified. Then you'd have to justify why management spent 200k on a system when a 20k system would have been sufficient.
I know this is probably not the answer you were hoping for but unfortunately it's the only answer I can give you from where I'm sitting.
Good luck
March 19, 2008 at 9:54 am
Hi SQLZ,
Thanks a lot, you've given me more than enough info and i appreciate it big time!! 😀
Cheers mate!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply