April 10, 2013 at 9:28 am
I have transactional PUSH replication and recently got the following error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
I manaully ran job "Reinitialize subscriptions having data validation failures", but the problem still persists.
When I created this replication, I set it without synchronization from snapshot because the database is rather large, it's about 200 GB. I restored backup and set it with initilization from backup. There were couple of arrors for violation of PK, I cleared them, and now there are no PK violations.
Now, what options I have in order to fix above error?
My version is: 2008 R2 Enterprice on both servers.
Replication: transactional, push.
Size of publication database: close to 200 GB.
Thanks.
April 10, 2013 at 9:36 am
Based upon what you are showing, I would ask are both the publication and subscriber tables identical?
Please post the schemas for both as well as indexes.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
April 10, 2013 at 10:11 am
Hmm.. Good questions.
But it's very difficult to answer them. I just checked we have 137 tables and 189 indexes on subscriber side. We have more on publisher, but some tables are not replicated. If I will put all of them here, it will be practically nearly impossible to compare them.
The replication was setup about a month ago from backup. At that point they were identical. But since then, they may have been altered on any side - publisher and subscriber.
I just read some articles, should I implement "not for replication" strategy for keys, identity columns, etc ?
April 10, 2013 at 10:54 am
Without the schemas of at least the table(s) you are talking about, I suspect that there is a unique index on the subscriber that is not on the publisher. This is the only way you can have a duplicate index error on the subscriber. I also wonder if there are calculated fields on one side (probably publisher) that are not on the subscriber. You cannot replicate a calculated field.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
April 10, 2013 at 10:55 am
One other thing, are you replicating deletes? you could get the duplicate index error if you delete a record and reinsert it on the publication side. The delete would not replicate but the insert of the new record would and that could also give you a duplicate index error.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
April 10, 2013 at 11:55 am
Yes, I have unique indexes on both sides, actually Primary Keys. I already fixed 2 errors of violation of PK by manually by deleting record from subscription.
About deletes, I don't know, I never specifically made any changes for them whiile setting replication. How can I check if they are replicated ?
Thanks
April 10, 2013 at 1:22 pm
Did you modify the Article Properties when you created the publication? There is a place where you can tell it to not replicate deletes in the statement delivery section.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply