Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server drive configurations on SAN Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 1:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:56 PM
Points: 82, Visits: 301
Use to be that you broke your database files out to different drives, even on a SAN. D: for data, E: for logs, F: for large index file group, and G: for tempDB. The theory was that each drive had different disks and luns and spindles and whatever and it increased the efficiency of the database.

I am being told now that the new 2012 database server we will put all the files on one drive because it is a SAN and that is all taken care of by the SAN. From what I have seen on testing, I am not sure that is true, but I was told to let it be.

Is it true and it does not matter anymore or am I right to be concerned?

Thanks!!

The new SAN is from Nimble Storage if that helps.

Thanks!!!!

Jim
Post #1592327
Posted Monday, July 14, 2014 2:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 5:40 AM
Points: 80, Visits: 343
It is possible that the SAN has one (or more) large pools of storage - possibly with flash, SAS, and NL-SAS (SATA) drives in the same pool with a tiering algorithm that moves recently (or frequently) accessed data to the fastest disks. Their could be several sets of RAID5 or (hopefully) RAID10 sets backing the pool(s).

So, your storage admins think that it doesn't matter, and they may be right. Still, there may be less contention at some level if you ask for separate LUNs.

Monitor your latency: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

Post #1592345
Posted Tuesday, July 15, 2014 1:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:26 PM
Points: 3,214, Visits: 2,336
hmmm ... I believe that it it still a best practice to separate out the following to separate disk drive letters:
- database data
- database transaction log
- tempdb
- system databases
- database full backups
- database differential/t-log backups

Note: you should also have separate internal drive for the OS, the system pagefile and application installation software.

Your SAN administrators may think it is OK, but really it is not.
More drive letters mean more I/O paths from Windows to the storage - more I/O paths implies and should create more throughput. This is provided the HBAs, fabric and storage frame can handle it.

The only way that the 'one drive' scenario begins to make sense is if you are using 'mount points' for your SAN storage within Windows.

There can be a rather indepth discussion when it comes to RAID levels. Once you decide upon a drive strategy then it would be time for the RAID level discussion.




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1592772
Posted Tuesday, July 15, 2014 2:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 345, Visits: 3,344
The vendor will have a best practice guide for SQL Server on their SAN.
I suspect it may suggest otherwise.
I'd most certainly keep SQL Server and "Other" storage totally separate - and that includes the OS drives for SQL Server boxes. Many (not all - the one at my place is spot on) simply regard a SQL Server box as another file server, it's most certainly not - totally different situation and requirements


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1592809
Posted Tuesday, July 15, 2014 2:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:26 PM
Points: 3,214, Visits: 2,336
I would be cautious concerning the vendor best practices since they are 'one size fits all' usually.

The 'best practice read order is:
- Microsoft
- your server hardware manufacturer
- your fiber channel card manufacturer
- then the SAN vendor

I have used Hitachi, IBM and EMC SANs in the past.

FYI ... We are an HP HW shop. We use both IBM and IBM SVC and are migrating EMC VNX and EMC VMAX SAN architectures for all of our heavy hitting VLDBs.




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1592811
Posted Tuesday, July 15, 2014 2:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:56 PM
Points: 82, Visits: 301
I have always broke them up but my new client insists that it is OK. Even when I was testing a report query that ran for 32 minutes on the new 2014 server (with all the files on one SAN drive) and 17 minutes on the old 2005 server (with separate drives). I have pushed it as far as I can, now we will have to see if they are right. We migrate this weekend.

Thanks!

Post #1592815
Posted Wednesday, July 16, 2014 10:47 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:26 PM
Points: 3,214, Visits: 2,336
good luck to you and your client.

if anything, just remember there is always time to do it again ... and again ... and again ...

consulting does have its benefits !




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #1593182
Posted Thursday, July 17, 2014 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:06 PM
Points: 41, Visits: 613
Also, we format our SQL Drives with a 64k blocksize as the OS drive is formatted with the default 4k blocksize. We use separate drives for OS, SQL Data, SQL Logs, TempDB and SQL Backups on our EMC SAN.
Post #1593814
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse