Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Limit snapshot size when adding new article to SQL Server replication


By:   |   Read Comments (30)   |   Related Tips: More > 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


Last Update:






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.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 

 Excellent article!!!!


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

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

Hi,

This article refers to transactional replication.


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

 

Hi,

 

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

 

Thanks.


Friday, June 03, 2016 - 9:56:50 AM - LpP Back To Top

 

 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

 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

Top solution, thanks very much.


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

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

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

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

Thanks Mohammed, it worked perfectly.


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

 

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


Monday, September 08, 2014 - 3:57:06 AM - kamil Back To Top

Works perfect, thanks for the solution. 


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

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 09, 2013 - 7:44:19 AM - shahul hameed Back To Top

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 03, 2013 - 2:09:26 AM - Binu Back To Top

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

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

 


Friday, April 05, 2013 - 7:48:50 PM - Mohammed Moinudheen Back To Top

@KD,

Was that new article selected? Is this transactional replication.

Thanks,

Mohammed Moinudheen

 


Friday, April 05, 2013 - 11:06:21 AM - KD Back To Top

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

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

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

Trying using a domain account instead of local system.


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

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

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

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

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

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

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

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

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?


Learn more about SQL Server tools