question

MightyMax avatar image
MightyMax asked

SQL 2012 Hardware configuration

Hello, I am looking for the best approach to configuring a new Server install with SQL 2012 We have a Proliant DL380p Gen8 server 2 CPU, 4 cores (so 8 cores total) Win2K8 Enterprise 8GB Memory 2 Smart Array 420 controllers 10 Disks. My thinking was to do the following: 2 Drives mirrored for OS 4 Drives RAID 5 on same controller as OS to hold MDF files On the other controller 4 Drives RAID 5 to hold LDF files. We currently have Windows 2K3 with SQL 2000 in place on DL360 G5's and it looks like the Disk I/O is the bottleneck when large queries are done. So will the above disk splits work or should I just take and do 2 drive mirrors on all disks and then have 5 different logical drives vs. 3 logical drives using RAID 1 and RAID 5? Thanks for any help, Seth
sql-server-2012hard-disks
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I pulled down the tools that DirkHondong recommended.... Ran SQLIO and generated a lot of data...not sure the meaning of some of it.... But I ran it through the tool at http://tools.davidklee.net/sqlio/sqlio-analyzer.aspx And it generated a lot of info.... It looks like to me that RAID10 will be the better option, as mentioned by other users. In some cases the numbers between RAID 5 and RAID 10 are pretty close, while in others there is significant differences. I wish I had a better understanding of what I was looking at, but oh well. The numbers are far better than the results I got from our current DB server, so I am pretty optimistic.
1 Like 1 ·
Also, is there any software I can get to do a stress test on the server and the configuration to see how it performs?
0 Likes 0 ·
For IO Stress you can use SQLIO Keep in mind: this is an IO stress utility only and has nothing to do with SQL http://www.microsoft.com/en-us/download/details.aspx?id=20163 Then you can use SQLIOSim http://support.microsoft.com/kb/231619 to SIMULATE SQL Srv Io behaviour. If you want to test your installation, then you should get an actual workload and run it against your new system.
0 Likes 0 ·
Great, thanks for the info DirkHondong With this I should be able to test which RAID setup I can use that will best fit my servers hardware, correct? So if RAID 5 works better than RAID 1, I should be able to see that with these tests?
0 Likes 0 ·
With SQLIO you'll push the drives to their limits so you can determine the throughput.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
First of all, if you can, go with Windows 2008 R2. As regards storage, it really comes down to the kind of capacity requirements you have. If you have enough storage capacity to do RAID10 for your data and logs then I would highly recommend doing so (different RAID10 volumes for the data and logs just to be clear). If that's not an option then RAID5 for the data and RAID10 for the logs. If your transaction logs don't get that large you might consider a RAID1 for the logs with 2 drives and give the other 2 to the data files, remember the more spindles you can get to spread your data out the better off you will be. Out of interest, you don't mention backups here. Are you backing up your databases directly to a network location?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well on our current setup, we do a nightly backup of the DBs, then hourly transaction logs to a folder, and I have a routine where I take the DBs and transaction logs and zip them to an offsite location. Delete the DB and transaction logs and let the 24hr cycle begin again. So that runs every night. So I will try to do something similar with the new server. All 10 Drives are 146GB SAS drives. On our current setup I have 1 RAID 5 array that is 200GB (a 2 drive mirrored array for OS), so 6 disks total. It houses SQL, MDF, LDF and the backup files mentioned above.... That takes up about 100 GB
0 Likes 0 ·
Yes, this will be Win2K8 R2 64-bit Enterprise SP1
0 Likes 0 ·
@SirSQL On one of my current DB Servers. The .ldf files total around 1GB in size right now. The .mdf files total around 44GB in size right now, and obviously will/can grow. So if I read and understand your post. The .ldf files should belong on a RAID 10 (that would total 4 drives) and The .mdf files will go on the other 4 drives as RAID 5 Leaving my remaining 2 drives as RAID 1 for OS. And I should install SQL to the OS drive. Again, these are SAS 146GB, 15K RPM drives (10 total).
0 Likes 0 ·
sql-lover avatar image
sql-lover answered
Check your current disk and Tlog utilization. You need to know in advanced if you have OLTP databases or long time running queries that read and barely do updates or inserts. If you have long time running queries, like to generate reports. don't put mdf on RAID5, use RAID10. RAID5 won't give you the speed you need and if one disk goes down, it will be horrible slow, until you replace the disk. For the Tlog, RAID1 should be enough. Tlog read sequential so if you put ldf on RAID5 you are forcing the heads to write more than required , making the server slow as well.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@sql-lover I do know that we have some queries that pull from a lot of DB's And if I am watching Perfmon, the Buffer Cache Hit Ratio and Disk Queue Length takes a beating....severe beating. We do have things that do inserts and updates, but I think the biggest bulk of our stuff comes from pulling data from various DBs and tables.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.