SQL Server – Benefits of Having Instance Root Directory on a Separate Drive

sql serversql-server-2008-r2

I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed machine which is running an instance name other than the default and they've configured the instance root directory to be on the D drive along with the mdf files. This means that on what would normally be a relatively clean drive with just folders and database files on it I now have a full installation of the SQL Server binaries as well.

i.e. I now have the following :

C:\Program Files\Microsoft SQL Server\ --Base Install
D:\Microsoft SQL Server\MSSQL10_50.MyInstance --Instance Binaries
D:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\DATA --Data Files
E:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\LOGS --Log Files

Where normally I would run with something like :

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ --Base Install & Default Instance Binaries
D:\MSSQL\DATA --Data Files
E:\MSSQL\LOGS --Log Files

I can understand why having a separate instance binary folder is necessary, but I can't see why it would be useful to put all those binaries on a separate drive.

Can anyone tell me why it might be a reasonable thing to do? Or perhaps it just makes no difference at all? To me it just seems terribly untidy…

Best Answer

With regards to splitting the instance root there are a couple arguments in favor of doing it.

  1. 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.
  2. 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..

Related Question