Post reply

Transactional replication with drop/create view. Best practice advice

  • November 12, 2008 at 11:58 am

    #202796

    We have transactional replication set up on a large database where the publication holds over 20,000 articles.

    The Database supports a 3rd party application which is having a patch applied to it. This patch will alter several replicated tables to add columns (not a problem) and also drop/recreate 6 views (problem ?) which are part of the publication.

    I assume that once the views are dropped/recreated this will invalidate the publication.

    Recreating the publication / re-snapshotting takes many hours.

    What would be the best way of handling the drop/create of these views (we have no control over the sql being to do this since it's run via the 3rd party application) or will replication deal with this anyway without any intervention ?

    I'm relatively new to Sql Server Replication hence the question

  • November 12, 2008 at 8:17 pm

    #897872

    Dropping objects that are included in a publication is not possible. The DROP commands will fail. You will need to remove the views from the publication(s) first. This can be done without affecting the rest of the publication (i.e. the other tables and view will continue to be replicated and will not need to be reinitialised). This does assume that you specified @replicate_ddl = 1 when you created the publication(s).

    You can remove an article from a publication by using the stored procedures sp_dropsubscription and sp_droparticle. You must specify the name of the article that contains the view(s) in question.

    When the changes have been made, you can then add the articles back in to the publication, add then into the subscription and generate a snapshot.

  • November 13, 2008 at 2:19 am

    #897935

    Thanks for the information

    Is there a way of dropping/recreating the relevant articles without having to do a complete snapshot to resync the replicate database since only 6 objects (out of many thousand) are changing ?

  • November 13, 2008 at 8:20 am

    #898124

    Have you thought of . . . to perform the update to the views manually by using a linked server and performing the update to the views using a script.

    Nebpgmr

  • November 16, 2008 at 7:35 pm

    #899128

    Why not create a separate publication for the views ?

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

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