SQL Server Performance

How to dynamically add tables to replication?

Discussion in 'Performance Tuning for SQL Server Replication' started by Teknion, May 1, 2006.

  1. Teknion New Member

    I needed to replicate a server using transactional. Problem was that every day, new tables would be added to the publisher. As far as I can tell, the only way to add new tables is to halt the publication, remove the subscriber, add the tables, start the publication snapshot again as you add the subscriber.

    My solution was to set up automated replication when new tables come in. They are always the same 20 tables, just different names, so a user simply needs to submit a form, and I would execute SQL code to create a new publisher, add the subscriber, and schedule the initial snapshot.

    I did this by spying on the replication wizard using profiler, and figuring out which stored procs where executed in the process.

    Though it works, I am not sure if I did everything correctly, and how stable it is. Is there a better, easier way to ensure ALL data, including new tables, can be 'replicated' or 'mirrored' on another server?

  2. ndinakar Member

    check out sp_addarticle and sp_addsubscription

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************

Share This Page