Chat now with support
Chat with Support

SharePlex 9.2.5 - 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

Configure replication to share or distribute data

These instructions show you how to set up SharePlex for the purpose of sharing or distributing data from one source system to one or more target systems. This strategy supports business requirements such as the following:

  • reporting to support real-time decision making
  • data sharing to support research and transparency requirements
  • data integration throughout an enterprise
  • customer service inquiries and other query-intensive applications
  • data auditing and archiving

Supported sources

Oracle and SQL Server

Supported targets

All

Capabilities

This replication strategy supports the following:

  • Replication to one or more target systems
  • Replication between databases on the same system
  • Replication between schemas in the same database (Oracle) 
  • Identical or different source and target names
  • Use of vertically partitioned replication
  • Use of horizontally partitioned replication
  • Use of named export and post queues
  • Use of transformation

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.
  • No DML or DDL should be performed on the target tables except by SharePlex. Tables on the target system that are outside the replication configuration can have DML and DDL operations without affecting replication.
  • If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.

Important! These instructions assume you have a full understanding of SharePlex configuration files. They use abbreviated representations of important syntax elements.

For more information, see Configure data replication.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following:

  • source_specification[n] is the fully qualified name of a source object (owner.object) or a wildcarded specification.
  • target_specification[n] is the fully qualified name of a target object or a wildcarded specification.
  • host is the name of a system where SharePlex runs. Different systems are identified by appending a letter to the names, like hostB.
  • db is a database specification. The database specification consists of either o. or r. prepended to the Oracle SID, TNS alias, or database name, as appropriate for the connection type. A database identifier is not required if the target is JMS, Kafka, or a file.

Important!

Replicate within the local system

Replication on the same system supports the following configurations:

  • Within one Oracle instance, replicate to different tables within the same schema or to the same table in different schemas.
  • Replicate between different SQL Server databases on the same system.
  • Replicate to from an Oracle instance to any SharePlex-supported target on the same system.

On the Windows platform, SharePlex does not support replication between Oracle databases that reside on the same system, but you can replicate to Open Target targets on the same system.

Configuration options

datasource_specification

   
source_specification1 target_specification1 hostA[@db]
source_specification2 target_specification2 hostA[@db]
Example

This example shows how you can replicate data to the same Oracle instance, to a different Oracle instance (Unix and Linux only), and to different target types, all on the same local system.

Datasource:o.oraA    
hr.emp hr.emp2 hostA@o.oraA
hr.sal hr.sal2 hostA@o.oraB
fin.* fin.* hostA@r.mss
act.* !file hostA

Configuration when using SharePlex Manager

Replication from and to the same machine omits an Export process. However, SharePlex Manager expects an export queue to exist. If using this configuration with SharePlex Manager, you must explicitly configure an export queue as follows. The hostA* component in the routing map creates the export queue and an Export process, which sends the data to an Import process, then the post queue.

datasource_specification

   
source_specification1 target_specification1 hostA*hostA[@db]
source_specification2 target_specification2 hostA*hostA[@db]

Replicate to a remote target system

Configuration options

datasource_specification

   
source_specification1 target_specification1 hostB[@db]
source_specification2 target_specification2 hostB[@db]
Example

The last line in this example shows how you can replicate data to different target types on the same remote target system.

Datasource:o.oraA    
hr.emp hr.emp2 hostB@o.oraB
hr.sal hr.sal2 hostB@o.oraB
fin.* !file hostB

Replicate to multiple target systems

This topology is known as broadcast replication. It provides the flexibility to distribute different data to different target systems, or all of the data to all of the target systems, or any combination as needed. It assumes the source system can make a direct connection to all of the target systems. All routing is handled through one configuration file.

For more information, see Configure replication through an intermediary system.

Configuration options

If the target specification is identical on all targets

If the target specification of a source object is identical on all target systems, you can use a compound routing map, rather than type a separate entry for each route. For more information, see Configure data replication.

datasource_specification

   
source_specification1 target_specification1 hostB[@db]+hostC[@db][+...]
source_specification2 target_specification2 hostC[@db]+hostD[@db][+...]
If the target specification is not identical on all targets
  • When the target specification of a source object is different on some or all target systems, you must use a separate configuration entry to specify each one that is different.
  • You can use a compound routing map for routes where the target specifications are identical.

datasource_specification

   
source_specification1 target_specification1 hostB[@db]
source_specification1 target_specification2 hostC[@db]
Example

Note: This example does not cover all possible source-target combinations. The last entry in this example shows the use of horizontally partitioned replication to distribute different data from the sales.accounts table to different regional databases.

Datasource:o.oraA    
hr.emp hr.emp2 hostB@o.oraB
hr.emp hr."Emp_3" hostC@r.mssB
hr.emp !jms hostX
cust.% cust.% hostD@o.oraD+hostE@o.oraE
sales.accounts sales.accounts !regions
Related Documents