Can you replicate if databases are renamed?
-
We have a situation where databases may be moved between machines and end up with different names on different machines (it's a long story). Is there a way to get replication to work under this condition?
As an example, suppose database A is a publisher and database B is a subscriber. B gets copied to another machine (actually backed up and restored) and is named C on the second machine. Editing occurs on C. It is returned to the first machine (again through backup/restore) and gets yet another name D. Not only that, but the database A did not stay mounted the entire time, but was backed up and restored and now has database name E. Is it possible to then merge changes from D back into E?
-Andy Barnhart
Edited by - arbarnhart on 10/15/2002 12:14:38 PM
-
Wow. Thats a lot to follow. Certainly you could do it manually, since you really only need to compare D & E, do the inserts, deletes, then the updates (depending on what you mean/want). Transactional wouldnt really help you since it is normally one way only (you can post changes back but normally this involves a trigger...maybe..but more work!), snapshot wouldnt make sense, so possibly you could get merge to do it.
Do you have a good reason for doing this? Sorry, had to ask!
Andy
-
The reason is because of the same interim migration version I described in another posting you answered. We are converting from a proprietary format to SQL Server and for mostly political reasons have to release a version with some new functionality in less time than required for the full port. So we are coding new features in .NET components that access SQL Server for data and keeping the old base product code using its proprietary format. But due to a single file requirement and a requirement for being able to share the file around in a disconnected workgroup, we have coded an extension to the old format to allow us to store the database (actually a compressed backup) in the file. When the file is opened, we do a restore with move (rename, for all practical purposes). We found we needed to use a generated name to avoid conflicts in rare circumstances. Although rare, they do occur and we have to deal with that.
Anyway, we did some experimentation with replication in this scenario and it seemed to have some dependancy on the publisher and subscriber not changing names. Merge functionality is exactly what we need. Other logic in the base product should limit, if not eliminate, collisions. I told you it was a long story
So is there something we are overlooking? Is there a way to replicate if both databases have been renamed? The only workaround I have come up with is really ugly (aka rustic elegance) - generate a couple of GUIDs to use as temporary database names only during publish/subscribe operations. Either that or store off what the temp names were at the time the subscriber database was created, but that requires a few extra steps and therefore time.
-Andy
PS - We are pretty strong Windows API, MFC, ATL and COM developers, but our SQL is a bit more limited (not idiots, but we might be hard pressed to prove that). So if there is an obvious answer, don't assume we've already tried it.
quote:
Wow. Thats a lot to follow. Certainly you could do it manually, since you really only need to compare D & E, do the inserts, deletes, then the updates (depending on what you mean/want). Transactional wouldnt really help you since it is normally one way only (you can post changes back but normally this involves a trigger...maybe..but more work!), snapshot wouldnt make sense, so possibly you could get merge to do it.Do you have a good reason for doing this? Sorry, had to ask!
Andy
http://www.sqlservercentral.com/columnists/awarren/
Edited by - arbarnhart on 10/15/2002 7:00:30 PM
-
I just dont have enough experience with merge to see the looming roadblocks. I "think" no matter what you do with the db names you'll have to go through a fix up process to get it to work. Might be horrible, might not. Dont forget you'll need (or it will add) a GUID in each table. Hotek is probably the guy to ask about merge - try http://www.mssqlserver.com or look for him in the MS newsgroups.
Thats probably worth a couple hours experimenting, but given you're comfortable coding I think the do it yourself approach makes more sense. Adds are easy, deletes are easy, so you'll spend the time on the updates. It's easier if you're only merging back to the master and then generating a new copy for the client rather than doing a true merge. For changed rows (say where timestamp on client doesnt match publisher) you could either update the whole row, or do a column by column compare update. I've got a project that does some of this running, it IS doable.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply