How to Add Tables to an Existing Publication and Syncing Without Reinitializing the Subscription in SQL Server Replication

Problem:

When I add a new table to the existing publication, I had to reinitialize the subscription. This creates a snapshot for all the articles present in the subscription instead of only the newly added tables. This is a problem if the size of the existing tables is huge.

I want to create a snapshot only for the new tables and not all the existing tables present in the subscription.

Solution:

The below publication property must be false to be able to achieve our requirement.
a. immediate_sync = false
b. allow_anonymous = false

You can check these properties either in the publisher database or in the distribution database. Execute the below query to check the same.

-- execute in published database
use publisher_db_name;
select immediate_sync,allow_anonymous,* from syspublications

-- execute in distribution database
USE distribution;
select immediate_sync,allow_anonymous,* from dbo.MSpublications;

If either of the property has a value as true (1), then change the respective publication property to false. Execute the below script in the publisher database.

USE publisher_db_name;
IF EXISTS(SELECT * FROM syspublications WHERE PUBLISHER_DB = N'publisher_db_name' AND PUBLICATION=N'Your_Publication_Name' AND allow_anonymous=1)
BEGIN
	EXEC sp_changepublication
		@publication = 'Your_Publication_Name',
		@property = N'allow_anonymous',
		@value = 'False';
END
GO

USE publisher_db_name;
IF EXISTS(SELECT * FROM syspublications WHERE PUBLISHER_DB = N'publisher_db_name' AND PUBLICATION=N'Your_Publication_Name' AND immediate_sync=1)
BEGIN
	EXEC sp_changepublication
		@publication = 'Your_Publication_Name',
		@property = N'immediate_sync',
		@value = 'False';
END
GO

You need to add the tables to the publication. You can do it either through the UI or by executing the below script in the publisher database.

USE publisher_db_name;
GO

DECLARE @PublicationName SYSNAME = N'Your_Publication_Name';
DECLARE @TableName SYSNAME = N'TestTable01'; --new table that you want to add to the publication.
DECLARE @AddArticleSql NVARCHAR(MAX);

SET @AddArticleSql = (SELECT 'EXEC sp_addarticle @publication = N'''+@PublicationName+''', @article = N'''+NAME+''', @source_owner = N''dbo'', @source_object = N'''+NAME+''', @type = N''logbased'', @description = N'''+NAME+''', @creation_script = N'''', @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'''+case when OBJECTPROPERTY(OBJECT_ID(NAME), 'TableHasIdentity')=1 then 'manual' else 'none' end +''', @destination_table = N'''+NAME+''', @destination_owner = N''dbo'', @status = 24, @vertical_partition = N''false'', @ins_cmd = N''CALL [sp_MSins_dbo'+NAME+']'', @del_cmd = N''CALL [sp_MSdel_dbo'+NAME+']'', @upd_cmd = N''SCALL [sp_MSupd_dbo'+NAME+']''' 
			FROM sys.tables
			WHERE NAME = @TableName)

EXECUTE sp_executesql @AddArticleSql

You can verify that the table is added to the publication using the script below.

--execute in distributor server
SELECT * 
FROM distribution.dbo.MSarticles AS A
INNER JOIN distribution.dbo.MSpublications AS P ON P.publication_id = A.publication_id AND A.publisher_id = P.publisher_id AND A.publisher_db = P.publisher_db
WHERE A.publisher_db = N'publisher_db_name'
AND P.publication = N'Your_Publication_Name'
AND A.article IN ('TestTable01')

After adding a new table to the publication, add a Subscription by executing the below script.

USE publisher_db_name;
GO

DECLARE @publication_name SYSNAME = N'Your_Publication_Name';
DECLARE @subscriber_server_instance SYSNAME = N'SUBSCRIBER_SERVER_HERE';
DECLARE @subscriber_db_name SYSNAME = N'subscriber_db_name';

EXEC sp_addsubscription 
		@publication = @publication_name, 
		@subscriber = @subscriber_server_instance, 
		@destination_db = @subscriber_db_name, 
		@sync_type = N'Automatic', 
		@subscription_type = N'pull', --pull or push
		@update_mode = N'read only'

Execute the below script in the publisher database to verify that subscription to the new table is present. The subscription_status property will be there as 1 as we just added the subscription but it is not yet actively synchronizing.

USE publisher_db_name;
GO

EXEC SP_HELPSUBSCRIPTION @article='TestTable01'

Start the snapshot agent to generate the snapshot of the newly added table.

USE publisher_db_name;
GO

EXEC sp_startpublication_snapshot @publication = N'Your_Publication_Name'

You can check the snapshot folder to verify the snapshot is generated only for the new table added to the publication.

If the distribution agent job is not running, start the job. If it is already running, you can check the distribution agent’s job history. If the snapshot agent is completed, the distribution agent will automatically synchronize the new table.

-- execute in distributor server
SELECT * 
FROM distribution.dbo.MSDistribution_history AS H
INNER JOIN distribution.dbo.MSDistribution_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-1
AND A.publisher_db = 'publisher_db_name'
AND A.subscriber_db = 'subscriber_db_name'
ORDER BY H.time desc

You can verify the newly added table and data are present in the subscriber server. Compare the row counts of the newly added table between the publisher and subscriber server to confirm.

Thanks for reading this far and I hope this article is helpful for you.

You may also like other articles on SQL replication below.

https://blog.rajanand.org/sql-server-replication-queries

https://blog.rajanand.org/how-to-find-which-command-has-failed-in-sql-server-replication

​Problem:

​Solution:

Problem:

Solution: