Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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    
share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
-1 as while the advice is to create a snapshot you don't have to (it's just the easiest way to do it) –  Andrew Bickerton May 28 '12 at 8:20
    
It is the recommended approach in SQL Server Books Online. The problem with your approach is that it is prone to errors. –  Brandon Williams May 29 '12 at 3:10
    
I agree with your advice of it is the recommended approach, I just disagree with the must part of the statement. –  Andrew Bickerton May 29 '12 at 7:40
add comment

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

share|improve this answer
1  
-1 Following this approach is error prone. For example, when I add an article in this fashion and synchronize I get an error: "Error executing a batch of commands. Retrying individual commands". –  Brandon Williams May 29 '12 at 3:12
    
Could not find stored procedure 'dbo.sp_MSins_MyNewTable' –  Brandon Williams May 29 '12 at 3:15
    
@BrandonWilliams Apologies Brandon, I missed a key step: Running a snapshot does 2 things 1. Create table and replication sps 2. initial population of data. These would need to be done in alternative manner. –  Andrew Bickerton May 29 '12 at 7:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.