Disk 'layout' question

Question

Disk 'layout' question

Sam Greene

SSCertifiable

Points: 5252
More actions
July 31, 2008 at 10:59 am

#130426

Hi,
We have our drives laid out like this:
C:Local OS
SAN
D:System (Program Files)
G:Data
I:Data
J:Transaction
We now have the option of selecting which of 2 Fiber Channel HBAs the drives can use until one of the cards fail, at which time, they will both use a single card.
I was thinking either
Option A
1. Backups and System
2. Data 1, Data 2, Transaction Logs
Option B
1. Backups, System, Transaction Logs
2. Data 1, Data 2
Here are some assumtions I've made...
Conventional advice on SQL drive layout is that data files should not be on the same drive as OS files and that transaction logs should be on different drive than the data files. (Can transaction logs be with System files or backup files?)
Backup and Data should be separate so backups don't slow the system.
Let me know if you have any suggestions or experience with this. I may do some performance tests in different configs.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Somewhat hard to answer that. First of all though can't the HBA's be load balanced? If so that would seem to be the best solution.

Ultimately your utilization of the system disks through the HBA are going to be limited and the backups are something that is scheduled and controlled by you. So, having those with any of the combination is most likely going to be ok.

The main question then is how much you are using the transaction log for the databases. Simple backups of the translog should give you a pretty good idea of how much data change you are seeing in the time frame between tranlog backups.

Understanding your database read and write utilization is going to be critical as well so hopefully you have those balanced on disk based on that already. I would most likely consider those to be seperated between the 2 HBA's.

In most, and I mean most, environments you will not see your HBA communication become a bottleneck before you are well taxing your disk IO with that being the bottleneck so, you really do have some great flexibility here.

I would again ask if they can be load balanced though.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

the 2 HBA's should be multi homed so that in the case of an HBA failure you dont lose any of your data drives.

In the options you are quoting loss of an HBA or its network path means loss of data drives. Each HBA must be able to service all LUNs

sam (7/31/2008)
Hi,
We have our drives laid out like this:
C:Local OS
SAN
D:System (Program Files)
G:Data
I:Data
J:Transaction
We now have the option of selecting which of 2 Fiber Channel HBAs the drives can use until one of the cards fail, at which time, they will both use a single card.
I was thinking either
Option A
1. Backups and System
2. Data 1, Data 2, Transaction Logs
Option B
1. Backups, System, Transaction Logs
2. Data 1, Data 2
Here are some assumtions I've made...
Conventional advice on SQL drive layout is that data files should not be on the same drive as OS files and that transaction logs should be on different drive than the data files. (Can transaction logs be with System files or backup files?)
Backup and Data should be separate so backups don't slow the system.
Let me know if you have any suggestions or experience with this. I may do some performance tests in different configs.
thanks

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sam Greene SSCertifiable Points: 5252 More actions · Answer 3

Perry Whittle (7/31/2008)
the 2 HBA's should be multi homed so that in the case of an HBA failure you dont lose any of your data drives.
In the options you are quoting loss of an HBA or its network path means loss of data drives. Each HBA must be able to service all LUNs
sam (7/31/2008)
We now have the option of selecting which of 2 Fiber Channel HBAs the drives can use until one of the cards fail, at which time, they will both use a single card.

I thought that's what I said right there - we are trying to set up which drives will be on which HBA when both are functioning.

I'm waiting on an answer from the windows admin to see if the drivers support load balancing, but I was also of the opinion that the SAN disk access would be the bottleneck in the flow, not the fiber optic.

Thanks for the help so far.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Don't forget about your tempdb data files.

Those need to be separated from the rest: separate disk array, device etc. (I'm not clear on this terminology).

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]