Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Broke Replication by adding nonclustered index on subscriber the wrong way


Broke Replication by adding nonclustered index on subscriber the wrong way

Author
Message
456789psw
456789psw
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 831
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
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3563 Visits: 3104
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
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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 Smile

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
456789psw
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 831
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 assumeHehe
)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search