About 'RESOURCE' Database in SQL Server 2005

1. 'Resource' Database is a new DB introduced in SQL Server 2005. Its shortly referred as 'RDB'.

2. Its an 'Hidden' and 'Read-only' DB.

3. You could find its 'mssqlsystemresource.mdf' file here -- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (If you have SQL Server 2005 installed in your box)

4. This is the database where all 'System objects' which belongs to 'Sys.' Schema physically reside. i.e., has definition of all System objects. These 'System objects' (like, sp_help) logically appear in every other DB in the server.

Schema -- It is similar to namespaces in .NET, it helps in logical grouping.
Sys -- Is newly created schema in SQL Server 2005. All System Objects resides here.

5. If you run this below script from any of your SQL Server 2005 database it would return 1741 rows. Those are 'definitions' of ALL the system objects which actually reside in RDB.

Select [name], object_definition(object_id) from sys.system_objects

6. This RDB makes 'Service Pack' upgades easy.

In SQL Server 2000 -- Lets assume a change happens to a system object. It would go and touch ALL database where this system object exists. Its bit tedious and risky. That was the reason, Service packs have a disclaimer urging us to backup our databases before we start the installation process :)

In SQL Server 2005 -- Since System Objects definitions is now stored only in Resource Database, Service Pack upgradation becomes quite simpler.

Technorati tags: SQL Server 2005