SQL Server System Databases

SQL Server System databases will be created as part of SQL Server installation. SQL server internal operations will be performed using System databases.

User databases are created based on organization requirement.

SQL Server System Databases:

1. Master
2. Model
3. MSDB
4. Tempdb
5. Resource database
6. Distribution database

Master Database:

The first database loaded by SQL Server when services starts is master database. Once master database starts then only other databases will start.
master database stores critical information like SQL Server configuration such as CPU information, memory configurations, sp_configure settings.
master database stores other system databases infromation in sys.databases table.
In master database, we have sysaltfiles table which contains location of all system databases mdf and ldf will be stored. To start any database it has to come to master database and then starts database.
SQL logins are will be stored in sys.logins table of master database. If any user connected to SQL Server externally, user login will be authenticated in master database.
master database stores other server objects like linked servers, endpoints etc.

Model Database:

model database will act as a template in creating new user databases.
Every new database acquire properties from master database. New database goes to master database and those properties.
Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Msdb database:

msdb database is mainly used by SQL Server Agent for scheduling alerts and jobs.
In SQL Server we will do automation in the form of job. SQL Server Agent service will be responsible for this jobs.
All SQL Server jobs information will be stored in sysjobs, syschedules and syssteps table inside msdb database.
SQL Server Agent Service will read msdb database scheduled tables, based on this it will start and stop tasks automatically.
History related information like backup, restoration, log shipping history will be stored.

tempdb database:

tempdb database stores all temporary objects.
temporary objects that are created by SQL server for internal operations such as temporary tables, temporary stored procedures etc.
All the transformation, internal calculations, conditions sorting operations will be performed in tempdb database.
Whenever SLQ server started, tempdb database will be deleted and new copy will be created.

Resource database:

resource database is hidden database, we can see only mdf and ldf physically.
resource database is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Powered by k2schools