Help: SQL Server

I have been doing a lot of community activities and lately I have learnt that there is a lot of confusion about resource database. I am going to burst major myth which I mentioned in title.

First, let’s understand why there was a new “hidden” system database introduced in SQL 2005. I am not sure if you have ever seen SQL 2000 Service Pack setup screens. Here is the of the screen where system objects are modified.

Prior to SQL 2005, all system objects were in master database and they were open for end user to alter by changing “allow updates” configuration using sp_configure. To safe guard system objects from modification (which might cause unexpected behaviors) product modification has been made and system objects definition is moved to a new hidden databases called “resource” database. Another reason of having resource database is to avoid running script to modify system objects during patching of SQL. Rather than running ALTER commands, just replace resource database file. I must point out that running script is one of many steps during upgrade process. (Keep this in mind as I am going to come back to this point later). During patching process (service pack or major product release) SQL Server setup used to drop and create thousands of system objects. It might take around 10 minutes and during that time SQL server is unavailable for production usage. Resource database is introduced to reduce the down time because script execution is now changed to a file copy of resource database.

Here are some properties of this database.

• Database ID = 32767
• Database name = MSSQLSystemResource
• Data file name = mssqlsystemresource.mdf
• Log file name = mssqlsystemresource.ldf
• State = Read Only / Hidden
• Contains = Pre-created system T-SQL code like Stored procedures, extended procedures, catalog views
• Does NOT contain user data or user metadata.

Since it’s a hidden database, we can’t view the objects located in the database. There are two ways to do that (and this is strictly for learning purpose) In real time, you never have to worry about this database.

• Start SQL in Single User Mode (Refer earlier blog)
• Attach the MDF and LDF file as user database.

There has been changes done in various SQL version about the physical location of the files. Since they are MDF and LDF, initially they were kept in DATA folder, along with master database files. but then there have been problems when DBA used to move master database to new location and service pack used to fail. Customers also showed their concern about backup of this database (as the files are visible in data folder). In SQL 2008 onwards, location has been changed and it is now kept in the same location where sqlservr.exe resides.

For all practical purposes we should treat resource database files as binaries/DLLs. Since we have MDF and LDF files, we have been calling it as database. Now, if it’s a DLL, how to get version of current resource database? There are two ways.

SELECT SERVERPROPERTY('ResourceVersion') 'Resource Version';
GO

SQL Server ERRORLOG also shows this information.

Now comes the real conversation.

DBA: Can I use the resource database to uninstall service pack?

Balmukund: can you please explain more?

DBA: I have taken copy of mdf and ldf files before applying 2008 service pack 1. After patching is complete, I want to rollback SP1.

Balmukund: Okay. how would you do it?

DBA: Stop SQL services, keep back the old files of mssqlsystemresource and start SQL services.

Balmukund: Oh no. That’s not something you should do. Those files are just like a DLL of a huge SQL Product. By replacing file you would introduce version mismatch between SQLServr.exe and Resource database.

DBA: But I learned that resource database can be used to rollback service pack.

Balmukund: No, that information is not correct. During the whole upgrade process replacing file is just one of the step. Who would take care of unregistering DLLs, keeping back old version of files etc?

DBA: So, I can’t uninstall a Service Pack?

Balmukund: I didn’t say that. Starting with service packs (Service Pack 1) in SQL Server 2008 you can uninstall them from Add/Remove Programs like any other update. Till SQL 2005, only way was uninstall SQL completely and reinstall again (Refer KB http://support.microsoft.com/kb/314823)

DBA: What you would be if resource database is lost?

Balmukund: There are two possible options. First and the easiest one is to copy the resource database files from another instance that is the same version, service pack, cumulative update (patch level is very important). Second option is to rebuild system databases. http://msdn.microsoft.com/en-us/library/dd207003.aspx

I truly hope this blog uncovers few facts about hidden database.