System Databases in SQL Server

SS supports 3 types of dbs

 * System Dbs
 * Sample ,,
 * User Defined Dbs.
Note:
 Don't install Sample dbs in production server.

System Databases

* These are mandatory dbs which consists of meta data of ther server.
* We cannot configure the following features on system dbs.
  * Replication
  * Log Shipping
  * Db Mirroring

* By default at the time of installation 5 system dbs     are created.
  * Master
  * Model
  * MsDb
  * TempDb

  * Resource (Hidden db)

* We cannot detach the system dbs directly.
* To detach system dbs the server should be running in    single user mode with trace flag 3608

 c:\Net start mssqlserver /m /c /T3608

* Only Model and MSDB can be detached.

Note:
 To detach MSDB what are the steps
 
 Sol:
  * Start server with single user mode        with trace flag 3608
  * Stop SS agent service.

1. Master
---------
* It works as entry point for SQL Server.
* Total server level settings are stored in master db.
* We cannot detach master db.
* To restore master db we have to run the server in     single user mode.
* Its db id is 1.
* By default guest user is enabled.
* Its recovery model is SIMPLE.
* It consists of
 * All dbs details (sysdatabases)
 * Logins (SysLogins)
 * Linked servers information (sys.servers)
 * Error Messages (sysmessages)
 * Server level configuration settings
    etc
FAQ:- How to move master db.
 
Steps
------
 1. Stop server
 2. Move the master db data file and TLog file     into different location.
 3. Provide read/write permissions on the     destination folder to SQL Server service     account.
 4. Change the path in start up parameters.
 5. Start the SQL Server service.

* We need daily backups.

2. Model
--------
* Works as template for new dbs.
* It consists of system defined objects (1788/1978)     which are copied into every new db.
* No need of regular backups.

FAQ:- Once I create a new db there should be one user       ssrs_user.
Sol:
 Create the user (ssrs_user) in model db.

3. MsDb
-------
* Consists of total scheduling information.
* Automation details are stored in this db.
* It consists of the details

 * Backup and restore
 * Db Mail
 * Log Shipping
 * SSIS Packages
 * Maintenance plans
 * Jobs
 * Alerts
 * Operators
  etc
* It consists of speacial roles related to SSIS and Db    mail.
 * DatabaseMailUserRole
 * db_ssisAdmin
 * db_ssisltdUser
 * db_ssisoperator

* Regular backups are required.

4. TempDb
---------

* Consists of Temp objects like 
 * Temp tables, cursors, resultsets of join
 * The process of ordering, grouping is done    here. 
 * DBCC Checkdb(Dbname) utilizes more tempdb
 * Rebuilding indexes consumes more space in    tempdb.
* No need of backups.
* It is created/cleared when the server is started.
* Add similar no of data files similar to no of CPUs in   the server.
* Place tempdb files in highest rpm disk.

FAQ:- TempDb is growing fastly. How u know which        transaction is causing the problem?

Sol:
 DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

FAQ:- If TempDb full. How to resolve the issue?

 1. Short term fix
 -----------------
  * Find long running query which is     causing tempdb to grow and kill it by     user acceptance. 
  * Truncate the T.Log file of tempdb     periodically by setting the recovery      model to simple.

 2. Long Term Fix
 ----------------
  * Restart the server by raising CM with     client.

5. Resource
-----------
* Consists of all system defined objects physically.
* It maintains the service packs changes.
* By using old data file and T.Log file of this db we   can rollback any changes (sps).
* This db files are present in

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

6. Distribution
---------------
* It is create automatically when the instance is   qualified as distributor in replication feature.
* It consists of complete replication details.

Note:
 If we install Reporting Services then 2 more  dbs are created automatically
  * ReportServer
  * ReportServerTempdb