Chat now with support
Chat with Support

SharePlex 9.2.4 - Installation and Setup for SQL Server Source

About this Guide Conventions used in this guide SharePlex pre-installation checklist Download the SharePlex installer Install SharePlex on Linux and UNIX Install SharePlex on Windows Assign SharePlex users to security groups Set up replication between SQL Server databases Set up replication from SQL Server to a different target type Generic SharePlex demonstration-all platforms Solve Installation Problems Database Setup Utilities General SharePlex Utilities Uninstall SharePlex Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Set up replication from SQL Server to Oracle

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.

Review column names

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.

Ensure column length compatibility

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:

  • For SQL Server char and varchar columns less than or equal to 1000 bytes in length, define the Oracle columns as CHAR and VARCHAR, and specify the length (semantics) as character.
  • For SQL Server char and varchar columns greater than 1000 bytes in length, define the Oracle columns as CLOB.
  • For SQL Server nchar columns less than or equal to 1000 characters in length, define the Oracle columns as NCHAR equal in size or greater than the SQL Server ones.
  • For SQL Server nchar columns greater than 1000 characters in length, define the Oracle columns as NCLOB.
  • For SQL Server nvarchar columns less than or equal to 2000 characters in length, define the Oracle columns as NVARCHAR equal in size or greater than the SQL Server ones.
  • For SQL Server nvarchar columns greater than 2000 characters in length, define the Oracle columns as NCLOB.
  • For SQL Server binary and varbinary columns less than or equal to 2000 bytes in length, define the Oracle columns as RAW equal or greater than the SQL Server ones.
  • For SQL Server binary and varbinary columns greater than 2000 bytes in length, define the Oracle columns as BLOB.

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 on the source

Configure SharePlex and the database on the source system as follows.

Run Database Setup

Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.

Ensure that all tables have a primary key

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.

Configure replication

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:

  • r. identifies the source database as non-Oracle, in this case SQL Server.
  • database_name is the name of the SQL Server database. Important! database_name must be the actual name of the database, not a data source name (DSN).
  • src_owner.table is the owner and name of the source table.
  • tgt_owner.table is the owner and name of the target table.*
  • host is the name of the target system.
  • o. identifies the target database as Oracle.
  • SID is the ORACLE_SID of the target Oracle database.

Important!

  • If a database is case-sensitive, enclose the case-sensitive object names in quotes.
  • If the letter case of the column names on the source is different from the letter case of the target columns, for example the source is all capitals while the target is lower case, use the column mapping feature to map the column names in the configuration file. See Map source and target columns in the SharePlex Administration Guide for more information.

Source configuration example

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

Configure SharePlex on the target

  1. Make certain that the database setup meets all of the requirements in Set up Oracle database objects for replication on page 1 .

  2. Run Database Setup for Oracle to establish a database account and connection information for SharePlex. See Database Setup for Oracle.
  3. See Set up an Oracle environment for replication for additional Oracle setup instructions.

Set up replication from SQL Server to MySQL

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.

Review column names

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 on the source

Configure SharePlex and the database on the source system as follows.

Run Database Setup

Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.

Ensure that all tables have a primary key

  • To replicate from a source SQL Server database to a target MySQL 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 MySQL target tables must have corresponding keys.

Configure replication

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:

  • database_name is the name of the SQL Server database. Important! database_name must be the actual name of the database, not a data source name (DSN).
  • src_owner.table is the owner and name of the source table.
  • tgt_owner.table is the owner and name of the target table.*
  • host is the name of the target system.
  • database_name is the name of the target database.

Important!

  • If a database is case-sensitive, enclose the case-sensitive object names in quotes.
  • If the letter case of the column names on the source is different from the letter case of the target columns, for example the source is all capitals while the target is lower case, use the column mapping feature to map the column names in the configuration file. See Map source and target columns in the SharePlex Administration Guide for more information.

Source configuration example

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

Configure SharePlex on the target

  1. Make certain that the database setup meets all of the requirements in Open Target checklist .

  2. Run Database Setup for MySQL (mysql_setup) to establish a database account and connection information for SharePlex. For more information, see Database Setup for MySQL.

Set up replication from SQL Server to PostgreSQL

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.

Review column names

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 on the source

Configure SharePlex and the database on the source system as follows.

Run Database Setup

Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.

Ensure that all tables have a primary key

  • To replicate from a source SQL Server database to a target PostgreSQLdatabase, 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 PostgreSQL target tables must have corresponding keys.

Configure replication

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:

  • database_name is the name of the SQL Server database. Important! database_name must be the actual name of the database, not a data source name (DSN).
  • src_owner.table is the owner and name of the source table.
  • tgt_owner.table is the owner and name of the target table.*
  • host is the name of the target system.
  • database_name is the name of the target database.

Important!

  • If a database is case-sensitive, enclose the case-sensitive object names in quotes.
  • If the letter case of the column names on the source is different from the letter case of the target columns, for example the source is all capitals while the target is lower case, use the column mapping feature to map the column names in the configuration file. See Map source and target columns in the SharePlex Administration Guide for more information.

Source configuration example

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

Configure SharePlex on the target

  1. Make certain that the database setup meets all of the requirements in Open Target checklist .

  2. Run Database Setup for PostgreSQL (pg_setup) to establish a database account and connection information for SharePlex. For more information, see Database Setup for PostgreSQL.

Set up replication from SQL Server to Kafka

Overview

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.

Guidelines for posting to Kafka

  • A SharePlex Post process acts as a Kafka producer. A SharePlex Post process can write to one or more topics that have one or more partitions.
  • The SharePlex Post process does not create a topic itself, but you can configure the Kafka broker to auto-create topics.

Configure SharePlex on the source

When replicating data to Kafka, configure the source database and SharePlex on the source system as follows.

Run Database Setup

Run Database Setup for SQL Server to establish a database account and connection information for SharePlex. See Database Setup for SQL Server.

Ensure that all tables have a primary key

  • To replicate from a source SQL Server database to any target, 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 target tables must have corresponding keys.

Configure replication

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:

  • database_name is the name of the source SQL Server database.
  • src_owner.table is the owner and name of the source table.
  • !kafka is a required keyword indicating SharePlex is posting to Kafka.
  • :tgt_owner.table is optional and specifies the owner and name of a target table. Use this feature if you want the data to appear as if it came from a table other than the source table. Allow no spaces between !kafka and :tgt_owner.table. Type case-sensitive names in the correct case and enclose them within double quotes, as in "MySource"."MyTable."

  • 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.

Source configuration example

Datasource:r.mss1

my_source.my_table !kafka sysprod

Configure SharePlex on the target

These instructions configure the SharePlex Post process to connect to Kafka. You must have a running Kafka broker.

To configure post to Kafka

  1. Create a Kafka topic.
  2. Start sp_cop. (Do not activate the configuration yet.)
  3. Run sp_ctrl.

  4. 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.

Set the Kafka record format

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.

View and change Kafka settings

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:

  • queue queuename is the name of a Post queue. Use this option if there are multiple Post processes.
  • property and value are shown in the following table.

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:

  • A fixed partition number: Directs Post to post messages only to the specified partition number. For example, setting it to 0 directs Post to post only to partition 0. This option is suitable for use in testing or if the target has multiple channels of data posting to the same Kafka topic.
  • The keyword rotate: Directs Post to apply messages to all of the partitions of a topic in a round-robin fashion. The partition changes with each new message. For example if a topic has three partitions, the messages are posted to partitions 0,1,2,0,1,2, and so on in that order.
  • The keyword rotate trans: This is similar to the rotate option, except that the partition is incremented with each transaction rather than with each message. For example, if a topic has three partitions, the messages are posted to partition 0 until the commit, then to partition 1 until the commit, and so on in that order. This option is suitable if you are replicating multiple tables to a single topic. It allows you to distribute data across several partitions, while still preserving all of the operations of a transaction together in a single partition. This enables a consumer that reads from a single partition to receive a stream of complete transactions.
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:

  • The output must be in JSON. Set the record property of the format category to json:

    target x.kafka set format record=json

  • Commits must be disabled. Set the commit property of the json category to no:

    target x.kafka set json commit=no

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.

Set recovery options

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.

Example

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

Related Documents