Sql-server – How to delay replication start

replicationsql-server-2008-r2transactional-replication

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The issue is that, either using SSMS wizard or tsql, when I start the publication (no subscription created yet), immediately I get lot of deadlocks on the tables been replicated. I'm checking all this information and more but I can't manage to do what I need. Haven't found a clear answer online. What I'm missing here?

And this is the code I'm trying to run:

EXEC sp_replicationdboption @dbname = N'db_name',
     @optname = N'publish',
     @value = N'true'
GO

EXEC sys.sp_addlogreader_agent @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1
GO

EXEC sys.sp_addqreader_agent @job_login = NULL,
     @job_password = NULL,
     @frompublisher = 1
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'db_name',
     @description = 
     N'Transactional publication of database ''db_name'' from Publisher ''distributor''.',
     @sync_method = N'native',
     @retention = 0,
     @allow_push = N'true',
     @allow_pull = N'false',
     @allow_anonymous = N'true',
     @enabled_for_internet = N'false',
     @snapshot_in_defaultfolder = N'true',
     @compress_snapshot = N'false',
     @ftp_port = 21,
     @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',
     @allow_queued_tran = N'false',
     @allow_dts = N'false',
     @replicate_ddl = 1,
     @allow_initialize_from_backup = N'true',
     @enabled_for_p2p = N'false',
     @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'db_name',
     @frequency_type = 1,
     @frequency_interval = 0,
     @frequency_relative_interval = 0,
     @frequency_recurrence_factor = 0,
     @frequency_subday = 0,
     @frequency_subday_interval = 0,
     @active_start_time_of_day = 0,
     @active_end_time_of_day = 235959,
     @active_start_date = 0,
     @active_end_date = 0,
     @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1

EXEC sys.sp_addsubscription
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @destination_db = N'db_name',
    @subscription_type = N'Push',
    @sync_type = N'initialize WITH backup',
    @backupdevicetype = N'Disk',
    @backupdevicename = N'F:\Backup\db_name\',
    @update_mode = N'read only',
    @subscriber_type = 0;

-- Add subscription agent
EXEC sys.sp_addpushsubscription_agent
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @subscriber_db = N'db_name',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1,
    @frequency_type = 64,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 4,
    @frequency_subday_interval = 5,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @dts_package_location = N'Distributor'
GO

Best Answer

Got it. See my code, there is an option when creating the publication

EXEC sp_addpublication @publication = N'db_name',
       ...
    @immediate_sync = N'true'
    ...
GO

That's the equivalent of checking on the publication creation wizard the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

enter image description here

That's why when running it, tried to automatically create the snapshot. And as last snapshot is from couple of months, there is lot work to do in the middle. Ran with false value and now everything is ok, runnig smoothly and without issues.