ResourceDB Location changes !!!

As we work with SQL Server 2005 the concept of using system objects shifted from the traditional MASTER database to an hidden READONLY database called as RESOURCEDB. We have already discussed briefly about ResourceDB in a different article. In this tip we will bring out some of the changes that happened post SQL Server 2008 release to this internal database.

This tip was around the change of location for ResourceDB database.

• The resource db files mssqlsystemresource.mdf and mssqlsystemresource.ldf were installed in the DATA folder in SQL 2005.
• In SQL 2008, they are installed to the BINN folder.

And this change was interesting for me when I was searching for these ResourceDB files for backup. But it made a lot of sense, What this means in failover clusters is that, we will have ResourceDB files installed only on the local drive and not shared drives. For example: C:\Program Files\Microsoft SQL Server\MSSQL10.<InstanceID>\MSSQL\BINN\.

Moreover, the ResourceDB are ACL'ed to be read-only to avoid any tampering of the ResourceDB. To check on your systems to if the ProductVersion and the ResourceDB versions are the same, use the below SQL statements

SELECT     ServerProperty('ResourceVersion')
GO
SELECT     ServerProperty('ProductVersion')

Tampering with the ResourceDB source definitions is not recommended and has to be done only via hotfixes or Service Packs.