Post reply

Location of system databases (Master, Model, MSDB) in a virtual world.

  • March 9, 2018 at 5:44 pm

    #333853

    We run all of our dozen+ SQL servers as virtuals under Hyper-V.   Our servers are configured with the Master, MSDB and Model system databases on E: and F: (the same as out user database) and Temps on G:.  Hyper-V allows snap-shots of drives.

    It seems to me like it would be better to put Master, Model and MSDB on the C: (system) drive.  If I ever have to do complete server restore I can just use the last snapshot of the C:.  Boot it.  Then restore all of my user databases.

    Does anyone see a problem with my thinking?

    In the old days when SQL server lived on actual hardware the C: drive dying was a real risk and it made sense to put the system databases on the data and log drives.  In the virtual world this doesn't make as much sense to me.  If I install a service pack that blows up a server I can always fall back to the last good server snapshot.  I do SQL backups Master, Model and MSDB... but they rarely change.  We don't add/remover users often or change security.  All the jobs in MSDB are generic across all of our servers and do not change.  We do a complete set of Complete + Differential + log backup so we can restore our user databases to any point in time down to 10 minutes.  Taking a snapshot of our Data (E) and Logs (F) drives seems redundant, not very useful in a disaster recover situation and takes up a bunch of backup media.

    We did a disaster recover drill.  The old SQL server with the system databases on the C: drive was a lot faster to get up and running than the new SQL servers with the system databases on E: and F:.

    What are your thoughts?

  • March 13, 2018 at 2:44 pm

    #1983052

    Nobody has any comments?  Genius?  Completely off my rocker?

  • March 13, 2018 at 7:22 pm

    #1983077

    There is no way in hell that I'll ever allow anyone in a company that I work for to install the system databases (or any other database) on the same drive as the operating system whether it's a VM or not.  There are just too many things that can go and have gone wrong.  For things like clustering, it can be a form of severe aggravation and death by SQL. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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