SQL Server - SQL Server 2005 Resource Database

Asked By Justi

05-Sep-08 11:47 AM

Hello,

I've run into an interesting situation with a customer. We will be moving
the system databases from one drive to another. I've done this before without
any issues. My concern is the following paragraph, found in the
http://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx MSDN article:

you move the master database, you must also move the Resource database to the
same location. For more information, see Moving System Databases. Also, do
not put the Resource database in either compressed or encrypted NTFS file
system folders. Doing so will hinder performance and prevent upgrades."

I've read many other documents that support this paragraph when running SQL
2005, however this information is new to me. I've moved the system databases
before, without moving resource. I know for a fact we have customers out
there who's master databases reside on separate drives from the resource
database location.

What I need to know is, why does the resource database depend on the
location of the master database, and what are the implications of it residing
in a different location? The fact that a SQL 2005 instance will start does
not preclude the fact that something will break down the road.

Thank you,
Justin

05-Sep-08 05:20 PM

When I followed the link you supplied it brought up the documentation
for SQL Server 2008.  My understanding is that the requirement that
master and resource databases remain together is new with 2008.  So
even if we can get away with it with 2005 it is no longer a good idea
to do that.

Roy Harvey
Beacon Falls, CT

On Fri, 5 Sep 2008 08:47:01 -0700, Justin

05-Sep-08 06:14 PM

Hello Roy,

Thank you for your response. The article I posted is for SQL 2005.

In 2008 the requirement for the Resource and Master database to remain in
the same folder no longer exists, because it is stored in <drive>:\Program
Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\ by default. Please
refer to the SQL 2008 version of the article for more details:

http://msdn.microsoft.com/en-us/library/ms190940.aspx

Regards,
Justin

06-Sep-08 04:30 AM

In 2008, the resource database indeed is in the binn folder. And it has to be - so BOL is incorrect
about the section on how to move resource and a BOL update will fix that.

Yes, I understand your question was about 2005, I just wanted to mention 2008 first to get that out
of the way. One thing I've heard about causing problems if resource files aren't where master.mdf
is, is patching (hotfix, GDR, CU, sp).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

07-Sep-08 03:10 AM

Hi Tibor,

Thanks for the reply. I've heard the same in that it can cause issues with
hotfixes, service packs, etc... However I haven't observed any issues myself,
and I haven't found any documentation illustrating the details. Do you have
any links or useful articles describing these issues?

Thanks,
Justin

07-Sep-08 02:29 PM

Hi Justin,


I'm afraid not. This is only what I'm heard, so I've stayed away from separating these...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

08-Sep-08 12:41 PM

In SQL Server 2005, failure to keep both the resource .mdf and .ldf files in
the same location as master.mdf  will cause hotfixes, CUs, SPs, etc. to fail
because that is the way the code is written. Obviously that's not optimal
and it's changed for SQL Server 2008, but I would strongly discourage you
from trying to separate the resource and master files. It's just not worth
the headache you will incur later.  There are a couple KB articles out there
on the subject.  Here's one regarding the location of the resource .mdf and
.ldf files not being located together:
http://support.microsoft.com/kb/947989/en-us

BTW, the SQL Server 2005 topic will be updated in the next scheduled release
to state this more strongly.

--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx