?
Solved

SQL Server Disk Drives

Posted on 2012-09-06
6
Medium Priority
?
561 Views
Last Modified: 2012-09-07
Hello All

As far as I am aware best practice states, when installing SQL server, to separate Data and Logs onto separate disk drives.  Is this still the same when you are installing SQL server onto a virtual machine?  So you have a separate virtual disk for Data and Logs, I assume you should aim to have the data on one LUN and logs on another?

Cheers
V.
0
Comment
Question by:vision_on
6 Comments
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 38372287
Yes, because you need fast operations.

Install OS in RAID-1 and then configure RAID-1 for logs from 2 separate LUNs and RAID-5 from 3 separate LUNs for SQL databases

I have set up similar configuration (based on SQL Server 2008R2) few months ago and everything works smoothly

Regards,
Krzysztof
0
 
LVL 12

Expert Comment

by:Seaton007
ID: 38372291
That is correct.  They are separated for better performance, so separate LUN's/spindles is the way to go.
0
 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 2000 total points
ID: 38372426
IO is about physical disks.  If you're LUNs are on the same physical array, performance improvement would be negligible compared to that of a single LUN (albeit their might be a minor boost depending on caching in relation to the same LUN).

The idea of separating logs from data is that logs are very heavy on sequential writes meaning physical heads do not need to seek back and forth across the platters like they would for random data access.  With spindles dedicated to the logs, the logs get a boost purely in terms of seek time and the data spindles are not clobbered by more random writes  that by themselves are not random at all.  That said, I'd say you need separate LUNs on separate physical disks.  If you're short on space, I have shared space with the logs or the data on the OS disk, since OS activity is relatively minor on a dedicated sql machine.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:vision_on
ID: 38372530
So is it really necessary to create a RAID 5 volume (3 separate disks presented to the VM)within the OS of the VM for Data and a RAID 1 volume (2 separate disks presented to the VM)within the OS of the VM for logs?  The LUNS will already span disks across the storage subsystem.

I thought it would be sufficient to have 1 disk for OS on one LUN, 1 disk for Data on another LUN and 1 disk for Logs on another LUN, is that not the case?

Cheers
V.
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 38372765
Yes, you're right. But in case of single LUN for logs and databases when disk would fail, you loose all data and you need to add new drive. After that, you need to restore logs or databases from backup. You need to be sure that your SQL server is backup regularly and without any problem.

However, in the second case, double RAID may lead to lower performance. I'm not sure what's worse :)

My scenario was addressed to physical machine

Krzysztof
0
 
LVL 11

Accepted Solution

by:
b_levitt earned 2000 total points
ID: 38372867
You are correct - let  your SAN/VMHost handle your physical arrays.  You would NOT need or want to create a software raid inside of the OS.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 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