[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

SQL 2012 Raid questions?

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
whorsfall
Asked:
whorsfall
1 Solution
 
Paul MacDonaldDirector, Information SystemsCommented:
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
 
sventhanCommented:
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
 
whorsfallAuthor Commented:
Hi,

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

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

Thanks,

Ward
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Paul MacDonaldDirector, Information SystemsCommented:
"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
 
insidetechCommented:
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
 
Scott PletcherSenior DBACommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now