23

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    
13

You can add the article through SSMS using the GUI and even apply filters to it. As long as you do not change any of the other properties of the article 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 snapshot. 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.

  • I did everything explained in this answer but still found myself waiting on the entire replicated database to synch. I did NOT get prompted to reinitialize after adding the new articles, but it still did a full initialization. Be careful. – JzInqXc9Dg Mar 29 at 16:50
7
  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

3

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.

* This is the recommended approach in SQL Server Books Online. The problem with your approach is that it is prone to errors.

2

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!

1

Major edit This is a complete rewrite of this answer (taking on board valid criticism that previous version was error prone and would cause problems)

Also posted a demo of how to apply this to: Youtube - SQL Server Replication: How to add an article without taking a snapshot.

IMPORTANT: This is NOT a recommended approach from Microsoft, so you will be on your own with regards to getting it working, do NOT apply directly to your production environment without significant isolated testing and getting yourself comfortable with the steps!

Steps to follow:

Planning steps:
    * Choose Publication that article will be added to
    * Gather information about the publication 
        exec sp_helppublication '[Name of Publication]'
        https://msdn.microsoft.com/en-us/library/ms189782(v=sql.105).aspx
        - replication frequency = 0 - this is Transactional replication (THIS IS A REQUIREMENT FOR THIS METHOD)
        - replicate_ddl = 1 - means ALTER TABLES will apply SQL Server generated repl procs
        - independent_agent = 1 - means that you will only affect tables in this publication when deploying
    * Identify which subscribers are going to be affected

Pre-deployment steps (can be done at any time)
    1. Create table on subscribers
    2. Create custom replication procs on subscribers
       (Customisation will ignore if the IUD has already been applied to subscriber - because you have manually sync'd the data)

Deployment/Potential impact:
    3. Stop Distribution Agents to all subscribers for this publication
    4. Add article to publication on publisher
    5. Sync data from publisher to subscriber
    6. Start Distribution Agents to all subscribers for this publication
    7. Monitor/Verify all data has arrived

Optional follow on:
    8. Apply standard repl procs (removing if not exists checks)
       This is optional as the generated repl scripts should be fine for the most part

Note:  When ALTER table scripts are applied on the Publisher (when replicate_ddl = 1) repl procs will automatically be recreated by the Distribution Agent (so any customisation will be lost)

To verify:

  • perform insert on publisher - verify row arrives on subscriber
  • perform update on publisher - verify change arrives on subscriber
  • perform delete on publisher - verify row deleted on subscriber
  • verify that last n rows have arrived and match between publisher and subscriber

EXAMPLE Process

A) Create yourself a table on your publisher:

/* Deliberately applying IDENTITY, DEFAULT & INDEX to demonstrate usage on subscriber */
CREATE TABLE [dbo].[TableNotUsingSnap](
    [Id] [int] NOT NULL IDENTITY(1,1),
    [Note_Text] [varchar](4096) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [LoggedDate] [datetime] NOT NULL CONSTRAINT DF_TableNotUsingSnap_LoggedDate DEFAUlT GETUTCDATE(),
 CONSTRAINT [PK_TableNotUsingSnap] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO 

CREATE NONCLUSTERED INDEX [IDX_NC_TableNotUsingSnap_LoggedDate]  ON [dbo].[TableNotUsingSnap]
(
    [LoggedDate] ASC
) INCLUDE ([Note_Text])
GO

B) Create yourself a job/proc/script to do some inserts/update/deletes on [TableNotUsingSnap] (you can then use this to validate how the subscriber is sync'd correctly using this method.

Pre-steps:

1. Create your table on the subscriber

/* example script to add a table to a publication without running the snapshot agent 
Steps: 
    Pre steps: 
    1. Create table on subscribers
    2. Create replication procs on subscribers

    Deployment/Potential impact:
    3. Stop Distribution Agents to all subscribers for this publication
    4. Add article to publication on publisher
    5. DTS data from publisher to subscriber
    6. Start Distribution Agents to all subscribers for this publication
    7. Monitor/Verify all data has arrived

=========================================================
Notes:
    * Drop unnecessary FK's, Indexes
    * Do NOT have IDENTITY(1,1), DEFAULTS
    * Do have a Clustered PK
    * Create appropriate indexes for your subscribers use case */ 

-- RUN ON SUBSCRIBER
IF OBJECT_ID('dbo.TableNotUsingSnap') IS NOT NULL
    exec sp_rename 'dbo.TableNotUsingSnap', 'TableNotUsingSnap_20170127'
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableNotUsingSnap](
    [Id] [int] NOT NULL,
    [Note_Text] [varchar](4096) NOT NULL,
    [CreatedDate] [datetime] NULL,
    [LoggedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_TableNotUsingSnap] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. Create your replication stored procedures (update/insert/delete) - on the Subscriber

You can create the repl procs:

  • Manually (be careful as it is very easy to make a mistake!)
  • Add the article using the MS Snapshot method on a Dev machine and script off the repl procs (ready for you to add your tweaks)
  • Create/Find some kind of generator

The change you will need to apply:

  • sp_MSins_[Schema][TableName] - Add IF NOT EXISTS (SELECT 'row already exists' FROM [Schema].[TableName] dest WITH (NOLOCK) WHERE dest.Id = @c1) to not insert if it's already there
  • sp_MSupd_[Schema][TableName] - Comment out the IF @@rowcount = 0 ... exec sp_MSreplraiserror ... to ignore an update that doesn't get applied (as the record may have been deleted on the publisher before you sync'd the data)
  • sp_MSdel_[Schema][TableName] - Comment out the IF @@rowcount = 0 ... exec sp_MSreplraiserror ... to ignore a delete that doesn't get applied (as the record may have been deleted on the publisher before you sync'd the data)

sp_MSins_dboTableNotUsingSnap:

/* Customised Replication insert proc utilized to support adding to replication without a snapshot. */
create procedure [dbo].[sp_MSins_dboTableNotUsingSnap]     
    @c1 int,     
    @c2 varchar(4096),     
    @c3 datetime
AS 
BEGIN
    IF NOT EXISTS (SELECT 'row already exists' FROM [dbo].[TableNotUsingSnap] dest WITH (NOLOCK) WHERE dest.Id = @c1)
    BEGIN
        insert into [dbo].[TableNotUsingSnap]
            ([Id],
            [Note_Text],
            [Repl_Upsert_UTC]) 
        values 
            (@c1,
            @c2,
            @c3)  
    END
END
GO

sp_MSupd_dboTableNotUsingSnap:

/* Customised Replication insert proc utilized to support adding to replication without a snapshot. */
create procedure [dbo].[sp_MSupd_dboTableNotUsingSnap]     
    @c1 int = NULL,     
    @c2 varchar(4096) = NULL,     
    @c3 datetime = NULL, 
    @pkc1 int = NULL, 
    @bitmap binary(1)
AS 
BEGIN
    declare @primarykey_text nvarchar(100) = '' 

    if (substring(@bitmap,1,1) & 1 = 1)
    begin 
        update [dbo].[TableNotUsingSnap]
        set [Id] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Id] end, 
            [Note_Text] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Note_Text] end,
            [Repl_Upsert_UTC] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Repl_Upsert_UTC] END
        WHERE [Id] = @pkc1

        /*  Commented out while adding to publication
        if @@rowcount = 0
            if @@microsoftversion>0x07320000
            Begin
                set @primarykey_text = @primarykey_text + '[id] = ' + convert(nvarchar(100),@pkc1,1)
                exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableNotUsingSnap]', @param2=@primarykey_text, @param3=13233
            End */
    END
    ELSE
    BEGIN
        update [dbo].[TableNotUsingSnap]
        set [Note_Text] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Note_Text] end,
            [Repl_Upsert_UTC] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Repl_Upsert_UTC] END
        WHERE [Id] = @pkc1

        /*  Commented out while adding to publication
        if @@rowcount = 0
            if @@microsoftversion>0x07320000
            Begin
                set @primarykey_text = @primarykey_text + '[id] = ' + convert(nvarchar(100),@pkc1,1)
                exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableNotUsingSnap]', @param2=@primarykey_text, @param3=13233
            End */
    end
END
GO

sp_MSdel_dboTableNotUsingSnap:

/* Customised Replication insert proc utilized to support adding to replication without a snapshot. */
create procedure [dbo].[sp_MSdel_dboTableNotUsingSnap]
    @pkc1 int
as
begin  
    declare @primarykey_text nvarchar(100) = ''

    delete [dbo].[TableNotUsingSnap]
    where [Id] = @pkc1

    /* ignore if the record doesn't exist when deleting it 
    if @@rowcount = 0
        if @@microsoftversion>0x07320000
        Begin
            set @primarykey_text = @primarykey_text + '[Id] = ' + convert(nvarchar(100),@pkc1,1)
            exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableNotUsingSnap]', @param2=@primarykey_text, @param3=13234
        End */
end
GO

DEPLOYMENT STEPS

3. Stop the distribution agent - On the Distributor (Push) or Subscriber (Pull)

/*  example script to add a table to a publication without running the snapshot agent
    Steps:
        Pre steps:
        1. Create table on subscribers
        2. Create replication procs on subscribers

        Deployment/Potential impact:
    **  3. Stop Distribution Agents to all subscribers for this publication
        4. Add article to publication on publisher
        5. DTS data from publisher to subscriber
        6. Start Distribution Agents to all subscribers for this publication
        7. Monitor/Verify all data has arrived

    =========================================================
    Note: check your publication settings:
          if @independent_agent = N'false'
            you will need to stop the distribution agent which will affect ALL
            publications going to that subscriber

          if @independent_agent = N'true'
            you will need to stop the publication specific distribution agent 
            (to each subscriber)

          Plan your live release around that knowledge!
*/

-- IF PUSH REPLICATION: RUN ON DISTRIBUTION SERVER
-- IF PULL REPLICATION: RUN ON SUBSCRIBER SERVER

/* disable the Job first */
exec msdb..sp_update_job @job_name = '[Distribution agent job]', @enabled = 0
GO

/* wait for 10 seconds - precaution ONLY */
WAITFOR DELAY '00:00:10.000'
GO

/* now stop the job */
exec msdb..sp_stop_job @job_name = '[Distribution agent job]'
GO

/* 
    NOTE: You might recieve an error about stopping a job that is already stopped.  You can ignore that error.
                It is up to you to verify that the job has been stopped correctly!
*/

4. Now add the article to the publication - On the Publisher

Key parameters:

  • sp_addarticle - @pre_creation_cmd = N'none' used to tell Distribution agent to not drop and generate its own objects
  • sp_addsubscription - @sync_type = N'none' used to tell Distributer that it doesn't need to create a new snapshot, it can just queue the IUD commands up

sp_addarticle:

exec sp_addarticle 
    @publication = N'Publication Name',
    @article = N'TableNotUsingSnap',
    @source_owner = N'dbo',
    @source_object = N'TableNotUsingSnap',
    @type = N'logbased',
    @description = N'',
    @creation_script = N'',
    @pre_creation_cmd = N'none',        /* this is a critical flag - tells SQL Server to not drop/recreate the repl procs/object on the subscriber */
    @schema_option = 0x0000000008004093,
    @identityrangemanagementoption = N'none',
    @destination_table = N'TableNotUsingSnap',
    @destination_owner = N'dbo',
    @status = 16,
    @vertical_partition = N'false',
    @ins_cmd = N'CALL [sp_MSins_dboTableNotUsingSnap]',
    @del_cmd = N'CALL [sp_MSdel_dboTableNotUsingSnap]',
    @upd_cmd = N'SCALL [sp_MSupd_dboTableNotUsingSnap]'
GO

-- Adding the transactional subscriptions
exec sp_addsubscription @publication = N'Publication Name',
    @subscriber = N'Subscriber Server',
    @destination_db = N'Subscriber DB',
    @subscription_type = N'Push',
    @sync_type = N'none',               /* tell SQL Server not to sync/snapshot this change to the publication */
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0
GO

5. Sync your data across

Now you need to copy your data across to your subscriber, you could:

  • Create a linked Server and copy it across
  • Use the Export/Import Wizard
  • Restore a backup and apply diffs
  • Extract out the table using SSMS Toolpack 'Generate Insert Statements...'

The exact method you use I leave up to the reader, it will also depend on how long you are willing to have your Distribution Agent stopped for.

EXTRA: As an added step in your tests, here is a good spot to run your script (from Step (B)) to create IUD actions on [TableNotUsingSnap] so that you can gain confidence in this method.

6. Restart the distribution agent - On the Distributor (Push) or Subscriber (Pull)

/*  example script to add a table to a publication without running the snapshot agent
    Steps:
        Pre steps:
        1. Create table on subscribers
        2. Create replication procs on subscribers

        Deployment/Potential impact:
        3. Stop Distribution Agents to all subscribers for this publication
        4. Add article to publication on publisher
        5. DTS data from publisher to subscriber
    **  6. Start Distribution Agents to all subscribers for this publication
        7. Monitor/Verify all data has arrived

    =========================================================
    Note: check your publication settings:
          if @independent_agent = N'false'
            you will need to stop the distribution agent which will affect ALL
            publications going to that subscriber

          if @independent_agent = N'true'
            you will need to stop the publication specific distribution agent 
            (to each subscriber)

          Plan your live release around that knowledge!
*/

-- IF PUSH REPLICATION: RUN ON DISTRIBUTION SERVER
-- IF PULL REPLICATION: RUN ON SUBSCRIBER SERVER

/* disable the Job first */
exec msdb..sp_update_job @job_name = 'Distribution agent job', @enabled = 1
GO

/* wait for 10 seconds - precaution ONLY */
WAITFOR DELAY '00:00:10.000'
GO

/* now stop the job */
exec msdb..sp_start_job @job_name = 'Distribution agent job'
GO

/* 
    Now go and make sure everything is working ok!
*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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