Configuring tables for replication

You can use sp_setreplicate or sp_setrepproc system procedure or the ALTER TABLE statement to configure tables for replication. A table is identified as a primary data source using the ALTER TABLE statement with a single clause:

ALTER TABLE table-name
SET REPLICATE ON;
The effects of setting REPLICATE ON for a table

Setting REPLICATE ON places extra information into the transaction log. Whenever an UPDATE, INSERT, or DELETE action occurs on the table. The SQL Anywhere Replication Agent uses this extra information to submit the full pre-image of the row, where required, to Replication Server for replication.

Even if only some of the data in the table needs to be replicated, all changes to the table are submitted to Replication Server. It is Replication Server's responsibility to distinguish the data to be replicated from that which is not.

When you update, insert, or delete a row, the pre-image of the row is the contents of the row before the action, and the post-image is the contents of the row after the action. For INSERTS, only the post-image is submitted (the pre-image is empty). For DELETES, the post-image is empty and only the pre-image is submitted. For UPDATES, both the pre-image and the updated values are submitted.

The following data types are supported for replication:

Data type Description ( Open Client/Open Server type )
Exact integer data types int, smallint, tinyint
Exact decimal data types decimal, numeric
Approximate numeric data types float (8-byte), real
Money data types money, smallmoney
Character data types char(n), varchar(n), text
Date and time data types datetime, smalldatetime
Binary data types binary(n), varbinary(n), image
Bit data types bit
Notes

SQL Anywhere supports data of zero length that is not NULL. However, non-null long varchar and long binary data of zero length is replicated to a replicate site as NULL.

If a primary table has columns with unsupported data types, you can replicate the data if you create a replication definition using a compatible supported data type. For example, to replicate a DOUBLE column, you could define the column as FLOAT in the replication definition.

Side effects of setting REPLICATE ON for a table

There can be a replication performance hit for heavily updated tables. You could consider using replicated procedures if you experience performance problems that may be related to replication traffic, since replicated procedures send only the call to the procedure instead of each individual action.

Since setting REPLICATE ON sends extra information to the transaction log, this log grows faster than for a non-replicating database.

Minimal column replication definitions

The SQL Anywhere LTM supports the Replication Server replicate minimal columns feature. This feature is enabled at Replication Server.

For more information about replicate minimal columns, see your Replication Server documentation.