With regards to splitting the instance root there are a couple arguments in favor of doing it.
- Some People are in favor of keeping their "C" drive dedicated to just the OS and OS binaries. This can give you some different options for recovery in the event of a crash on the C drive, it can help keep the OS from causing or receiving space related issues from sharing with other apps.
- You are isolating SQL Server's binaries from other programs and ensuring availability of some of the critical folders like the Logs folder where error logs go - this folder needs to be accessible for SQL Servers start up. You are protecting yourself from others, basically.
You can put the SQL Server binaries/instance files in the same place you tend to put your other program files. But if you do that - at least make sure you take your system database files and potentially your default backup location and move it someplace else..
Here is what I tend to do when given an unlimited number of drive letters to play with (at a minimum.. Letters aren't important here):
- C - OS and system level files. Only
- D - Program files for all apps (including SQL Server)
- S - Instance level files/SQL Server system databases and log files typically (except for TempDB) (note.. If I have multiple instances, I won't make 4 of these.. I'd put all SQL binaries for all instances on S in most situations, with the folders providing the separation)
(ED- Another note - I often don't have an "S" drive available. At the end
of the day, having your system database files for Master, Model, MSDB
and Resource db living on the same drive as some of your user
database files, but in a separate folder for logical separation to keep things less confusing isn't the end of the world.)
- F - Data files for user databases
- L - Log file drive for user databases
- T - TempDB
- X - Backup drive (though in a lot of cases I elect to stream a backup across to a network drive, not paying for a copy after the backup and I'm immediately backing up to storage someplace else.)
I'll often have more data and log drives and sometimes another TempDB drive. Add in multiple instances and you can run out of drive letters quickly. You can certainly get away with putting your instance level files on C:. And I do a lot of health checks for clients that were setup like that - and I never say "oh wow.. we have to fix that now" - Now if their TempDB file(s) are there, too, I'll typically have them change that. Sometimes move their master and MSDB databases as well..
But the world won't end if you don't split these things up. I think the benefit is really just keeping -your- files separate. As a DBA you should have a healthy paranoia around other roles at your company, other applications, other installations, etc. and the more you can isolate yourself from the potential for conflicts, the better you'll be. And it gives you some more options for reinstallation and recovery. So yes separate your binaries from C.. But my advice wouldn't be to go crazy on a separate drive for each instance..
