Post reply

Reinitialize subscription remove index from subscriber?

  • January 23, 2013 at 2:07 am

    #288290

    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.

  • January 23, 2013 at 9:58 am

    #1580181

    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.

  • January 23, 2013 at 2:34 pm

    #1580272

    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

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

  • January 24, 2013 at 1:56 am

    #1580415

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

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

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