@sync_type in SQL Server Replication

@sync_type in SQL Server Replication

You might come across the below error message or see synchronization status as uninitialized, if you do not need to transfer Schema and initial data for published tables, to the Subscriber first (an enviroment which probably does not need to be initialized first) and @sync_type is set to
automatic in your sp_addsubscription command.

Msg 21064, Level 16, State 1, Procedure sp_MSreplraiserror, Line 18
The subscription is uninitialized or unavailable for immediate updating as it is marked for reinitialization. If using queued failover option, run Queue Reader Agent for subscription initialization. Try again after the (re)initialization completes.
Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Line 13
The transaction ended in the trigger. The batch has been aborted.

The solution is setting the @sync_type as below, which would assume that the Subscriber already has the schema and initial data for published tables where appropriate.

@sync_type = N'replication support only'

exec sp_addsubscription @publication = N'testPub1', @subscriber = N'SQL1508C', @destination_db = N'Test2', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

Be cautious during database migrations (cut overs), check the @sync_type in your replication set up scripts.

Cheers