Post reply

Initialisation of snapshot in transactional replication

  • January 18, 2012 at 6:06 am

    #253829

    Hi all

    Our current ERP system often has releases that contain schema changes to various tables that we use in replication. These releases are actually done through the application and as such we don't always have view of the tables being changed.

    What this means for the replication is that we end up dropping and recreating the replication each release and waiting for a new snapshot to be generated and copied. This can take some time.

    Does anyone have any suggestions on how this could be sped up. At present all I can think of is to identify those tables that are more static and put them in another publication, and hence only recreate a subset of the articles to be replicated.

    Thanks in advance

  • January 18, 2012 at 6:44 am

    #1435199

    In theory you should never have to resetup the whole publication and reinitialise. Some more questions.

    Do you have any visibility of what articles are changing as part of these updates?

    Is replicate DDL turned on?

    If replicate DDL is on then thats half your problem solved since new columns and code will be automatically replicated, leaving just brand new articles to publish. These can be worked out by looking at the system catalogs for new objects (and sys.articles). The its simply a matter of sp_addarticle, sp_addsubscription and running a snapshot to deliver the new articles.

    If replicate DDL is off, it takes more work to find out whats changed and becomes an issue to add columns to an article (not impossible).

  • January 18, 2012 at 7:47 am

    #1435248

    Hi there, it seems we do have DDL changes on. The problem I have been told however is that the application does a drop and create of the tables instead of an alter, and buffers the data and re-imports it.

    Is there any advantage of an alter over a drop and create that you know of? I will start googling!!

    Thanks for the reply though.

  • January 18, 2012 at 10:55 am

    #1435407

    Ok. This poses another question. If the updates drops/creates does it remove the article from the publication? You cannot drop a published table without removing it from replication

    Altering objects means these changes automatically propagate to the subscriber when ddl is replicated. Leaving you to just publish new objects. Reduces the replication admin overhead

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply