Configuring the Replication System to Reduce Replication Definitions

Configure the replication system to reduce replication definitions in an Adaptive Server warm standby or multisite availability environment.

1. At the primary Replication Server, suspend log transfer:

   suspend log transfer from all

2. At the primary data server, stop the RepAgent:

   sp_stop_rep_agent dbname

3. At the primary and replicate Replication Servers, set the site version to the upgraded Replication Server version if necessary. The new site version must be 1571 or later.

   sysadmin site_version, new site version

4. Drop all replication definitions that exist only to define primary keys, quoted identifiers, and customized function strings.
   1. At the RSSD or ERSSD of the primary Replication Server, use rs_helpcheckrepdef to identify such replication definitions.
   2. At the primary Replication Server, use drop replication definition to drop the replication definitions listed by rs_helpcheckrepdef.
5. At the primary database, create a unique index for tables that do not have a primary key or a unique index, and that do not have replication definitions.

   RepAgent selects as the primary key:

   • The primary index defined on a table, or
   • A unique index with the lowest index identifier if there is no primary index

   Note: If there is no primary key or unique index defined on the table, RepAgent does not send primary key information to the LTL. If the table does not have a replication definition, Replication Server treats all columns in the table, except text, image, or unitext columns, as primary key columns. This may degrade replication performance for the table.
   1. Execute sp_setreptable without any parameters to see primary key and index information for all tables that you had marked for replication with sp_setreptable.

      Name       Repdef Mode   Index Mode   Primary Key
      ---------  ------------  -----------  -------------------
      hola       owner_off     no index     hola_index
      inds       owner_off     no index     inds_1335724833
      t applied  owner_off     no index     t applied_855723122
      t5         owner_off     no index     con_pk_t5

      You can also specify a single table. For example:

      sp_setreptable inds

   2. Create a unique index for any table without a unique index or primary key.
      See Adaptive Server Enterprise > Transact-SQL Users Guide > Creating Indexes on Tables.
6. If any of the replication definitions that you dropped in step 4 have customized function strings, re-create them as customized target-scope function strings at the Replication Server that controls the standby or MSA database. If there is more than one target database, re-create the function strings for each of them.
7. Resume log transfer and start the RepAgent.
   1. At the primary data server, start RepAgent:

      sp_start_rep_agent dbname

   2. At the primary Replication Server, resume log transfer:

      resume log transfer from all

   Note: If you change the primary key for a table schema, you must suspend and resume all connections from databases that subscribe to the table and have dynamic_sql turned on.