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

Create and populate SharePlex groups on Unix and Linux

Where and when to create the SharePlex groups on Unix and Linux depends on whether you install SharePlex as a root or non-root user.

  • If you install as non-root, create the groups in the /etc/group file before you run the SharePlex installer. In a cluster, create them on all nodes.*
  • If you install SharePlex as a root user, you can direct the installer to create the groups in the /etc/group file. If you install in a cluster, the installer creates the groups on the primary node, but you must create them yourself on the other nodes.

* The groups must exist because the installer adds the SharePlex Administrator user to the spadmin group during the installation process. In a cluster, this user is only added to the primary node. You must add the SharePlex Administrator user to the other nodes.

To create the groups in /etc/group

# groupadd spadmin

# groupadd spopr

# groupadd spview

To assign a user to a group

  1. Open the /etc/group file.
  2. Add the Unix or Linux user name to the appropriate group. To assign a list of user names to a group, use a comma-separated list (see the following example).

    spadmin:*:102:spadmin,root,jim,jane,joyce,jerry

    If the password field is null, no password is associated with the group. In the example, the asterisk (*) represents the password, “102” represents the numerical group ID, and spadmin is the group. The group ID must be unique.

  3. Save the file.

Users can verify their authorization levels by issuing the authlevel command in sp_ctrl.

Create and populate SharePlex groups on Windows

On Windows, the SharePlex groups are created in the Windows User Accounts control panel by the SharePlex installer. To assign users to these groups, use that control panel after you install SharePlex.

Users can verify their authorization levels by issuing the authlevel command in sp_ctrl.

Set up replication between SQL Server databases

Contents

 

About these instructions

SharePlex can post replicated SQL Server source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface. These instructions contain the basic setup steps to take to support this target.

For additional information, see the following:

  • For the data types and operations that are supported when using SharePlex to replicate from and to a SQL Server database, see the "System Requirements — SQL Server Capture" section in the SharePlex Release Notes.
  • For additional configuration options, activation steps, and monitoring information, see the SharePlex Administration Guide.
  • For reference documentation on SharePlex commands, parameters and utilities, see the SharePlex Reference Guide.

Configure SharePlex on the source

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

Run Database Setup

If you did not run Database Setup for SQL Server during the installation of SharePlex, run the utility now to establish a database account and connection information for SharePlex. Database Setup for SQL Server

Ensure that all tables have a primary key

To replicate from a source SQL Server database to a target 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. See the SQL Server source checklist for more information about these and other pre-configuration requirements for a SQL Server source.

All Oracle targets must have corresponding keys.

Ensure varchar(max) 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.

Configure replication

To configure replication from a SQL Server source to a SQL Server target, 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:

  • r. identifies the database as non-Oracle.
  • 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.

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. For more information, see Map source and target columns in the SharePlex Administration Guide.
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

If you did not run Database Setup for SQL Server during the installation of SharePlex, run the utility now to establish a database account and connection information for SharePlex. For more information, see Database Setup for SQL Server.

Set up replication from SQL Server to a different target type

Contents

 

About these instructions

This chapter contains instructions for configuring SharePlex to replicate from SQL Server to another type of target, for example SQL Server to PostgreSQL. This is known as heterogeneous replication.

These instructions highlight specific tasks that are pertinent to the flow of data between source and target. Refer to other topics in the SharePlex documentation as needed to complete the configuration, deploy any optional features that apply, and monitor and maintain the environment.

For additional information, see:

  • For the SharePlex-supported datastores, data types and operations that are supported by SharePlex, see the "System Requirements — SQL Server Capture" section of the SharePlex Release Notes.
  • For additional configuration options, activation steps, and monitoring information, see the SharePlex Administration Guide.
  • For reference documentation on SharePlex commands, parameters and utilities, see the SharePlex Reference Guide.
Related Documents