none
SQL Server 2008 R2 file locations

    Question

  • Hi.  I know this is an often asked (and answered question), but hoping for a quick reminder and validation of what I found elsewhere.  Besides, it's been quite a while since I've done a SQL install.

    I need to install SQL Server 2008 R2 on a VM with shared storage.  I have only C, D, and L available to me.  I know to isolate user databases on D:, and tran log files on L:  Is there a reason not to install all binaries and shared files on C:?  Can I put all databases files (including system and user databses) in the same folder on D:, and all tran logs (including system and user logs) in the same folder on L:?  Or should I separate system files from user files?  Should I push the storage guy for a separate LUN/volume for tembdb database, or is that no longer necessary with shared storage?

    Thanks!

    Thursday, January 19, 2017 8:16 PM

Answers

  • Yes, putting user & system data files together on same drive is fine. And putting system and user log files on same drive is fine. Main thing is to keep data & log files on separate LUN's, if possible. You could put system db/log files and user db/log files in separate folders just for manageability or organization purposes, but otherwise it's not required from a technical standpoint.

    If I have high volume/heavy workload database(s), I like to put tempdb data file(s) on a dedicated tempdb drive if possible, so to answer your question, it depends on how heavy the db workload will be and if application makes heavy use of creating temp objects like temp tables as to whether or not you should recommend an additional tempdb drive. Kind of depends on performance of I/O subsystem too. If they are high-throughput disks vs lower, etc.

    If possible, I like to install SQL instance binaries on a separate drive other than the System drive (C: ) just in case OS / Server crash, it should make it a bit easier to recover and get everything back online again. Core/shared sql components still have to go on system drive.

    Another storage configuration recommendation for best SQL Server performance is to format data/log drives with 64k block size instead of the Windows default 4k block size. However, keep C drive as 4k block size.

    [Please mark as answer if this post helps you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, January 19, 2017 9:28 PM
    • Marked as answer by District9 Thursday, January 19, 2017 11:29 PM
    Thursday, January 19, 2017 8:29 PM

All replies

  • Yes, putting user & system data files together on same drive is fine. And putting system and user log files on same drive is fine. Main thing is to keep data & log files on separate LUN's, if possible. You could put system db/log files and user db/log files in separate folders just for manageability or organization purposes, but otherwise it's not required from a technical standpoint.

    If I have high volume/heavy workload database(s), I like to put tempdb data file(s) on a dedicated tempdb drive if possible, so to answer your question, it depends on how heavy the db workload will be and if application makes heavy use of creating temp objects like temp tables as to whether or not you should recommend an additional tempdb drive. Kind of depends on performance of I/O subsystem too. If they are high-throughput disks vs lower, etc.

    If possible, I like to install SQL instance binaries on a separate drive other than the System drive (C: ) just in case OS / Server crash, it should make it a bit easier to recover and get everything back online again. Core/shared sql components still have to go on system drive.

    Another storage configuration recommendation for best SQL Server performance is to format data/log drives with 64k block size instead of the Windows default 4k block size. However, keep C drive as 4k block size.

    [Please mark as answer if this post helps you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, January 19, 2017 9:28 PM
    • Marked as answer by District9 Thursday, January 19, 2017 11:29 PM
    Thursday, January 19, 2017 8:29 PM
  • "Is there a reason not to install all binaries and shared files on C:?"

    If you need them from another location for development or a sandbox.

      "Can I put all databases files (including system and user databses) in the same folder on D:, and all tran logs (including system and user logs) in the same folder on L:?  "

    I believe SQL Server manages the log files, I suggest using what Microsoft provides.

     "Or should I separate system files from user files?"

    Typically system files are separate from user files, so yes, I believe this would be applicable for organizational purposes. Especially, if there are multiple services running; those would most likely need to be within system file folder or such.

    "Should I push the storage guy for a separate LUN/volume for tembdb database, or is that no longer necessary with shared storage?"

    You could though this is based on your organizations needs. I am not too sure about the shared storage, though I think tempdb should be on the same server as SQL Server 2008 for performance reasons.



    • Edited by Juan Davila Thursday, January 19, 2017 8:33 PM Grammar
    Thursday, January 19, 2017 8:32 PM