Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2012 Raid questions?

Posted on 2013-05-15
6
Medium Priority
?
297 Views
Last Modified: 2016-12-08
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Paul MacDonald
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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 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