Edit

Share via

Transactional Articles - Regenerate 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 execute 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 is not supported for Oracle Publishers).

It is also possible to specify custom procedures to replace one or more of the default procedures. The custom procedures should be changed if the schema change will affect 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:

  • The first option is to use a custom scripting procedure to replace the defaults used by replication:

    1. When executing sp_addarticle (Transact-SQL), ensure the @schema_option 0x02 bit is to true.

    2. Execute sp_register_custom_scripting (Transact-SQL) and specify a value of 'insert', 'update', or 'delete' for the parameter @type and the name of the custom scripting procedure for the parameter @value.

    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.

  • The second option is to use a script that contains a new custom procedure definition:

    1. When executing sp_addarticle (Transact-SQL), set the @schema_option 0x02 bit to false so replication does not automatically generate custom procedures at the Subscriber.

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

    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 executed after a subsequent schema change.

Related content