SAP® Replication Server® Data Assurance

[arun_sitaraman]

Why Data Assurance?

We are in a data driven economy, where data drives business.  Business agility depends on data availability, where it is needed, when it is needed. Accurate decisions depend on the consistency of the data. While SAP® Replication Server® delivers data in real-time, across boundaries, the SAP® Replication Server® Data Assurance insures the consistency of data where it is needed, when it is needed. SAP® Replication Server® Data Assurance is the data insurance protection for critical business data consistency.

What is Data Assurance?

SAP® Replication Server® Data Assurance compares row data and schema between two or more databases, and reports discrepancies, and helps reconcile inconsistencies. SAP Replication Server Data Assurance is a scalable, high-volume, and configurable data comparison product. It allows users to run comparison jobs even during replication by using a “wait and retry” strategy that eliminates any down time. SAP Replication Server Data Assurance is available as part of the SAP® Replication Server® Premium Edition in the 2015 Packaging.

Data Assurance Product Overview

Ensures data consistency between sources and targets

• Support different data type comparisons
• Usability
  • Data Assurance can run as a Windows Service
• Supported databases:
  • HANA, ASE, IQ
  • Oracle, MS SQL, IBM DB2

Supported Topologies

Figure 1

Figure 1 show the two topologies that are supported today. On the left is a single server topology with a local agent. Here the Data Assurance Server and Data Assurance agent co-exist on the same node. On the right is a distributed topology where the data agents reside independently and coexist with the source and target. Table 1 below provides the guidelines to consider these topologies.

Local Topology Use Case Scenario	Distributed Topology Use Case Scenario
Low network latency between the DA server and database servers Few concurrent comparisons are required Ease of deployment and maintenance preferred over maximum performance High network latency between the DA server and database servers Many concurrent comparisons The external sort function is used to work with very large tables Performance takes precedence over ease of deployment and maintenance	High network latency between the DA server and database servers Many concurrent comparisons The external sort function is used to work with very large tables Performance takes precedence over ease of deployment and maintenance

                                                                                        Table 1

Installation

Data Assurance can be installed in Command Line Interface (CLI), Graphical User Interface (GUI), or silent modes. Table 2 provides an installation checklist for the product.

Operating System checklist

Pre-install checklist

On Unix systems, insure you have gzip/unzip or tar utilities On Windows systems, insure you have winzip or winrar utility On Unix systems, create a sybase user account and set read, write, and execute permissions

Data Assurance server Java RMI port – the Java RMI port used by outside applications to connect to the DA Server’s Java API. The default is 4500. TDS port – the port used by the command line tool (CLT) or isql utility. The default is 4501. DASD port – the port on which the system database runs if you enable the system database To accept outside connections. The default is 4503. Data Assurance agent Java RMI port – the Java RMI port used by the DA server to connect to the DA agent. The default is 4510. TDS port – the port used by command line tool (CLT) or isql utility. The default is 4511. DTS port – the port used by the DA agent to stream fingerprints back to the DA server during comparisons. The default is 4512.

                                                                                              Table 2

Successful deployment of the Data Assurance Server and Agent(s) will be similar to one of the two following sample summaries as shows in Figures 2 and 3.

Sample Local Topology Deployment Summary:

                                                                              Figure 2

Sample Distributed Topology Deployment Summary

                                                                                        Figure 3

Data Assurance Operational Checklists

Successful Data Assurance Server and Agent Operation can be achieved with adherence to the following operational process checklists:

Row Comparison or Reconciliation Process

Process Steps

Either Use the local DA agent Remote DA agent Create source and target database profiles Create a compareset Create a job Run the job Monitor the job Optionally show the history of the job

Database Objects Schema Comparisons

Process Steps

Either Use the local DA agent Remote DA agent Create source and target database profiles Create a schema job Run the job Optionally show the history of the job

Import a Job based on Replication Definitions and Subscriptions

Process Steps

Either Use the local DA agent Remote DA agent Create source and target database profiles Create a RSSD database profile Import a job from SAP Replication Server Alter the comparison options in the imported job Alter or add a schedule to the imported job Run the job Monitor the job

Tune the Data Assurance Server for Better Performance

Process Steps

View default values for configuration parameters for the server Alter the default value(s) for the configuration parameter(s) of interest

Backup and Restore

Process Steps

Create a Backup of the current DASD View the backup copy Restore the DASD from backup copy The DA server shuts down after backup Restart the DA server

Delete Job History and Data Assurance System Database (DASD) backup

Process Steps

To delete history use Drop history Truncate history To drop DASD backup use Drop backup Truncate backup

Data Assurance Reconciliation

The strength of Server® Data Assurance lies in not only comparing data between the source and target systems but also reconciling them for consistency, insuring data quality. Data Assurance Jobs control reconciliation.

                                                                            Figure 4

From the add clauses listed in Figure 4 above, reconciliation can be controlled as follows:

• The AUTO_RECONCILE argument helps enable automatic reconciliation, in that job.
• The CREATE_RECON_SCRIPT argument, when set to true creates a script for reconciliation.
  • The show reconcile command shows the path of this script
  • This argument can be combined with the AUTO_RECONCILE argument

Examples:

(1) DA applies reconciliation to the target (with optional difference rechecking phases):

COMPARE_MODE  = ROW_COMPARE

CREATE_COL_LOG = TRUE

AUTO_RECONCILE = TRUE

(2) DA writes 0..3 INSERT/UPDATE/DELETE scripts to fix all differences manually (with optional rechecking phases):

COMPARE_MODE        = ROW_COMPARE

CREATE_COL_LOG      = TRUE

CREATE_RECON_SCRIPT = TRUE

(3) Both of the above together (not sure why you would, but you can):

COMPARE_MODE = ROW_COMPARE

CREATE_COL_LOG      = TRUE

AUTO_RECONCILE      = TRUE

CREATE_RECON_SCRIPT = TRUE

Notes (0..3😞

• If there are no differences, DA will write 0 scripts.
• If there are missing rows (only), DA will write 1 INSERT script.
• If there are missing and inconsistent rows (only), DA will write 1 INSERT script and 1 UPDATE script (2 in total).
• If there are missing, inconsistent and orphaned rows, DA will write all 3 scripts.

SAP Replication Server 15.7 SP300 Direct Reconciliation Enhancement

• A new compare mode called DIRECT_RECON has been added to improve below performances
  • Materialization – populate/repopulate a target table using all source rows
  • Reconcile the target table rows without replication latency. All differences presumed to be unchanging need to be reconciled as soon as possible
    • Compare and reconcile rows in a single step, all in memory
    • Reconcile with the appropriate insert, delete, or update statement directly to rows in the target table that are identified as being different.
    • Do not save row values to disk
    • No detailed text or XML report
    • Enable multi-threaded reconciliation per comparison partition

Direct Reconciliation Example

This example shows how to create a job that uses direct reconcile:

                1> create job job_direct_recon

                2> add comparison cmp1

                3>  set compareset cs1

                4>  and set compare_mode to direct_recon

                5>  and set auto_recon_num_threads = 2

                6> go

The two new changes are:

• new compare_mode “direct_recon”
• the job option “auto_recon_num_threads”        /* determines how many threads are

              created/partition* when performing the

              direct reconciliation.*/

Notes:

The direct reconcile threads are managed internally and are only active for the duration of the “compare and reconcile” phase. (* internal DA partitions, not database partitions)

Figure 5 below shows a sample Direct Reconciliation Example Output:

                                                                                  Figure 5

Show Reconcile command

• The show reconcile command shows reconciliation information.
• show reconcile <job_name> {<history_id> | latest} [<comparison_name> <target_index> <script_type>]
  • job_name  the name of the job for which to show the reconciliation script.
  • history_id  the job history sequence ID of the reconcile script to be shown.
  • comparison_name  the name of the job comparison.
  • target_index  the index of the target. Use zero (0) for the first target.
  • script_type  the type of script to return: insert, update, or delete.

• Figure 6 below shows an example output of Show Reconcile command

Show reconciliation scripts for “job6” with history ID 29:

> show reconcile job6 29

            > go

                                              Figure 6

Data Assurance Security and Access Control

Security	Access Control
Kerberos Network based authentication protocol for client/server communication Centralized and secure authentication Authentication with a trusted third party Key Distribution Center (KDC) Data Assurance can be configured to user Kerberos for secure authentication Data Assurance supports Secure Socket Layer (SSL) Protocol for encryption on the wire	Lightweight Directory Access Protocol (LDAP) Standard client/server protocol for directory service access Data Assurance binds LDAP users as administrators Data Assurance support user authentication to external LDAP servers Password management Strong password policy -P start up recovery isql –X password encryption

  Data Assurance Performance and Tuning Guidelines

Performance Settings

Tuning

Deployment settings Use a distributed environment Install DA agent on a machine that shares a fast Ethernet connection with your database, to minimize the database-to-agent JDBC network traffic Run DA server on a separate machine Reduce Network Latency General Operational Guidelines Daily row count checks Weekly row data checks Schedule comparisons after replication Optimize database for select and order by statements Limit primary keys, perhaps only one Run database_hash than literal Choose summary report than column log

Row Comparison Optimization Configure non key columns comparisons with row_hash Row comparisons options order database_hash agent_hash isql –X password encryption NOTE: Please read the Users guide for more details on hash types

Data Assurance Resources

Useful Links and Guides
New Features Guide http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc60011.1571202/doc/pdf/da_nfg.pdf SAP Replication Server Data Assurance http://help.sap.com/saphelp_repserver1571300/helpdata/en/fc/6f9e32bd1c10149b26ed53906080e5/content.h... SAP Replication Server Data Assurance Installation Guide http://help.sap.com/saphelp_repserver1571300/helpdata/en/01/ea47a8bd1d1014957cf08654ffa10c/frameset....

  Acknowledgement: Thanks to Steve Jones (stev.jones@sap.com) and Carl Soane (carl.soane@sap.com) for their inputs.