ALL SYSTEM DATABASE IN SQL SERVER 2008

Understanding system database in SQL Server 2008

There are flowing types of system database in SQL server 2008.
1.    Master Database.

The master Database records all the server-specific configuration, including authorized user, database, system configuration settings, and remote server. In addition, it records the instance-wide metadata, such as logon accounts, endpoints, and system configuration settings.
                The Master database is the Main DB of SQL Server. It actually records all the system level info's. Every instance of SQL Server has its independent Master db; because it has to  record instance level configuration information. The information being captured in the Master database includes SQL Server instance level configurations,  SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures,System level Functions ,linked server configurations,etc.
Apart from that The master database  stores the initialization information of the SQL Server. Therefore if the master database becomes unavailable, the SQL Server database engine will not be started.
. The Master database  contains two physical files, namely master.mdf (data file) and mastlog.ldf (log file). By default when you are installing SQL Server 2008 the data and log file for master DB are installed in the following folder location Drive:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\
If master database gets corrupted the SQL Server Service will not start.

2.    Temp Database.
The temp Database as the name suggest is a temporary database.
 It holds all temporary tables and stored procedures. 
Sql Server usese isr to resolve large or nested queries,
This is also being used while sorting data before displaying the results to the user, online indexing, BCP.

Temp database  store's temporary tables (#temptable or ##temptale), table variables, ,row versioning, create or rebuild indexes sorted in TempDB, cursors, work tables etc. Each time the SQL Server instance is restarted all objects in tempdb are destroyed, so permanent objects cannot be created in this database. 
Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state.
To Shrink TempDb either, restart SQL Server or Kill Transaction, or one can use shrink DB Command
3.    Model Database.
The model database works as a template or a prototype for the new database. Whenever a database gets created, the contents of the model database get copied to the new database.
In this database, one can set the default values for the various arguments to be specified in the Data Definition Language (DDL) Statements to create database objects. 
in addition, if some one  want every new database to contain a particular database object, you can add the object to the model database. 
Each time when new Db gets created the added objects also gets created.
UD tables, user defined data types, SP, UDF 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 (Simple Full or Bulk Logged)for the Model database are applied to future user defined databases

4.    Msdb Database.
The Msdb database  is used to support the SQL Server Agent. 
The SQL Server Agent is a tool that Schedules periodic activities of the SQL Server, such as backup and database mailing, External processes, DTS, SSIS and Job excution, scheduled indexing, System DB Based scheduled operations.
 The Msdb database contains task scheduling, exception handling, alert management, and system operator information . The Msdb database contains a few system-defined tables that are specific to the database.  
            Additional Information 
•    Provides some of the configurations for the SQL Server Agent service 
•    For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database.
Inside MSDB Datbase one can store SSIS Packages too lin the similar directory structures such as file systems.

5.    Resource Database. 
The resource Database is a read-only database containing all the system objects. this has  system-defined procedures and views that are included with SQL Server. The Resource database is responsible for physically storing all of the SQL Server  system objects. This database has been added to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

 

SQL Server 2005's 5th system database - MSSQLSystemResource (The Invisible Database)

MSSQLSystemResource is a database that complements the master db. It is like the name smartly impels a resource database.
All system stored procedures, views and functions are stored here.

This database is hidden from the user. We can't view it in Object Explorer or with the use ofsp_helpDB or by selecting from a sys.databases view. Resource database does not contain any of user data. This database has to be backed up using file-based backup or by using Drive Backups.

So how do we know its presence?
-Go to the Data directory of your SQL installation [Install Drive]:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and there you will find the MSSQLSystemResource.mdf andMSSQLSystemResource.ldf.

If you want to see what is there in this database:

1. Stop the SQL Server service.
2. Copy both files and rename the copies to MSSQLSystemResource_Test.mdf andMSSQLSystemResource_Test.ldf.
3. Start the SQL server service
4. Attach the new files.
5. Query the database.

 

Resource Database in SQL Server 2008

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sysschema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Physical Properties of Resource:

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database:

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

Note: After restoring a backup of mssqlsystemresource.mdf, you must reapply any subsequent updates.

Accessing the Resource Database:

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');

GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO

6.    Distribution Database.
(Importance with merging and replication in Sql Server)
Primary data source to support SQL Server replication, The Distributor is a server that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication. To set up replication, you must configure a Distributor. Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.
Actullay there are many ways to configure replication, which can be a further topic of discussion.

7. ReportServer Database.
This is the Primary database for Reporting Services to store the Meta data and object definitions 
A Reporting Services application uses two SQL Server relational databases for internal storage. By default, the databases have names as  ReportServer and ReportServerTempdb. ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.

The Db has very specific role when you are adding new types of role on your report server and modeling the sceurity over the current report server security architecture.