Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 13. Using SQL Remote with Replication Server
This section describes how to configure Replication Server for use with the SQL Remote Open Server
The Replication Server connection to the SQL Remote Open Server must have several configuration parameters set.
Set the dsi_xact_group_size parameterBy default, Replication Server groups multiple transactions into larger transactions. The dsi_xact_group_size parameter controls the maximum size of a grouped transaction.
The dsi_xact_group_size parameter must be set to -1 to disable transaction grouping. Transactions that originate from different remote databases in a SQL Remote setup must not be grouped together.
You can set the parameter using the following statement:
CONFIGURE CONNECTION TO "ssqueue_server" SET dsi_xact_group_size TO '-1'
Set the dsi_fadeout_time parameterFor performance reasons, the SQL Remote Open Server does not commit changes to the stable queue after every transaction. The SQL Remote Open Server does commit changes to the stable queue when any of the following happens:
One thousand transactions are sent to the SQL Remote Open Server
Replication Server disconnects from the SQL Remote Open Server
An operation on sr_marker replicates through to the SQL Remote Open Server
The commits to the stable queue are needed for the following reasons:
Make newly replicated transactions available to the Message Agent sending process
Release any page locks in the stable queue
The dsi_fadeout_time connection parameter controls the amount of idle time before Replication Server disconnects from a data server. This should be set low so the SQL Remote Open Server will commit changes to the stable queue whenever there is idle time. A value of 20 seconds is recommended.
You can set the parameter using the following statement:
CONFIGURE CONNECTION TO "ssqueue_server" SET dsi_fadeout_time TO '20'
Set the dsi_num_threads parameterThe SQL Remote Open Server does not support multiple DSI threads. Replication Server should not be configured to use multiple DSI threads on SQL Remote connections.
Create replication definitions for SQL Remote dataReplication 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 11.5 has a new dsi_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_data_style will not work with REPLICATE MINIMAL COLUMNS.
Suspend and restart the connectionAfter configuring the Replication Server connection to the SQL Remote Open Server, you should suspend and resume the connection so that the parameter settings can take effect. The following commands accomplish this task:
suspend connection to ssqueue_name go resume connection to ssqueue_name go