Solved

SQL 2012 Raid questions?

Posted on 2013-05-15
6
273 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 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

22 Experts available now in Live!

Get 1:1 Help Now