July 14, 2014 at 1:47 pm
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
July 14, 2014 at 2:21 pm
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/[/url]
July 15, 2014 at 1:23 pm
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.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 15, 2014 at 2:39 pm
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.
July 15, 2014 at 2:46 pm
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.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 15, 2014 at 2:54 pm
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!
July 16, 2014 at 10:47 am
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 !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 17, 2014 at 1:36 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply