SharePlex 9.0.2 - Preinstallation Checklist

Confirm database ownership

Make certain that the SQL Server source databases that you will be replicating are owned by a SQL Server user, not a domain user.

Configure a Data Source Name

Create a System (not User) Data Source Name (DSN) for the SQL Server database on the Windows system. The DSN can use either Windows NT authentication or SQL Server authentication. If you configure the DSN to use NT authentication and are using SQL Server 2012 or later, grant the NTAuthority\SYSTEM user the sysadmin fixed server role. ( For earlier versions of SQL Server, sysadmin is granted to the NT Authority\SYSTEM user by default.)

Test connection to the database through this DSN.

If SharePlex will be replicating data from, or to, databases that are in different SQL Server instances on the same system, each of those databases must have a unique name. Because SharePlex identifies a database by its name, if two or more databases have the same name, SharePlex will only connect to one of them.

If databases in different SQL Server instances have the same name but you are only using SharePlex for one of them, the names can remain identical without causing connection conflicts.

Install SQL Server Replication Components

SharePlex Capture makes use of the underlying components of the native SQL Server replication components. SQL Server Replication must be installed before you install and set up SharePlex, and then the SharePlex Database Setup utility must be run to configure a local Distribution Agent. This utility is typically run as part of the SharePlex installation procedure.

Be ready to quiesce the source database

Before you activate a configuration to capture from a SQL Server source, you must quiesce the tables that are in that configuration. The tables must remain quiesced for the entire activation process.

Satisfy requirements for database naming

These use cases can cause connection problems for SharePlex unless resolved as recommended.

Names of replication databases are identical among local instances

If SharePlex will be replicating data from, or to, databases that are in different SQL Server instances on the same system, each of those databases must have a unique name. Because SharePlex identifies a database by its name, if two or more databases have the same name, SharePlex will only connect to one of them.

If databases in different SQL Server instances have the same name but you are only using SharePlex for one of them, the names can remain identical without causing connection conflicts.

Source and target have the same name, but different case

SQL Server source and target databases that have the same name but different case collation can cause connection problems for SharePlex. If you cannot either change the case of the databases to be identical, or rename one of them to a different name, you can use the following workaround after you install SharePlex and run the mss_setup utility.

Workaround:

1. On the target system, open the connections.yaml file in the SharePlex variable-data directory.
2. Copy the entire set of parameters, but do not delete the original set.
3. Paste the copied set of parameters after the original set.
4. In the pasted set of parameters, change the case of the r.database parameter to match the case of the source database.

Example:

r.my_database:
  database: MY_DATABASE
  dsn: my_sp1
  dstype: sqlserver
  password: 558ec793ac
  plugin: sqlserver
  plugin_direction: target
  plugin_version: 1
  user: qarun
r.MY_DATABASE:
  database: MY_DATABASE
  dsn: my_sp1
  dstype: sqlserver
  password: 558ec793ac
  plugin: sqlserver
  plugin_direction: target
  plugin_version: 1
  user: qarun

Considerations if replicating to Oracle

These use cases apply only if you are replicating from SQL Server to an Oracle target.

Character column definitions:

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.

To view a table representing these relationships, see Set up replication from SQL Server to Oraclein the SharePlex Installation and Setup Guide.

Letter Case:

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.

Perform any required database upgrades.

Perform any required database upgrades before you install SharePlex. This ensures that SharePlex gets the most current object definitions when you run Database setup during the installation and setup steps.

Confirm the database release version.

Verify that the release version of the database is supported by SharePlex. For more information, see Basic system requirements.

Consider character sets

When replicating to an Open Target target (non-Oracle target), SharePlex supports replication from any Oracle Unicode character set and the US7ASCII character set. SharePlex posts data to Open Target in the Unicode character set, and therefore if the source data is Unicode or US7ASCII, no conversion on the target is required.

However, if the following are true, conversion is required on the target:

• If the character set of the source data is anything other than Oracle Unicode or US7ASCII, you must install an Oracle client on the target to perform the conversion to Unicode for posting to the target.
• If the data must be posted to the target database in any character set other than Unicode, you must install an Oracle client on the target to perform the conversion and use the target command to identify the target character set for Post to use.
• If you are replicating LOB data, conversion is required regardless of what the source character set is.

To perform conversion with an Oracle client on Linux

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.
2. Set ORACLE_HOME to the client installation. Set ORACLE_SID to an alias or a non-existing SID. SharePlex does not use them and a database does not have to be running.
3. Download the Oracle-based SharePlex installer, rather than the Open Target installer, to install SharePlex on the target system. The Oracle-based installer includes functionality that directs Post to use the conversion functions from the Oracle client library to convert the data before posting to the target database.
4. Follow the instructions for SharePlex for Oracle (not the ones for installing on Open Target).
5. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To perform conversion with an Oracle client on Windows

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.
2. In the SharePlex Registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\QuestSoftware\SharePlex\port_number, set ORACLE_HOME to the Oracle client installation location, and set ORACLE_SID to an alias or a non-existing SID. No Oracle database is required. SharePlex only needs to use the client libraries.
3. SharePlex using the Windows installer.
4. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To apply Unicode and US7ASCII data without conversion

If the source data is Unicode or US7ASCII and you are not replicating LOB data, no conversion or Oracle client is needed. Set the SP_OPX_NLS_CONVERSION parameter to 0 to disable conversion, and then restart Post if it is running.

Consider Case

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.

Install the appropriate ODBC driver.

Verify that the appropriate ODBC driver is installed for your target, and install one if there is not one present.

For Microsoft SQL Server, make certain to do the following:

Install the Microsoft SQL Server ODBC Driver. It must be that driver, not the Microsoft SQL Server Native Client, or SharePlex will return an error when you run the Database Setup utility to configure Post to connect to the database. To tell the difference between the two drivers:

• The Microsoft SQL Server ODBC Driver has versions such as 06.02.9200.
• The Microsoft SQL Server Native Client has versions such as 11.00.3513.

To test a driver with SharePlex you can use the OTS utility. You can use a standalone version of this utility before you install SharePlex, or you can run the utility from the installation directory after you install SharePlex. See the OTS documentation in the SharePlex Reference Guide for more information. Note: SharePlex is already certified to work through ODBC with SQL Server, PostgreSQL, and Sybase targets.

Enable case sensitivity on key columns

Enable case-sensitivity for the data of any character-based primary key columns or columns that form a unique index. This ensures that Post compares the correct source and target key values so that it updates the correct target row and prevents unique constraint errors. Unless the key values are case sensitive, cases like the following can happen:

Create table Sales (CustName varchar(20) primary key);

insert into Sales values (‘abc company’);

(Succeeds)

insert into Sales values (‘ABC Company’);

(Fails with unique constraint violation error)

Disable triggers, cascade deletes, and foreign keys on the target tables.

Triggers, cascaded DELETES, and foreign keys must be disabled on Open Target tables.

DML changes resulting from triggers, cascaded DELETES, and foreign keys on the source system enter the transaction log and are replicated to the target database by SharePlex. If the same mechanisms are allowed to occur on the target parent table, they initiate changes to the child tables that are duplicated through replication. These duplicate operations cause out-of-sync errors.

All tables with foreign keys to one another must all be included in the replication configuration for accurate replication of the source foreign key results. All tables with referential constraints must exist in the target database. If you leave one or more out, the referential integrity could become corrupted.

To get additional information

Additional post-installation setup steps are required to support Open Target database targets. For more information, see Set up replication between different database types. of the SharePlex Installation Guide.