April 9, 2024 at 6:53 pm
I previously posted a question about setting up replication with custom replication stored procedures (for delete especially) to allow for a longer retention period on the subscriber than on the publisher. The following two MSDN articles were referenced, and I thought I understood them.
However, I've now noticed something strange and I think I've misunderstood how the custom replication procs must be set up. When I create the subscription and its agent, the definition of my custom replication proc gets overwritten with the default contents. However, if I "register a custom scripting proc" (sp_register_custom_scripting) then I can get it so that it does not overwrite my custom proc, but I have no idea why this happens.
I had assumed that the custom scripting proc should somehow return the definition of the custom stored proc for the replicated delete, but there is no documentation or examples about how to use sp_register_custom_scripting. I've tried to figure out how it works (tried OUTPUT param and tried inspecting the param that was passed in, but to no avail.)
I'm going to paste a full repro script here, which creates a test database "test1" and sets it up, then backs it up and restores it as test2. This mirrors how I would be using replication to set up the subscriber with the current data, which will then be given a longer retention period. As part of the setup, I have the custom stored proc and there are some commented out bits showing what I've tried and what happens if I don't use sp_register_custom_scripting at all (the custom proc gets overwritten when the subscriber is created).
Any explanation of the correct way to use custom replication stored procs and sp_register_custom_scripting or a pointer to where I'm going wrong would be much appreciated. I know the repro script is long, but I figured it's worth giving a full script that shows the exact issue.
SET XACT_ABORT ON
USE master
IF DB_ID('test1') IS NOT NULL ALTER DATABASE test1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE IF EXISTS test1
IF DB_ID('test2') IS NOT NULL ALTER DATABASE test2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE IF EXISTS test2
--ALTER DATABASE test1 SET MULTI_USER
GO
CREATE DATABASE test1
GO
USE test1
GO
CREATE TABLE table1 (id1 INT NOT NULL, id2 INT NOT NULL, val VARCHAR(100))
GO
ALTER TABLE table1 ADD CONSTRAINT pk_table1 PRIMARY KEY (id1, id2)
GO
CREATE PROCEDURE table1_custom_delete
@pkc1 INT, @pkc2 INT
AS
BEGIN
RETURN
END
GO
CREATE PROCEDURE mystery_proc
@param VARCHAR(100)
AS
BEGIN
PRINT '******************************************************************'
PRINT @param
RETURN
END
GO
BACKUP DATABASE test1 TO DISK = 'c:\temp\test1.bak' WITH FORMAT, COMPRESSION;
RESTORE DATABASE test2 FROM DISK = 'c:\temp\test1.bak'
WITH
STATS = 5,
MOVE 'test1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\data\test_data.mdf',
MOVE 'test1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\data\test_log.ldf'
GO
-- Adding the distribution database
USE master
DECLARE @host VARCHAR(100) = @@SERVERNAME
EXEC sp_adddistributor @distributor = @host,
@password = N''
GO
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 = 13,
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data',
@log_file = N'distribution.LDF',
@log_file_size = 9,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@deletebatchsize_xact = 5000,
@deletebatchsize_cmd = 2000,
@security_mode = 1
GO
RECONFIGURE
GO
-- Adding the distribution publishers
DECLARE @host VARCHAR(100) = @@SERVERNAME
EXEC sp_adddistpublisher @publisher = @host,
@distribution_db = N'distribution',
@security_mode = 1,
@trusted = N'false',
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
USE [test1]
EXEC sp_replicationdboption @dbname = N'test1'
,@optname = N'publish'
,@value = N'true'
GO
-- Adding the transactional publication
USE [test1]
EXEC sp_addpublication @publication = N'test_pub'
,@description = N'Transactional publication of database ''test1''.'
,@sync_method = N'concurrent'
,@retention = 0
,@allow_push = N'true'
,@allow_pull = N'true'
,@allow_anonymous = N'false'
,@enabled_for_internet = 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'
,@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_addarticle @publication = N'test_pub'
,@article = N'table1'
,@source_owner = N'dbo'
,@source_object = N'table1'
,@type = N'logbased'
,@description = N''
,@creation_script = NULL
,@pre_creation_cmd = N'none'
,@schema_option = 0x000000000803509F
,@identityrangemanagementoption = N'manual'
,@destination_table = N'table1'
,@destination_owner = N'dbo'
,@vertical_partition = N'false'
,@ins_cmd = N'CALL sp_MSins_dbotable1'
,@del_cmd = N'CALL table1_custom_delete'
,@upd_cmd = N'SCALL sp_MSupd_dbotable1'
GO
/*
-- If the following sp_register_custom_scripting is run, then the custom delete procedure is not deleted, but I don't understand what this proc is doing.
EXEC sp_register_custom_scripting @type = 'delete',
@value = 'mystery_proc',
@publication = 'test_pub',
@article = 'table1'
-- If the following sp_register_custom_scripting is run, then this error about an expected parameter occurs
-- Msg 6549, Level 16, State 1, Procedure sys.sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr, Line 0 [Batch Start Line 130]
-- A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr':
-- System.Data.SqlClient.SqlException: Procedure or function 'table1_custom_delete' expects parameter '@pkc2', which was not supplied.
EXEC sp_register_custom_scripting @type = 'delete',
@value = 'table1_custom_delete',
@publication = 'test_pub',
@article = 'table1'
GO
*/
-----------------BEGIN: Script to be run at Publisher -----------------
USE [test1]
DECLARE @host VARCHAR(100) = @@SERVERNAME
EXEC sp_addsubscription @publication = N'test_pub'
,@subscriber = @host
,@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
EXEC sp_addpushsubscription_agent @publication = N'test_pub'
,@subscriber = @host
,@subscriber_db = N'test2'
,@job_login = NULL
,@job_password = NULL
,@subscriber_security_mode = 1
,@frequency_type = 64
,@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 = 20240409
,@active_end_date = 99991231
,@enabled_for_syncmgr = N'False'
,@dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher -----------------
-- Check the article registration
SELECT * FROM dbo.sysarticles
-- Compare the definition for the custom delete proc on test1 and test2 DBs
USE test1
EXEC sp_helptext table1_custom_delete
USE test2
EXEC sp_helptext table1_custom_delete -- The contents of the custom proc is overwritten as soon as sp_addpushsubscription_agent is run.
-- Insert a row into table1 in test1, wait a while, and check that the row was replicated.
-- Then delete the row, wait a bit and check that the row was *not* deleted from test2 (subscriber).
USE test1
INSERT INTO table1 (id1,id2,val) VALUES (1,1,'hi')
SELECT * FROM test1..table1
SELECT * FROM test2..table1
DELETE FROM table1
SELECT * FROM test1..table1
SELECT * FROM test2..table1
April 10, 2024 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 24, 2024 at 1:55 pm
The tl;dr is "How do I set a custom delete replication proc for a table article?" and also "How does one use sp_register_custom_scripting?"
(This'll be my last bump, just holding thumbs for some info)
August 23, 2024 at 11:16 am
The mystery_proc in the above script prevents columns from being added to the replicated table. Updating it to the following solved that problem, making it a viable workaround.
CREATE PROCEDURE mystery_proc
@artid INT = '-1' -- A default value is required. SQL Server calls this proc with and without parameters when a column is added to a replicated table.
AS
BEGIN
RETURN
END
September 30, 2024 at 12:59 pm
So, final update on this for anyone who wants to know how to properly use custom replication stored procedures.
If you're using schema_option & 0x02, it tells replication to generate default replication stored procedures for insert, update and delete (the familiar sp_MSins... sp_MSupd... sp_MSdel... procs). Providing the name of a different stored procedure when calling sp_addarticle is not enough, as SQL Server will just generate the default replication stored procedure with that name (this is the issue I was running into).
The solution is to use sp_register_custom_scripting to register a "scripting" stored procedure that will return the definition of the custom replication stored procedure.
This is needed because for the insert and update replication stored procs, if columns are added or removed from a replicated table, the procs need to be updated to handle the column changes. (Example: new column is added, the proc must populate the new column.)
The scripting proc that you register with sp_register_custom_scripting proc is not well documented, but it works as follows:
With this proc in hand, you call sp_register_custom_scripting proc to register it for the particular type of operation (@type = 'delete', 'insert' or 'update').
So, taking the above into account, the original repro script in the first post can be modified to have the "mystery_proc" (now a solved mystery) as follows:
CREATE OR ALTER PROCEDURE mystery_proc
@artid INT
AS
BEGIN
SELECT 'CREATE OR ALTER PROCEDURE table1_custom_delete
@pkc1 INT, @pkc2 INT
AS
BEGIN
RETURN -- hey there, I was created by the mystery proc :)
END'
RETURN 0
END
GO
And then register it as follows:
EXEC sp_register_custom_scripting @type = 'delete',
@value = 'mystery_proc',
@publication = 'test_pub',
@article = 'table1'
Running the repro script with these changes, after the subscription agent starts up and re-creates the replication stored procedures on the subscriber, the contents of table1_custom_delete will reflect our little "hey there" comment to show that it was recreated. We can then also add or remove columns from the published table and replication will continue happily.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply