System database in SQL Server

System Databases:

Master:

• The Master database is core database to manage SQL server instance.
• It contains the meta data of the our database

              Instance configuration

              Logins

              End points

              Files of each database

              Linked\remote server

• Per instance configuration.
• The database ID for Master database is 1.

Resource:

• It was introduced in SQL Server 2005.
• It contains tables, meta data, stored procedure that SQL Server needs to run.
• Prior to SQL Server 2005 the system related data was stored in master database.
• It is read-only database that is not accessible via SSMS.
• It does not contain the information about instance and database, because it is only written during an installation of new service pack.
• Rollback to the old service pack. Prior to SQL Server 2005, it couldn't be rolled back to the previous version.
• The database ID for the Resource database is 32767.

Temp:

• It is used for to store temporary objects i.e. tables, views, cursors, create or rebuild indexes sorted in TEMPDB. etc..
• Each time a SQL Server instance is restarted, the TempDB database is reset to its original size.
• So permanent objects can not be created in this database.
• The database ID for the TempDB database is 2.

Model:

• It is template database for all use defined databases.
• When ever a new database is created a copy of the model database is created and renamed with the name of the database being created.
• The only time this does not apply is when you restore or attach a database from a different server.
• The database ID for the Model database is 3.

MSDB:

• This is mostly the SQL Server Agent's database.
• SQL Server Agent uses this database for storing automated job definitions, job schedules, operators and alert definitions.
• SSIS also use for storing SSIS packages.
• The database ID for the MSDB database is 4.

Distribution:

• This database was created when configuring the SQL Server replication.
• This database is responsible for replicate the data.
• It stores the all types of replication history.