A database server application such as Microsoft SQL Server 2005 uses a lot of information in order to operate. Considering the purpose of a database is to store information, it only makes sense that SQL Server dips into its own technology for storing and accessing the information required for operating in a set of its own databases. Each of the databases used plays a specific role in the operation of the SQL Server. The system databases use the same components of databases created by users with: tables, views, stored procedures, and other database objects.
System Defined Database In SQL-SERVER:
- Per instance configurations
- Databases residing on the instance
- Files for each database
- Linked\Remote servers
- The first database in the SQL Server startup process
- System object definition
- Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
- Prior to SQL Server 2005 the system related data was stored in the master database
- Read-only database that is not accessible via the SQL Server 2005 tool set
- The database ID for the Resource database is 32767
- The Resource database does not have an entry in master.sys.databases.
- Manage temporary objects listed in the purpose above
- Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state.
- User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
- The database configurations such as the recovery model for the Model database are applied to future user defined databases.
- SQL Server Agent Jobs, Operators and Alerts
- DTS Package storage in SQL Server 7.0 and 2000
- SSIS Package storage in SQL Server 2005.
- Database responsible for the replication meta data
- Supports the data for transaction replication between the publisher and subscriber(s).
- Reports security
- Job schedules and running jobs
- Report notifications
- Report execution history.
- Session information