These are the following System Database in SQL Server 2005:
1. master
2. tempdb
3. model
4. msdb
5. Resource
1- Master Database
· Master database is system database and it contains information about running server’s configuration.
· Only Master database is the one which is absolutely must have database. Without Master database SQL Server can not be started.
· This database is the head of the entire related database. It is also recommended not to give permission of master database to any user & it is also important to update the backups of master database which may take effects on other databases related to this master database.
· The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings
2- Tempdb Database
· This database is a temporary database that holds all temporary tables and stored procedures.
· Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc
· All the temporary tables & result generated by GROUP BY, ORDER BY & DISTINCT clauses are stored in tempdb database.
· In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space.
· No need to back up the tempdb database You are actually prevented from performing backup or restore operations on tempdb by SQL Server 2005.
· The tempdb database is implemented in the tempdev.mdf and templog.ldf files.
3- Model Database
· When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database.
· Provides templates or prototype for a new database means whenever a database is created the contents of model database will be copied to new database.
· 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 modeldev.mdf and modellog.ldf physical files implement the model database.
· It is a probably a good idea to periodically back up the model database. The size isn’t all that large, and if you make changes, those changes will be preserved.
· By default when you are installing SQL Server 2008 the Model database related data and log file are created in the following folder location Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\.
4- Msdb Database
· The msdb database is used by SQL Server to store information on operations performed by SQL Server.
· SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc Primary database to manage the SQL Server Agent configurations.
· Functanality - SQL Server Agent Jobs, Operators and Alerts,DTS Package storage in SQL Server 7.0 and 2000,SSIS Package storage in SQL Server 2005.
· This database contains task scheduling, exception handling, alert management & system operator information needed for SQL Executive Service. For example: I can query the how many emails have been sent to administrator, scheduled backup for next time & also the history of previous scheduled backups.
· The msdb database uses the msdbdata.mdf and msdblog.ldf files.
5- Resource Database
· The Resource database is responsible for physically storing all of the SQL Server 2005 system objects.
· The Resource database is a read-only, hidden system database that contains all the system objects that are included with SQL Server.
· This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
· Resource database uses the mssqlsystemresource.mdf and mssqlsystemresource.ldf files.
Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2
No comments:
Post a Comment