Microsoft SQL Replication to SAP IQ

Set the required values of the ra_autocorrect_on_mark and lr_partial_updates_allowed parameters:

• Set the value of the ra_autocorrect_on_mark parameter to true.

  ra_autocorrect_on_mark

  Determines whether to auto correct a table marked for replication immediately.

  Default

  true

  Values

  • true – Enables autocorrection of tables marked for replication.
  • false – Disables autocorrection of tables marked for replication.

  Command

  Isql>ra_config ra_autocorrect_on_mark,true

• Set the value of the lr_partial_updates_allowed parameter to false.

  lr_partial_updates_allowed

  Converts partial updates to varchar(max), nvarchar(max), and varbinary(max) large object (LOB) datatypes at the primary database to a full update. Sends partial updates to LOB data to databases that have native support.

  Default

  true

  Values

  • true – Sends changes to LOB datatype columns as partial updates to Replication Server. Replication Server sends partial updates to the replicate databases that have native support for partial LOB updates, such as Microsoft SQL Server.
  • false – Converts partial LOB updates to a full update, which is consumed in heterogeneous replication environments. If partial LOB updates are detected, an error exception occurs.

  Command

  Isql>rq_config lr_partial_updates_allowed,false

See the Configuration Parameter Reference chapter of the Reference Manual Replication Agent 15.7.1 for Linux, UNIX, and Microsoft Windows Guide for more information.

Make sure to meet the following Replication Server requirements for the replication:

• Use a new connection profile to create the connection from Microsoft SQL Server to SAP IQ: rs_msss_to_iq.

  Isql>create connection to IQ154.iqdemo
  using profile rs_msss_to_iq;standard
  set username iq_maint_user
  set password iq_maint_user_ps
  

  Connection profiles are a part of the Replication Server installation package, and are registered when you install Replication Server. For details about connection profiles, see the SAP IQ Replicate Database Configuration chapter of the SAP Replication Server 15.7.1 Heterogeneous Replication Guide.
• In MultiSite Availability (MSA) setup, replication definitions are not required for tables without LOB columns while replication definitions with standby clauses are required for tables with LOB columns.

  isql>create replication definition rep_T
  with primary at PDS.pdb
  with all tables named T
  (col1 bigint,
  col2 int,
  col3 varchar(200),
  col4 image ,
  col5 text,
  col6 int,
  col7 money,
  col8 int,
  col9 datetime,
  col10 varbinary(20))
  primary key (col1)
  send standby replication definition columns
  go
  

• Use Microsoft SQL datatype translation for replication definitions.

  isql>create replication definition rd_test_mssql3
  with primary at PDS.pdb
  with all tables named test_mssql
    ( id int
    , c1 binary(20)
    , c2 bit
    , c3 char(10)
    , c4 date
    , c5 decimal
    , c6 float
    , c7 image
    , c8 money
    , c9 unichar(10)
    , c10 unitext
    , c11 univarchar(50)
    , c12 numeric
    , c13 real
    , c14 smalldatetime
    , c15 smallint
    , c16 smallmoney
    , c17 text
    , c18 time
    , c19 timestamp
    , c20 tinyint
    , c21 varbinary(20)
    , c22 varchar(50)
    , c23 rs_msss_datetime
    , c24 rs_msss_datetime2
    , c25 rs_msss_datetimeoffset
    , c26 rs_msss_bigint
    , c27 text
    , c28 unitext
    , c29 image
    , c30 varchar(38)
    )
    primary key (id)
  send standby replication definition columns
  

  For Microsoft SQL datatype mappings, see Replication Agent 15.7.1 Primary Database Guide for Linux, UNIX, and Microsoft Windows chapter of the SAP Replication Server, heterogeneous edition 15.7.1 Guide.

• Use UTF-8 settings to replicate Microsoft SQL UTF-16 data.

  To replicate UTF-16 data from MSSQL server to IQ, configure both Replication Server and SAP IQ to use UTF-8. See the Character Sets chapter of the SAP Replication Server 15.7.1 Design Guide for Unicode UTF-8 and UTF-16 support.