• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1253
  • Last Modified:

SQL Server - Replication - non-clustered indexes are moved on subscriber


I recently started replication on my server.  Starting out, the publisher and subscriber tables were indexed indentically, but I soon noticed that when my subscription job runs (each hour), it completely wipes out the indexes in all the subscription tables.  I have replaced the indexes several times only to find them gone soon after.

I thought about running a script to run each hour to re-index each table after it subscribes, but that seems clumsy.  Is there any other way to retain the indexes from the publisher in the subscriber?

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if I remember correctly, snapshot replication does recreate the tables (from scratch), so you will indeed either change replication type, or implement the index creations automatically.
What kind of replication you are configuring. Looks like it is snapshot replication.
1. If it is snapshot replication, why are you running it every hour. If the synchronization of the tables are so important, you can create transactional replication instead.
2. There is an option in the replication which you can use while creating the replication. It is @schema_option = 0x00000000000000F3 in sp_addarticle procedure.
MaxwellTurnerAuthor Commented:
It is a snapshot replication.  The sole subscriber is my website datasource which only needs periodic updates.  I used to only update once each night, but now that we are partially migrated from Access to SQL Server, I decided once per hour was better.  I am very new to replication and it seemed like snapshot what I needed ... I will look into Transactional.

SOLVED:  Sorry, I wish I had discovered this before I posted.  I just noticed that I can edit the artricle properties in SSMS ... just had to turn "Copy non-clustered indexes to destination" to True.  When I generate a CREATE script from my publications in SSMS, it shows @schema_option = 0x00000000080350DD.  The hourly job just ran and WOOHOO, my subscriber is now indexed.

It seems I have another problem now though ... one table in in a subscriber database ends up with 0 records after the subscriber job runs ... 4497 records in the publication table!??  All of ther other 24 tables have the correct number of records.  I currently have the Publisher jobs running on the hour and the Subscriber job running at 2 minutes past the hour  ... do I need to allow more time between the publication and subscription? ... I am assuming that is how to schedule this, or I missing something about syncronization?

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Anthony PerkinsCommented:
>>just had to turn "Copy non-clustered indexes to destination" to True. <<
Right, in order to include non-clustered indexes the @schema_option has to be a Bitwise OR with 0x40.

>>one table in in a subscriber database ends up with 0 records after the subscriber job runs<<
I suspect the value in the same @schema_option is the cause.
I am agree with @angelIII
Sometime due to some reason replication recreate l subscribe tables

Following thread may use full for you situation
MaxwellTurnerAuthor Commented:
Thanks everyone for their help.  Got everything working (fingers crossed).  The one table had 0 records in it after subscribing was because I was mistakenly publishing the same table to the same subscription ... not exactly sure why it was causing the problem, but it certainly seems that would cause a conflict ... has worked fine since I couaght that.

Not sure how to award this as I kinda solved it myself, but ... TempDBA was the one who sent me in the right direction with the schema_option and sp_addarticle comment, so I guess it is yours

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now