February 27, 2015 at 1:29 pm
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.
February 27, 2015 at 2:02 pm
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.
February 27, 2015 at 3:48 pm
with minimal startup just master, other than that master and model, tempdb is re created if it doesn't exist. SQL Server Agent wont start if msdb is missing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2015 at 6:04 am
An alternative point of view regarding file locations:-
Unless organisational standards dictate otherwise, I tend to leave Master, Model and MSDB in the default installation locations.
I do however, unless sternly told not to (!), insist that TempDB, with its appropriate number of files, is stored on a separate logical and physical drive, all on its own.
The reason for keeping the other system databases as default is partly for ease of recreation in times of disaster recovery - a little bit less messing around and a set of standards that you know you can rely on and repeat at will.
March 2, 2015 at 10:50 am
If you want just start the server then you need master database is enough.
if you want more services like agent services,db mail services etc. then you need model and MSDB databases.
March 2, 2015 at 11:08 am
cpathidba (3/2/2015)
If you want just start the server then you need master database is enough.if you want more services like agent services,db mail services etc. then you need model and MSDB databases.
incorrect, if you start the server and model is missing the service will fail to start. The only time SQL can be started with just master is to use the trace flag 3608
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 2, 2015 at 11:38 am
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?
All except MSDB (which is required for SQL Agent to start).
The TempDB files aren't required, but the location which the files should go has to exist)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2015 at 2:01 am
Hi,
all databases required in order to start and function your sql instance.
master & model &resource, is required missing of this db will affect the sql server instance to start
tempdb - does not matter, it will recreate using your model databases.
msdb - missing of this database fail to start your sql server agent
September 1, 2016 at 3:47 am
Hi
what about resource DB. Is resource DB required to startup the instance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply