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

Posted on 2011-10-07
Last Modified: 2012-05-12

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?

Question by:MaxwellTurner
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 25

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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?

    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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.
    LVL 5

    Expert Comment

    I am agree with @angelIII
    Sometime due to some reason replication recreate l subscribe tables

    Following thread may use full for you situation
    LVL 1

    Author Comment

    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


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now