Reserved Resource Database in SQL Server 2005

Also called as RDB this is a new concept in SQL Server 2005 which I thought was worth taking a look at. In previous version of SQL Server 2000 whenever we had to upgrade a system defined object we had to drop and recreate. And rollback of the same was also something that we as DBA's had to break our heads. All the service packs have a disclaimer that states we backup our databases before we start the installation sequence . Even though I recommend it strongly, let us take a tour of how the new reserved Resource Database improves this current process and how we can captilalize this change.

The mssqlsystemresource.mdf is what is called the "Resource Database". It contains pre-created copies of all system objects like "sp_help", "sys.objects", DMV's, System functions and many others. These system objects logically appear in the "sys" schema of *every* available database.

The intent behind the RBD is simple:

(a) To allow for faster upgrades (upgrade is now a file copy instead of DROP and re-CREATE 1000s of system objects)

(b) To allow for rollbacks of QFEs (file copy semantics).

The RDB is a "read only" database. It only contains pre-created SQL system objects. It does *not* contain any user or system data or metadata. But internally it is very much like any other database. The catalog base tables have the same structure and schema as any other database. It just so happens to contain the pre-CREATE-ed system objects, and nothing more. You cannot "use" the RDB while in multi-user mode. Since there are no public or callable interfaces in it, there is no reason for customers to build dependencies on it in their production systems. There are ways to directly modify it in single-user mode, but this is only intended for Product Suppport Services or the Customer Support Services Team. This is the moral equivalent of the old "allow updates" in SQL Server 2000.

Even the door for updating or connecting to the database is closed for most part of the time. You can access the same in a single user mode and it allows connection to the database. The SQL Server Management Studio IDE and sys.databases view also abstract the existence of this database.

Some of the quick check queries available for versioning of RDB include the following

select serverproperty('resourceversion'), serverproperty('resourcelastupdatedatetime')

But the interesting thing is sometimes the definition for these system objects. So here is a query that gives the object definition for each of these fabulous view definitions. These are primarily stored on the RDB database but can be executed from all the database. So the query for the same would look like:

select name as object_name, object_definition(object_id) as sql_definition from sys.system_objects

The above query gave me 1740+ rows and this is a handy way to find out the definition of all the system objects that we use. Interestingly these can be used to learn the inner working of SQL Server 2005 architecture too. I leave that part to you ...

Conclusion

Even though SQL Server 2005 is filled with tons and tons of new features to explore, there are a lot to understand and how the new version really works. These simple but yet innovative architecture allows easy administration and deployment of fixes for system objects is a nifty feature that DBA's can encash moving forward.