SharePlex can capture from a SQL Server source database and replicate the data to an Oracle target.
These instructions provide an overview of the steps required to support replication between these database types.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
SQL Server defines CHAR and VARCHAR data in bytes, whereas Oracle can define it in bytes or characters depending on the semantics definition of the database or the specific table. Additionally, SQL Server allows larger maximum column sizes than Oracle. To allow for these differences in column length, adjustments must be made to the Oracle target table definitions as follows to ensure that the target columns can fit all of the data:
The following chart represents these relationships:
SQL Server Source column definition | Length (bytes) | Required Oracle column definition |
---|---|---|
char length |
<=1000 >1000 |
CHAR(length char) CLOB |
varchar length |
<=1000 >1000 |
VARCHAR(length char) CLOB |
nchar length |
<=1000 >1000 |
NCHAR(length)
NCLOB |
nvarchar length |
<=2000 >2000 |
NVARCHAR(length) NCLOB |
binary |
<=2000 >2000 |
RAW(length) BLOB |
Configure SharePlex and the database on the source system as follows.
Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.
To replicate from a source SQL Server database to a target Oracle database, all SQL Server source tables must have a primary key. This is a requirement of the native SQL Server replication, which is used in part by SharePlex for data capture. See the SQL Server source checklist for more information about these and other pre-configuration requirements for a SQL Server source.
All Oracle target tables must have corresponding keys.
To configure replication from SQL Server to Oracle, use the following syntax in the configuration file on the source system.
Note: See Configure data replication in the SharePlex Administration Guide for additional information about creating a configuration file.
Datasource:r.database_name | ||
src_owner.table |
tgt_owner.table |
host@o.SID |
where:
Important!
The following configuration file replicates table HR.EMP from the source to target table Region1.Emp on target system. The target table is case-sensitive.
Datasource:r.mss1 | ||
HR.EMP |
"Region1"."Emp" |
sysprod@o.Ora1 |
Make certain that the database setup meets all of the requirements in Set up Oracle database objects for replication on page 1 .
SharePlex can capture from a SQL Server source database and replicate the data to a MySQL target.
These instructions provide an overview of the steps required to support replication between these database types.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
Configure SharePlex and the database on the source system as follows.
Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.
To configure replication from SQL Server to MySQL, use the following syntax in the configuration file on the source system.
Note: See Configure data replication in the SharePlex Administration Guide for additional information about creating a configuration file.
Datasource:r.database_name | ||
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
Important!
The following configuration file replicates table HR.EMP from the source to target table Region1.Emp on target system. The target table is case-sensitive.
Datasource:r.mss1 | ||
hr.emp |
region1.emp |
sysprod@r.mydb |
Make certain that the database setup meets all of the requirements in Open Target checklist .
SharePlex can capture from a SQL Server source database and replicate the data to a PostgreSQL target.
These instructions provide an overview of the steps required to support replication between these database types.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
Configure SharePlex and the database on the source system as follows.
Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.
To configure replication from SQL Server to PostgreSQL, use the following syntax in the configuration file on the source system.
Note: See Configure data replication in the SharePlex Administration Guide for additional information about creating a configuration file.
Datasource:r.database_name | ||
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
Important!
The following configuration file replicates table HR.EMP from the source to target table Region1.Emp on target system. The target table is case-sensitive.
Datasource:r.mss1 | ||
hr.emp |
region1.emp |
sysprod@r.mydb |
Make certain that the database setup meets all of the requirements in Open Target checklist .
The SharePlex Post process can connect and write to a Kafka broker. The data can be written in JSON or XML output as a sequential series of operations as they occurred on the source, which can then be consumed by a Kafka consumer.
These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
When replicating data to Kafka, configure the source database and SharePlex on the source system as follows.
Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.
On the source, create a SharePlex configuration file that specifies capture and routing information. The structure that is required in a configuration file varies, depending on your replication strategy, but this shows you the required syntax for routing data to Kafka.
Datasource:r.database_name | ||
src_owner.table | !kafka[:tgt_owner.table] | host |
where:
host is the name of the target system.
Note: See Configure data replication in the SharePlex Administration Guide for additional information about creating a configuration file.
Datasource:r.mss1
my_source.my_table !kafka sysprod
These instructions configure the SharePlex Post process to connect to Kafka. You must have a running Kafka broker.
To configure post to Kafka
Issue the target command to configure posting to a Kafka broker and topic. The following are example commands.
sp_ctrl> target x.kafkaset kafka broker=host1:9092,host2:9092,host3:9092
sp_ctrl> target x.kafka set kafka topic=shareplex
See View and change Kafka settings for command explanations and options.
Note: Specify more than one broker so that SharePlex will attempt to connect to the other brokers in the list if any one of them is down.
SharePlex can output to either XML or JSON format as input to Kafka. XML is the default. To set the input format and specify format options, use one of the following target commands:
target x.kafka set format record=json
or:
target x.kafka set format record=xml
To view samples of these formats, see the format category of the target command documentation in the SharePlex Reference Guide.
To view current property settings for output to Kafka, use the following target command:
target x.kafka show
To change a property setting, use the following command.
target x.kafka [queue queuename] set kafka property=value
where:
Table 1: Kafka target properties
Property | Input Value | Default |
---|---|---|
broker=broker |
Required. The host and port number of the Kafka broker, or a comma delimited list of multiple brokers. This list is the bootstrap into the Kafka cluster. So long as Post can connect to one of these brokers, it will discover any other brokers in the cluster. |
localhost:9092 |
client_id=ID |
Optional. A user-defined string that Post will send in each request to help trace calls. |
None |
compression.code={none, gzip, snappy} | Optional. Controls whether data is compressed in Kafka. Options are none, gzip or snappy. | None |
partition={number | rotate | rotate trans} |
Required. One of the following:
|
0 |
request.required.acks=value | Optional. This is a Kafka client parameter. By default it is set to a value of -1, which means all. Consult the Kafka documentation about this subject, because all really means all in-sync replicas. This parameter can be used in conjunction with the min.insync.replicas broker parameter to tune behavior between availability and data consistency. Important: It is possible for data to be lost between a Kafka producer (SharePlex in this case) and a Kafka cluster, depending on these settings. | -1 |
topic=topic_name |
Required. The name of the target Kafka topic. This string may contain the special sequences %o or %t. The %o sequence is replaced by the owner name of the table that is being replicated. The %t sequence is replaced by the table name of the table that is being replicated. This feature may be used in conjunction with a Kafka server setting of auto.create.topics.enabled set to 'true'. Also view your server settings for default.replication.factor and num.partitions because these are used as defaults when topics are auto created. Important! If using multiple topics, you must also set the following properties with the target command:
|
shareplex |
* To avoid latency, if Post detects no more incoming messages, it sends the packet to Kafka immediately without waiting for the threshold to be satisfied.
If the Kafka process aborts suddenly, or if the machine that it is running on aborts, row changes may be written twice to the Kafka topic. The consumer must manage this by detecting and discarding duplicates.
Every record of every row-change operation in a transaction has the same transaction ID and is also marked with a sequence ID. These attributes are id and msgIdx, respectively, under the txn element in the XML output (see Set up replication from SQL Server to Kafka). These two values are guaranteed to be the same if they are re-written to the Kafka topic in a recovery situation.
If desired, you can configure Post to include additional metadata with every row-change record by using the following command:
target x.kafka [queue queuename] set metadata property[, property]
Table 2: Optional metadata properties
Property | Description |
---|---|
time | The time the operation was applied on the source. |
userid | The ID of the database user that performed the operation. |
trans | The ID of the transaction that included the operation. |
size | The number of operations in the transaction. |
target x.kafka set metadata time, userid, trans, size
To reset the metadata
target x.kafka [queue queuename] reset metadata
To view the metadata
target x.kafka [queue queuename] show metadata
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy