Advertisements
Pre request: Create replication using How_to_configure-Transactional_Replication_Issues_Reproduce.docx
Note: After creating publication, we need to take differential backup and restore to subscriber.
Test 1
Try with new publication using normal snapshot and initialize from backup and add table from GUI and T-SQL. Try start the snapshot agent and see. – Working data is coming after initialize from backup and snapshot agent also starts and pushing it if we reinitialize it.
-- This is one DB as publisher with four publication and subscriber model. This will have 4 snapshot agent & Dis agent, 1 log reader agent
use master
exec sp_adddistributor @distributor = N'Node1', @password = N'G0d$peed'
GO
-- Adding the agent profiles
-- Updating the agent profile defaults
exec sp_MSupdate_agenttype_default @profile_id = 1
GO
exec sp_MSupdate_agenttype_default @profile_id = 2
GO
exec sp_MSupdate_agenttype_default @profile_id = 4
GO
exec sp_MSupdate_agenttype_default @profile_id = 6
GO
exec sp_MSupdate_agenttype_default @profile_id = 11
GO
-- Adding the distribution databases
use master
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA', @data_file = N'distribution.MDF', @data_file_size = 57161, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA', @log_file = N'distribution.LDF', @log_file_size = 176, @min_distretention = 0, @max_distretention = 120, @history_retention = 120, @security_mode = 1
GO
-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = N'Node1', @distribution_db = N'distribution', @security_mode = 0, @login = N'Repl_Dist_Pub_connection', @password = N'G0d$peed', @working_directory = N'\\NODE1\Repl_Snap_files', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
/*
exec sp_addsubscriber @subscriber = N'Node2', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node3', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node1', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node1', @type = 0, @description = N''
GO
*/
/****** End: Script to be run at Publisher ******/
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'muthu', @optname = N'publish', @value = N'true'
GO
exec [muthu].sys.sp_addlogreader_agent @job_login = N'muthu\Svc_repl_logreader', @job_password = 'G0d$peed', @publisher_security_mode = 0, @publisher_login = N'Repl_Connect_Log_Agent', @publisher_password = N'G0d$peed'
GO
--exec [muthu].sys.sp_addqreader_agent @job_login = null, @job_password = 'G0d$peed', @frompublisher = 1
---==============================================
---==============================================
--1
GO
-- Adding the transactional publication
use [muthu]
exec sp_addpublication @publication = N'muthu_Replica', @description = N'Transactional publication of database ''muthu'' from Publisher ''Node1''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @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
exec sp_addpublication_snapshot @publication = N'muthu_Replica', @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 = N'muthu\Svc_repl_snapshot', @job_password = 'G0d$peed', @publisher_security_mode = 0, @publisher_login = N'Repl_Connect_Snap_Agent', @publisher_password = N'G0d$peed'
/*
exec sp_grant_publication_access @publication = N'muthu_Replica', @login = N'tncreplicator1'
GO
*/
-- Adding the transactional articles
use [muthu]
exec sp_addarticle @publication = N'muthu_Replica', @article = N'Repl_Tbl1', @source_owner = N'dbo', @source_object = N'Repl_Tbl1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030073, @identityrangemanagementoption = N'none', @destination_table = N'Repl_Tbl1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dboRepl_Tbl1]', @del_cmd = N'CALL [dbo].[sp_MSdel_dboRepl_Tbl1]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboRepl_Tbl1]'
GO
use [muthu]
exec sp_addarticle @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @source_owner = N'dbo', @source_object = N'Repl_Tbl2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'Repl_Tbl2', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [sp_MSins_dboRepl_Tbl2]', @del_cmd = N'CALL [sp_MSdel_dboRepl_Tbl2]', @upd_cmd = N'SCALL [sp_MSupd_dboRepl_Tbl2]'
-- Adding the article's partition column(s)
exec sp_articlecolumn @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @column = N'N', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @column = N'N1', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
-- Adding the article synchronization object
--exec sp_articleview @publication = N'muthu_Replica', @article = N'Home Dynamix, LLC_$Vendor Receipt', @view_name = N'syncobj_0x3934464243413435', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
-- Check and change allow_anonymous & immediate_sync 0 i.e false
use Muthu
EXEC sp_helppublication @publication='Muthu_Replica'
--T-SQL: To enable this publication option we need execute the "sp_changepublication" stored procedure at the Publisher on the publication database.
use Muthu
EXEC sp_changepublication
@publication = 'Muthu_Replica',
@property = 'allow_initialize_from_backup',
@value = 'True'
GO
--When immediate sync is on, all changes in the log are tracked immediately after the initial snapshot is generated even if there are no subscriptions.
EXEC sp_changepublication
@publication = 'Muthu_Replica',
@property = 'immediate_sync',
@value = 'True'
GO
EXEC sp_helppublication @publication='Muthu_Replica'
-- backup the db
backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak' with init
backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with differential,init
--Go to subscriber
restore filelistonly from disk = '\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak'
alter database [Muthu_Replica] set single_user with rollback immediate
restore database [Muthu_Replica] from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak'
with
move 'Muthu'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica.mdf',
move 'Muthu_log'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica_log.ldf',
replace,norecovery
restore database Muthu_Replica from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with recovery
/*
Execute the stored procedure sp_addsubscription at the Publisher on the publication database,
Specify the following parameter:
@sync_type = 'initialize with backup',
@backupdevicetype = 'Disk',
@backupdevicename = 'Backup Path'
*/
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'Muthu_Replica'; -- change
SET @subscriber = N'Node2'; -- cahnge
SET @subscriptionDB = N'Muthu_Replica'; -- change
--Add a push subscription to a transactional publication.
USE [Muthu]
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N'push',
@sync_type = 'initialize with backup',
@backupdevicetype = 'Disk',
@backupdevicename = '\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak', -- change
@update_mode = N'read only';
-- insert a row and validate
select * from [dbo].[Repl_Tbl1]
insert into [dbo].[Repl_Tbl1] values (3,'B',getdate())
-- to add new table use how to add single artile into existing publication
create table [dbo].[Repl_Tbl7] (ID int primary key)
insert into [Repl_Tbl7] values (1)
Try with new publication initialize from backup and add table from GUI and T-SQL. Try start the snapshot agent and see. – Working only data is coming after initialize from backup and snapshot agent is not started it, it says – a snapshot was not generated because no subscriptions needed initialization.
-- This is one DB as publisher with four publication and subscriber model. This will have 4 snapshot agent & Dis agent, 1 log reader agent
use master
exec sp_adddistributor @distributor = N'Node1', @password = N'G0d$peed'
GO
-- Adding the agent profiles
-- Updating the agent profile defaults
exec sp_MSupdate_agenttype_default @profile_id = 1
GO
exec sp_MSupdate_agenttype_default @profile_id = 2
GO
exec sp_MSupdate_agenttype_default @profile_id = 4
GO
exec sp_MSupdate_agenttype_default @profile_id = 6
GO
exec sp_MSupdate_agenttype_default @profile_id = 11
GO
-- Adding the distribution databases
use master
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA', @data_file = N'distribution.MDF', @data_file_size = 57161, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA', @log_file = N'distribution.LDF', @log_file_size = 176, @min_distretention = 0, @max_distretention = 120, @history_retention = 120, @security_mode = 1
GO
-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = N'Node1', @distribution_db = N'distribution', @security_mode = 0, @login = N'Repl_Dist_Pub_connection', @password = N'G0d$peed', @working_directory = N'\\NODE1\Repl_Snap_files', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
/*
exec sp_addsubscriber @subscriber = N'Node2', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node3', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node1', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'Node1', @type = 0, @description = N''
GO
*/
/****** End: Script to be run at Publisher ******/
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'muthu', @optname = N'publish', @value = N'true'
GO
exec [muthu].sys.sp_addlogreader_agent @job_login = N'muthu\Svc_repl_logreader', @job_password = 'G0d$peed', @publisher_security_mode = 0, @publisher_login = N'Repl_Connect_Log_Agent', @publisher_password = N'G0d$peed'
GO
--exec [muthu].sys.sp_addqreader_agent @job_login = null, @job_password = 'G0d$peed', @frompublisher = 1
---==============================================
---==============================================
--1
GO
-- Adding the transactional publication
/*
Configure and change in exec sp_addpublication
@allow_anonymous = N'false'
@immediate_sync = N'false'
@allow_initialize_from_backup = N'true'
*/
use [muthu]
exec sp_addpublication @publication = N'muthu_Replica', @description = N'Transactional publication of database ''muthu'' from Publisher ''Node1''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @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'true', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
/*
Configure and change in exec sp_addpublication_snapshot
@frequency_interval = 1
@frequency_relative_interval = 1
@frequency_subday = 8
@frequency_subday_interval = 1
*/
exec sp_addpublication_snapshot @publication = N'muthu_Replica', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'muthu\Svc_repl_snapshot', @job_password = 'G0d$peed', @publisher_security_mode = 0, @publisher_login = N'Repl_Connect_Snap_Agent', @publisher_password = N'G0d$peed'
/*
exec sp_grant_publication_access @publication = N'muthu_Replica', @login = N'tncreplicator1'
GO
*/
-- Adding the transactional articles
use [muthu]
exec sp_addarticle @publication = N'muthu_Replica', @article = N'Repl_Tbl1', @source_owner = N'dbo', @source_object = N'Repl_Tbl1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030073, @identityrangemanagementoption = N'none', @destination_table = N'Repl_Tbl1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dboRepl_Tbl1]', @del_cmd = N'CALL [dbo].[sp_MSdel_dboRepl_Tbl1]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboRepl_Tbl1]'
GO
use [muthu]
exec sp_addarticle @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @source_owner = N'dbo', @source_object = N'Repl_Tbl2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'Repl_Tbl2', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [sp_MSins_dboRepl_Tbl2]', @del_cmd = N'CALL [sp_MSdel_dboRepl_Tbl2]', @upd_cmd = N'SCALL [sp_MSupd_dboRepl_Tbl2]'
/*
Configure and change in exec sp_addarticle
@creation_script = null
@identityrangemanagementoption = N'manual'
*/
-- Adding the article's partition column(s)
exec sp_articlecolumn @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @column = N'N', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'muthu_Replica', @article = N'Repl_Tbl2', @column = N'N1', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
-- Adding the article synchronization object
--exec sp_articleview @publication = N'muthu_Replica', @article = N'Home Dynamix, LLC_$Vendor Receipt', @view_name = N'syncobj_0x3934464243413435', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
-- Check and change allow_anonymous & immediate_sync 0 i.e false
use Muthu
EXEC sp_helppublication @publication='Muthu_Replica'
--T-SQL: To enable this publication option we need execute the "sp_changepublication" stored procedure at the Publisher on the publication database.
use Muthu
EXEC sp_changepublication
@publication = 'Muthu_Replica',
@property = 'allow_initialize_from_backup',
@value = 'True'
GO
--When immediate sync is on, all changes in the log are tracked immediately after the initial snapshot is generated even if there are no subscriptions.
EXEC sp_changepublication
@publication = 'Muthu_Replica',
@property = 'immediate_sync',
@value = 'True'
GO
EXEC sp_helppublication @publication='Muthu_Replica'
-- backup the db
backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak' with init
backup database [Muthu] to disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with differential,init
--Go to subscriber
restore filelistonly from disk = '\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak'
alter database [Muthu_Replica] set single_user with rollback immediate
restore database [Muthu_Replica] from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_full.bak'
with
move 'Muthu'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica.mdf',
move 'Muthu_log'to'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Muthu_Replica_log.ldf',
replace,norecovery
restore database Muthu_Replica from disk ='\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak' with recovery
/*
Execute the stored procedure sp_addsubscription at the Publisher on the publication database,
Specify the following parameter:
@sync_type = 'initialize with backup',
@backupdevicetype = 'Disk',
@backupdevicename = 'Backup Path'
*/
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'Muthu_Replica'; -- change
SET @subscriber = N'Node2'; -- cahnge
SET @subscriptionDB = N'Muthu_Replica'; -- change
--Add a push subscription to a transactional publication.
USE [Muthu]
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N'push',
@sync_type = 'initialize with backup',
@backupdevicetype = 'Disk',
@backupdevicename = '\\NODE1\Repl_Snap_files\Muthu_Replica_diff.bak', -- change
@update_mode = N'read only';
-- insert a row and validate
select * from [dbo].[Repl_Tbl1]
insert into [dbo].[Repl_Tbl1] values (3,'B',getdate())
-- to add new table use how to add single artile into existing publication
create table [dbo].[Repl_Tbl7] (ID int primary key)
insert into [Repl_Tbl7] values (1)
Try add new table in GUI and start the snapshot agent and check – Working
Try adding new table by using sp_addarticle (Do not run sp_refreshsubscriptions) T-SQL for single article and start the snapshot agent and check – Working
For this both options. manually we need to run sp_addsubscription SP
All existing table DML are replicating correctly.
Newly added article are replicating correctly.
Only thing is we need manually run sp_addsubscription SP, otherwise it will say
Snapshot was not generated because no subscriptions needed initialization
