Replicating Tables and Functions

Use MSA capabilities to replicate particular tables or functions.

1. Mark tables, stored procedures, and database for replication and create the database replication definition.

   In this example, we are replicating table1 and table2 only. You can identify particular tables in either of two ways:

   • Mark the database for replication using sp_reptostandby. Create the database replication definition and identify specific tables for replication using create replication definition. You must also tell the RepAgent to send replicate data to replicate as well as standby databases.

     At the primary data server, enter:

     sp_reptostandby primary_db, ‘all’
     sp_config_rep_agent primary_db,
       ‘send warm standby xacts’, ‘true’

     At the primary Replication Server, enter:

     create database replication definition rep_1B
       with primary at PDS.pdb
         replicate tables in (table1, table2)

   • Alternatively, mark particular tables and stored procedures for replication using sp_setreptable and sp_setrepproc. Then, create the database replication definition. For example:

     sp_setreptable table1, ‘true’

     sp_setrptable table2, ‘true’

     create database replication definition rep_1A
       with primary at PDS.pdb

     Note: You can replicate DDL changes only if you mark its database for replication using sp_reptostandby.

   See the Replication Server Heterogeneous Replication Guide for non-ASE data servers.

2. Create the database subscription. To subscribe without materialization, follow the procedure in the simple scenario to replicate the database. You can also subscribe using bulk materialization.
   Note: You can also use sp_reptostandby to mark the database and then create table replication definitions and subscriptions—without creating a database replication definition. This method eliminates the need to mark individual tables, yet allows you to select and replicate partial tables. The database connection parameter rep_as_standby must be on.
   Be aware of considerations when dealing with encrypted columns.