Transactional Replication
Design:
Node1 – distributor & Publisher. One publisher with many subscribers.
Pre request:
- Repl_Dist_Pub_connection — sp_adddistpublisher@login mainly used to connect distributor and publisher initial configuration. Remember or store the password for adding another publisher for this remote distributor will need this password.
- Create new threeAD service account or SQL login for replication job agents run i.e. @job_login. Some prefer to run all 3 in single account as well.
Muthu\Svc_repl_snapshot , Muthu\Svc_repl_logreader & Muthu\Svc_repl_distributor
- We need 4 SQL or windows login for connect publisher and subscriber to do data movement.
Ex: 4 login are SQL login names:
Repl_Connect_Snap_Agent — sp_addpublication_snapshot @publisher_login
Repl_Connect_Log_Agent — sp_addlogreader_agent @publisher_login
Repl_Connect_Dist_Agent — sp_addpushsubscription_agent @subscriber_login
Total we need 7 accounts and it is always better to have dedicated 7 account. Still you can do all in single login as well. In case of troubleshooting it hard to find in single login.
- Create share path with R/W permission to Svc_repl_snapshot for snapshot files BCP i.e DDL & DML of replicated objects
Better create share path on distributor server and add permission at security of folder and advanced security of permission. Svc_repl_snapshot (Full control) , Svc_repl_logreader (Only read) & Svc_repl_distributor (Only read)
Better create all logins into all SQL servers and grant all necessary permission, before configure replication.
All logins should have db owners to replicated DB, for distributor DB give DB owner as well to the logins.
- Create DB on subscriber and grant permission to login accounts.
For any issue in access we can grant sysadmin to all logins for testing.
We can configure replication by GUI and TSQL .Overview what it is doing from GUI or T-SQL.
- Configure Distribution DB sp_adddistributor, sp_adddistributiondb, sp_adddistpublisher
- Add Log reader agentsp_addlogreader_agent
- Add publicationsp_addpublication
- Add snapshot agentsp_addpublication_snapshot
- Add articlesp_addarticle
- Add columns sp_articlecolumn– Optinoal
- Add sp_articleview — Optinoal
- Add subscription — sp_addsubscription
- Add Distribution agent — sp_addpushsubscription_agent
Configuration
For existing replication you have to script out and use the same script. To add article you can use dynamic SQL.
Notes:
In add table sp – make sure to check and change the identity setting to manual or none and status to 16 which is default.
What is status 16 and 24?
16 (default) Uses parameterized statements.
24 Includes the column name in INSERT statements and uses parameterized statements.
Distributer and Publisher Creation
use master exec sp_adddistributor @distributor = N'node1', @password = N'G0d$peed@123' 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 = 6 GO exec sp_MSupdate_agenttype_default @profile_id = 11 GO exec sp_MSupdate_agenttype_default @profile_id = 14 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 = 803, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data', @log_file = N'distribution.LDF', @log_file_size = 424, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @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@123', @working_directory = N'\\NODE1\Repl_Snap_files', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER' GO -- 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@123' GO
Publication example:
-- Adding the transactional publication use [Muthu] exec sp_addpublication @publication = N'Muthu-BI', @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'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 exec sp_addpublication_snapshot @publication = N'Muthu-BI', @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@123' -- Adding the transactional articles use [Muthu] exec sp_addarticle @publication = N'Muthu-BI', @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 = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Repl_Tbl1', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboRepl_Tbl1]', @del_cmd = N'CALL [sp_MSdel_dboRepl_Tbl1]', @upd_cmd = N'SCALL [sp_MSupd_dboRepl_Tbl1]' GO use [Muthu] exec sp_addarticle @publication = N'Muthu-BI', @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'false', @ins_cmd = N'CALL [sp_MSins_dboRepl_Tbl2]', @del_cmd = N'CALL [sp_MSdel_dboRepl_Tbl2]', @upd_cmd = N'SCALL [sp_MSupd_dboRepl_Tbl2]' GO use [Muthu] exec sp_addarticle @publication = N'Muthu-BI', @article = N'Repl_Tbl3', @source_owner = N'dbo', @source_object = N'Repl_Tbl3', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'Repl_Tbl3', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboRepl_Tbl3]', @del_cmd = N'CALL [sp_MSdel_dboRepl_Tbl3]', @upd_cmd = N'SCALL [sp_MSupd_dboRepl_Tbl3]' GO -- Adding the transactional subscriptions use [Muthu] exec sp_addsubscription @publication = N'Muthu-BI', @subscriber = N'Node2', @destination_db = N'Muthu-BI', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 exec sp_addpushsubscription_agent @publication = N'Muthu-BI', @subscriber = N'Node2', @subscriber_db = N'Muthu-BI', @job_login = N'muthu\Svc_repl_distributer', @job_password = 'G0d$peed', @subscriber_security_mode = 0, @subscriber_login = N'Repl_Connect_Dist_Agent', @subscriber_password = 'G0d$peed@123', @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
Auto generate add article
-- select * from sys.objects where type ='u' -- change the @publication & identityrangemanagementoption = manual & @status = 24 -- Check your existing script use master --/* select name,'use [hdxdb];exec sp_addarticle @publication = N''HDXDB_Replica'', @article = N'''+name+''', @source_owner = N'+''''+OBJECT_SCHEMA_NAME(object_id)+''', @source_object = N'''+name+''', @type = N''logbased'', @description = N'''', @creation_script = N'''', @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N''manual'', @destination_table = N'''+name+''', @destination_owner = N'+''''+OBJECT_SCHEMA_NAME(object_id)+''',@status = 24, @vertical_partition = N''false'', @ins_cmd = N''CALL [sp_MSins_'+OBJECT_SCHEMA_NAME(object_id)+name+']'',@del_cmd = N''CALL [sp_MSdel_'+OBJECT_SCHEMA_NAME(object_id)+name+']'', @upd_cmd = N''SCALL [sp_MSupd_'+OBJECT_SCHEMA_NAME(object_id)+name+']'' 'from sys.objects where type ='u' and is_ms_shipped <>1 --*/ --use HDXDB_Replica select name,'Drop table ['+name+']'from sys.objects where type ='u' and is_ms_shipped <>1 and name in ( 'A_tbl_DBA_Repl', 'a_tbl_DBA_Identity', 'Canary_PubName' ) order by 1 /* exec sp_addarticle @publication = N'PublicationTest', @article = N'ReplTable', @source_owner = N'dbo', @source_object = N'ReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'ReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboReplTable', @del_cmd = N'CALL sp_MSdel_dboReplTable', @upd_cmd = N'SCALL sp_MSupd_dboReplTable' GO */ -- Get columns sp_articlecolumn -- change the @publication use muthu --/* select o.name,c.name,'use [hdxdb];exec sp_articlecolumn @publication = N''HDXDB_Replica'', @article = N'''+o.name+''', @column = N'''+C.name+''',@operation = N''add'',@force_invalidate_snapshot = 1, @force_reinit_subscription = 1' from sys.objects O join sys.columns C on O.object_id=C.object_id where type ='u' and is_ms_shipped <>1 and is_published =1 and C.is_replicated=1 order by 1
Errors
— This is generated by doing TSQL add article. This can be fixed. But I used GUI as quick fix / workaround.
Better add tables from GUI so that your table identity column an @status will auto generated easily @identityrangemanagementoption = N’none’
and if you have any special characters in table name that will not error out.
1
Msg 21830, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2111 [Batch Start Line 0]
You cannot specify schema_option 0x4 (script identity as identity rather than the base data type) for article ‘ssi_BlockLog’.
The value specified for the parameter @identityrangemanagementoption is NONE. To replicate identity as identity, the value must be MANUAL or AUTO for publications that do not support queued updating subscriptions.
Msg 15006, Level 16, State 1, Procedure sp_MSreplcheck_name, Line 44 [Batch Start Line 0]
‘Magnolia Brook LLC$Sales Prepayment %’ is not a valid name because it contains invalid characters.
Msg 15006, Level 16, State 1, Procedure sp_MSreplcheck_name, Line 44 [Batch Start Line 0]
‘Magnolia Brook LLC$Purchase Prepayment %’ is not a valid name because it contains invalid characters.
Query was canceled by user.
2
— This is becasue we restored full and diff before creating publication
The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor.
Retry the operation again with a more up-to-date log, differential, or full database backup. The subscription to publication ‘Transactional’
has expired or does not exist.
