April 7, 2010 at 6:43 am
George
In the context of DR/relocation, I absolutely agree. I felt like the original question was only a matter of curiosity on 'how-to' manage system databases relocation, rather than building a reliable DR strategy.
David B.
David B.
April 7, 2010 at 8:41 am
Interesting discussion, thanks guys!
To answer some of the speculation, yes, this is absolutely just a "What if..." type of practice thing for me. I'm trying to go through some worse case scenerios of recovering things, and thought this would be properly annoying.
April 7, 2010 at 9:15 am
llevity (4/7/2010)
Interesting discussion, thanks guys!To answer some of the speculation, yes, this is absolutely just a "What if..." type of practice thing for me. I'm trying to go through some worse case scenerios of recovering things, and thought this would be properly annoying.
not a bad idea, you will learn a lot about how SQL hangs together, via all the problems you will hit!
---------------------------------------------------------------------
April 7, 2010 at 10:38 am
george sibbald (4/7/2010)
does SQL rely on info in the master database or the registry? I expect and hope the registry but have never actually tried out this scenario.
as far as i am aware the only database location in the registry is the master database. The fact that you use an
ALTER DATABASE MODIFY FILE
would lead me to think the resource database location is buried in the master!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2010 at 11:36 am
In SQL2008 (unlike SQL2005) you cannot move the resource database, which would mean you cannot run alter database against it
http://msdn.microsoft.com/en-us/library/ms345408.aspx
so it's still up for grabs where this info is held
---------------------------------------------------------------------
April 7, 2010 at 12:04 pm
BOL always has some inconsistencies. In the copy i have it says you can move it but not recommended as if you do it is always returned to the Binn folder after a patch\SP
From my copy of BOL
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.
maybe they changed it as too many people were hosing their SQL Server systems 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2010 at 12:33 pm
I didn't run into any resource db issues in my experiments, but luckily, my sql binaries go to the default c:\... location on all installs.
What a recipe for disaster that seems, though... a system db you cannot alter the location of?!
April 7, 2010 at 1:40 pm
llevity (4/7/2010)
I didn't run into any resource db issues in my experiments, but luckily, my sql binaries go to the default c:\... location on all installs.What a recipe for disaster that seems, though... a system db you cannot alter the location of?!
Thing is you should no longer need to move it.
Any chance you can test with different sql binary locations?
---------------------------------------------------------------------
April 12, 2010 at 2:42 am
What a recipe for disaster that seems, though... a system db you cannot alter the location of?!
Remember that the key thing with the resource database is that it is a read-only entity that you need in order to run SQL Server. We have had other read-only entities needed to run SQL Server for some time... program binaries and registry entries come to mind, but we do not complain that we have no or very limited control over where they are placed.
IMHO we should accept that the best and safest location for master and resource is where they are placed by the SQL Server install, and build our DR capability around this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply