Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Resource database - SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:53 AM
Points: 2,667, Visits: 4,091
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;


This is what I see in BOL:

"In SQL Server 2008, the location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. The database cannot be moved."

http://msdn.microsoft.com/en-us/library/ms345408(v=sql.100).aspx

Post #1062539
Posted Friday, February 11, 2011 7:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:23 AM
Points: 3,241, Visits: 140
I saw that in:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm

Post #1062562
Posted Friday, February 11, 2011 7:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:36 PM
Points: 1,786, Visits: 1,116
Good question though I got it wrong. I thought i would be able to move the resource database in SQL Server 2008. After i got it wrong, i tried moving the resource data and log file in different drive and i was not able to start the sql server service then which proved that we cannot move the resource database to different drive. Good one and learnt a new lesson today. I am still not convinced though, if we could move other system databases, why we cannot move the resource database inSQL 2008?

Thanks,
Post #1062569
Posted Friday, February 11, 2011 8:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,383, Visits: 1,754
Lady Di (2/11/2011)
I saw that in:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/72bb62ee-9602-4f71-be51-c466c1670878.htm



That URI indicates to me that you have help set up to use local help first. One of the major pitfalls of doing that is that if there are typos in the help system, they may be corrected online, but they won't get updated in your local copy, so you could be working from inaccurate information.

I don't have local help to verify (I prefer not to use it for just that reason), but I believe that's what's happened here.
Post #1062639
Posted Friday, February 11, 2011 8:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,383, Visits: 1,754
Abi Chapagai (2/11/2011)
Good question though I got it wrong. I thought i would be able to move the resource database in SQL Server 2008. After i got it wrong, i tried moving the resource data and log file in different drive and i was not able to start the sql server service then which proved that we cannot move the resource database to different drive. Good one and learnt a new lesson today. I am still not convinced though, if we could move other system databases, why we cannot move the resource database inSQL 2008?

Thanks,

The best answer I could find to that was here:
http://www.sqldev.org/sql-server-setup--upgrade/moving-resource-database-in-sql-server-2008-doesnot-work-96677.shtml
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.

*This implies that you may be able to move a resource database if you also move the SQL Server executable... any brave hearts with unimportant SQL 2008+ boxes want to try it?
Post #1062644
Posted Friday, February 11, 2011 9:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #1062682
Posted Friday, February 11, 2011 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 6,133, Visits: 8,398
sknox (2/11/2011)
The best answer I could find to that was here:
http://www.sqldev.org/sql-server-setup--upgrade/moving-resource-database-in-sql-server-2008-doesnot-work-96677.shtml
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1062684
Posted Friday, February 11, 2011 10:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 262, Visits: 920
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?
Post #1062693
Posted Friday, February 11, 2011 10:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,383, Visits: 1,754
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.
Post #1062728
Posted Friday, February 11, 2011 11:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 6,133, Visits: 8,398
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).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1062763
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse