Replication Script

  • Hi,

    I have created merged replication. How can I change database structure without having to recreate replication job again? (Is it possible to create replication script and just run this script after I've changed db structure? If yes, how?)

    Thanks.

    dusan

  • You wan't be able to change the db structure until you drop the publication. You need to drop the publication and change the merge publish property with the sp_replicationdboption stored procedure. Then change the db structure, and recreate the publication.

  • You can do add/drop using sp_repladdcolumn/drop column.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • How can I create replication script. Just in case...

    dusan

  • In Enterprise Manager, select the server, then from the Tools menu choose Replication -> Generate SQL Script. Do this on both the publisher and the subscriber.

    Jay Madren


    Jay Madren

  • Yes you can change the table structure with sp_repladdcolumn, but not the db structure.

    If you want, to avoid syncronizing again with a snapshot, you can re syncro withhout the snapshot. But you should pause the publishers, to avoid new transactions to take place.

  • Got a little how to for you. Create a table you want to add to your publication. Afterwards issue something like that (depending the options you want to use and assuming you use merge replication):

    EXECUTE sp_addmergearticle

    @publication = 'your_publication',

    @article = 'new_table',

    @source_owner = 'dbo',

    @source_object = 'new_table',

    @type = 'table',

    @description = null,

    @column_tracking = 'true',

    @pre_creation_cmd = 'drop',

    @creation_script = null,

    @schema_option = 0x000000000000CFF1,

    @article_resolver = 'your_resolver',

    @subset_filterclause = null,

    @resolver_info = 'your_resolver_info',

    @vertical_partition = 'false',

    @destination_owner = 'dbo',

    @auto_identity_range = 'false',

    @verify_resolver_signature = 0,

    @allow_interactive_resolver = 'true',

    @fast_multicol_updateproc = 'true',

    @check_permissions = 0,

    @force_invalidate_snapshot = 1

    This will add the new table seamlessly to your publication. You won't have to do anything else. Note: you should always invalidate the snapshot if you do schema changes.

    To add a single column to a table that already is published, use the following (without creating the column first):

    EXECUTE sp_repladdcolumn

    @source_object = 'existing_table',

    @column = 'new_column',

    @typetext = 'your_type (including nullability/checks/foreign keys in standard SQL syntax)',

    @publication_to_add = 'existing_publication',

    @force_invalidate_snapshot = 1

    @force_reinit_subscription = 0

    Hope this can shed some light. Best regards,

    Chris.

Viewing 7 posts - 1 through 6 (of 6 total)

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