OS + SQL Server binaries on same drive?

  • Balkoth

    SSC Eights!

    Points: 954

    Hello everyone,

    My question deals with best practices. When installing SQL Server 2008, should the OS and SQL Server binaries be installed on their own separate drives even when the System Databases and TempDB will reside on their own disk drives? For example:

    C - OS

    D - SQL Server binaries

    E - mdf + ndf files

    F - ldf files

    G - TempDB

    If I shouldn't install the OS and SQL Server binaries on the same drive, what are the reasons?

    Thank you in advance for any help or direction given.

  • GSquared

    SSC Guru

    Points: 260824

    I've always put SQL Server's binaries in the default location in Program Files on C drive. Can't think of any reason to do otherwise.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715344

    I'm with GSquared. I've never seen a reason to move SQL Server binaries anywhere. It's the data, log, and backups that are important.

  • Lowell

    SSC Guru

    Points: 323376

    i seem to remember that the biniaries that are the core srvice and stuff must be installed on the %systemdrive%, with no choice in the matter...you can move the databases, i thought, but the binaries go on that drive(C: on most of my machines)

    I think the client tools can be installed/re-installed elsewhere, but

    am i wrong or did that change?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • KerberosHoldingsLLC

    SSC-Addicted

    Points: 475

    balkoth (6/20/2011)


    Hello everyone,

    My question deals with best practices. When installing SQL Server 2008, should the OS and SQL Server binaries be installed on their own separate drives even when the System Databases and TempDB will reside on their own disk drives? For example:

    C - OS

    D - SQL Server binaries

    E - mdf + ndf files

    F - ldf files

    G - TempDB

    If I shouldn't install the OS and SQL Server binaries on the same drive, what are the reasons?

    Thank you in advance for any help or direction given.

    Assuming that the split between MDF's and NDF's was well thought out, I would split E as mdf on one LUN, NDF on another to benefit from potential parallel reads.

  • Balkoth

    SSC Eights!

    Points: 954

    Thank you everyone for the quick responses! I received my answer and I was given more food for thought, thanks again.

  • Syed Jahanzaib Bin hassan

    SSChampion

    Points: 11410

    If you have multiple drives not partition then separate drives gives you performance advantage,when you go for the default drive then there are several other processes are running on this default windows partition or drive we can say like OS level logs,Master File table,log and etc .

    If you have multiple drives then go for separate drives for

    tempdb

    SQL Server Logs

    Binaries

    and offcourse database files should have different drives

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    From past experience I have seen the below issue , as a result i think it would be better to install SQL on its own drive.

    The OS drive becomes full because users keep connecting to the system and copying files to their desktop , pretty soon the drive runs out of space. i.e 0 mb free on c drive and user profile stop getting loaded.

    Jayanth Kurup[/url]

  • KerberosHoldingsLLC

    SSC-Addicted

    Points: 475

    Jayanth_Kurup (6/21/2011)


    From past experience I have seen the below issue , as a result i think it would be better to install SQL on its own drive.

    The OS drive becomes full because users keep connecting to the system and copying files to their desktop , pretty soon the drive runs out of space. i.e 0 mb free on c drive and user profile stop getting loaded.

    Best practice recommendation: never, ever, under any circumstances, allow users to have console or share access to the server running sql server.

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    Am sorry i should have mentioned this is a development environment.

    Jayanth Kurup[/url]

  • KerberosHoldingsLLC

    SSC-Addicted

    Points: 475

    Jayanth_Kurup (6/21/2011)


    Am sorry i should have mentioned this is a development environment.

    Understood, provided your UAT environment matches production. It's the only way to do valid UAT testing.

    Also, for the record, when I refer to a LUN, I am referring to a RAID set of physical disks, not some logical grouping. And I agree with one of the previous posters, in that splitting the OS from the sql binaries by physical LUN is a good practice. One needs to leave a fair amount of free space for the OS. Microsoft, in the infinite wisdom, insists on dumping a lot of transient garbage on the the OS drive during OS and/or SQL patch / SP installation.

    In addition, the OS swap file should go on its own, separate, physical LUN. Something along the lines of:

    C$ OS

    E$ OS Swap File

    F$ SQL binaries

    G$ Program files

    H$ SQL mdf files

    I$ SQL ndf files

    J$ SQL log files

    K$ TempDB

    L$ SQLIO files (IO files from ETL processes, scripts, etc.)

    M$ SQL Backup (*.bak files, which should then be copied to another server

    restore testing).

  • GSquared

    SSC Guru

    Points: 260824

    KerberosLLC (6/21/2011)


    Jayanth_Kurup (6/21/2011)


    Am sorry i should have mentioned this is a development environment.

    Understood, provided your UAT environment matches production. It's the only way to do valid UAT testing.

    Also, for the record, when I refer to a LUN, I am referring to a RAID set of physical disks, not some logical grouping. And I agree with one of the previous posters, in that splitting the OS from the sql binaries by physical LUN is a good practice. One needs to leave a fair amount of free space for the OS. Microsoft, in the infinite wisdom, insists on dumping a lot of transient garbage on the the OS drive during OS and/or SQL patch / SP installation.

    In addition, the OS swap file should go on its own, separate, physical LUN. Something along the lines of:

    C$ OS

    E$ OS Swap File

    F$ SQL binaries

    G$ Program files

    H$ SQL mdf files

    I$ SQL ndf files

    J$ SQL log files

    K$ TempDB

    L$ SQLIO files (IO files from ETL processes, scripts, etc.)

    M$ SQL Backup (*.bak files, which should then be copied to another server

    restore testing).

    You need to add three or four more to that for separate tempdb data files. And the tempdb log should really be on its own I/O system, too, of course. And really, each OLTP database should have its own I/O channels, to avoid contention. And ...

    ... But really, that's almost certainly just going to make administration more difficult. Separate the O/S and binaries from the data files, and separate those from the log files, and tempdb if possible. Anything beyond that is probably premature optimization for most companies. Even the separation of tempdb from the rest is overkill unless you have the traffic to warrant it.

    And if the reason to separate the binaries from the O/S is that developers are filling up C-drive with junk, or because Windows patches are filling it up with temp files, then FIRE YOUR SYS ADMINS. Overfull C-drive on any system being used in a business environment is inexcusably incompetent administration. Fire them, and scan for malware on every system they ever touched, because if they're that incompetent, they almost certainly missed things like BonziBuddy on the web server or something equally moronic. If your devs are the problem, fire them too.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715344

    I tend to agree with GSquared above. The only other separation I worry about is backups. Data backups should not be with data files, and log backups should not be with log files, if possible.

    If I have 3 drives (LUNs, arrays, whatever, just 3 separate physical, not logical, locations), I would do

    1: OS, SQL binary, backups (data + log)

    2: data

    3: log files (ldf)

    That's usually the minimum. If I can get more, I might move tempdb separate, or heavy used databases to separate LUNs, but at that point, I'd be looking at wait stats and usage across the various files to determine what might benefit from separate drive heads.

  • Syed Jahanzaib Bin hassan

    SSChampion

    Points: 11410

    Do not split more drives on the same RAID group

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • brhunter-540485

    SSC Enthusiast

    Points: 146

    When installing SQL server to the same drive as your OS, be very diligent in making sure that EVERY file that SQL server can cause to grow is moved off the system drive onto a drive other than the system.  We recently had an issue where SQL Server / SSIS was installed to the system drive.  We have a sizeable SSIS ETL that runs on this machine.  The ETL process filed up the SSISDB data files (default System drive install) and crashed the machine.  Had we been able to get to a command prompt, we could've resuscitated SQL server in under an hour. As it stood, it took much longer to get the machine back.  

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply