Tran Replication

Question

Post reply

Tran Replication

T_SqueaKquel

Mr or Mrs. 500

Points: 558
More actions
April 26, 2010 at 9:34 pm

#219623

Does Insert only replication replicated only committed inserts to subscriber or does it even try to replicate failed inserts??
Apparently I had insert failures at publisher due to PK violation, but the same error appeared at subscriber thereby failing the replication..is this normal behavior? What is a potential fix for such issue?
Thanks..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sandy_1006 SSC Eights! Points: 953 More actions · Answer 1

Hi,

Pk violation is what you should be looking at and try to analyse as why this violation has happened.

T_SqueaKquel Mr or Mrs. 500 Points: 558 More actions · Answer 2

Yes, I know my best bet is to find why PK violations happened in the first place. But, these duplicate data was rejected by SQL Server already, then why did I get error messages in replication monitor where it says Distributor to subscriber getting PK violations..?

T_SqueaKquel Mr or Mrs. 500 Points: 558 More actions · Answer 3

Any other ideas guys..I want to fix or reason this weird behavior but haven't found any help...Thanks.

M&M SSC-Insane Points: 21700 More actions · Answer 4

Is it because someone is directly updating the subscriber database?

M&M

Animal Magic SSChampion Points: 13964 More actions · Answer 5

what type of transactional replication are you using? is it queued or immediate updating. If its immediate then i believe it will try and commit the transaction at both the publisher and subscriber before committing the transaction, that could be your issue. im not 100% on that though.

ps, add verbose output to the replication job and make sure its the same transaction making your replication fail

T_SqueaKquel Mr or Mrs. 500 Points: 558 More actions · Answer 6

Its a unidirectional replication with immediate update. We didn't such errors before, so I can tell that it will not replicate until it commits on publisher. My guess is someone inserted on the subscriber and that messed up everything...can that be the case??

Animal Magic SSChampion Points: 13964 More actions · Answer 7

T_SqueaKquel (4/28/2010)
Its a unidirectional replication with immediate update. We didn't such errors before, so I can tell that it will not replicate until it commits on publisher. My guess is someone inserted on the subscriber and that messed up everything...can that be the case??

yes that would be a more feasible reason if permissions allow someone to do that. There are a few ways around it

1) Dont allow inserts on the subscriber side

2) If you are going to allow inserts then change your replication type to either merge or transactional with updateable subscribers, that way you can let sql manage your identity ranges (or you can do it yourself), that way the primary key is never duplicated

3) If you dont want the inserted subscriber details showing on the publisher then you could chnage the identity seed on the subscriber side so that it starts at -1 and takes off -1 each time (assuming your publisher uses a 1,1 identity seed right now. That way you know all the negatives have been inserted on the subscriber side. If you do do this then make sure your identity column has the "not for replication" enabled so that the replicated values dont use a negative seed.

John