SharePlex 12.0 - Installation and Setup guide

Set up Replication from PostgreSQL to SQL Server

Overview

SharePlex can post replicated source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface.

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.

Configure SharePlex on the source

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

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 SQL Server.

where:

• dbname is a PostgreSQL database name of source
• src_schema.table is the schema 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.

Note: See Configure SharePlex to replicate data in the SharePlex Admin Guide.

Source configuration example

The following example replicates table SCOTT.EMP from PostgreSQL instance dbnameA to the MS SQL Server on host sysprod

Datasource: r.testdb
SCOTT.EMP    SCOTT.EMP   sysprod@r.mydb

Configure SharePlex on the target

Perform the following steps to 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 SQL Server (mss_setup) to establish a database account and connection information for SharePlex. For more information, see Database setup for SQL Server.

Installation and Setup for Cloud-Hosted Databases for PostgreSQL

Contents

Overview of SharePlex Setup on Cloud for PostgreSQL

SharePlex supports databases installed as services of Amazon Web Services (AWS), Google Cloud SQL for PostgreSQL, and Microsoft Azure. To view the cloud databases that SharePlex supports, refer to the Supported Cloud Platforms section in the SharePlex Release Notes document for the respective databases.

There are some differences in the way that SharePlex installs in an IaaS cloud environment, a PaaS cloud environment, and a SaaS cloud environment. These differences are only in the installation and configuration of SharePlex. Once installed and configured, SharePlex operates in the cloud the same way that it operates in on-premise installations. For detailed information regarding how SharePlex is installed in various environments, see Overview of SharePlex Setup on Cloud.

Post to PaaS Cloud from the Source Server for PostgreSQL

These instructions help you set up SharePlex to replicate to a target cloud database directly from the source server. All of the SharePlex processes will run on this server. In a high-volume transactional environment, this may generate unacceptable overhead on the production system. In that case, you should use an intermediary server.

See also:

Post to PaaS cloud from an intermediary server for PostgreSQL

Overview of SharePlex setup on cloud databases

Source PostgreSQL to cloud PostgreSQL

All steps are performed on the source server:

1. Complete the SharePlex pre-installation checklist.

2. Install SharePlex. See Install SharePlex on Linux/Unix for PostgreSQL Database.

3. Run pg_setup to establish connection information for the source PostgreSQL database. See Database Setup for PostgreSQL.

4. Add an entry in the local ODBC.ini or ODBCINST.ini files that points to the target cloud database. This enables you to query the target database from the source server.

5. Run pg_setup to establish connection information for the target cloud database. Run the utility as the master user that was created when the service was created. This user has the required DBA privileges.

6. Specify the following in the routing map of the SharePlex configuration file:

   • The name of the source host to specify the target system.

   • The PostgreSQL database name of the target cloud database.

   In the following example, source1 is the source system and postgresqldatabasename is the alias from ODBC files of the target cloud database.

   datasource: r.orcldbprim
   #source tables	target tables	routing map
   splex.demo_src	splex.demo_dest	source1@r.postgresqldatabasename

For more information, see Configure SharePlex to replicate data in the SharePlex Administration Guide.

Source PostgreSQL to cloud Open Target

All the below steps are performed on the source server:

1. Complete the SharePlex preinstallation checklist.

2. Install SharePlex. See: SharePlex on Linux/Unix for PostgreSQL Database.

3. Run the appropriate database setup utility for the source database. See:

   • Database Setup for PostgreSQL.

4. Install the appropriate ODBC client of the target cloud database.

5. Run the appropriate database setup utility for the target cloud database. See Database Setup Utilities.

6. Specify the following in the routing map of the SharePlex configuration file:

   1. the name of the source server as the target host.

   2. the name of the cloud database as the target database

For more information about how to specify configuration components, see Configure SharePlex to replicate data in the SharePlex Administration Guide.