Using the Replication Definition Change Request Process

Learn from the use case which consolidates all the steps to follow to use the replication definition change request process.

Prerequisites

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.

Task

Group your changes to the primary schema and stored procedure together.
Log in to the primary Replication Server.
Wait until all subscriptions and articles for the replication definitions are valid, that is, there is no materialization or dematerialization in progress.
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'
```
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:
- rs_objects:
  configure replication server set sts_full_cache_rs_objects to 'off' go
- rs_columns:
  configure replication server set sts_full_cache_rs_columns to 'off' go
- rs_objfunctions:
  configure replication server set sts_full_cache_rs_objfunctions to 'off' go
Tip: Execute the Adaptive Server update statistics command on the RSSD tables periodically if there are many RSSD changes. For replication definition change requests, such as to create, alter, or drop replication definitions, the affected tables are rs_objects, rs_columns, and rs_objfunctions. For function string change requests, such as to create, alter, or drop function strings, the affected tables are rs_funcstrings and rs_systext.
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 4, 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.
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:
1. Alter the replicate table schema or stored procedures, as required.
2. Alter any customized function strings as required, and wait for the customized function strings to arrive at the replicate site.
3. Resume the replicate DSI.
If you changed the sts_full_cache setting for any of the tables in step 5, you can change the settings back now.

Sybase strongly recommends that you follow step 4 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 6c 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 7 if the replicate schema, stored procedures, or customized function strings do not require any changes.

If the Replication Agent shuts down because of a failed replication definition command, you can skip faulty commands to recover Replication Agent.

If the database does not support rs_send_repserver_cmd, you need to wait until the primary database log does not have any data rows for the schema that you are changing, and then execute the alter replication definition request at the primary Replication Server.