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

share|improve this question

migrated from stackoverflow.com Nov 12 '12 at 15:09

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 9 down vote accepted

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)
  • 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..

share|improve this answer

Well, there are only 26 possible drive letters in Windows. 1
But you have the possibility to use mount points. 2

So if you need to install 25 different (SQL, Web, ...)Servers on one machine it could make sense to have one drive-letter for one of the servers.
But if you have only one server it would make more sense to have different drive-letters for the log files, the database and the program-files.
You can also split the log/database/program-files if they are in different folders.

  1. stop the SQL server
  2. add a partition
  3. copy all the database-files to the partition
  4. change mount point to the folder, where the database-files have been (for example: d:\database)
  5. finished
share|improve this answer
    
This doesn't answer the question at all. I wasn't asking about drive letters or mount points, I was asking why it might make sense to put the instance root on a separate drive. Since these are system files, to me it makes sense to leave them on the system drive. –  adhocgeek Nov 12 '12 at 10:58
    
I'm sorry I misunderstood you. A good point to put everything belonging to SQL together on one drive could be the simplicity of a backup-script, which only needs to be run on this drive. But if you put everything in one drive only, the database, binaries and logs should be in seperated folders. And I came to the point with the mountpoints, that you can reorganize the Data on different partitions without changing the whole system. –  gnomix Nov 12 '12 at 14:15
    
@gnomix You can always edit your answers (and questions) by clicking the 'edit' link below them. –  dezso Nov 12 '12 at 14:22
    
@gnomix The simplicity of potential backup scripts is one of the main reasons I tend to put Data and Logs on their own drives, but I have never had a need to backup the system files. I'd like to know if this is a common requirement. My instinct suggests to me that putting system files on a non-system drive could cause issues. –  adhocgeek Nov 12 '12 at 14:31
    
Plus, separating a drive into two or more partitions won't really help disk I/O. But yes, I totally agree with you. It makes things look so much nicer to put the database files in the root of the drive and it makes it easier to spot things. I usually put everything into directories like D:\Data E:\Logs F:\Backup ,... –  user1207758 Nov 15 '12 at 21:56

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.