[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1153
  • Last Modified:

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

SERVER 2008
REPLICATION: Snapshot

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?

Max
0
MaxwellTurner
Asked:
MaxwellTurner
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.
0
 
TempDBACommented:
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.
0
 
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?

Max
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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.
0
 
AlokJain0412Commented:
Hi
I am agree with @angelIII
Sometime due to some reason replication recreate l subscribe tables

Following thread may use full for you situation
   
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/8c0c65d3-10e7-4768-a4e6-88cc3bd516f5
0
 
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

Max
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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