Summary of Procedures for Replicating Tables

The replication procedure consists of several steps required to replicate data between tables using table replication definitions and subscriptions.

1. Plan your replication system and verify that you have prepared the replication system correctly.
2. Create the table as the Database Owner in the primary database, if it does not already exist, or, if there is a different table owner, specify the table owner name when you create the replication definition.

   • In Adaptive Server, use create table to create the table, or use sp_help to verify that the table exists.

   • If you are replicating data from a source other than Adaptive Server, create the table according to the instructions for your database software. Other data server steps in this procedure may vary for heterogeneous replication.

3. If your database supports stored procedures, you can execute rs_send_repserver_cmd in the primary database to create one or more replication definitions for the table from which you want to copy data. Otherwise, in the primary Replication Server, create one or more replication definitions. Each replication definition can be subscribed to by a different site that uses a different table view.

   When you create replication definitions, anticipate the requirements for the subscribing table, as described in step 8. The replication definition may contain all or a subset of the columns in the source table. It may specify the same or different table names, owner names, column names, or datatypes for the source and destination tables. It may change the datatype of the replicated value.

4. If you are using publications, execute the following steps at the primary Replication Server.
   1. Create one or more publications for the tables you want to replicate using create publication.
   2. Create one or more articles, replication definition extensions, for each replication definition you want to include in the publication using create article. You can include a where clause to specify a subset of rows to send to the destination database.
   3. Validate the publications, using validate publication, so that you can create subscriptions against them.
5. Mark the primary table for replication.

   In the primary Adaptive Server, use sp_setreptable to enable table replication. This step allows the RepAgent thread to forward transactions for the table to the primary Replication Server.

   Note: For non-Adaptive Server primaries, see your Replication Agent documentation for instructions on marking tables and columns.
6. If the primary table contains text, unitext, image, or rawobject columns, you may need to use sp_setrepcol in the primary Adaptive Server to adjust the replication status for these columns.
   Note: For non-Adaptive Server primaries, see your Replication Agent documentation for instructions.
7. Prepare a login name for the user creating the subscription. Login names that create subscriptions at replicate Replication Servers must also exist at the primary Replication Server.
8. In the replicate database, create a table that matches the schema published by the replication definition. Create the replicate table as the Database Owner or as the same table owner specified in the replication definition.

   In Adaptive Server, use create table to create the table, or use sp_help to verify that the table exists.

   The replicate table may have the same or different name and/or the same or different owner name as the primary table. It may contain all or a subset of the columns in the primary table, with the same or different column names or datatypes. The replication definition must specify any such differences between the primary and replicate tables.

   Note: The replicate table may include a column that is not in the replication definition if the column accepts null values, has a defined default value, or you use a custom function string to apply a value to that column.
9. Grant the replicate database maintenance user login name select, insert, delete, and update permissions on the replicate table. The maintenance user executes commands for replicated transactions.
10. If necessary, customize your database operations using functions, function strings, and function-string classes. Replication Server function strings execute data server operations.

    See Replication Server Administration Guide Volume 2 > Customizing Database Operations for details.

11. Create a subscription in the replicate Replication Server. If you are using publications, proceed to step 12.

    Log in to a replicate Replication Server and create one or more subscriptions to the table replication definition for the data you want to copy. You can subscribe to all the rows in the replication definition’s columns, or use a where clause to copy only certain rows.

    A replicate database can subscribe to multiple replication definitions of a primary table, but a replicate table can subscribe to only one replication definition of a primary table.

    When you create a subscription, the replicate table is filled in with the initial table data in a process called materialization. In most cases, Replication Server copies data into the replicate table automatically. You can also manually materialize the data.

12. If you are using publications, create a publication subscription against the publications created in step 4. Execute create subscription at the replicate Replication Server.

    When you create a publication subscription, Replication Server creates subscriptions against each article in the publication. Article subscriptions do not contain where clauses.

13. Check the subscription status.

    Verify that the subscription data has fully materialized in the replicate database and that transactions are replicating successfully.

• Commands for Managing Table Replication Definitions
  Replication Server provides commands and stored procedures to manage table replication definitions.