November 17, 2014 at 3:25 pm
I had my infrastructure manager recently ask me why the system databases are in a different directory than the user databases.
His other question, which I was totally clueless on, was why the system database log files (LDFs) are in the same directory as the MDFs, which our user databases have them in different directories.
I've been unable to find any URLs as to the Why's. If you can forward me an article as to why that is, it would help a lot.
TIA
Andre
November 17, 2014 at 3:30 pm
Usually you stick the system databases (besides tempDB, that has its own set of rules) onto their own spindle set in self defense. Master and MSDB are a royal PITA to rebuild if they go down, and don't have a lot of churn. They're used for logins, agent schedules, job storage, database settings, etc. Drives which do have a lot of churn, your data drives, are a lot more likely to go under.
Thus, you keep your critical DBs (Master, MSDB, and Model (needs to be there for tempDB to initialize)) out of harm's way. Leaving the logs on the same drive because there's nearly no churn is fine... there's not a lot of change against them anyway.
Caveat: TempDB should be treated like a hyperactive User DB with a sugar addiction. Try to get that DB off in it's own cell (spindle) and pad the walls (put the ldf on a different spindle).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2014 at 4:17 pm
Besides Tempdb which can be a very big database, the other system databases are tiny, i.e their sizes are in MBs, and the question for their log files fails in water. Keep them on the OS drive which is usually equal to "don't touch them".
In general you're not recommended to use the system databases for user purposes. Only the Tempdb is the one that can be massively used by your development, and it's to be configured for such cases.
Igor Micev,My blog: www.igormicev.com
November 17, 2014 at 4:37 pm
The best answer I've had for keeping the LDF and MDFs for system databases in the same directory is that they're that way by default on install and there's no reason to move them, since other than TempDB, they're usually low impact.
TempDB IS in a different and very fast separate drive letter.
November 17, 2014 at 8:50 pm
andre.quitta (11/17/2014)
I had my infrastructure manager recently ask me why the system databases are in a different directory than the user databases.
Contrary to what the others have said and except for TempDB, mine aren't. When I do an install of a new instance, I put the MDFs for Master, Model, and MSDB on the same "drive" as the user MDFs (Drive M:). I do the same with the LDFs, which are on a separate "drive" (Drive L:) from the MDFs/NDFs. To be sure, none of that is on the C: drive, which I relegate to only the operating system except for the PAGEFILE (Drive P:). There are some MDFs/LDFs that I don't keep on those two user DB drives. The "Archive" database MDFs and LDFs are located are located on Drive K: ("K" for "Keeper"). TempDB MDF/NDFs are on Drive T: and TempDB LDF is on Drive U:. We have "scratch pad" databases and the MDF/LDF/NDF files for those live on Drive S:.
Backups live in a whole 'nuther world because if the SAN catches fire or takes some other kind of hit, I don't want my backups to be there. It takes more time and a bit more care to keep backup performance up but I use NAS for my backups. I've also figured out a way to map the UNC (varies from one environment to another) of the NAS to Drive R: (for "Restore") so that I don't need to worry so much about where the backups actually are. I just reference Drive R: for the backups.
His other question, which I was totally clueless on, was why the system database log files (LDFs) are in the same directory as the MDFs, which our user databases have them in different directories.
As the others have stated, that's simply what the defaults are. In theory, you can have a desktop box with just a single Drive C: for SQL Server and that's the minimum that they built the default around. With SANs and the ridiculously large sizes of drives nowadays, it's a bit of a chore to actually put things on "separate spindles" like they should be without wasting an absolute shedload of disk space especially since RAID uses multiple incredibly large disks. MDFs should be on separate spindles from LDFs (hardly ever happens in reality because of what I just said) so that if one or the other goes haywire, you either still have an ACID MDF or you can do a "TailLog" backup to recover but would also be crazy to dedicate a single large drive to the very small sizes of the 3 "M" system databases. So we just settle for separation by "logical" drives according to how the SAN was configured and hope the SAN gods accept our burnt offerings. :w00t:
I've been unable to find any URLs as to the Why's. If you can forward me an article as to why that is, it would help a lot.
I've never actually looked for the reasons WHY on the default installations. I've always just taken it for granted and have always changed it (to keep Drive C: "pure" as the operating system disk), so I can't actually point you to a decent URL on the subject other than the installation guide which explains the defaults but not the reason WHY for the defaults. It's just assumed that every system is going to have a Drive C:.
I don't know if any of that answers your questions the way you thought they should be answered, but that's my reasoning and my recommendations. Your mileage may vary. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply