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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
  1. Add new articles in your Publication property window (uncheck the Show only checked articles in the list)
  2. right click the same Publication node and go to "View Snapshot Agent Status"
  3. click start and just note the log in the same windows which shows this new article is only synced
  4. after a short time the new articles will be synced in subscribers without going to initializing all previously synched

enter image description here

share|improve this answer

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
1  
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

@Brandon Williams - it's easy to pass this error. Just recreate procedures on the publication database. Execute this on the publication database: EXEC sp_scriptpublicationcustomprocs 'PublicationName';

Take the script and run it on the subscription databases. The errors will be gone.

share|improve this answer

I had the same question, and even though I've been a DBA for a while, I haven't really dealt with replication deeply enough to be completely comfortable with it, so I thought the following resources and guides were helpful:

  • This blog, which provided a good outline of the process. It also reminds us that, if you have a large existing publication, and it's option is set to "immediate_sync", it will cause an entirely new snapshot to be prepared every time you add or change an article. So he has a handy tip to change that option, using sp_changePublication @publication='MyPub', @property='immediate_sync', @value='false';

  • MSDN blog post in "repltalk" (sounds like a good resource in general!) - not "directly directly" related but still helpful

  • This question, where @Brandon-Williams pointed out that, if it's a Pull subscription, you should also refresh it, using sp_refreshSubscriptions @publication = 'MyPub'

  • SSMS Replication Monitor - convenient way to stop & start the agents (snapshot, log-reader) when following the guide.

Here are the actual steps I followed, which worked well & met the approval of my supervising DBA:

  1. Open up Replication Monitor, select the publication, go to Agents, right-click Log Reader Agent, click Stop.
  2. Set the publication to not allow-anonymous & not immediate-sync, using sp_changePublication - yes, as @cody_konior points out, this is under-documented, but it did work fine in my case. YMMV
  3. Created the table at the subscriber manually using script, filled with data using linked-server query (since it was small). You could also use SSIS, BCP, or some other means of doing this. And it might not be necessary, if you're OK with the repl-snapshot doing it for you. I just wanted to prep it manually the first time.
  4. Add the article (table) using sp_addArticle
  5. Add all columns of the table using sp_articleColumn (specified publication & article, DIDN'T specify columns -> implies ALL columns)
  6. Exec'd sp_refreshSubscriptions for that publication to refresh the pull-er
  7. Open up Replication Monitor again, select the pub, go to Agents, right-click Snapshot Agent, click "Start". It will run once, creating the new snapshot.
  8. Right-click Log Reader Agent, click "Start". It will start & continue running as normal, & your replication should now be working again.

And while yes, you could do most of the changes with the SSMS GUI, I find it helpful to script it all out so it can be A) under source-control (change-control), and B) deployed repeatedly or to multiple instances. Unfortunately I didn't spend the time to scripting out the Agent stops/starts, but that shouldn't be too hard given that they're just SQL Agent Jobs. You just have to do that whole "find the JobID using the Job-Name" trick (query sysjobs -- really, MS?)...

Hope that helps future readers!

share|improve this answer
    
Your first link is a little broken, it's including "blog post" as part of the URL which breaks when following it. (However having read it I don't think that particular article is relevant to the question at hand). The second article is definitely relevant and the main way of doing it. I will note though that it's hit and miss, and the impacts of modifying any of those options aren't disclosed anywhere by Microsoft - which sucks. – Cody Konior May 24 at 2:02
    
Thanks! I fixed the link. Even though it doesn't sound directly relevant (because it's talking about a subscription init'd using a backup), the steps (for what he calls "Option 2") are helpful as a general outline to get going, but I will edit the answer to clarify my exact steps that actually worked. – NateJ May 24 at 14:25

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
3  
-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

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.