none
questions about custom indexes on replication target tables RRS feed

Answers

  • As Kevin said, when you reinit it drops the existing objects and recreates them from the publication. So your custom indexes are gone.

    I suggest a slightly different method to what is described in the link.  I always create stored procs in the PUBLISHER and replicate them to subscribers called "Replication_BeforeSnapshot" and "Replication_AfterSnapshot".  Then the script in the publication is always the same, "EXEC Replication_BeforeSnapshot" and "EXEC Replication_AfterSnapshot".  Even if they do nothing initially, you have the process in place to support custom processes.

    In that way, you can manage the index creation within your publisher project, instead of manually on the subscriber.

    In some cases, the before snapshot must make sure processes are terminated before applying the snapshot.  In my case, disables the reporting user(s) and kills existing reports from running, so the users do not get incorrect data during the snapshot.  Then the after reenables the users.  Yes, they get errors, but that is more acceptable than incorrect data.

    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 2:28 PM

  • When/If you need to re-init the subscriber the default way that happens is a Schema and Data bulk copy of the table definition and contents.  Your subscriber table definition including indexes gets blown away.  I say default because that was the behavior a long time ago...if there are changes I am not aware of them.


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    • Proposed as answer by Tom Phillips Friday, November 15, 2019 2:22 PM
    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 2:20 PM
  • You can do anything you want on the subscriber.

    If the index changes, I would change the proc Replication_AfterSnapshot and rerun it.  You don't really need to run a snapshot to change the indexes.

    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 3:21 PM

All replies


  • When/If you need to re-init the subscriber the default way that happens is a Schema and Data bulk copy of the table definition and contents.  Your subscriber table definition including indexes gets blown away.  I say default because that was the behavior a long time ago...if there are changes I am not aware of them.


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    • Proposed as answer by Tom Phillips Friday, November 15, 2019 2:22 PM
    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 2:20 PM
  • As Kevin said, when you reinit it drops the existing objects and recreates them from the publication. So your custom indexes are gone.

    I suggest a slightly different method to what is described in the link.  I always create stored procs in the PUBLISHER and replicate them to subscribers called "Replication_BeforeSnapshot" and "Replication_AfterSnapshot".  Then the script in the publication is always the same, "EXEC Replication_BeforeSnapshot" and "EXEC Replication_AfterSnapshot".  Even if they do nothing initially, you have the process in place to support custom processes.

    In that way, you can manage the index creation within your publisher project, instead of manually on the subscriber.

    In some cases, the before snapshot must make sure processes are terminated before applying the snapshot.  In my case, disables the reporting user(s) and kills existing reports from running, so the users do not get incorrect data during the snapshot.  Then the after reenables the users.  Yes, they get errors, but that is more acceptable than incorrect data.

    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 2:28 PM
  • thx Tom and Kevin. Can those custom indexes be filtered? If yes, how do you manage changing filter rules, just coordinate re inits with known biz rule changes?
    Friday, November 15, 2019 3:14 PM
  • You can do anything you want on the subscriber.

    If the index changes, I would change the proc Replication_AfterSnapshot and rerun it.  You don't really need to run a snapshot to change the indexes.

    • Marked as answer by db042190 Friday, November 15, 2019 3:30 PM
    Friday, November 15, 2019 3:21 PM
  • You need to deploy the indexes via a post snapshot script. 

    This is the supported way of doing this.

    However you will find that these indexes will be applied sequentially one after the other. I normally script them out and then run them in parallel - one script for each table to minimize the time taken. I stop the distribution agent while the indexes are being created.

    Friday, November 15, 2019 4:11 PM
    Moderator
  • thx, how do you manage to run things in parallel given the mostly sequential nature of sql server?
    Friday, November 15, 2019 5:44 PM
  • There is no built-in way to make them parallel.  You could create several SQL Agent jobs and have the proc just execute the jobs.  However, that also makes them async.  In my situation, we do not want any access until the snapshot AND the indexes are created.  So we wait for everything to finish before reenabling logins.

    Reiniting the snapshot should be a rare circumstance, so waiting a few extra mins is not the end of the world.

    Friday, November 15, 2019 5:51 PM
  • thx, we just plumbed a job scheduling tool thru powershell to sql agent etc. so I suppose that is another avenue. Also i think i read somewhere of a trick for synced parallel processing from sql agent but i could be wrong.  i'l post that link if i can find it.
    Friday, November 15, 2019 5:58 PM