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 123»»»

Moving System Databases Expand / Collapse
Author
Message
Posted Friday, March 21, 2008 12:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:50 PM
Points: 243, Visits: 168
Comments posted to this topic are about the item Moving System Databases
Post #472687
Posted Friday, March 21, 2008 4:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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... )



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #472732
Posted Friday, March 21, 2008 7:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #472774
Posted Friday, March 21, 2008 7:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #472780
Posted Friday, March 21, 2008 7:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 916, Visits: 993
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.

David



Post #472811
Posted Friday, March 21, 2008 10:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
The real answer should be mssqlsystemresource ...

---
SQLSlayer
Making SQL do what we want it to do.

Post #472961
Posted Friday, March 21, 2008 10:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 916, Visits: 993
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



Post #472974
Posted Friday, March 21, 2008 10:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #472981
Posted Friday, March 21, 2008 2:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #473097
Posted Friday, March 21, 2008 2:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.


---
SQLSlayer
Making SQL do what we want it to do.

Post #473099
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse