August 27, 2009 at 7:32 am
Dear all, I admit that I've messed up. :blush:
We have a table that is replicated each night as part of a large collection. This table is about 60,000 rows comapred to several much larger ones. For some reason, the refresh didn't happen one night and as a get round, i was asked to copy the data from the source to the target. I did so, but :ermm: I just did an export & import (exporting the existing target first too) and Lo & behold, the data was correct.
Overnight, however we got a failure because of duplicate keys. Fair enough, I thought and reversed my process by clearing the target table and putting back the export. The job then ran through, but all the old data was lost in the taregt table and just the additions were there at the end.:Whistling:
So, I've messed it up and can't work out how to extricate myself. I worry that reinitializing the replication group will cause an enornmous refresh of all the other HUGE tables that could take a very long time to complete.:sick:
Any suggestions? I suppose I should say that Help About tells me it's version 8.0 on Win Server 2003
i would consider removing the table from the replication group, dropping the target and then re-adding it, but I don't know how. I've just inherited a working system, well it was until a few days ago!:Whistling:
I feel a complete fool. :blush:
August 27, 2009 at 8:13 am
After the replication failed when you manually synchronized the tables, did you try just letting it replicate the next day?
If the tables were already synched, for the prior day, they should just catch up the following day.
- 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
August 27, 2009 at 8:22 am
Replication is by table. You can drop that article for the table and reinitialize just that table and article with a snapshot.
The export/import duplicates replication functionality. If you are using snapshot replication, you should execute that snapshot the next day, not import/export.
August 27, 2009 at 8:24 am
Hindsight is a wonderful thing I'm afraid. It appears that would have been the correct action to take, i.e. do nothing and let it catch up. Unfortunately the pressure to get the table data sorted meant I, um, did something bad.:doze:
I guess what happenned overnight is that the replication decided that all the rows in the target table were invalid and dropped them and then just added the ones it thought were outstanding. Perhaps if I had just added the pending rows, they would have been deleted as invalid and then the process would have run as expected.:ermm:
What a mess.:pinch:
Given a few pointers, I could probably remove the table from the Article (is that the correct name) delete the target table and then re-add the entry into the article. Would that rebuild it overnight? Will I confuse the other vast tables?
Thanks for the thoughts so far. I still feel a fool.:blush:
August 27, 2009 at 8:38 am
You really need to read up on replication and how it works. It's not that complicated, but it's not something we can necessarily give you a quick answer to.
A publication is a series of articles. an article is a table. Try reading this (http://www.replicationanswers.com/AlterSchema2005.asp) and Books Online, to better understand what you're looking at. I'm not sure how things are configured there, so it may or may not be an issue if you remove rows or add additional ones.
September 15, 2009 at 4:18 am
I am confused. You say you "removed" the records you had manually added in, and now you say you think replication "removed" the records.
My experience is of transactional replication, but I'd have thought if you emptied that table and repopulated it, let replication run under a "continue on data inconsistencies" profile it would then run correctly and ignore the primary key issues when trying to add in the records you manually did. Then switcht he profile back to the default and you are back in sync.
Seem straightforward?
September 15, 2009 at 4:37 am
Yes, well perhaps my explanation is getting confusing. Me not knowing the official terms for the items may also be blurring what I am trying to say, so my apologies for that.
The target table was manually emptied (delete from aaaaa) and then data from the source was export/imported to it using the Enterprise Manager Wizard. That fixed the table for that day, but there is a job that replicates overnight for this and many other much larger tables.
The overnight job has presumably decided that I had some invalid rows in the target table, deleted them and then added in the rows that had changed on the source since the last valid replication was done.
Having messed it up, is there a way to effectively say that all the rows in a single source table are to be replicated for a single night without affecting the other tables? In Oracle terms, it would be with the option REFRESH=FULL on the command. The table is small enough to run this in the day to hopefully get the snapshot logs (oracle terms again I'm afraid) back in sync with the state of the target table so that the overnight job will be happy.
I still feel a fool for doing this.
Robin
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply