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

Installer checklist

Requirement Completed? (Y/N)

Assign a directory to store the downloaded SharePlex installation package.

This directory requires approximately the following disk space:

  • Unix and Linux: 200 MB
  • Windows: 60 MB plus 400 MB of temporary disk space

It can be removed after SharePlex is installed.

 

Plan the SharePlex product directory.

You can create a directory for the SharePlex software files or let the SharePlex installer create it. This directory requires approximately the following disk space:

  • Unix and Linux: 120 MB
  • Windows: 600 MB plus 20 MB for the MKS Toolkit®

Install this directory on the following:

  • Unix and Linux: a separate filesystem from the one that contains the database.
  • Windows: a separate internal hard drive or partition from the one that contains the database.

Do not install SharePlex on a raw device.

 

Plan the SharePlex variable-data (working) directory.

This directory is installed by the SharePlex installer with a name of your choosing. It contains the working data and varies greatly in size in correlation to the volume of data being generated. Install this directory on a separate filesystem from the one that contains the database, but not on a raw device.

To replicate data from more than one database on a system, use a variable-data directory for each one. Ideally they should be on different filesystems.

Do not install the variable-data directory within the SharePlex product directory. Both directories contain identically named files, and SharePlex utilities that clean up the environment (if this becomes necessary) could remove the wrong files. You can install both directories under one parent directory if desired.

Note: Always monitor disk usage when there is an active SharePlex configuration, especially when there are peaks in transaction activity.

 

Create the SharePlex security groups.

SharePlex provides three security groups to enable access control through sp_ctrl. On Unix and Linux, unless you install SharePlex as a root user, the SharePlex Administrator user and the SharePlex admin group must exist prior to installation. For more information, see Assign SharePlex users to security groups.

Note: If you install as root, you are prompted by the installer to create these groups.

 

Get a valid SharePlex license key.

You must have a valid permanent or trial license key from Quest to run SharePlex. The installer prompts for the license key and the text string in the Site Message that Quest Software provides with the license.

The current license model for SharePlex is to license for a specific host, which depending on edition is licensed by core(s) or socket(s) and specific message repository (i.e. database, JMS/text files) etc. Specifics of license terms should be obtained from your account manager.

 

Windows system checklist

Requirement Completed? (Y/N)

Confirm that the Windows version is supported.

Review the SharePlex Release Notes to make certain your operating system is supported.

 

Be prepared to restart the system.

On the Windows platform, SharePlex installs the MKS Toolkit® operating environment from Parametric Technology Corporation (PTC). The default folder for the MKS Toolkit® is C:\Program Files\MKS Toolkit.

Set system permissions so that the MKS Toolkit files cannot be moved or removed after they are installed.

If this is a first-time MKS Toolkit installation, you will be prompted to restart the system.

 

Adjust the page size.

SharePlex needs an additional 200 MB of page file size if more than 80 percent of the current total page file size is being used. Greater page size enables SharePlex to process large transactions more quickly.

 

Assign a user who will own the SharePlex directories.

Assign a member of the Windows Administrator group to own the SharePlex installation and variable-data directories. This user must exist before you run the SharePlex installer and must have system privileges to read the Oracle redo logs.

 

(Oracle) Verify the Oracle Registry entries.

(Test machines only) On machines where Oracle has been installed and uninstalled many times, the Oracle entries in the Registry may be corrupted. Before you install SharePlex on a test machine, uninstall all Oracle software and delete all Oracle Registry entries. Then, re-install Oracle by using the Oracle installation program, which creates Registry entries correctly. SharePlex relies on these entries to obtain database environment information.

 

(Oracle) Set ORACLE_HOME as the first entry in the PATH variable.

SharePlex expects the path to the Oracle binaries to be the first entry in the Windows PATH system variable. Change the variable, if needed, and verify that the path is correct.

 

SQL Server source checklist

Requirement Completed? (Y/N)

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 you are using SharePlex for multiple 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.

 

Ensure that tables have a primary key

  • To capture from a source SQL Server 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.
  • All target tables must have corresponding keys.

 

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 you are using SharePlex for multiple 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 an Oracle target

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.

 

Address varchar(max) issues

The following issues are known to exist when capturing SQL Server varchar(max) data types:

Length compatibility:

If replicating varchar(max) data to a SQL Server target, make certain that the data size does not exceed 1GB in length. Although SQL Server supports varchar(max) data of up to 2 GB in length, the Windows ODBC driver supports VARCHAR(max) data of up to 1 GB in length. Sample ODBC error message, which is included in the Post error message: [ODBC SQL Server Driver]Invalid precision value.

Bulk inserts:

Bulk inserts that contain tables with large-value varchar(max) columns causes the SQL Server replication components (used by SharePlex) to truncate the values. The truncation is from size 7900 onward. For example, a column value of 8001 bytes is replicated with a size of 101, and a column value of 11000 is replicated with a size of 3100. To work around this issue, configure the table to store large-value data types out-of-row by issuing the following command:

exec sp_tableoption 'owner.table','large value types out of row', 'on'

 

Open Target checklist

All Open Targets
Requirement Completed? (Y/N)

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.

 

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.

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.

 

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.

 

 

SQL Server Target
Requirement Completed? (Y/N)

Configure a System 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.

Important!

If you are using SharePlex for multiple 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.

 

 

 

PostgreSQL Target
Requirement Completed? (Y/N)

Configure a System Data Source Name (Windows installations)

If you are replicating data to a PostgreSQL target on Windows, you must use a DSN (data source name) as the connection method. This DSN must exist before you install SharePlex.

 

Add a source entry to the PostgreSQL pg_hba.conf file

For a PostgreSQL target (non-cloud), make certain that the PostgreSQL pg_hba.conf file has an entry for the SharePlex source server IP address, which gives that server access to PostgreSQL target server and its databases.

Example entry:

host    all     all     sourceip     md5
 
Related Documents