March 21, 2008 at 12:21 am
Comments posted to this topic are about the item Moving System Databases
March 21, 2008 at 4:54 am
The explanation offered doesn't make much sense to me.
"tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful."
But who has suggested dropping and recreating model? It was about moving. And whether tempdb is moved before or after model is moved, in both casees it exists. Just in a different location.
"msdb is used by the SQL Server agent and has no bearing on the stability of the database engine."
And how does it follow from this that it can only be moved after tempdb?
Can someone please provide a pointer to some resource (BOL, MSKB, maybe even some non-MS web site) that confirms this?
As far as I know, and as far as I read from Books Online, system databases can be moved in any order you want. Also, you don't have to move all or none, you can just move one or two and leave the rest in the default location.
The only thing I found that is vaguely related is that **IF** you move system databases by detach and attach (not the method I'd prefer - BOL describes a much easier method), and **IF* you also do this for model and msdb at the same time, **THEN** you need to reatrtach model before you can reattach msdb. This is documented here: http://support.microsoft.com/kb/224071.
But unless someone can point me to some documentation I overlooked, I'll have to conclude that this QotD is wrong. It should be removed from the site, as it can only create confusion.
(But seeing that the erroneous March 6 question hasn't been removed yet either, I don't expect this to happen... )
March 21, 2008 at 7:17 am
As I don't truly make any adjustments to the model, the important one to me is the msdb containing all the scripts for jobs. For me, the important one of the three to relocate is the msdb. In the article Hugo has given, I'm not sure the model is needed for relocation of the msdb. http://support.microsoft.com/kb/224071
Curious - can we move MAPI functions from 2000 to 2005 [which uses SMTP]? Wouldn't detach and attach of model and msdb pose a problem for database mail?
Jamie
March 21, 2008 at 7:28 am
This question makes no sense, and the answer doesn't clarify anything.
When moving system databases, I can't find any documentation that says it has to be in a particular order. If it were required that it be in a specific sequence, I would think that Microsoft would have said so in either BOL or MSDN.
Here's the MSDN article on the subject:
http://msdn2.microsoft.com/en-us/library/ms345408.aspx
I can't find anything in it that says it has to be in a certain sequence.
It looks to me like the author of the question was confused by something he read and didn't understand. I'd like to see some documentation otherwise.
This web page is considered a very reputable source for data about SQL Server. This question (like the age calculation one a little while back), could actually result in spreading confusion. I really do think there needs to be some vetting on these.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 7:56 am
Despite the complete vagueness of the questions, plus the fact it does not matter which order you move the system databases, I chose tempdb as needing addressed next mainly because the proper placement of this database will have the highest performance impact compared to the other system databases.
Personally, I would not recommend moving the master database in SQL Server 2005 because if you do you need to move the mssqlsystemresource hidden system database and I've seen where this does not work and SQL Server will not start up. If you install SQL Server to the proper folders, you will not need to move the master database.
Also the statement about the placement of model impacting tempdb is completely wrong... model has nothing to do with moving the tempdb. See Moving System Databases in BOL.
The statement about MSDB having no bearing on the stability of the database engine is a bit misleading as well. Between model and MSDB, MSDB is used a lot more and generally it's location is much more important then the model database. Model does not completely impact the location of new databases since I can define the default folder locations independently of where model is located. Out of all the system database, the placement of model would be my least concern.
Sorry, but this was not a good question and even worse answer. I really do not care about the points but rather someone new to SQL Server reading the question and answer and being mislead. :blink:
David
March 21, 2008 at 10:23 am
The real answer should be mssqlsystemresource ...
March 21, 2008 at 10:35 am
The real answer should be mssqlsystemresource ...
Adam is correct and using that answer would have made the question make sense... and even be a good question.
David
March 21, 2008 at 10:52 am
not sure how this question go through. I don't even remember seeing it, but I must have approved it for some reason.
I'll reword and change it around. Points will be awarded when I get back from my kid getting a tooth removed.
March 21, 2008 at 2:12 pm
Still wrong. That's the procedure for moving between servers. This is the procedure for moving the location:
http://technet.microsoft.com/en-us/library/ms345408.aspx
Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.
Joshua Perry
http://www.greenarrow.net
March 21, 2008 at 2:18 pm
Joshua Perry (3/21/2008)
Still wrong. That's the procedure for moving between servers. This is the procedure for moving the location:http://technet.microsoft.com/en-us/library/ms345408.aspx
Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.
If you move the master, the mssqlsystemresource has to reside in the same location ... doesn't matter if you're moving servers or to a new drive.
March 21, 2008 at 2:22 pm
That has nothing to do with what appears to be a new question:
[font="Courier New"]If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
In SQL Server 2005, how would you move the location of the msdb database?[/font]
You use the following procedure to move the location of the msdb database:
http://technet.microsoft.com/en-us/library/ms345408.aspx
You're also wrong about having to move mssqlsystemresource to the same location as master, even though that's what it says in books online it's not true. SQL Server 2005 runs just fine with those databases in different locations, even on different physical drives. I know this for sure because I currently run master mdf on the E drivem aster ldf on the F drive, and mssqlsystemresource is still on C. Moving mssqlsystemresource can actually cause SQL Server 2005 to not start.
Joshua Perry
http://www.greenarrow.net
March 21, 2008 at 2:24 pm
Joshua Perry (3/21/2008)
That has nothing to do with what appears to be a new question:[font="Courier New"]If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
In SQL Server 2005, how would you move the location of the msdb database?[/font]
You use the following procedure to move the location of the msdb database:
Ah, re-read the earlier posts ... as stated, the question didn't make any sense. We were referring to as what the next step would be.
March 21, 2008 at 2:29 pm
Joshua Perry (3/21/2008)
Also, setting trace flag 3608 at startup and running in single user mode are the same thing, so two of the answers are identical.
Not entirely true. Trace flag 3608 prevents SQL Server from recovering any database except the master database. Single user mode is exactly that, only one connection allowed.
March 21, 2008 at 2:31 pm
The question of the day has changed. When I answered it the question stated
"If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
Of the other three - which do you need to address second?"
Basically asking what sequence of databases to move... which does not matter. The answer then went on to state that MODEL should be moved next.
Now the question is asking about how to move the MSDB database... which Joshua you are correct.
Also, did not know that mssqlsystemresource could be in different location as master.. thanks for the tip. I may have to try this out when I get some time.
So Steve, do I get a retry on the QOTD since it's been changed? 😀
David
March 21, 2008 at 2:32 pm
The question of the day has changed.
Ok, I thought I was going crazy ... I thought that is what the QotD said when I answered it as well ... thanks for clearing that up.
Also, did not know that mssqlsystemresource could be in different location as master.. thanks for the tip. I may have to try this out when I get some time.
The mssqlsystemresource db has to be in the same location as master ... I remember last time I moved the master db and didn't know about moving mssqlsystemresource, yeah ... SQL wasn't to happy about it.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply