Chat now with support
Chat with Support

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

Demonstration of replication

This section demonstrates the speed and accuracy of SharePlex replication. It can also be used to verify that SharePlex was installed and configured correctly.

Verify replication startup

This test verifies that replication is working properly.

Perform these steps in the native SQL interface of the database.

  1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

    truncate table splex.demo_src;

  2. TRUNCATE the target splex.demo_dest table.
  3. Insert and commit a record into splex.demo_src:

    insert into splex.demo_src values (‘Jim’, ‘123 Main Street’, ‘123-456-7890’);

    commit;

  4. Verify that the record exists in splex.demo_dest on the target system:

    select * from splex.demo_dest;

    The query should show the replicated record, and only that record.

Verify replication of large data volumes

This test verifies that SharePlex replicates large volumes of data quickly and accurately.

Perform these steps in the native SQL interface of the database.

  1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

    truncate table splex.demo_src;

  2. TRUNCATE the target splex.demo_dest table.
  3. Run the insert_demo_src script to insert 500 rows into the splex.demo_src table.
  4. Verify that all of the record exist in splex.demo_dest on the target system:

    select count (*) from splex.demo_dest;

    The count should match the number of records inserted by the Inserts script.

Verify queuing and continuity of replication

This test shows you how:

  • SharePlex queues replicated data on the source system if the target system is unavailable.
  • SharePlex resumes replication from where it left off when the target becomes available.
  1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

    truncate table splex.demo_src;

  2. TRUNCATE the target splex.demo_dest table.
  3. On the target, shut down SharePlex to simulate that this system is unavailable.

    sp_ctrl(source)> shutdown

  4. On the source, run the insert_demo_src script to insert records into splex.demo_src.
  5. On the source, issue the following command to show the status of the local SharePlex queues.

    sp_ctrl(source)> qstatus

    The output should show a backlog of messages.

    Notes:

    • If SharePlex on the target were running instead of stopped, there would be no messages in the queue. Replication of 500 rows (and the clearing of the queues) would typically happen faster than the time it took to run the script and then issue the qstatus command. To confirm that the queues are storing the records, continue to issue the command. The backlog value should be the same each time.
    • If your environment permits it, you can perform the same test with the same results by unplugging the network connection to the source system before you run the script.
  6. On the target, start sp_cop to allow replication to resume.
  7. On the target, run sp_ctrl.

  8. On the target, verify that the SharePlex processes started.

    sp_ctrl(target)> status

  9. On the target, verify that the records inserted by the script on the source system now exist in the target database.

    select count (*) from splex.demo_dest;

    The count should match the number of records inserted by the Inserts script.

Verify SharePlex capture recovery

This test shows how SharePlex recovers after an interruption to data capture.

  1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

    truncate table splex.demo_src;

  2. TRUNCATE the target splex.demo_dest table.
  3. On the source, stop the Capture process.

    sp_ctrl(source)> stop capture

  4. On the source, run the Insert script to generate a transaction to splex.demo_src.

  5. On the source, start the Capture process.

    sp_ctrl(source)> start capture

  6. On the source, verify that Capture is running and that it processed the data. Look at Capture state and Operations captured.

    sp_ctrl(source)> status

  7. On the target, use the native SQL interface to verify that all records inserted by the script on the source now exist in the target database.

    select count (*) from splex.demo_dest;

Demonstration of data compare and repair

This demonstration shows you how to use the SharePlex compare command to compare the source and target data, and then repair rows that are out of synchronization.

  1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

    truncate table splex.demo_src;

  2. TRUNCATE the target splex.demo_dest table.
  3. On the source, use the insert_demo_src script to insert rows into splex.demo_src.
  4. On the target, verify that all of the data posted to splex.demo_dest. If the command shows that data is still in the queue, issue the command again.

    sp_ctrl(target)>qstatus

  5. On the source, issue the compare command in sp_ctrl.

    sp_ctrl (source)>compare splex.demo_src

    Note: The command determines the correct target table from the configuration file.

  6. On the source, view the results of the comparison. There should be no rows out of synchronization.

    sp_ctrl(source)>compare status

  7. On the target, UPDATE splex.demo_dest to change the values of the NAME column in two or more rows. The UPDATE causes the source and target tables to be out of synchronization.

  8. On the source, issue the compare command again. It should show that the rows you updated in splex.demo_dest are out of synchronization.

    sp_ctrl(source)>compare splex.demo_src

  9. On the source, issue the repair command to repair the rows that are out of synchronization.

    sp_ctrl(source)> repair splex.demo_src

  10. On the source, verify that the repair was performed.

    sp_ctrl(source)>repair status

  11. On the target, you can manually verify that the repair was accurate by using a SELECT statement to view all rows in both tables.

    select * from splex.demo_src;

    select * from splex.demo_dest;

Demonstration of named post queues

This demonstration shows you how to use named post queues to process different tables through different, parallel Post processes to improve performance.

Clean up the replication environment

NOTE: The demonstration objects are assumed to be in the schema named splex.

  1. If you ran previous demonstrations, do the following:

    1. On the source and target, run sp_ctrl and issue the following command to shut down sp_cop.

      sp_ctrl(source)shutdown

      sp_ctrl(target)shutdown

    2. On the source and target, run the ora_cleansp or mss_cleansp utility according to the instructions in SharePlex utilities. This removes the queues from the previous demonstrations and deactivates the previous configuration.
  2. On the source and target, TRUNCATE the splex.demo_src and splex.demo_dest tables.

    truncate table splex.demo_src;

    truncate table splex.demo_dest;

Create a configuration file

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

You will configure the following:

  • demo_src on the source replicates through a post queue named q1 to target demo_dest.
  • demo_dest on the source replicates through a post queue named q2 to target demo_src.
  1. Run sp_ctrl.

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

    sp_ctrl(source)>create config postq_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 postq_config

Configuration templates

SQL Server source to Oracle target

datasource:r.source_database_name

splex.demo_src

splex.demo_dest

target_system:q1@o.target_SID

splex.demo_dest

splex.demo_src

target_system:q2@o.target_SID

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.
SQL Server source to SQL Server or other Open Target target

datasource:r.source_database_name

splex.demo_src

splex.demo_dest

target_system:q1@r.database_name

splex.demo_dest

splex.demo_src

target_system:q2@r.database_name

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

Activate the configuration

Important! Make certain you created the scripts described in Prework for the demonstrations.

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 postq_config

    Note: Configuration names are case-sensitive.

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

    sp_ctrl(source)>list config

Generate data

On the source, run the following scripts to insert data into the two local demonstration tables (in this demonstration, the demo_dest table is used as a source):

  • Run the insert_demo_src script to insert 500 rows into the splex.demo_src table.
  • Run the insert_demo_dest script to insert 500 rows into the splex.demo_dest table.

View the post queues

  1. On the target, run sp_ctrl.
  2. On the target, issue the show post command with the detail option. This command shows statistics about the number of messages processed and the queues that were used.

    sp_ctrl(target)> show post detail

    In the output, the Queue field shows the name of each post queue, in this case q1 and q2, and the Operations Posted field shows the number of operations that were posted. Each queue should show 500 operations posted.

Solve Installation Problems

Contents

 

Overview

This chapter reviews some common problems that you could experience when installing or running SharePlexfor the first time after installation.

Sometimes there are special installation instructions that supersede or supplement certain instructions in this manual. In addition, there can be known issues for this version that you should be aware of during or after installation. Please read the Release Notes for the version of SharePlex that you are installing before you begin the installation process.

 

Related Documents