System Databases on C or with User Databases?

Question

Post reply

System Databases on C or with User Databases?

n00bDBA

SSCrazy

Points: 2986
More actions
April 13, 2012 at 3:33 am

#256394

Hi all,
Just watching a video of Robert Davis talking about putting a better SQL Server into production. He came up with a few things i am currently questioning and was hoping for some insight from those more experianced!
1: Install SQL Server onto a separate drive from Windows? IF you do this. where do you install other software? Virus protection, system center client etc? C: with Windows OS or D: with SQL Server?
2: When you install SQL do you put the System Databases on the same drive as your User Databases or leave them on C: with the OS (Or D: if you put the SQL install on another drive)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

If you have enough disks, best setup would be:

Sys db's on own disk, but have tempdb on its own drive.

user db's on own disk.

split mdf and ldf's as well.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Henrico Bekker (4/13/2012)
Sys db's on own disk
user db's on own disk.

I never see a need to dedicate a whole drive\volume to what are essentially little used and extremely small databases (by comparion to user dbs).

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 3

I usually have Windows and SQL binaries installed on the C drive. I move the data and log file defaults to another drive (preferably 2 separate physical drives) and then the backups of the data to their own drive, the log drive, or the c: drive.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Henrico Bekker (4/13/2012)
If you have enough disks, best setup would be:
Sys db's on own disk, but have tempdb on its own drive.
user db's on own disk.
split mdf and ldf's as well.

I would say the best practice would be, always leave C: drive alone for Windows OS. Install all other applications on D: and then if you have enough disks then break down as Henrico mentioned.

TA.

Regards,
SQLisAwe5oMe.

n00bDBA SSCrazy Points: 2986 More actions · Answer 5

Hey all,

Thanks for the advice.. Sadly never had a server with that many disks to seperate windows from applications, But I try to put Logs and Data files on different Disks.

So Steve! I take it you include the system databases as SQL binaries?

If youve moved the system files to the data and log drives. Would you move them back to the C drive?

Thanks for the replies! and sorry for the delay in getting back to you.

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 6

Henrico Bekker (4/13/2012)
If you have enough disks, best setup would be:
Sys db's on own disk, but have tempdb on its own drive.
user db's on own disk.
split mdf and ldf's as well.

I'd stick with this unless you have a specific reason not to. Like say you had a old highly active etl server full of dts packages with a 150GB msdb, or you have no way to get more drives.

What you want to avoid is a log file, tempdb, VSS or a pagefile growing out of control on the C or where master or tempdb is, and shutting down the entire server.

For performance you typically want tempdb split into seperate files, how many and how big depends on your environment. Then put them on their own fast drive.

The log files work sequentially and are slowed down if they are mixed with anything that reads/writes randomly. If you dump a bunch of log files on 1 physical disk you'll have the same issue. Though if you have enough spindles it won't matter.

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 7

master/model/msdb usually are small enough, with few transactions, so including them with binaries is fine. Tempdb I treat as another user database.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Steve Jones - SSC Editor (4/21/2012)
master/model/msdb usually are small enough, with few transactions, so including them with binaries is fine. Tempdb I treat as another user database.

+1, but the only exception is if the instance is acting as a distributor.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 9

Good point, Perry. I don't think of the distributor database as a system database, and even from Microsoft's use of it, it's in some type of purgatory between system and user db, but in general I'd treat that like a user db.