February 11, 2011 at 9:44 am
Nice question thanks!
I'm not sure why we would want to move the resource DB, it is small (~60MB) and read-only during operation, so it is probably just loaded into memory.
February 11, 2011 at 9:45 am
sknox (2/11/2011)
The best answer I could find to that was here:which shows that SQL Server 2008+ has a hardcoded process at startup that attaches the resource database from the same location as the executable file*, but doesn't explain why Microsoft has chosen to make it do that. I haven't seen a reason from Microsoft.
I haven't either, but I think I can guess.
The reason the resource database was introduced was to make applying service packs easier. Instead of dropping and creating system objects in the master database, the SP executable simply replaces the MDF file for the resource database. This was described in one of the links mentioned in this topic.
So I guess that having to search for the current location of the resource database felt like too much work for too little gain. One simple, immutable, hardcoded location is the easiest possibility.
Or they had bad experiences with customers placing the resource DB on a bad spot and expecting MS to fix the mess, and wanted to avoid those problems going forward.
February 11, 2011 at 10:10 am
Hugo Kornelis (2/11/2011)
The reason the resource database was introduced was to make applying service packs easier. Instead of dropping and creating system objects in the master database, the SP executable simply replaces the MDF file for the resource database. This was described in one of the links mentioned in this topic.
So I guess that having to search for the current location of the resource database felt like too much work for too little gain. One simple, immutable, hardcoded location is the easiest possibility.
Or they had bad experiences with customers placing the resource DB on a bad spot and expecting MS to fix the mess, and wanted to avoid those problems going forward.
I appreciate the speculation; it sounds like a reasonable explanation for the scenario...
Would you care to voice an opinion on whether this kind of heavy-handedness is a good idea?
I answered "yes" (movable) because it was unthinkable that there should be a requirement for files to live in any particular location. Microsoft forced the registry on us and now they can't be bothered to use it to find a file location? That's the kind of laziness I'd force a coworker to rewrite/fix, why are we collectively ok with it from Microsoft?
February 11, 2011 at 10:53 am
Mike Dougherty-384281 (2/11/2011)
Hugo Kornelis (2/11/2011)
The reason the resource database was introduced was to make applying service packs easier. Instead of dropping and creating system objects in the master database, the SP executable simply replaces the MDF file for the resource database. This was described in one of the links mentioned in this topic.
So I guess that having to search for the current location of the resource database felt like too much work for too little gain. One simple, immutable, hardcoded location is the easiest possibility.
Or they had bad experiences with customers placing the resource DB on a bad spot and expecting MS to fix the mess, and wanted to avoid those problems going forward.
I appreciate the speculation; it sounds like a reasonable explanation for the scenario...
Would you care to voice an opinion on whether this kind of heavy-handedness is a good idea?
I answered "yes" (movable) because it was unthinkable that there should be a requirement for files to live in any particular location. Microsoft forced the registry on us and now they can't be bothered to use it to find a file location? That's the kind of laziness I'd force a coworker to rewrite/fix, why are we collectively ok with it from Microsoft?
While I agree with you in principle, there are practical considerations. In the case of the Resource database, I think it's important to note that the location isn't strictly hardcoded (i.e, it MUST Be on C:\) but once set it can't be changed, much like the Windows system folders. For example, have you tried to move your registry or Windows system folders? Even that could be made possible, but then you hit against the boot sector....
There always comes a point where a given system needs a known starting point. Provided the documentation is clear on that and allows you to plan adequately (i.e, MS should include in the planning steps information about the potential size of the Resource database and any performance requirements -- I don't recall seeing this, they could probably improve here), I don't see a real problem.
February 11, 2011 at 11:40 am
Mike Dougherty-384281 (2/11/2011)
Would you care to voice an opinion on whether this kind of heavy-handedness is a good idea?
I see no problem wirth it. I would if I could not change the drive, which might be an issue if my C drive is almost full. But I can change the drive,. The location where the resource database lives is always on the same drive where the data files of the instance are installed, but in a hard-coded path.
Asking to be able to move the resource database is, to me, like asking for the ability to move two or three of the DLL files in <drive>:\Program Files\Microsoft SQL Server\<instance_name>\MSSQL\Binn to a different location - I don't doubt that people will be able to come up with a use case, but I still doon't think it's worth the bother.
(And I chose the Binn directory on purpose for this comparison, since most service packs also replace some of the DLL and EXE files in that directory, just like they replace the resource database with a newer version).
February 11, 2011 at 11:53 am
Lady Di (2/11/2011)
In SQL Server 2008 BOL you can read this:In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The database can be moved;
If you look at the latest version of the page you will find that has changed. And at the bootom of the page you will find the change history section, with a single (undated) entry which reads
Updated the section "Moving the Resource Database" to indicate that the Resource database cannot be moved.
Tom
February 11, 2011 at 2:22 pm
Dupe alert! I submitted this same question back a couple of months ago. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 11, 2011 at 3:54 pm
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 11, 2011 at 4:13 pm
I gave it a try on SQL Server 2008 (10.0.2531.0) and I can confirm the resource database can be moved.
1. Stop SQL
2. Move mssqlsystemresource.mdf and mssqlsystemresource.ldf to C:3. From the cmd prompt run sqlservr.exe -f -T3608 (NB: I was in the folder C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn folder)
4. From another cmd prompt run sqlcmd and use
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'c:\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'c:\mssqlsystemresource.ldf');
GO
5. Close both cmd prompts and Start SQL Server normally.
Can I get my 1 point back?
The strange thing is that when you query the path e.g. by starting SQL using sqlservr.exe -m then from another prompt using sqlcmd -A and running select * from mssqlsystemresource.dbo.sysfiles I got
e:\sql10_katmai_t\sql\mkmastr\databases\objfre\i386\mssqlsystemresource.mdf and
e:\sql10_katmai_t\sql\mkmastr\databases\objfre\i386\mssqlsystemresource.ldf
I don't have an E drive.
February 13, 2011 at 12:30 am
Hi everybody ,
My answer was Yes, because I have read in my local MSDN this :
Moving the Resource Database
In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The database can be moved; however, we recommend against moving it for two reasons:
Applying SQL Server service packs and hotfixes restores the database to the \Binn location.
Moving the Resource database in a failover cluster environment to a nonclustered location will cause failover cluster failure.
To move the Resource database, follow these steps.
Stop the instance of SQL Server if it is started.
Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.
For the default (MSSQLSERVER) instance, run the following command.
Copy Code
NET START MSSQLSERVER /f /T3608
For a named instance, run the following command.
Copy Code
NET START MSSQL$instancename /f /T3608
For more information, see How to: Start an Instance of SQL Server (net Commands).
Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the data file. Do not change the name of the database or the file names.
Copy Code
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.
Set the Resource database to read-only by running the following statement.
Copy Code
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
Exit the sqlcmd utility or SQL Server Management Studio.
Stop the instance of SQL Server.
Restart the instance of SQL Server.
Is that wrong ?
Thanks in advance.
February 13, 2011 at 7:01 am
khelloufsofiane 6183 (2/13/2011)
Hi everybody ,My answer was Yes, because I have read in my local MSDN this :
Moving the Resource Database
In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The database can be moved; however, we recommend against moving it for two reasons:
...
Is that wrong ?
Thanks in advance.
Yes it's wrong. It was an error in an early version of that BoL page. The current up to date SQL 2008 BoL page (Moving System Databases) states that it cannot be moved. If you try doing what was on your old version of that page you will discover that your SQL stops working and you have to undo it.
This has been pointed out be several people in earlier comments; and the first reference given in the explanation of the answer is to the current version of that BoL page. When you have an issue with a QotD answer it's sometimes a good idea to look at the references given and read the discussion before jumping in.
Tom
February 13, 2011 at 10:13 pm
The Resource database (mssqlsystemresource)depends on the location of the master database. If we move the master database, can't the resource database also would follow the master database?
Thanks.
February 14, 2011 at 12:52 am
Good question, Hardik! Thank-you.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
February 14, 2011 at 2:43 am
I managed to do it so i answered yes... =(
February 14, 2011 at 7:29 am
Sourav-657741 (2/13/2011)
The Resource database (mssqlsystemresource)depends on the location of the master database. If we move the master database, can't the resource database also would follow the master database?
No, in SQL SERVER 2008 (and 2008 R2) the resource database has to live in the same place as the SQL Server executables.
Tom
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply