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.

I'm looking for some clarification of various things I've read. I understand it to be best practice to store your transaction logs on a separate drive (spindle) than where you MDFs and NDFs are housed.

Does this same principal hold true in a virtualized environment on a SAN?

share|improve this question
2  
Sort of similair question : dba.stackexchange.com/q/35404/8783. Also consider partition alignment as a check mark to make sure it is as per best practice. –  Kin Mar 22 at 16:59
    
It depends - does your SAN have (or allow) dedicated spindles? I.e. are all your "drives" going to the same physical disks, and do they have to? Can you get SSD's for certain things (separating data and log files tends to make less difference to almost no difference once good SSD's are in the mix)? –  Anti-weakpasswords Mar 22 at 22:52

1 Answer 1

up vote 6 down vote accepted

It's a good idea to keep them separated, even if the drives are currently backed by the same storage device. It buys a lot of future flexibility, at a slight expense in management overhead for both the DBAs and the Windows/storage admins.

  • If you want to change the physical storage that backs either of the drives in the future, you won't have to go through all the databases and move the files at that point -- they'll already be in the right spot. Simply present different storage to the VM with the same drive letter, and assuming all the files and permissions are there, you're done. Simple.

  • If you collect Performance Monitor (PerfMon) physical disk stats, it's a good idea to record data file activity separately from log file activity. If that's even possible with all the files on the same drive, it probably won't be very easy.

Really I can't think of any significant downsides. There is the possibility that if you want to use a storage-level snapshot technology in the future, the SAN may not support having the files in two different physical locations. Consult your SAN admins or vendor on this; it may not be applicable now necessarily, but might come into play in the future. This isn't a negative, but it may play into the setup strategy as a constraint/requirement.

Aside from that, it's just a bit of extra management to keep things in the right spot, and you have to make sure that any automation scripts you have also respects the difference in location by the type of file. But the latter is something you should be doing anyway when writing scripts, so I don't see that as a negative.

It may be prudent to separate tempdb specifically into another location as well.

share|improve this answer
    
Thank you for your time Jon, I'll be sure to make the necessary adjustments. I remember reading somewhere and heard from my own SAN admin that it doesn't matter where the files are stored because it is shared resource anyway. That seemed counterintuitive to other articles I've ready, hence the impetus for the question. –  Sean Perkins Mar 22 at 18:17
    
@Sean: No problem. Yep, your SAN admin is right... at the moment. But it's tough to say where it might go in the future. Please note that I updated my answer with something I thought of just now. Sorry I didn't think of it at the time. –  Jon Seigel Mar 23 at 16:57

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.