Add article to transactional publication without generating new snapshot

Question

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours).

From this article, I've seen how to do this with a partial snapshot by setting immediate_sync off, but that didn't work for us.

Ideally I'd like to just run this as part of our db script to create the table, so if we want it replicated we do:

Create Table ...    
sp_addArticle ...    
sp_PushThisToOurSubscribersNow    

Answer 1

You can add the article through SSMS using the GUI and even apply filters to it. As long as you do not change any other article's properties you will not need to generate a full snapshot.

When you hit OK in the publication GUI (after adding the article), it will close without prompting to reinitialize - if it does prompt to reinitialize, then you have changed something which requires a full reinitialize. If that happens, hit cancel and try again.

After you add the article you can simply start the snapshot job and you will notice that it only generates a snapshot for the new article (called a mini-snapshot).

Then check your distribution job, and notice that it created the table at the subscriber and bulk-copied your data.

Good luck, and let me know if you require further assistance.

Answer 2

As noted in Adding Articles to and Dropping Articles from Existing Publications, you must create a new snapshot for the publication.

To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0. Call sp_addarticle, then sp_addsubscription. If subscriptions are pull you must also call sp_refreshsubscriptions. Then generate a snapshot and only a snapshot for the newly added article will be generated.

Answer 3

You can add a new article without redoing a snapshot, you only need to run the snapshot when there is data to be sent across to the subscriber (though the below can be used to get around this).

1. Create your table on the subscriber side
2. Create your replication stored procedures (update/insert/delete)
3. Add the article onto the Publication as NOSYNC
4. Populate the table with the current dataset as on the publisher

IMPORTANT: At this point if the article starts receiving updates from the publisher before you have finished step 4, you will get Replication errors!
To overcome these errors you can:

/*  comment out raise of error as you know it will generate errors until you have finished syncing the data */
if @@rowcount = 0               
    if @@microsoftversion>0x07320000                   
        exec sp_MSreplraiserror 20598  

1. modify your upd/del repl procs to not generate an error (make sure you change it back):

2. Turn off your subscription agent (disable and stop the SQL Agent job on the subscriber), to stop your subscriber from receiving any updates until you have finished syncing the table.

---

Example SQL to use:

1) Create the table on your subscriber and publisher

CREATE TABLE [dbo].[MyNewTable](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MyNewTable] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
 ) ON [PRIMARY]
) ON [PRIMARY]

2) Add the new table to the publication as NOSYNC

IF NOT EXISTS(select sa.name from sysarticles sa with (nolock) join syspublications sp with (nolock) on sp.pubid = sa.pubid 
            where sp.name = 'MyPublication' and sa.name = 'MyNewTable')
BEGIN
    exec sp_addarticle 
        @publication = N'MyPublication', 
        @article = N'MyNewTable', 
        @source_owner = N'dbo', 
        @source_object = N'MyNewTable', 
        @type = N'logbased', 
        @description = N'', 
        @creation_script = N'', 
        @pre_creation_cmd = N'none', /* this tells replication not to drop the table */
        @schema_option = 0x00000000000000F3, 
        @identityrangemanagementoption = N'none', 
        @destination_table = N'MyNewTable', 
        @destination_owner = N'dbo', 
        @status = 16, 
        @vertical_partition = N'false', 
        @ins_cmd = N'CALL sp_MSins_MyNewTable', 
        @del_cmd = N'CALL sp_MSdel_MyNewTable', 
        @upd_cmd = N'MCALL sp_MSupd_MyNewTable'

    exec sp_addsubscription
        @publication = N'MyPublication', 
        @subscriber = N'MySubscriberServer', 
        @destination_db = N'MySubscriberDB', 
        @subscription_type = N'Pull', 
        @sync_type = N'none', /* this is the primary indicator that a snapshot is not require */
        @article = N'all', 
        @update_mode = N'read only', 
        @subscriber_type = 0
END
GO

Note: You will need to create your own replication procedures:

• sp_MSins_MyNewTable
• sp_MSupd_MyNewTable
• sp_MSdel_MyNewTable

CAUTION: This approach will work, however it should only be done by experienced DBAs who understand the risks of NOT following the recommendations from MS