Limit snapshot size when adding new article to SQL Server replication

By: Mohammed Moinudheen   |   Comments (33)   |   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.

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.

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.

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.

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.

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

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.

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

About the author

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