Resource Database

What is resource database?

Resource database, also known as resource DB or RDB, is a hidden, read-only database in SQL Server. Microsoft SQL Server is a relational database management system (RDBMS) created and marketed by Microsoft. Like other relational database software, SQL Server supports structured query language (SQL) for working with relational databases. The primary interface tool for SQL Server is SQL server management studio (SSMS), which works in both 32-bit and 64-bit systems.

The resource database is the fifth database. It supplements the master database because the SQL Server now relies on it. It contains all system objects included with SQL Server 2005 and above. System objects such as sys.objects are physically stored in the resource database, but they also exist logically in every database's sys schema. 

The resource database can only hold system objects and cannot store user data or metadata. Stored procedures, catalog views, and extended procedures are examples of pre-created system T-SQL code stored in the resource database.

The actual name of the resource database is mssqlsystemresource. The resource database has one data file and one log file named mssqlsystemresource.mdf and mssqlsystemresource.ldf, correspondingly, and are located, by default, in <drive letter>:\Program Files\Microsoft SQL Server\MSSQL<version num>.<instance_name>\MSSQL\Binn\.

The resource database simplifies and accelerates the process of upgrading to a new version of SQL Server. Previously, updating SQL Server involved removing and recreating system objects. Since the resource database file contains all system objects, admins may well carry out an update by simply transferring the resource database files (mssqlsystemresource.mdf and mssqlsystemresource.ldf) to the local server.

Why is the resource database important?

The primary goal of a resource database is to make updates to the server as quickly and efficiently as possible. Because all system objects are present in the resource database, database administrators (DBAs) can overwrite the previous resource database files with the new resource database files during the upgrade. 

This action will update all system objects in the database. In earlier versions of the SQL Server, all system objects had to be discarded and recreated during the upgrading process, which was time-intensive. In addition, the setup would fail if any of the objects were missing or caused a problem.

Best practices for resource database

There was no resource database in SQL Server 2000 and prior versions. It was first introduced in SQL Server 2005, making upgrades more accessible and faster. Every SQL server instance in a database has an ID. The highest number of databases that can be stored in a single instance is 32767. This is also the resource database ID, and it is the same for every resource database in every instance. As a result, the resource database is given the instance's maximum possible database ID.

Many people are unaware of the existence of the resource database because of its hidden status. Some DBAs believe that since this is a database, it requires the same level of upkeep as a traditional database, which is not quite correct. This can lead to more problems in the future.

Here are some best practices to follow when working with a resource database:

• Avoid moving the resource database files. While special procedures exist for transferring the files of other system databases, changing the file location of the resource database is neither embraced nor advised. Upgrading SQL Server may result in installing a new resource database, which permanently stores its files in this fixed location.
• Be consistent with backups to restore the resource database. The resource database cannot be backed up or restored using typical backup and restore procedures used for other databases in SQL Server as it is not accessible using SQL Server tools. Even though the resource database's data file extension is .mdf, it can be treated as a .exe file. Admins can use this method to create a file-based or disk-based backup.
• Apply service pack to multiple instances using the resource database. The DBA must copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the target instance to administer a service pack to multiple instances.
• Use the resource database to roll back changes. If a DBA needs to undo the changes made by service packs, the admins must replace the .mdf and .ldf files with those from a previously backed-up version.