In SQL Server 2005 and later versions, SQL Server contain 5 system databases. These are
1) Master
Master database is soul of SQL server. SQL Server service cannot start if the master database is unavailable ,
2)Resource database.
Why was resourse database introduced in SQL Server 2005?
In SQL Server 2000 all the executable system objects were stored in Master database , hence master database in sql 2000 contains 1200 objects while in sql 2005 master database contains just 70 objects. Where are rest of objects present in 2005 answer is resource database.System executables objects are like code from microsoft so now these executables are stored in protected, read only database much in a manner similar to DLL.SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node. The Resource database does not contain user data or user metadata. 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_50.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do notshare this file. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.
Advantages of Resource Database
Prior to SQL Server 2005, whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
3) Model
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.Outside of its role as a template, model doesn’t do anything else. If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database.
4) TempDB
Tempdb is rough book of sql server.It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.Operations within tempdb are minimally logged.Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.The size of tempdb can affect the performance of a system.By default tempdb starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized too small, system resources will be used growing the tempdb file. This overhead can be avoided by increasing the default size of tempdb.
5) Msdb
SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.One important item is that msdb holds backup history. Using the msdb tables like backupset , it’s possible to determine when each database and filegroup was last backed up.
If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.
- Master
- Resource
- Model
- Tempdb
- Msdb
Now the question is , why SQL needs system databases?
System databases are used by SQL Server for its own maintenance and management.To understand this better let's have look at each of them.
Master database is soul of SQL server. SQL Server service cannot start if the master database is unavailable ,
Master database is the one which is absolutely needed for starting SQL Server - this statment in itself describes the importance of master database.The master database records all the system-level information for a SQL Server system. It captures instance level information such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. sys.databases catalgue view contains one row per database in the instance of Microsoft SQL Server.In SQL Server 2005 and later versions the system objects are stored in Resource Database rather than in Master Database.
Why was resourse database introduced in SQL Server 2005?
In SQL Server 2000 all the executable system objects were stored in Master database , hence master database in sql 2000 contains 1200 objects while in sql 2005 master database contains just 70 objects. Where are rest of objects present in 2005 answer is resource database.System executables objects are like code from microsoft so now these executables are stored in protected, read only database much in a manner similar to DLL.SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node. The Resource database does not contain user data or user metadata. 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_50.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do notshare this file. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.
Advantages of Resource Database
Prior to SQL Server 2005, whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
- The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert back to the previous version of the SQL Server Instance and attached all User databases.
- In SQL Server 2005 onwards the changes will be made to the Resource Database, which will indeed reflect the changes in all the system and user database of the particular instance
- If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance
- If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version
3) Model
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.Outside of its role as a template, model doesn’t do anything else. If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database.
4) TempDB
Tempdb is rough book of sql server.It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.Operations within tempdb are minimally logged.Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.The size of tempdb can affect the performance of a system.By default tempdb starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized too small, system resources will be used growing the tempdb file. This overhead can be avoided by increasing the default size of tempdb.
5) Msdb
SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.One important item is that msdb holds backup history. Using the msdb tables like backupset , it’s possible to determine when each database and filegroup was last backed up.
If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.
No comments:
Post a Comment