SQL Remote User's Guide
Using SQL Remote with Replication Server
Configuring Replication Server
Replication definitions for tables being replicated by SQL Remote must have certain characteristics. This section describes those characteristics.
In some circumstances SQL Remote replicates an UPDATE operation as an INSERT or a DELETE (see Replication of updates). This is referred to as subscription migration in the Replication Server documentation. In order to replicate an UPDATE as an INSERT, SQL Remote requires the full pre-image of the row. This means that Replication Server must specify the values of every column in the WHERE clause of any UPDATE to a table that might need to be replicated as an INSERT.
The simplest way to achieve this is to list all columns in the PRIMARY KEY of the replication definition. This forces Replication Server to include every column in the WHERE clause of every update. REPLICATE MINIMAL COLUMNS can be used on these replication definitions to prevent every column from being listed in the SET clause of the update.
Replication Server does not accept TEXT or IMAGE columns in the primary key of a replication definition. You should include all the columns except for the TEXT and IMAGE columns in the PRIMARY KEY list of your replication definition, and specify all the TEXT and IMAGE columns in the ALWAYS_REPLICATE clause. You should use REPLICATE ALL COLUMNS, instead of REPLICATE MINIMAL COLUMNS in your replication definition. This forces Replication Server to send the pre-image of the TEXT and IMAGE columns to the SQL Remote Open Server whenever an update occurs.
Replication Server 11.5 has a new dsi_sql_data_style for SQL Remote. This data style automatically includes all columns in the WHERE clause of every UPDATE. It is not necessary to list all columns in the PRIMARY KEY of the replication definition. A replication definition using REPLICATE MINIMAL COLUMNS prevents Replication Server from keeping the full pre-image of rows being updated, so the SQL Remote dsi_sql_data_style will not work with REPLICATE MINIMAL COLUMNS.