Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As part of my ongoing saga of migrating from one SAN to another, the new SAN vendor said that I need to have my index data file in the same drive as my primary data file. Otherwise, given the tools that come with the SAN, I would need to create a new LUN just for this database and create it as its own volume.

At any rate, is this an acceptable practice ... to put index filegroups (.ndf) on the same drive as the data file?

Thanks

share|improve this question
    
Has a vague whiff of bovine fecal matter to me. Take it back to the SAN vendor and get them to explain why they believe this is the case. OTOH I can't see any reason why you shouldn't put indexes on a separate filegroup. Doesn't matter what LUN the files in the filegroup are mounted off. –  ConcernedOfTunbridgeWells Feb 27 '12 at 16:37
    
it has to do with the expected configuration by the snapshot manager. we've been pushing back, but they're pretty adamant. at any rate, i know best practice is to split out data and log files, but i was unsure about these secondary files. thanks. –  swasheck Feb 27 '12 at 16:42
    
I can see the reasoning now. I presume they're talking about LUNs on a per-database level because the snapshot manager can do it by file or by LUN? –  ConcernedOfTunbridgeWells Feb 27 '12 at 16:45
    
The snapshot manager is only at the volume level, so all databases in that volume must have the same configuration. Of the 5 databases on this test server, only 1 has the secondary data file for the indexes. The choices are to either move the secondary data file into the LUN/drive with the primary data file, or to create a completely separate structure for this database and put it in its own volume. –  swasheck Feb 27 '12 at 16:53
    
Do you actually need the snapshot manager for your DR - can you do something with replication instead? –  ConcernedOfTunbridgeWells Feb 27 '12 at 16:56

1 Answer 1

up vote 6 down vote accepted

It sounds like the vendor's snapshotting functionality is putting some pretty serious limitations on your flexibility for database layouts. If you have performance issues (and it would be a very long way from the first time someone had database performance issues on a SAN) then you have very little flexibility to spread the database across multiple physical volumes.

Does the vendor insist you also put the log files on the same volume as well? If so, then your log files will be on a busy shared volume with no write buffering from the SAN's cache due to Microsoft's policy I/O reliability certification programme for SQL Server. MS requires SAN vendors to honour FILE_FLAG_WRITETHROUGH, which SQL Server opens its files with. If your SAN vendor is going to force ALL files for a database to live on the SAME volume you could have performance issues.

I'd look into alternatives to the snapshot manager for DR before signing up to that. What did you do on your old system?

EDIT Per the chat - if you can use mount points to organise your database files then one set of LUNs per database is probably better than forcing all the databases into the same format.

share|improve this answer
    
It's less about where the files are and more about every single database on the volume needing to be in the same configuration. The secondary index file is the only issue because only 1 of 5 databases has this configuration (and that ndf is on a separate drive/LUN). All other dbs have logs on separate drive from primary data files. –  swasheck Feb 27 '12 at 17:12
    
@swasheck - What do you mean by configuration - logging modes and suchlike? –  ConcernedOfTunbridgeWells Feb 27 '12 at 17:13
    
Sorry. By configuration I mean file system structure and data file strategy. Thus, all volumes must use drives X: and Y: for Data and Logs respectively. If we use Z: drive for indexes, then all databases who have data files in X: and logs in Y: MUST have an accompanying secondary data file in Z:. One alternative is to move the secondary data file for that one db into the X: drive. Then all databases have their data files in X: and log files in Y:. –  swasheck Feb 27 '12 at 17:24
    
The other alternative is to create another set of LUNs for this database. D: for data, E: for log, and F: for indexes. This will then be grouped as its own volume and the X:, Y: will be another volume. –  swasheck Feb 27 '12 at 17:25
    
@swasheck - There was an interesting conversation on The Heap a couple of days ago about mounting database volumes under a path rather than as drive letters. If you go with separate LUNs then mounting them under a path rather than a drive letter might be easier to manage. –  ConcernedOfTunbridgeWells Feb 27 '12 at 18:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.