October 17, 2008 at 5:20 pm
Assuming no constraints on hard drive space, what drive should the system databases (master, model, msdb, tempdb) be located? Should it be on the main OS drive (normally C)? An auxilary drive containing the other "user" databases? Or does it not matter at all?
Previously, all databases were on the main OS drive (Dell PERC 5/i SCSI drive). However, we just bought an auxilary drive (NEXSAN SATABeast SCSI Disk Drive) and planned to migrate all user databases to it. However, I didn't know if I should move the system databases as well.
October 17, 2008 at 5:58 pm
model and msdb don't matter... master doesn't really either
Ideally tempdb is on a different drive from the user databases because of the IO throughput.
there are lots of read/writes on tempdb; its best if you're not reading to one sector and writing to another sector, but rather to another disk.
it would also be ideal if the drive were striped as opposed to mirrored or RAID-5.
Striped and mirrored is ideal. RAID 5 is next best. mirrored is poor. striped only is OK if you don't need to recover anything from it ever 🙂
~SQLBOT
Craig Outcalt
October 17, 2008 at 6:33 pm
SQLBOT (10/17/2008)
model and msdb don't matter... master doesn't really either
Are there any advantages/disadvantages to moving them/leaving them alone? For example, do I gain/lose anything if SQL Server crashes and needs to be completely rebuilt?
October 17, 2008 at 9:59 pm
the only realy thing here is disk availability.
Having master on some form of RAID is invaluable as you likely know. After that rely on your good backups and documentation for recoverability in the event you lose master.
Many folks will claim the best overall approach is master, model, msdb on C; tempdb on its own drive and the data files on the other.
The benefit is if you lose the data file drive, you still have master... my thought on this is that you're still down, but recovery is simpler.... and now you have the added problem of possibly losing the C drive with your binaries and master, which is an entirely different recovery scenario and adds 2X the documentation to your recovery procedures 🙂
In this day and age with a SAN, lots of this thinking is outmoded, but still clings on to our best practices.
If I lose a disk today it's probably because of an HBA or fibre switch. It's not even my job to recover that!
~Craig
Craig Outcalt
October 18, 2008 at 5:09 pm
SQLBOT (10/17/2008)
ideal if the drive were striped as opposed to mirrored or RAID-5.
RAID 5 employs striping with parity !!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2008 at 4:09 am
Hi...
Yes go with what Craig suggested. Put tempdb on a separate drive(It'll be better if it is a RAID array and best will be RAID 0 which will give best read & write performance) And you and also have multiple files on different drives but with the same physical size (otherwise the largest file will be used the most). It will be better the number of physical files in tempdb is equal to the number of processors in the server. But it all depends on the over all size of you server and number of databases running plus how much tempdb is being used by each db.
Here are some more links for your reference.
http://sqlblogcasts.com/blogs/thepremiers/archive/2007/10/17/tempdb-best-practices.aspx
http://www.mssqltips.com/tip.asp?tip=1432
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1307255,00.html
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Hope this helps.
Rohit
October 21, 2008 at 5:26 am
I would agree that placement of master, mssqlsystemersource, and model does not matter. The main drivers for any type of DB placement are data security and performance, neither of which really apply to these DBs. It is definitely critical to have good backups of master and mssqlsystemresource in case anything goes wrong, but that is all. My advice is to take a copy of the mdf and ldf files of these 2 databases when SQL is stopped and save this to a separate drive, as well as taking normal SQL backups of master while SQL is running.
When you look at system maintenance, SQL2005 SP2 was definitely more restrictive on where master and mssqlssytemresource DBs could be located than was SP1. My view is there is an ongoing risk that moving master or mssqlsystemresource from its default location could cause problems with applying maintenance, and I will continue to hold this view until we have had at least 4 years of updates from MS where placement of master is not an issue.
msdb can be used by a lot of things nowadays, and regarding placement I would treat it the same way as any user DB. The start point for this is to put the data and log files on separate drives, then do whatever other work is needed depending on the amount of use msdb has.
tempdb is critical for performance. Again, this should have data and log files on separate drives. The data file should be on a drive shared with other DBs, and normally there should be one data file per processor core. If the drive holding tempdb is a SAN LUN and spread over many physical devices, it is normally OK to put all the tempdb data files on a single drive or mount point. If the tempdb location maps to DAS disks, then you want to think about spreading the tempdb data files over multiple drive letters or mount points.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply