Monday, May 31, 2010

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.

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.

1 comment:

Varinder Sandhu said...

Very well explained !!!

Recent Posts