HookSqlDba7 (2/27/2015)
With SQL Server 2012/2014, what system databases are requried in order for sql sever to start? Of the following system databases:master - contains all system-wide information.
msdb - holds information for SQL Server Agent; jobs, operators, alerts, etc.
model - template on which all user databases are based.
tempdb - holds temporary data.
resource - read-only hidden database that contains system objects that are mapped to the sys schema in every database.
I have heard the master and model databases are all that is required to start sql server. Is this true?
In addition, should these system databases be placed on the local drive of the database server to avoid the issue of not being able to start sql server if the SAN or NW storage is not available?
Thanks in advance.
You require master, model and tempdb. I think that msdb is needed too, but don't remember.
I had an issue few years ago with a very slow SAN and tempdb could not create itself on time (it was a SQL Cluster). This keep bringing the whole SQL instance down and bouncing back and forth, all because tempdb. There are some startup parameters that you can play with and start the instance with master only (if I recall well) but that's not standard practice and it's only during emergency situations.
Regarding where to put system databases, my preference is on RAID1, outside of a SAN. Yes, I know that if the SAN goes down, everything goes down, but it will be faster to recover if I have database backups or some type of SAN replication or snapshot.