Best Practice for Replicating Schema Changes RRS feed

  • Question

  • Hi,

    We manage several merge replication topologies (each topology has a single publisher/distributor with several pull subscriptions, all servers/subscribers are SQL Server 2008 R2).  When we have a need to perform schema changes in support of pending software upgrades we do the following:

    a) Have all subscribers synchronize to ensure there are no unsynchronized changes present in the topology at the time of schema update,
    b) Make full copy-only backup of distribution and publication databases,
    c) Execute snapshot agent,
    d) Execute schema change script(s) on publisher (*) when c and d are reversed this has caused issues with changes to view definitions which has resulted in us having to reinitialize subscriptions,
    e) Have subscribers synchronize again to receive schema updates.

    Each topology has it's own quirks in terms of subscriber availability and consequently the best time to perform such updates.

    The above process would seem necessary when making schema changes to remove tables, columns and/or views from the database, but when schema changes are focused on adding and/or updating objects, and/or adding/updating data, is the entire process above necessary?  In this instance, if it's possible to remove the step of coordinating the entire topology to synchronize prior to performing these changes I would like to do that.

    The process as we currently perform it works without issue, but I'd like to streamline it if and where possible, while maintaining integrity and avoiding potential for non-convergence.

    Any assistance or insight you can provide is greatly appreciated.

    Best Regards
    Tuesday, October 28, 2014 3:16 PM


All replies