Chat now with support
Chat with Support

SharePlex 9.2.3 - Upgrade Guide for SQL Server Source

System Requirements for SQL Server Capture

This chapter contains the requirements when capturing from a SQL Server source database and replicating to a supported target.

SharePlex support for SQL Server as a target is documented in "Set up replication from Oracle to SQL Server" in the SharePlex Installation and Setup Guide for Oracle Source.

Supported Operating Systems

The following operating systems are supported for capture from a supported SQL Server database and replication to a supported target database. For a list of supported source and target databases for SQL Server capture, see Supported Source and Target Databases.

SharePlex supports only the 64-bit version of the listed operating systems.

Notes:

  •  SharePlex supports the following operating systems only.
  • The SQL Server database is supported only on the listed Windows operating systems.

Supported Operating Systems for Source

  • Windows Server 2008, 2008 R2, 2012, 2012 R2, and 2016

Supported Operating Systems for Target

  • AIX 7.1 and 7.2
  • CentOS Linux 5.x, 6.x, and 7.x
  • HP-UX 11.31 Itanium
  • Oracle Linux (RHCK and UEK) 5.x, 6.x, and 7.x
  • RHEL Linux 5.x, 6.x, and 7.x
  • SuSE SLES 11.x, 12.x, and 15
  • Oracle Solaris (SPARC and x86) 10 and 11
  • Windows Server 2008, 2008 R2, 2012, 2012 R2, and 2016

Supported Source and Target Databases

When capturing from a SQL Server database and replicating to a target database, the following database types and versions are supported for source and target.

Supported SQL Server Sources Supported Targets

Supported versions

  • SQL Server 2008, 2008 R2, 2012, 2014, 2016, and 2017.

Supported versions for a cloud-based source:3

  • SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017 on an Amazon EC2 virtual machine or Microsoft Azure IaaS.

 

Supported database types and versions:

  • SQL Server 2008, 2008 R2, 2012, 2014, 2016, and 2017.

    NOTE: The SQL Server database is supported only on the Windows Server operating system.

  • Oracle 11gR2, 12cR1, 12cR2, 18c

    These Oracle versions are also supported when running on Oracle Exadata

  • MySQL 5.5, 5.6, 5.7
  • PostgreSQL 9.4, 9.5, 9.6, and 10
  • EDB Postgres Advanced Server 9.4, 9.5, 9.6, and 10 1

Supported message queues:

  • Apache Kafka 0.8.x, 0.9.x, 0.10.x, 0.11.x, 1.0.x, 1.1.x

Supported database types and versions for cloud-based target:

  • Amazon Aurora PostgreSQL-Compatible Edition
  • Amazon Aurora MySQL-Compatible Edition
  • Amazon RDS for SQL Server. 2  For supported versions, see Supported database types and versions above.

  • Databases listed in Supported database types and versions above are also supported when they are deployed on Amazon EC2, Microsoft Azure IaaS, or Microsoft Azure PaaS2.

Table Notes:

  1. SharePlex does not support CESU-8 data replication to EDB Postgres Advanced Server 10.

  2. In Microsoft Azure PaaS or Amazon RDS, SharePlex cannot be installed because there is no access to the underlying operating system. You must install SharePlex on a different server. You then configure SharePlex to interact with the target database through a remote connection. Because the connection originates locally, use the target database ODBC driver that matches the local server.

  3. Capture from a SQL Server database deployed on Microsoft Azure Platform as a Service (PaaS) or in Amazon RDS is not supported.

Additional Requirements

Requirement Details
ODBC Driver

For the following supported target databases, the applicable ODBC driver is required. Use the ODBC driver and version recommended by the vendor.

  • SQL Server
  • MySQL
  • PostgreSQL
  • EDB Postgres Advanced Server

Conditions of support

This section shows the limitations of capturing from a SQL Server source.

  • SQL Server native replication and SharePlex replication cannot be used at the same time for the same database.
  • All SQL Server tables in the SharePlex replication configuration must be defined in the database with a primary key. A SharePlex key definition is not sufficient, nor can SharePlex use all columns as a key. You can use SharePlex filtering features in the configuration file to omit tables that do not have a primary key.
  • A transaction must COMMIT to the SQL Server source before it can be captured by SharePlex.
  • Replication between tables in same database is not supported. Replication between tables in different databases in the same or another SQL Server instance is supported.

Supported operations

  • SharePlex supports only DML replication from SQL Server to any of the supported targets.
  • SharePlex does not support the replication of SQL Server DDL operations.

The DML operations supported are: INSERT, UPDATE, and DELETE operations between SQL Server source tables and supported target tables that contain supported column types.

Notes:

  • SharePlex only replicates DML changes that are made to SQL Server source objects that are listed in the configuration file.
  • SharePlex requires SQL Server Replication to be installed on the source system. See the SharePlex Installation Guide for more information.

Supported data types: SQL Server to SQL Server

SharePlex supports DML operations between SQL Server databases that contain the following SQL Server data types:

  • bigint
  • binary(n)
  • bit
  • char(n)
  • date
  • datetime
  • datetime2(p)
  • datetimeoffset(p)
  • float(p)
  • image
  • integer 1
  • money
  • nchar(n)
  • ntext
  • numeric(p,s)
  • nvarchar(n)
  • real
  • smalldatetime
  • smallint
  • text
  • time(p)
  • tinyint
  • uniqueidentifier
  • varbinary(n)
  • varchar(n)

NOTEs:

  1. Integer column with Identity property: In addition to the Integer data type, an Integer column with the Identity property assigned to it is also supported.

Supported data types: SQL Server to Oracle and Open Target

The following tables show the supported data type mappings between a SQL Server source and a given Oracle or Open Target database. These are the mappings that have been tested and are known to be successful across a variety of use cases. Mapping to unsupported data types may succeed but has not been tested.

SQL Server to Oracle supported data types

SQL Server Oracle

bigint

NUMBER

binary(n)

RAW (n)

bit NUMBER (1,s)
char(1:3999) CHAR (n)
char(4000:) CLOB

date

DATE

datetime TIMESTAMP (3)
datetime2(p) TIMESTAMP (p)
datetimeoffset(p) TIMESTAMP (p) WITH TIME ZONE
float(n) NUMBER
image BLOB

integer

NUMBER

money NUMBER
nchar(1:3999) NCHAR(n)
nchar(4000:) NCLOB
ntext NCLOB
numeric(p,s) NUMBER (p,s)
nvarchar(1:3999) NVARCHAR2 (n)
nvarchar(4000:) NCLOB

nvarchar(max)

NCLOB

real

NUMBER

smalldatetime

DATE

smallint

NUMBER

text CLOB

time(p)

DATE

tinyint

NUMBER

uniqueidentifier CHAR (36)
varbinary(n) RAW (n)

varbinary(max)

BLOB

varchar(1:3999) VARCHAR2 (n)
varchar(4000:) CLOB

varchar(max)

CLOB

SQL Server to MySQL supported data types

SQL Server MySQL

Notes

bigint BIGINT  
binary(1:255) BINARY (n)

 

binary(256:) LONGBLOB

 

bit TINYINT  
char(1:255) CHAR (n)

 

char(256:) LONGTEXT

 

date DATE

 

datetime DATETIME (0)

 

datetime2(p) DATETIME (p)

MySQL datetime supports up to 6 fractional digits, whereas SQL Server datetime2 supports up to 7 fractional digits.

float(n) FLOAT

 

image LONGBLOB

 

integer INTEGER

 

money NUMERIC (20,4)  
nchar(1:255) CHAR (n)

 

nchar(256:) LONGTEXT
ntext LONGTEXT
numeric(p,s) NUMERIC (p,s)

 

nvarchar(n)

VARCHAR (n)

The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

nvarchar(max)

LONGTEXT

real

FLOAT

 

smalldatetime

DATETIME (0)

 

smallint

SMALLINT

 

text

LONGTEXT

 

time(p)

TIME (p)

 

tinyint

TINYINT

 

uniqueidentifier CHAR (36)  

varbinary(1:255)

VARBINARY (n)

 

varbinary(256:) LONGBLOB  

varbinary(max)

LONGBLOB

 

varchar(n)

VARCHAR (n)

The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

varchar(max)

LONGTEXT

 

SQL Server to PostgreSQL supported data types

SQL Server PostgreSQL
bigint NUMERIC
bit BOOLEAN
char(n) CHAR(n)
date DATE
datetime TIMESTAMP(3)
datetime2(p) TIMESTAMP(p)
datetimeoffset(p) TIMESTAMP(p) WITH TIME ZONE
float(25:) DOUBLE PRECISION
float(1:24) REAL
integer INTEGER
money NUMERIC(20,4)
nchar(n) CHAR(n)
ntext TEXT
numeric(p,s) NUMERIC(p,s)
nvarchar(n) VARCHAR(n)
nvarchar(max) TEXT
real REAL
smalldatetime TIMESTAMP(0)
smallint SMALLINT
text TEXT
time(p) TIME(p)
tinyint SMALLINT
uniqueidentifier CHAR(36)
varchar(n) VARCHAR(n)
varchar(max) TEXT

SQL Server to Tibero supported data types

SQL Server Oracle

bigint

NUMBER

binary(n)

RAW(n)

bit NUMBER(1,s)
char(1:3999) CHAR(n)
char(4000:) CLOB

date

DATE

datetime TIMESTAMP(3)
datetime2(p) TIMESTAMP(p)
datetimeoffset(p) TIMESTAMP(p) WITH TIME ZONE
float(n) NUMBER
image BLOB

integer

NUMBER

money NUMBER
nchar(1:3999) NCHAR(n)
nchar(4000:) NCLOB
ntext NCLOB
numeric(p,s) NUMBER(p,s)
nvarchar(1:3999) NVARCHAR(n)
nvarchar(4000:) NCLOB

nvarchar(max)

NCLOB

real

NUMBER

smalldatetime

DATE

smallint

NUMBER

text CLOB

time(p)

DATE

tinyint

NUMBER

uniqueidentifier CHAR(36)
varbinary(n) RAW(n)

varbinary(max)

BLOB

varchar(1:3999) VARCHAR2(n)
varchar(4000:) CLOB

varchar(max)

CLOB

 

SQL Server to XML supported data types

SQL Server

XML

int

decimal

numeric

decimal

smallint

decimal

tinyint

decimal

float

decimal

real

decimal

date

dateTime

datetime

dateTime

datetime2

dateTime

datetimeoffset

dateTimeStamp

smalldatetime

dateTime

time

dateTime

char

string

nchar

string

text

coob

ntext

clob

nvarchar

string

binary

base64Binary

varbinary

base64Binary

image long

base64Binary

Supported SharePlex features

The following table shows whether or not SharePlex features are supported when SQL Server is the source.

SharePlex feature Supported with Oracle Target Supported with OpenTarget
Replication from SQL Server tables to any target where the version of SharePlex is earlier than 9.0. No No
Auto-add of new tables that satisfy a wildcard No No
flush command Yes Yes
reconcile command (target instantiation) Yes Yes
compare/compare using and repair/repair using commands No No
copy/copy using and append/append using commands No No
Hash horizontally partitioned replication No No
Column-based horizontally partitioned replication No No
Vertically partitioned replication Yes Yes
Column mapping Yes Yes
Key definition (SQL Server tables must have a defined key) No No
Build configuration with scripts No No
Named queues Yes Yes
Post Enhanced Performance No No
Transformation Yes No
Conflict resolution Yes No
Peer-to-peer replication (bi-directional) No SQL Server target only
Consolidated replication (many to one) Yes Yes
Broadcast replication (one to many) Yes Yes
Cascading replication (source-intermediary-target) Yes Yes
High availability replication (active/passive bi-directional) No No
Change history target (CDC) Yes No
Data encryption No No
Data compression No No
SSH No No
auth_hosts file No No
Monitoring scripts No No
SNMP monitoring No No
Continue posting on error (SP_OPX_CONT_ON_ERR) Yes Yes
Suspend on out of sync errors (SP_OPX_OUT_OF_SYNC_SUSPEND) Yes Yes
Reduced key (SP_OPX_REDUCED_KEY) Yes Yes
sp_ctrl commands See the command documentation in the Reference Guide. See the command documentation in the Reference Guide.
Logical Transaction Rollback on out-of-sync transactions Yes No

 

Related Documents