Broke Replication by adding nonclustered index on subscriber the wrong way

Question

Broke Replication by adding nonclustered index on subscriber the wrong way

456789psw

SSCarpal Tunnel

Points: 4728
More actions
December 12, 2013 at 10:39 am

#281201

Broke Replication by adding nonclustered index on subscriber the wrong way(know how to do it correctly now, little to late).
Is there any chance I can delete the ones I added and then reinitialize?
Or do I have to start start over from scratch?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

happycat59 One Orange Chip Points: 29386 More actions · Answer 1

Unless the index is unique and that subsequently gets violated, it is perfectly valid to have different indexes on publisher and subscriber.

What error do you get ?

As for getting things working again, you should be able to drop the indexes and start the distribution agent.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

happycat59 (12/12/2013)
Unless the index is unique and that subsequently gets violated, it is perfectly valid to have different indexes on publisher and subscriber.

Exactly. Adding indexes on the subscriber is normal and wont break replication. As far as I'm aware, there is no "wrong" way to add an index 🙂

You should be able to drop the index on the subscriber and nothing untoward will have happened.

We could do with knowing the error to help further.

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 3

Thanks guys....

Nothing in the error logs? strange(At this point I am just guessing I broke it)

The subscriber just disappeared. Added NC indexes and the next day I have no subscriber. I didnt check on the subscriber right adding them.

I deleted the new ones. Created a new subscriber but now I am getting PK Violations on "can not insert duplicate key in object "

At this point , looks like start over.

Going forward do I have to add indexes VIA the publication properties(snapshot/run additional scripts)?

Seems like I should just be able to go to the subscriber and run a normal Create non clustered index script.(could be I never broke it to begin with,unfortunately I am left to assume:hehe:

)