Reinitialize subscription remove index from subscriber?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Reinitialize subscription remove index from...

Reinitialize subscription remove index from subscriber?

Rate Topic

Display Mode

Topic Options

Author

Message

matt.osullivan

Posted Wednesday, January 23, 2013 2:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:30 AM
Points: 2, Visits: 57

We reinitialized the subscription for a replication and noticed afterwards that an index the was created on the subscriber database was missing. This index isn't replicated from the publisher. Is this correct that an index created on a subscriber database will be removed when reinitialized?

Thanks.

Post #1410411

DBA_Dom

Posted Wednesday, January 23, 2013 9:58 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 563, Visits: 58,911

That depends on a few settings. By deafult yes. When you reintialize a subscription it says I need a new fresh copy from a snapshot. Now it depends on how your publication is set to deliever that snapshot. There is a setting for each article for 'Action if name is in use'. This by default will drop and recreate the object. That will remove the index and all other contraints and objects asscoiated with that table (i.e. statistics,Foriegn Keys, triggers). If these objects are not part of the source table and in the setting for the article to be copied over then they will no longer exist at the source.

There are various ways to continue to have an index on a target table and not on the source. This just depends on what your enviroment is like.

Post #1410689

David Benoit

Posted Wednesday, January 23, 2013 2:34 PM

SSCrazy

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:26 AM
Points: 2,063, Visits: 3,440

If you look under the publication properties, Articles, select the article / table in question and look at the properties of that article you should be able to set "Copy nonclustered indexes" to True and then non-clustered indexes will be copied as part of the snapshot process. That said, if you add an index on the publisher, that will NOT replicate that to the subscriber. That will need to be done manually.

You can also use sp_changearticle to set the schema_option to allow for the copy of nonclustered indexes as part of the snapshot but that is a bit more complex and I would recommend staying in the GUI for this one.

Hope this helps.

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Post #1410799

matt.osullivan

Posted Thursday, January 24, 2013 1:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 8:30 AM
Points: 2, Visits: 57

Thanks for the replies, you have both been very helpful!

Post #1410988

« Prev Topic | Next Topic »

Permissions