Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Broke Replication by adding nonclustered index on subscriber the wrong way Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 10:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 22, 2014 10:30 AM
Points: 394, Visits: 794
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
Post #1522410
Posted Thursday, December 12, 2013 7:32 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 3,064, Visits: 2,676
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.



Post #1522550
Posted Monday, December 16, 2013 7:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1523217
Posted Monday, December 16, 2013 7:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 22, 2014 10:30 AM
Points: 394, Visits: 794
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
)
Post #1523237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse