Edit

Share via

Transactional articles - Regenerate custom procedures to reflect schema changes

  • Article

Applies to: SQL Server Azure SQL Managed Instance

By default, transactional replication makes all data changes at Subscribers through stored procedures that are generated by internal procedures, for each table article in the publication. The three procedures (one each for inserts, updates, and deletes) are copied to the Subscriber and executed when an insert, update, or delete is replicated to the Subscriber.

When a schema change is made to a table on a SQL Server Publisher, replication regenerates these procedures automatically by calling the same set of internal scripting procedures so that the new procedures match the new schema (replication of schema changes isn't supported for Oracle Publishers).

You can also specify custom procedures to replace one or more of the default procedures. The custom procedures should be changed if the schema change affects the procedure. For example, if a procedure references a column that is dropped in a schema change, references to the column should be removed from the procedure.

There are two ways for replication to propagate a new custom procedure to Subscribers:

  • Use a custom scripting procedure to replace the defaults used by replication
  • Use a script that contains a new custom procedure definition

Use a custom scripting procedure to replace the defaults used by replication

When you execute sp_addarticle, ensure the @schema_option 0x02 bit is to true.

Note

The custom stored procedure definition must be scripted using dynamic Transact-SQL, inside a wrapper stored procedure. This wrapper stored procedure must also include an @artid parameter of type int, to ensure that it's created on the Subscriber.

Execute sp_register_custom_scripting and specify a value of insert, update, or delete for the @type parameter, and the name of the custom scripting procedure for the @value parameter.

The next time a schema change is made, replication calls this stored procedure to script out the definition for the new user defined custom stored procedure, and then propagates the procedure to each Subscriber.

Example

In this example, assume that the Publisher and Subscriber are already configured, and you want to create a custom delete stored procedure.

  1. On the Subscriber, create a table to showcase the custom delete script.

    USE [SubscriberDB];
GO

CREATE TABLE DeleteLogging (id INT PRIMARY KEY);
GO

  2. Add the article from the Publisher. Note the values of the @schema_option, @ins_cmd, @upd_cmd, and @del_cmd parameters:

    USE [PublisherDB];

EXECUTE sp_addarticle
    @publication = N'PubName1',
    @article = N'Purchases',
    @source_owner = N'dbo',
    @source_object = N'Purchases',
    @type = N'logbased',
    @description = NULL,
    @creation_script = NULL,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'Purchases',
    @destination_owner = N'dbo',
    @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dboPurchases',  -- default
    @del_cmd = N'CALL custom_delete',          -- custom
    @upd_cmd = N'SCALL sp_MSupd_dboPurchases'; -- default
GO

  3. Create a stored procedure that scripts out the custom_delete stored procedure you want to use on the Subscriber. This is the wrapper stored procedure, as indicated previously.

    Returning non-zero values from this stored procedure results in custom_delete not being created on the Subscriber. The SELECT should return the full CREATE definition of the stored procedure that will be used on the Subscriber.

    Note the use of the required @artid parameter.

    USE [PublisherDB];
GO

CREATE OR ALTER PROCEDURE script_custom_delete (@artid INT)
AS
BEGIN
    SELECT 'CREATE OR ALTER PROCEDURE custom_delete
              @pkc1 INT
          AS
          BEGIN
              INSERT INTO DeleteLogging (id) VALUES (@pkc1)
          END';
    RETURN 0;
END
GO

  4. Register the custom script on the Publisher.

    USE [PublisherDB];
GO

EXECUTE sp_register_custom_scripting
    @type = 'delete',
    @value = 'script_custom_delete',
    @publication = 'PubName1',
    @article = 'Purchases';
GO

  5. Add a subscription. In this example, the @sync_type parameter is set to replication support only, so no snapshot is used.

    USE [PublisherDB];
GO

EXECUTE sp_addsubscription
    @publication = N'PubName1',
    @subscriber = @@SERVERNAME,
    @destination_db = N'SubscriberDB',
    @subscription_type = N'Push',
    @sync_type = N'replication support only',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;
GO

Use a script that contains a new custom procedure definition

When you execute sp_addarticle, set the @schema_option 0x02 bit to false so replication doesn't automatically generate custom procedures at the Subscriber.

Before each schema change, create a new script file and register the script with replication by executing sp_register_custom_scripting. Specify a value of custom_script for the @type parameter, and the path to the script on the Publisher for the @value parameter.

The next time a relevant schema change is made, this script executes on each Subscriber within the same transaction as the DDL command. After the schema change is made, the script is unregistered. You must re-register the script to have it execute after a subsequent schema change.

Related content