Solved

SQL 2012 Raid questions?

Posted on 2013-05-15
6
270 Views
Last Modified: 2013-06-03
Hi,

I am looking to get some general guide on SQL 2012 in regards to disk IO on a Raid disk. (I will be
running this on Windows 2008 R2 x64).

So I have two questions in this area:

1. What is the best way to split the installation up. I think it is data on one drive, logs on another
   etc?

2. Ok in regards to the drive splitting does it make a difference if the different logical drives
   are on the same physical disk.

   i.e.  D: E: and F: <--- are all on a logical disk that sits on one physical disk that is mirrored with
                           another.

   as opposed to.

   D: E: and F: being seperate physical disks that are mirrored?

Thanks,

Ward
0
Comment
Question by:whorsfall
6 Comments
 
LVL 33

Expert Comment

by:paulmacd
ID: 39168016
1) Yes. Splitting the databases and log files can improve performance.

2) Yes.  Ideally the separate drives would be accessed via separate controllers.  If the logical drives are on the same physical disk, you lose some benefits of the separation.  That said, with a properly configured RAID, it may not make much difference.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 39168031
This is  how  RAID should be used with database files.

RAID	Type of Raid	Gen.File	Database File	Redo Log File	Archive Log File
0	Striping	Avoid	OK	Avoid	Avoid
1	Shadowing	Best	OK	Best	Best
1+0	Striping and Shadowing	OK	Best	Avoid	Avoid
3	Striping with static parity	OK	OK	Avoid	Avoid
5	Striping with rotating parity	OK	Best if RAID0-1 not available	Avoid	Avoid

Open in new window

0
 

Author Comment

by:whorsfall
ID: 39168072
Hi,

Thanks for that. What does "Gen.File" mean.

Also does it make any difference raiding the share feature (SQL binaries)?

Thanks,

Ward
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:paulmacd
ID: 39168109
"What does "Gen.File" mean."
General file serving?

"Also does it make any difference raiding the share feature (SQL binaries)?"
You can end up with some fault tolerance.  In some cases you can improve performance too.
0
 
LVL 6

Expert Comment

by:insidetech
ID: 39168326
Word of advice... Looks like your question did get answered though point I want to make is with regard to  wether you need to worry about the disk I/o optimization.
Unless you are experiencing latency problems now, simple disk mirroring compared with any of the above will perform equally for small installations. The criteria for determining optimal configuration for SQL should be knowing what your workload is ie. number of transactions  and so on....
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39169087
1) The main reason to split any single db's data and log files to separate physical drives is recoverability, not performance.  Yes, performance is almost always helped too, but the capability to recover the data in the event of a drive failure is absolutely critical.

If you have RAID10 for logs, and RAID 5 for data -- a typical setup, to save some money -- then put all the log files on the RAID10 and all the data on the RAID5.

But if you use the same RAID for both/all drive sets, I say there's a strong case for mixing data and logs on the same drive, as long as the data for a specific db is not on the same drive as the log for that same db.

For example, dbA data and dbB log on drive1, and dbA log and dbB data on drive2.  Most people stick with "all logs on isolated drives" but frankly I think that hurts overall performance if the drive sets are all the same RAID type, since over half the writes are directed to one drive.

The final, critical consideration is tempdb.  Put it on the fastest drive set(s) available.  If they're the same, spread it out, so it gets the best overall response.

2) Yes, it makes a huge difference.  Multiple logical drives on one physical drive takes away most of the advantages of multiple drives.  Each separate drive letter still gets a separate i/o q in SQL, but that's about all you get out of it, if all are on a single physical drive / drive set.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now