Limit snapshot size when adding new article to SQL Server replication

By:   |   Comments (34)   |   Related: > Replication


Problem

I have transactional replication configured in my production environment. The business has requested that I add a new article to the publication. I would like to initialize only the new article added to the publication, so that I could avoid taking a total snapshot of all existing articles in the publication. In this tip we cover the steps need to achieve this.

Solution

This is a common request to add a new article to a publication.  To save time and disk space we will walk through how to only create the snapshot for the new article(s).

The below assumes transactional replication is already configured between two databases.


Snapshot for All Articles is Created (not what we want)

To add a new article to an existing publication, navigate to this path in SQL Server Management Studio (SSMS)

SSMS -> Replication -> Local Publications -> right click on your publication -> Properties -> Click on 'Articles' tab -> Uncheck 'Show only checked articles in the list'.

This will allow you to view the articles that are not published. In our example, for the sake of convenience, we only have one article (Article_1) that is already published and two non-published articles. Refer to the screenshot below. Once you have navigated to this section, chose Article_2 and click OK.

replication publication properties

After the article has been added, navigate to this path in SSMS.

SSMS-> Replication-> Local Publications-> right click on your publication -> Properties -> Click on 'View Snapshot Agent Status' -> Click on 'Start'

Once you click on 'Start', a snapshot of 2 articles would be generated. See screenshot below.

snapshot agent status

This job has basically created a snapshot of all the articles in the publication. In our case, snapshot of Article_1 (already published) and Article_2 (newly published), instead of only taking a snapshot of the newly added article. If you check the default snapshot agent folder (from this path: SSMS-> Replication-> Local Publications->  right click on your publication-> Properties-> Snapshot tab-> Location of Snapshot files), you will notice the snapshot files of all the articles (Article_1 and Article_2) in the publication.

So this is not what we want. We only want a snapshot of the new article.


Snapshot for New Articles is Created (what we want)

To only create a snapshot for the new articles, we need to disable the publication options; 'immediate_sync' and 'allow_anonymous'. You can get more details by running sp_helppublication on the publisher database. See screenshot below.

sp_helppublication output

During the initial replication setup through SSMS, both these options 'immediate_sync' and 'allow_anonymous' were enabled. We can confirm this if we use the 'Generate a script file with steps to create the publication' option when configuring the initial replication setup. See screenshot below.

sql server replication wizard

On checking the auto generated script, both options - 'immediate_sync' and 'allow_anonymous' are set to TRUE. Below is the default script generated through SSMS in this example for the sp_addpublication stored procedure.

-- Adding the transactional publication
use [REP_P]
exec sp_addpublication @publication = N'REP_P',
                      @description = N'Transactional pub of db ''REP_P'' from Publisher ''Server''.',
                      @sync_method = N'concurrent',
                      @retention = 0,
                      @allow_push = N'true',
                      @allow_pull = N'true',
                      @allow_anonymous = N'true',
                      @enabled_for_internet = N'false',
                      @snapshot_in_defaultfolder = N'true',
                      @compress_snapshot = N'false',
                      @ftp_port = 21,
                      @ftp_login = N'anonymous',
                      @allow_subscription_copy = N'false',
                      @add_to_active_directory = N'false',
                      @repl_freq = N'continuous',
                      @status = N'active',
                      @independent_agent = N'true',
                      @immediate_sync = N'true',
                      @allow_sync_tran = N'false',
                      @autogen_sync_procs = N'false',
                      @allow_queued_tran = N'false',
                      @allow_dts = N'false',
                      @replicate_ddl = 1,
                      @allow_initialize_from_backup = N'false',
                      @enabled_for_p2p = N'false',
                      @enabled_for_het_sub = N'false'
GO

To achieve what we want, both of these options need to be disabled. Run the below commands on your publisher database for your publication to make the changes.

--Run on your publisher database
EXEC sp_changepublication
@publication = 'your publication name',
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication 
@publication = 'your publication name',
@property = 'immediate_sync' ,
@value = 'false'
GO
 

Now, let's add Article_3 to the publication following the same steps we did above. Once added, run sp_helpsubscription on your publisher database to verify the 'subscription status' of the subscribed article. As per MSDN library, below are the details regarding flags for the 'subscription status' column.

Subscription status

Subscription status:

0 = Inactive

1 = Subscribed

2 = Active

In our case, the status is 1 which means 'subscribed'. See screenshot below.

sql server sp_helpsubscription output

Now, Article_3 is subscribed and to make it Active, run the snapshot agent as described above. Now, you will notice that the snapshot of only the one article (Article_3) is generated and not a snapshot for all articles in your publication. See screenshot below. You can further verify this by checking the default snapshot folder location as described above.

sql server snapshot agent status

On running, sp_helpsubscription again on the publisher database, the 'subscription status' will have changed to Active, thereby ensuring the added article is part of replication.

Next Steps
  • Consider testing this scenario through a simple transactional replication setup as described above for your understanding
  • Refer to other related tips on replication to get familiar with the concepts


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, May 16, 2024 - 4:15:45 AM - Lirus Back To Top (92242)
when we add new table to articles, then we dont want to reinitialize all existing table, we can just run snapshot,
then only new table will generate to subscriber. using start snapshot agen.
how about that?

Monday, February 5, 2018 - 9:18:29 PM - shunmuga Back To Top (75110)

 Hello Mohammed, 

 

Nice article and works for transactional replication. Provide some tips for merge replication snapshot limit for adding new article.

 

Thanks ,

Shun.

 


Monday, January 8, 2018 - 10:52:08 PM - govind Back To Top (74921)

Hello Mohammed,

 

Even after setting 'allow_anonymous' and 'immediate_sync' values to false and then adding article, snapshot is running for whole existing articles as well.

This is happening for my one client. I noticed while addining article it prompt for Reinitialize subscriber and on canceling it, It is not giving me to proceed. So I proceeded with mark for reinitialization and while running snapshot I noticed it was run for all existing articles.

Help me in this to understand behaviour.

 

Thanks,

Govind

 


Friday, December 8, 2017 - 11:37:50 AM - Krishna Kumar Rai Back To Top (73777)

Nice Article, 

 


Tuesday, May 23, 2017 - 8:34:31 AM - Madhu Mohan Back To Top (55976)

 

 Excellent article!!!!


Wednesday, June 15, 2016 - 2:08:43 AM - Mohammed Back To Top (41695)

Lpp,

I guess so. I haven't tried this on latest versions yet.

Thanks


Wednesday, June 15, 2016 - 2:06:08 AM - Mohammed Back To Top (41694)

Hi,

This article refers to transactional replication.


Tuesday, June 14, 2016 - 12:10:26 AM - Deepesh Back To Top (41681)

 

Hi,

 

I have merge replication set up. I do not see option for immediate_sync. Is there any other alternative?

 

Thanks.


Friday, June 3, 2016 - 9:56:50 AM - LpP Back To Top (41606)

 

 Mohammed ,

Is this the same case with latest versions too, like sql 2012 and 2014?

i mean turn off these properties if you want to generate the snapshot for newly added articles only.

 


Saturday, April 16, 2016 - 12:03:29 AM - mkm Back To Top (41240)

 Excellent Article. I was able to add new article to existing transactional replication without any issues. Thank you!!!

 


Thursday, March 31, 2016 - 8:57:23 AM - Phil Dale Back To Top (41104)

Top solution, thanks very much.


Monday, March 28, 2016 - 12:46:04 AM - sqld-_-ba Back To Top (41068)

Simple and it works!

There are so many posts on this exact scenario, but none as good as this.


Tuesday, February 23, 2016 - 3:30:08 AM - DevshishS Back To Top (40753)

This is the most simplest way to add new article in existing replication.

I followed same steps and it worked perfectly.

 

Kudos!!!

 


Thursday, October 29, 2015 - 12:41:35 PM - JP Back To Top (38992)

I have performed the steps as suggested a few times and has worked. However, when I attempted the same steps on a different server it performed a full snapshot rather than on the delta table. The only difference I found was that when trying to accept the delta changes from the Replication publication GUI it prompted the “reinitialise subscription” window with options to “mark for reinitialisation” or ‘cancel’. It seems you have to select “mark for reinitialisation” otherwise the changes will not be accepted. I believe this overrides the allow_anonymous/immediate_sync and full snapshot is performed. Any thoughts on this?

 


Tuesday, October 20, 2015 - 11:15:45 AM - Luis Back To Top (38943)

Thanks Mohammed, it worked perfectly.


Saturday, January 17, 2015 - 7:46:39 AM - Ashish Back To Top (35975)

 

Perfect solution.. Thanks for the article. It saved a lot time


Monday, September 8, 2014 - 3:57:06 AM - kamil Back To Top (34434)

Works perfect, thanks for the solution. 


Friday, April 11, 2014 - 3:21:08 PM - Marcos Back To Top (30051)

I am testing transactional replication and everything is working fine.  My problem is that when I attempt to add a new article, adjusting the parameters such that only my new article should replication over, the end result is the new article is still not replicated.  Microsoft suggested the same parameter adjustments as well.  Still, no success.

Any suggestions would be greatly appreciated.  Thank you.


Wednesday, October 9, 2013 - 7:44:19 AM - shahul hameed Back To Top (27088)

Sir Please rectify my problem Regarding Transactional Replication Schema Changes

  
I had done Transactional Replication from 32 remote servers to 1 main server successfully and the data is replicating now.what the problem I am going to face is if I want to add a new column  in a  specific table in all  remote servers,all the 32 remote servers will try to  replicate that new column name in a same table in main server,so i will face error like column name specified more than once,it should be unique(error I got when I tested as a sample).
 
            If I make Replicate Schema changes to False in publisher properties in publisher,It will not replicating  schema changes and also the value of the newly added column.But how can I replicate  the value of the newly added columns from remote servers to main server(In main server I will manually add the new columns for a specific table after that only I will add new column in remote servers).

               Please give your suggestion to rectify this problem sir.Thanks for your useful article regarding replication,It had helped me a lot to study about replication,as i was new to replication.Please give me favourable reply .

Monday, June 3, 2013 - 2:09:26 AM - Binu Back To Top (25247)

After adding new article on existing publication what steps need to be done on Subscribers/subscription


Saturday, May 18, 2013 - 1:58:39 AM - phani Back To Top (24024)

could  u plz tell me the use of immidiate_sync & Allow_anonymous options

 


Friday, April 5, 2013 - 7:48:50 PM - Mohammed Moinudheen Back To Top (23201)

@KD,

Was that new article selected? Is this transactional replication.

Thanks,

Mohammed Moinudheen

 


Friday, April 5, 2013 - 11:06:21 AM - KD Back To Top (23189)

I followed your process to add the article in existing publication,  but when I ran the snapshot agent it didn't added the article and neither table got added in subscription.

when ran the snapshot agent I got following message:

‘[0%] A snapshot was not generated because no subscriptions needed initialization’

 

 what I am misisng here, do I need to run sp_AddSubscription


Sunday, December 23, 2012 - 12:25:26 PM - SUMIT RANJAN Back To Top (21103)

Is any database size issue in transactional Replication.I have a database of size 300gb and I want to replicate same,but it generates error when adding new  publisher.

Error:(Unable to add new publication)

Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320
Cannot insert the value NULL into column 'pubid', table 'Database1.dbo.IHpublications'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348
Could not create the publication.

Where as other database which size is 40gb replicated easily.I am adding publication using wizard.

Please suggest me. I am waiting for your responce.


Friday, June 15, 2012 - 6:04:47 AM - Deepak Back To Top (18029)

Even after adding the login i am getting the error. Then I found out windows security mode is selected and I changed it to mix mode and it worked.


Thursday, June 14, 2012 - 9:31:07 AM - Mohammed Moinudheen Back To Top (17997)

Trying using a domain account instead of local system.


Thursday, June 14, 2012 - 5:32:31 AM - Deepak Back To Top (17988)

Thanks Mohammed for the clerification on the above issue.

Need more assistance from you.

This the error message I am getting while setting up tran. replication from sql server agent job which has to copy the data to subscriber. The SQL server 2005 and sql agent is installed under 'Local System'.

2012-06-14 09:24:17.234 Connecting to Subscriber 'subscriber1'
2012-06-14 09:24:17.250 Agent message code 20084. The process could not connect to Subscriber 'subscriber1'.
2012-06-14 09:24:17.281 Category:NULL
Source:  Microsoft SQL Native Client
Number:  18456
Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.


Wednesday, June 13, 2012 - 10:28:09 AM - Mohammed Moinudheen Back To Top (17970)

Deepak,

No, not required. For details of impact, please refer this tip: http://www.mssqltips.com/sqlservertip/2668/role-of-the-immediate-sync-option-for-sql-server-replication/

 


Wednesday, June 13, 2012 - 8:56:24 AM - Deepak Back To Top (17967)

Do we need to change these two parameterrs (1. Immediate_sync, 2. Allow_anonymous) to true after completion of the activity. What wiil be their impact in the future.


Saturday, March 31, 2012 - 7:45:25 PM - Ed Back To Top (16724)

Thank you for writting this article it answered the 'how to add an article' question well.  In my testing I had to run the sp_addsubscription after the creation of the article, but other than that process described delivers.


Monday, January 16, 2012 - 11:41:40 PM - Mohammed Moinudheen Back To Top (15672)

Can you try this on a test transactional replication configuration. I guess you might have tried this on a snapshot or merge replication configuration.


Monday, January 16, 2012 - 11:51:29 AM - kartik Back To Top (15663)

I have done everything which this you have said but when I run the snapshot agent the snapshot of

all the articles is generated what may be the reason.


Monday, September 26, 2011 - 2:01:03 AM - Mohammed Moinudheen Back To Top (14727)

Don,

Thanks for your comments. Regarding your questions, please find response below

1) In the first case, it is not necessary to revert to old settings, i.e, after changing publication properties-immediate_sync and Allow_anonymous

2) In the second case, it wouldn't be a straight forward method especialy if you need to add a column to an already existing article in publication and you want to take snapshot of only that article. It is because, the subscription would already be present and you would get error messages like 'Cannot create the subscription because the subscription already exists in the subscription database' or something like '[0%] A snapshot was not generated because no subscriptions needed initialization.'. You could also verify this by running, sp_helpsubcription on your publisher db, you would see a status of '2' in the subscription_status which means Active. I though I would cover this in another tip. :)


Friday, September 23, 2011 - 12:25:39 PM - Don Schaeffer Back To Top (14724)

Thanks - very useful article for those of us with publications whose snapshots take more than an hour.

I have two questions:

I'm assuming that afterwards the publication properties should be returned to their prior state - immediate sync and allow anonymous?  Or isn't that necessary?

Also, it would be most useful to only create a snapshot of an individual article whose underlying table has changed, say by the addition of a column.  Is this possible?















get free sql tips
agree to terms