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

Reinitialize subscription remove index from subscriber? Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 7:07 AM
Points: 2, Visits: 80
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
Posted Wednesday, January 23, 2013 9:58 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:28 PM
Points: 565, Visits: 65,730
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
Posted Wednesday, January 23, 2013 2:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:20 PM
Points: 2,105, Visits: 3,560
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
Posted Thursday, January 24, 2013 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 24, 2014 7:07 AM
Points: 2, Visits: 80
Thanks for the replies, you have both been very helpful!
Post #1410988
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse