x

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
more ▼

asked Sep 28 '12 at 07:30 PM in Default

MightyMax gravatar image

MightyMax
20 1 1 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?
Sep 28 '12 at 08:04 PM MightyMax

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.
Sep 29 '12 at 12:56 PM DirkHondong

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?
Sep 29 '12 at 01:22 PM MightyMax
With SQLIO you'll push the drives to their limits so you can determine the throughput.
Oct 01 '12 at 08:24 AM DirkHondong

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.
Oct 01 '12 at 12:21 PM MightyMax
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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?
more ▼

answered Sep 28 '12 at 07:50 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

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
Sep 28 '12 at 08:09 PM MightyMax
Yes, this will be Win2K8 R2 64-bit Enterprise SP1
Sep 28 '12 at 08:20 PM MightyMax

@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).
Sep 29 '12 at 01:20 PM MightyMax
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Oct 01 '12 at 01:12 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Sep 29 '12 at 01:57 PM

sql-lover gravatar image

sql-lover
0 1

@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.
Sep 29 '12 at 02:04 PM MightyMax
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x140
x13

asked: Sep 28 '12 at 07:30 PM

Seen: 1396 times

Last Updated: Oct 01 '12 at 01:12 PM