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
Best Answer
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.