Replication definition change request process

When you request changes to replication definitions, Replication Server coordinates the propagation of replication definition changes and data replication automatically. You can request replication definition changes directly at the primary database using the alter replication definition, alter applied replication definition, or alter request function replication definition commands, while making changes to the database schema.

In Replication Server:

You can use the rs_send_repserver_cmd stored procedure to execute replication definition change requests directly at the primary database.

Replication Server supports rs_send_repserver_cmd for these replication definition RCL commands:

• alter replication definition

• create replication definition

• drop replication definition

• alter applied function replication definition

• create applied function replication definition

• alter request function replication definition

• create request function replication definition

For example, to execute the “authors” alter replication definition request at the primary database to drop the address, city, state, and zip columns:

exec rs_send_repserver_cmd ‘alter replication
definition authors drop address, city, state, zip’

When you execute rs_send_repserver_cmd at the primary database, the Replication Agent sends the RCL command stored in rs_api to the Replication Server, which then executes the RCL command. This ensures that Replication Server replicates the primary data with the proper replication definition version—primary data before the rs_send_repserver_cmd is replicated with the old replication definition version, while primary data after the rs_send_repserver_cmd is replicated with the new replication definition version.

You do not always need to issue replication definition change requests directly from a primary data server. For example, you can execute the alter replication definition request directly from the primary Replication Server in these situations:

• If there is no subscription to the replication definition

• If there are subscriptions to the replication definition, but there is no data in the primary database log for the table or stored procedure

• If you are adding or dropping a searchable column to or from a table replication definition

• If you are adding or dropping a searchable parameter to or from a function replication definition

• If you are altering a replication definition to turn Dynamic SQL on or off

You can use alter replication definition, alter applied function replication definition, and alter request function replication definition with the with DSI_suspended option to suspend the standby DSI, if there is one, and each of the subscribing replicate DSI threads. Replication Server suspends the DSI thread in the standby or replicate database after Replication Server applies all the data for the old replication definition version to the standby or replicate database.

After Replication Server suspends a DSI thread, you can make changes to the target schema or target stored procedures, and to any customized function strings.

When you resume the DSI thread, Replication Server replicates the primary updates using the altered replication definition.

This example instructs Replication Server to suspend the target DSI after primary data that exists before you execute alter replication definition is replicated to the target database:

alter replication definition pubs_rep
alter columns with pub_name as pub_name_set
with DSI_suspended

You do not need to use with DSI_suspended if:

• There is no subscription to the replication definition.

• You do not need to change customized function strings.

• You do not need to change the replicate or standby database schema.

When Replication Agent sends a replication definition RCL to Replication Server to execute, and the replication definition RCL fails to execute, Replication Agent shuts down. To avoid this situation, Sybase recommends that you use admin verify_repserver_cmd at the primary database to verify that Replication Server can successfully execute a replication definition request before you execute the RCL directly from the primary database. Replication Server returns an error if it cannot successfully execute the request.

Replication Server supports admin verify_repserver_cmd for the same replication definition commands as rs_send_repserver_cmd:

• alter replication definition

• create replication definition

• drop replication definition

• alter applied function replication definition

• create applied function replication definition

• alter request function replication definition

• create request function replication definition

This example shows that admin verify_repserver_cmd can detect syntax errors, such as using the “columns” keyword in the command line:

admin verify_repserver_cmd, ‘alter replication
definition authors drop columns address, city, state, zip
with DSI_suspended’

Replication Server returns with a message, such as:

Line 1, character 71: Incorrect syntax with the keyword
'columns'.

To instruct Replication Server to skip a failed replication definition request the next time Replication Agent starts use sysadmin skip_bad_repserver_cmd.

In this example, sysadmin skip_bad_repserver_cmd instructs Replication Server and Replication Agent to skip the last failed replication definition command in the pubs2 database of the SYDNEY_DS data server:

sysadmin skip_bad_repserver_cmd, SYDNEY_DS, pubs2

If the replication definition request you execute at the primary database through rs_send_repserver_cmd fails at the primary Replication Server, Replication Agent shuts down. If you restart Replication Agent, the failed command executes again unless Replication Server skips the command. The replication definition request can fail if:

• A subscription is in progress when you execute the request.

• Replication Server cannot find the replication definition or a column.

• There are syntax errors.

If you can resolve the problem that caused Replication Agent to shut down, for example, by waiting for the subscription to complete or correcting the replication definition, you can then restart Replication Agent and Replication Agent reissues the replication definition request.

If the failed replication definition request is caused by an error that cannot be easily fixed, such as a syntax error:

• Execute the correct replication definition command directly at the primary Replication Server.

• Execute sysadmin skip_bad_repserver_cmd at the primary Replication Server to ensure that Replication Agent and Replication Server skip the most recent failed request that Replication Agent sent.

• Restart the Replication Agent.

This use case consolidates all the steps you must follow to use the replication definition change request process.

You can execute the alter replication definition request directly at the primary Replication Server in any of these situations:

• There are no subscriptions for a replication definition.

• There are subscriptions for a replication definition, but there is no data in the primary database log for the primary table or stored procedure.

• You are adding or dropping a searchable column parameter.

• You are turning Dynamic SQL on or off.

Group your changes to the primary schema and stored procedure together and:

1. Log in to the primary Replication Server.

2. Wait until all subscriptions and articles for the replication definitions are valid, that is, there is no materialization or dematerialization in progress.

3. For each replication definition affected by the primary schema and stored procedure changes, test the replication definition request using admin verify_repserver_cmd in the primary Replication Server:

   admin verify_repserver_cmd, ‘alter replication
   definition authors
   drop address, city, state, zip
   with DSI_suspended’
   

4. If you have a lot of replication definitions in the replication system, and you have many replication definition change requests, to enhance performance, you can disable sts_full_cache for these system tables:

   1. rs_objects – configure replication server set sts_full_cache_rs_objects to ‘off

   2. rs_columns – configure replication server set sts_full_cache_rs_columns to ‘off’

   3. rs_functions – configure replication server set sts_full_cache_rs_functions to ‘off’

5. At the primary database:

   1. Suspend any data changes to the primary tables and stored procedures that you want to alter.

   2. Alter the table schema and stored procedures.

   3. For the replication definition request that you verified in step 3, execute the rs_send_repserver_cmd stored procedure. For example:

      exec rs_send_repserver_cmd ‘alter replication definition authors
      drop address, city, state, zip
      with DSI_suspended’
      

   4. Resume data changes to the primary tables and stored procedures.

6. If you issue any of the replication definition requests using with DSI_suspended, then at each subscribing replicate site, wait until Replication Server suspends the DSI, then:

   • Alter the replicate table schema or stored procedures, as required.

   • Alter any customized function strings as required, and wait for the customized function strings to arrive at the replicate site.

   • Resume the replicate DSI.

7. If you changed the sts_full_cache setting for any of the tables in step 4, you can change then back now.

Sybase strongly recommends that you follow step 3 and use admin verify_repserver_cmd to test your replication definition requests. Using admin verify_repserver_cmd provides a higher probability that you can avoid a Replication Agent shutdown if a replication definition request command fails when you execute rs_send_repserver_cmd at the primary Replication Server.

If any of the schema changes requires you to perform an action at a replicate site, such as changing the replicate schema or a customized function string, then only the very last of the replication definition requests in step 4c affecting the replication definition needs to be issued with with DSI_suspended. In some cases, you may need to resume a replicate DSI more than once.

You can skip step 6 if the replicate schema, stored procedures, or customized function strings do not require any changes.