Moving Replicated Databases
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/movingreplicateddatabases.asp
-
Clever, but I don't recommend doing this at all. You are really risking getting things really screwed up! You are better off just scripting out replication including the jobs, removing it totally (sp_removedbreplication and the jobs), detaching, attaching, and just running the replication script again..It is a little longer, I know but alot safer in the long run..The problem with clever little shortcuts like this is they can fill volumes with what they don't tell you can go wrong! Don't gamble with your databases with cute little workarounds like this. You will probably regret it sooner or later. Stick to what has been tried, true, and tested. DBA's that start experimenting with things like this usually find themselves looking for another job...trust me I have seen it far too often.
Travis Lee Alltop
Sr. SQL Server DBA
-
Well, I can be help full in cases you have very big publications and re applying the snapshot takes a very long time. I would really test it in my databases before trying it out in a production environment, but supose that something goes wrong, you can always recreate the publications again.
-
Travis,
I understand your position, but its just a matter of mitigating the risk isnt it? Aside from that, what do you think could go wrong with this approach?
-
"I ran into this a couple years ago..."
Nice solution, but it was discussed on this forum three years ago and you may remember this topic...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=14226#bm70809
-
Its an older article, Steve sometimes will rerun one that he thinks is interesting on Friday.
-
"That's a nice trick, wish I had known it a few months earlier! Andy http://www.sqlservercentral.com/columnists/awarren/
Andy
-
Andy
Why not just back up the database and restore it to the new location? You don't have to touch the system tables then.
John
-
Biggest reason is time - detach, move, attach is fairly quick.
-
Andy
Surely not if that involves dropping and recreating publications, or hacking system tables? Backup and restore requires neither of those.
John
-
Interesting !
I have to move data files for a merge replicated database next week, and in testing just came across this rather annoying problem in Sql2000. I have a feeling in 2005 it would be a different story, as BOL for 2005 states you can do an ALTER DATABASE MODIFY FILE to change the file locations, but 2000 doesn't allow this. (Am I correct?)
I just tried Andy's trick and it seemed to work on my test publication.
Scripting out the publication and recreating it might seem easy to some of you, but actually it's a pain in the &$*%. When you do that, it renders the subscriptions useless, so you then have to visit each and every subscriber computer, drop the subscription database and recreate it. It's not difficult, and only takes 5 minutes, but then the snapshot has to be pulled down as well so that's another 5-10 minutes per machine. All our subscriptions are laptop computers - trying to get access to them is hard enough.
So, this little trick is a life saver.
I'm not a DBA by any stretch but I don't see much harm in changing one little flag temporarily... unless someone can give me a really good reason not to do it this way, it saves me a truckload of effort and time. Likewise, backup/restore takes time.
cheers!
Dave 🙂
-
actually, i have the same problem, i have 2 databases that are on a drive and due to space problem, i need to move them into a new Drive, the 2 databases are replicated into 3 sites,
i was following your steps but i had an error when executing
"update sysdatabases set category=0 where name='myDBName' : Ad hoc updates to system catalogs are not allowed
how can we solve the issue ???
P.S. i'm using Microsoft Sql Server 2005 Standard
Thank You,
-
That's another limitation of Andy's method from which mine doesn't suffer - you can't make updates directly to the system tables on SQL Server 2005.
John
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply