April 25, 2011 at 6:10 pm
Hi All,
We are planning to setup a new SQL server 2008 (enterprise edition).
Average data size is 100GB. Every weekend we will delete some data and add some new data to this database, around 10Gb data we will update in every week. Other 5 days in every week we just read and show these data in our application, So our main concern is data read should be more efficient, we are more looks on performance of data Read operation.
Our suggested hardware configuration is given below.
CPU :- 2 Intel 64 bit processor speed 3GHz or more
Hard Disk :- 2 RAID drives (SCSI fiber channel)
1. RAID 1 - for data storage
2. RAID 1 - For OS and SQL server applications
OS :- Windows 2008 64bit
RAM:- 4GB per processor, total 8GB
Please review this hardware configuration and advice me, is this good? what is your suggestions?
Thank you
Jefy
April 25, 2011 at 6:59 pm
jefydominic (4/25/2011)
Hi All,We are planning to setup a new SQL server 2008 (enterprise edition).
Average data size is 100GB. Every weekend we will delete some data and add some new data to this database, around 10Gb data we will update in every week. Other 5 days in every week we just read and show these data in our application, So our main concern is data read should be more efficient, we are more looks on performance of data Read operation.
Our suggested hardware configuration is given below.
CPU :- 2 Intel 64 bit processor speed 3GHz or more
Hard Disk :- 2 RAID drives (SCSI fiber channel)
1. RAID 1 - for data storage
2. RAID 1 - For OS and SQL server applications
OS :- Windows 2008 64bit
RAM:- 4GB per processor, total 8GB
Please review this hardware configuration and advice me, is this good? what is your suggestions?
Thank you
Jefy
So you want to quickly serve up 100GB of data on a server with just 8GB of RAM and only TWO rotating hard disks??? Um, good luck with that?? My LAPTOP has 2 hard drives! If you had 32 or better yet 64GB of RAM you might get away with that, but if you have either large reports that need to read lots of data or random IO requests spread throughout the 100GB of data you are in DEEP trouble with that poor IO subsystem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 26, 2011 at 4:48 am
Hardware requirement and analysis depend on the load of the Server,RAID Level 5 is best for databases and RAID Level 10 is costly but better than Level 5,Fiber channel Card HBA must be include in this specification,increase your RAM and how much processor core in these processors
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 26, 2011 at 9:52 am
Hi,
I can increase RAM to 32 GB per processor, total - 64GB.
Also i can change RAID configuration of DB storage hard disk to level 5 (Minimum space 2TB).
We are planning to use 2 Quad core processors - 64Bit, is it ok?
Thank you
Jefy
April 26, 2011 at 2:22 pm
It would depend on how many users you have for the application and the kind of reports that you will be running. Since it looks like a reporting database you will need to have better disk arrays to read large data. Remember if you allow users to write reports most of the queries become adhoc. Just increasing memory to 64 gig will itself not benefit much. You should also consider increasing the number of drives to 3 or 4 in Raid 10 configuration and have better partitions.
April 26, 2011 at 2:28 pm
with that amount of CPU/RAM your main contention will be I/O
Don't just grab from the top shelf if you don't require it, you will probably waste money.
Depending on your speed requirements 32 GB, or even 16 GB could be enough memory, and also depending on the speed requirements, 2 physical processors(sockets,not cores) can also be too much. I've seen bigger OLTP databases work well with much less.
Sure, the more the merrier, but pointy heads don't usually agree.
Any level of RAID should work as long as you have enough spindles, and of course, depending on your speed requirement.
You have 10 GB of data movement/week, how fast that needs to be moved around? You can take the whole week to move that or you MUST move that in 5 minutes? These things need to be sized to suit your needs, always having "scalability" on mind.
Is this a SAN or Direct Attached Drives? How many disks you have? How many disks you can get? And the big question, how many disks you need?
There is no straight "always right" answer, these things need to be sized per your requirements and growth expectations.
--
Thiago Dantas
@DantHimself
April 26, 2011 at 2:30 pm
Like the others have said, it really depends on workload as to what would be best.
Definitely increase the memory. Num cpus is negotiable - there is a lot more that goes into that selection than just getting a quad-core. There are dual cores out there that provide better performance than some quad-cores.
How many disks do you have - total spindles not number of arrays?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 4:08 pm
This is not a report data, it is a drug formulary data from all insurance companies, around 100GB data. We need to update 10GB of data every week with in of 2 hours. We are planning to run this update process in every sunday morning 3AM.
We didn't purchase any server. We are going to purchase a server.
We have planned to setup 2 to 4TB disk space with RAID 1 or 5 for data storage.
You are suggesting one RAID controller and configure 3 or 4 drives in this RAID controller with level 5, is it?
Thank you
Jefy
April 26, 2011 at 4:19 pm
I was suggesting 3-4 logical drives in possibly raid 10 config. One external raid controller should be fine for this. But this increases the cost for sure.
I have seen environments in the past where there were big investments made on processors and memory, but little emphasis was given to the underlying disk sub system resulting in average performance from the whole hardware setup.
April 26, 2011 at 4:24 pm
If possible, get multiple raid controllers and create multiple raid arrays.
OS you could go RAID 1.
Database, I would go at least RAID 5 but RAID 10 may be a better fit for you. Number of disks in your RAID setup will help to dictate RAID level and performance at that RAID level.
IF you get enough disks, such that you can create multiple RAID arrays, you should consider: tempdb and system databases being on a dedicated array, log files on a dedicated array, and data files on a separate array. These would all be in addition to your OS array.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 6:29 pm
Please review following revised hardware configuration.
CPU :- 2 Quad core processors - 64Bit,3GHz or more
Hard Disk :- 4 RAID Contollers (SCSI fiber channel) and 4 arrays
1. RAID Array level - 1 - for OS
2. RAID Array level - 5 - Temp DB and system Databases
3. RAID Array level - 5 - Application Database files
4. RAID Array level - 5 - Transaction Log
OS :- Windows 2008 64bit
RAM:- 16GB per processor, total 32GB
Can you revise above configuration?
Can i configure multiple RAID arrays in one RAID controller?
Thank you
Jefy
April 26, 2011 at 6:32 pm
Please review following revised hardware configuration.
CPU :- 2 Quad core processors - 64Bit,3GHz or more
Hard Disk :- 4 RAID Contollers (SCSI fiber channel) and 4 arrays
1. RAID Array level - 1 - for OS
2. RAID Array level - 5 - Temp DB and system Databases
3. RAID Array level - 5 - Application Database files
4. RAID Array level - 5 - Transaction Log
OS :- Windows 2008 64bit
RAM:- 16GB per processor, total 32GB
Can you revise above configuration?
Can i configure multiple RAID arrays in one RAID controller?
Thank you
Jefy
April 26, 2011 at 7:21 pm
jefydominic (4/26/2011)
Please review following revised hardware configuration.CPU :- 2 Quad core processors - 64Bit,3GHz or more
Hard Disk :- 4 RAID Contollers (SCSI fiber channel) and 4 arrays
1. RAID Array level - 1 - for OS
2. RAID Array level - 5 - Temp DB and system Databases
3. RAID Array level - 5 - Application Database files
4. RAID Array level - 5 - Transaction Log
OS :- Windows 2008 64bit
RAM:- 16GB per processor, total 32GB
Can you revise above configuration?
Can i configure multiple RAID arrays in one RAID controller?
Thank you
Jefy
For the requirements you specified so far, i still think a 2 socket config is still excessive.
The I/O specification seems adequate
About the controller, I'm no expert but sounds like a question for your vendor
--
Thiago Dantas
@DantHimself
April 26, 2011 at 7:55 pm
The specifications you have given may or may not be appropriate. It really depends on the workload and throughput you need to respond to certain queries, as mentioned above. Is there an existing system you can benchmark from?
The one thing I would change is that I would not use RAID 5 for the data or log. I'd switch to RAID 10 for data, RAID 1 or R10 for logs, depending on size needed.
Tempdb would also benefit from R10, but if cost/space is an issue, R5 should be fine.
April 26, 2011 at 8:02 pm
Agreed
The usual "depends" apply perfectly here.
Must always keep in mind RAID 5 is not best suited for heavy write operations (such as tempdb and transaction logs)
--
Thiago Dantas
@DantHimself
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply