SharePlex 9.2.3 - Installation and Setup for SQL Server Source

Set up replication to a cloud-hosted target

There are some differences in the way that SharePlex installs in an IaaS cloud environment and in a PaaS cloud environment. These differences are 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 a list of supported cloud configurations, see the "Basic System Requirements" section in the SharePlex Release Notes.

Installation in an IaaS (accessible) environment

If your cloud database service is a true IaaS virtual computing environment, you can install and run a custom application environment, access the operating system, and manage access permissions and storage. In this environment, SharePlex is installed directly on the cloud server just as you would install it locally, without any special setup requirements.

See the regular preinstallation and installation instructions in this manual. To determine the instructions that apply to your environment, review the table of contents or bookmarks as appropriate for the documentation delivery platform that you are using.

Installation in a PaaS (non-accessible) environment

You can install SharePlex to post to a PaaS target in one of the following ways:

• You can use your on-premises production source server to run all of the SharePlex replication components. In this setup, both source and target replication processes (and their queues) are installed on one server. The SharePlex Post process connects through a remote connection to the target cloud database.

  Note: In a high-volume transactional environment, the buildup of data in the post queues and the presence of multiple Post processes may generate unacceptable overhead for a production system. In that case, you should use an intermediary server.

• You can use an on-premises intermediary server to run the Import and Post components (and the post queues). Post connects to the cloud target through a remote connection. This method removes most of the replication overhead from the source server.

To post to a PaaS target from the source server

All steps are performed on the source server.

1. Complete the SharePlex preinstallation checklist.

2. Install SharePlex. See:

   • Install SharePlex on Windows

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

   • Database Setup for SQL Server

   Important:

   Reply Y when asked if the database will be used as a source.

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

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

   Important:

   • If the target is Aurora, use the mysql_setup utility.

   • If the target is SQL Server cloud, when asked if the database will be used as a source, enter N.

   • (All cloud targets) Specify the full target database name when prompted for the connection string.

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.

   In the following example using a MySQL target, source3 is the source system and sptest3 is the target database.

   datasource:r.mysource
   #source tables	target tables	routing map
   HR.EMP	"sptest3"."emp"	source3@r.sptest3

To post to a PaaS target from an intermediary server

1. Complete the SharePlex preinstallation checklist.

2. On the source server, install SharePlex for the source database. See:

   • Install SharePlex on Windows

3. On the source server, run the appropriate database setup utility for the source database. See:

   • Database Setup for SQL Server

   Important:

   • (Oracle setup only) When asked whether this is a bequeath connection, enter Y to use bequeath, unless this system is RAC.

   • Reply Y when asked if the database will be used as a source.

4. On the intermediary server, install the appropriate ODBC client of the target cloud database.

5. On the intermediary server, install SharePlex for the target cloud database. See:

   • Install SharePlex on Linux/Unix for Oracle Database
   • Install SharePlex on Windows

6. On the intermediary server, run the appropriate database setup utility for the target cloud database. See Database Setup Utilities.

   Important:

   • If the target is Aurora, use the mysql_setup utility.

   • If the target is SQL Server, reply N when asked if this database will be used as a source.
   • (All targets) Specify the full target database name when prompted for the connection string.

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

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

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

   In the following example using a MySQL target, intermediary3 is the intermediary system and sptest3 is the target cloud database.

   datasource:r.mysource
   #source tables	target tables	routing map
   HR.EMP	"sptest3"."emp"	intermediary3@r.sptest3

Generic SharePlex demonstration-all platforms

Contents

Prework for the demonstrations

Before you run the basic demonstrations, have the following items available.

Tables used in the demonstrations

You will replicate splex.demo_src from the source system to splex.demo_dest on the target system. These tables are installed by default into the SharePlex schema, which in these demonstrations is "splex." Your SharePlex schema may be different. Verify that these tables exist.

Description of the demo tables.

INSERT scripts

• Create a SQL script named insert_demo_src that inserts and commits 500 rows into the splex.demo_src table. You will run this script during some of the demonstrations.
• If you will be using the demonstration of named post queues, create a SQL script named insert_demo_dest that inserts and commits 500 rows into the splex.demo_dest table. You will run this script during some of the demonstrations.

Create and activate a configuration

SharePlex gets its replication instructions from a configuration file, which defines the objects that are to be replicated. The file specifies following:

• The datasource (source database) — the identifier of the source database.
• The source objects — the names of the objects that contain the data to be replicated.
• The target objects — the names of the target objects that will receive the replicated data.
• The routing map — the name of the target system and, if the target is a database, its identifier.

Note: This demonstration demonstrates replication from one database to another. It does not cover replication to a file or a messaging container.

Create a configuration file

Perform these steps on the source system. The demonstration objects are assumed to be in the schema named splex.

1. Run sp_ctrl.

2. Issue the following command to create a configuration file named sample_config in the default text editor.

   sp_ctrl(source)> create config sample_config

3. In the text editor, build your configuration file based on the appropriate template, as shown in the Configuration templates. Allow no spaces between the characters in a component (source specification, target specification, routing map), and place at least one space between each component.

4. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory of the variable-data directory.

5. In sp_ctrl, verify that the configuration file will activate successfully.

   sp_ctrl(source)>verify config sample_config

Configuration templates

SQL Server source to SQL Server or other Open Target target

where:

• source_database_name is the name of the source SQL Server database.
• target_system is the name or IP address of the target system.
• database_name is the name of the target database.

SQL Server source to Oracle target

where:

• source_database_name is the name of the source SQL Server database.
• target_system is the name or IP address of the target system.
• target_SID is the ORACLE_SID of the target database.

Activate the configuration

Perform these steps on the source system. When you activate a configuration, SharePlex is ready to capture transactional changes that are made to the specified source data.

1. Activate the configuration.

   sp_ctrl(source)> activate config sample_config

   Note: Configuration names are case-sensitive.

2. Confirm that the configuration activated successfully. The name sample_config should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)> list config

Troubleshooting Tips

If your configuration activation fails, isue the view config sample_config command to view the file. Compare it to the template and make sure all of the information you entered is correct. Make certain you specified the correct database identifier. Check your syntax for extra spaces or missing components.

Because the configuration file is not active, you can edit it directly with the following command:

sp_ctrl(source)> edit config sample_config

Save the changes, then re-try the activation.

Note: To change an active configuration, you must copy it to a new file first, and then edit and activate the copy. For more information, see Add or change objects in an active configuration in the SharePlex Installation and Setup Guide.