Replication Problem - Need Help Please
-
Hi
I am a newbie to this replication world. I am having a weired problem. I am definitely not doing something right... Just can figure out whats wrong...
I have two different database on two different servers. These databases were created from the same development machine (just copied on to those separate servers).
DB 1 was in production for 3 months and loaded with 1.3 GB of data.
DB 2 is in production for last 10 days.
What I want to do is merge the data from DB 1 and DB 2. Both the database should have exact same data after replication.
I am following all the default wizard settings to setup a merge replication from the server where DB 1 resides. After the initial snapshot the SQL MMC shows that the databases are in sync. But when I open the database DB 2 -- all the data which were entered before (10 days of work as I mentioned before) are gone. It did replicate all the data from DB 1 but no information on its own 10 days work data... Same goes to DB 1, it has all the info it had before but no data for DB 2 (10 days data).
Gurus, please help I need to merge these two database so that they have the exact same data. What am I doing wrong?
Thanks in advance
Faruk
-
Unfortunately replication won't do this for you as far as I know. Replication works a treat if you have one initial source database, which is then replicated to other machine(s). From then on, the other machines can send their changes back and receive changes from the main database.
In your situation, you have two separate and distinct databases that you wish to merge both ways. I think I have used a tool called SQLDiff in the past from ApexSQL (I might be wrong here - just from memory!). Goto google and do a search.
Also, do you wish to have the data continue to sync once you have done the initial merge? In this case, I suggest that you do the initial sync of data using the 3rd party tool. Then you can remove DB 2 and set up merge replication from DB 1 - the two should stay sync'd from then on.
Let me know how you go - I'm doing similar things shortly with replication
-
If I remember correctly, when the initial snapshot is created, you get to select whether or not the schema and data already exists. Did you select that option? That MIGHT make the two databases sync up as opposed to overwriting. We use merge replication, but haven't tried to use it to merge two databases.
-
ianyates is correct. Replication won't initially merge the data. You have to start with a Published database which contains all of the data.
SQL Data Compare (Red GAte) is another tool which does a good job of merging data. If you need to manually merge the data you may need to create a third database and use DTS to import the data from DB1 and DB2 database into this third database, appending the rows.
-
By the way, do any of your tables have IDENTITY columns? If so, have you set them up with check constraints so the two systems won't collide once you've merged the data? For the ones suggesting other packages to merge this data, do you know if they handle key collisions?
-
I think it might've been the one from RedGate that I've used in the past
Good point with the identity columns - they will cause grief!
DTS may well be an easy way of doing things too - get them both into a separate DB and have some key mapping tables to handle "old identity" from old DB to "new identity" in the new DB. Effectively, if you have your identity as a primary key with some foreign keys referencing it, you should (at least this is what did once but for int -> GUID - similar ideas though) for each table
- Create a oldID->newID table with newID as your identity column, also have a DB (int) column to signify which DB the data came from
- insert the data from your pkey identity column from the old table into the oldID column of your mapping table for the first DB - set the DB column to 1
- do the same as #2 but using the second DB - set the DB column to 2
- set identity insert on for the destination table
- insert the data from your old table in the first DB - join with the mapping table on OLDTable.ID = mappingTable.oldID and DB = 1 - you want to insert the newID column rather than the oldID column
- Repeat #5 but insert the data from the second db and have the join condition have DB = 2.
- turn off identity insert on the new destination table
I mightn't have thought it through properly, but it ought to work. You could rehash it a bit to just tip the data from one DB into the other - that would cut out steps 3 & 6 I suppose but then you can't retry it if it doesn't work as you've changed your first DB already!
Finally, when you copy across tables that reference your changed identity columns, you should again join to the mapping table to ensure that you insert the new value for the foreign key column (newID rather than oldID).
Cheers!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply