Adding new article to existing publication

  • I have my databases running on SQL Server 2005.

    I have Transactional replication setup between 2 databases - source being my transaction db and the target being the reporting database.

    I am going to add a new article to the existing publication, and my question over here is when I add the the new article do I have to generate a new snapshot and then reintialize the subscription.

    And also when I am generating the new snapshot will I be able to access both the source and the target databases.

    Thank you all in advance.

  • Adding/Dropping articles from existing publications: http://technet.microsoft.com/en-us/library/ms152493(SQL.90).aspx

    When you are generating the snapshot you will be able to access both the publisher and subscriber - from what I remember the snapshot agent takes a schema lock on the tables for which the snapshot is being generated and if the snapshot generation process takes a very long time then you might see blocking issues on the publisher.

  • Thank you very much.

    So, will it generate snapshot only on the new article or all the articles which are getting replicated.

    Thank you again for the reply.

  • The snapshot will be generated for all articles being replicated (i.e. all the existing articles plus any new articles being added to the publication) - however the distribution agent will only create the new article(s) and copy in the data for the new article(s).

    If you check on replication monitor - in the Snapshot Agent tab you'll see the snapshot for all articles being generated and if you go to the distribution agent (the "Distributor To Subscriber History" tab) you'll see that only the schema and data for the new articles are applied.

  • If you add the article using scripts and then generate the snapshot, the snapshot agent should only add the new articles and limit the impact to both the publisher and subscriber to just the new article.

    http://support.microsoft.com/kb/830210

    ----------------------------

    exec sp_addarticle

    @publication = N'', @article = N'',

    @source_owner = N'dbo', @source_object = N'',

    @destination_table = N'',

    @type = N'logbased', @creation_script = null, @description = null,

    @pre_creation_cmd = N'drop',

    @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',

    @ins_cmd = N'CALL sp_MSins_<table>',

    @del_cmd = N'CALL sp_MSdel_<table>',

    @upd_cmd = N'MCALL sp_MSupd_<table>',

    @filter = null, @sync_object = null

    GO

    exec sp_addsubscription

    @publication = N'',

    @subscriber = N'',

    @destination_db = N'',

    @subscription_type = N'Push', @sync_type = N'automatic',

    @article = N'all', @update_mode = N'read only', @frequency_type = 64, @frequency_interval = 1,

    @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,

    @frequency_subday_interval = 5, @active_start_time_of_day = 0,

    @active_end_time_of_day = 235959, @active_start_date = 0,

    @active_end_date = 0, @offloadagent = 0, @dts_package_location = N'Distributor'

    GO

  • MysteryJimbo (10/6/2010)


    If you add the article using scripts and then generate the snapshot, the snapshot agent should only add the new articles and limit the impact to both the publisher and subscriber to just the new article.

    http://support.microsoft.com/kb/830210

    ----------------------------

    exec sp_addarticle

    @publication = N'', @article = N'',

    @source_owner = N'dbo', @source_object = N'',

    @destination_table = N'',

    @type = N'logbased', @creation_script = null, @description = null,

    @pre_creation_cmd = N'drop',

    @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',

    @ins_cmd = N'CALL sp_MSins_<table>',

    @del_cmd = N'CALL sp_MSdel_<table>',

    @upd_cmd = N'MCALL sp_MSupd_<table>',

    @filter = null, @sync_object = null

    GO

    exec sp_addsubscription

    @publication = N'',

    @subscriber = N'',

    @destination_db = N'',

    @subscription_type = N'Push', @sync_type = N'automatic',

    @article = N'all', @update_mode = N'read only', @frequency_type = 64, @frequency_interval = 1,

    @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,

    @frequency_subday_interval = 5, @active_start_time_of_day = 0,

    @active_end_time_of_day = 235959, @active_start_date = 0,

    @active_end_date = 0, @offloadagent = 0, @dts_package_location = N'Distributor'

    GO

    Could you please clarify that?

    When I add an article thru the GUI it generates a full snapshot?

    http://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot">

    http://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • There are 2 options that need to be set to false for the publication in order to add an article and only snapshot that article vs the entire publication.

    immediate_sync and allow_anonymous

    This article explains it well: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/[/url]

  • JeremyE (10/2/2015)


    There are 2 options that need to be set to false for the publication in order to add an article and only snapshot that article vs the entire publication.

    immediate_sync and allow_anonymous

    This article explains it well: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/[/url]

    Thank you Sir! 🙂

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Is it possible to add more than 1 article at a time without having to do a full snapshot?

    I did it before and had nixed results.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I'm replicating from 3023R2 to 2914.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (10/3/2015)


    I'm replicating from 3023R2 to 2914.

    Either your fingers were misplaced on the keyboard or you're using a far newer version than I've ever seen. 😛

  • I had to replace my keyboard.

    I'm going from SQL Server 20122 Enterprise to SQL Server 2014 Enterprise.

    If you add multiple articles do you have to perform a full Snapshot?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • winash (10/6/2010)


    Adding/Dropping articles from existing publications: http://technet.microsoft.com/en-us/library/ms152493(SQL.90).aspx

    When you are generating the snapshot you will be able to access both the publisher and subscriber - from what I remember the snapshot agent takes a schema lock on the tables for which the snapshot is being generated and if the snapshot generation process takes a very long time then you might see blocking issues on the publisher.

    So you can add multiple articles at a time and get errors do I have to perform a complete Snapshot?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • winash (10/6/2010)


    The snapshot will be generated for all articles being replicated (i.e. all the existing articles plus any new articles being added to the publication) - however the distribution agent will only create the new article(s) and copy in the data for the new article(s).

    If you check on replication monitor - in the Snapshot Agent tab you'll see the snapshot for all articles being generated and if you go to the distribution agent (the "Distributor To Subscriber History" tab) you'll see that only the schema and data for the new articles are applied.

    OK, Thanks.

    I had added multiple articles at a time but I kept getting errors that it could not find various articles that had already been published.

    I can't determine why?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • JeremyE (10/2/2015)


    There are 2 options that need to be set to false for the publication in order to add an article and only snapshot that article vs the entire publication.

    immediate_sync and allow_anonymous

    This article explains it well: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/[/url]

    Where do you specify that?

    I could not find it in the article.

    Thank you.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 18 total)

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