This tutorial will walk us through the end-to-end steps in SAP HANA capture and replay tool.

What can I do with SAP HANA capture and replay?

Capture and replay tool could capture the real workload from source SAP HANA system and replay the captured workload to a target SAP HANA system. Both source system and target system should be on premise system.

The tool could be used to detect, analyze, or verify any potential issues for various scenarios, such as:

• Hardware change

• SAP HANA revision upgrade

• SAP HANA ini file change

• Table partitioning change

• Index change

• Landscape reorganization for SAP HANA scale-out systems

• Apply HINT to queries

The tool supports statements based performance comparison and result verification.However, the tool does not offer workload level comparison between the capture time from the source system and the replay time to the target system.

End-to-end Hands-On

Hands-on environment

SAP HANA Cockpit 2.0 SP13

Source system : SAP HANA 2.0 version 48

Control system : SAP HANA 2.0 version 54

Target system : SAP HANA 2.0 version 54

1) Create users for each database & register in the Cockpit

The first step is creating users for each database system(source, control, and target) and register each system in the Cockpit. All 3 database systems should be registered in the Cockpit to use SAP HANA capture and replay tool.

Privileges as a Technical User to register the resource in SAP HANA Cockpit

• CATALOG READ

• SELECT on the _SYS_STATISTICS

Database user privilege in source system:

• WORKLOAD CAPTURE ADMIN

• BACKUP ADMIN

• OPTIMIZER ADMIN

SQL commands:

CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;

GRANT CATALOG READ TO <username>;

GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;

GRANT WORKLOAD CAPTURE ADMIN TO <username>;

GRANT BACKUP ADMIN TO <username>;

GRANT OPTIMIZER ADMIN TO <username>;

Database user privilege in control system:

• WORKLOAD CAPTURE ADMIN

• WORKLOAD REPLAY ADMIN

• WORKLOAD ANALYZER ADMIN

SQL commands:

CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;

GRANT CATALOG READ TO <username>;

GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;

GRANT WORKLOAD REPLAY ADMIN TO <username>;

GRANT WORKLOAD CAPTURE ADMIN TO <username>;

GRANT WORKLOAD ANALYZE ADMIN TO <username>;

Database user privilege in target system:

• WORKLOAD REPLAY ADMIN

• USER ADMIN

Database recovery in step 5 will remove all target database users created before the recovery. there are 4 options for target database replay user.

Option 1 : Reuse source system SYSTEM user in the Target Database

Option 2 : Create a new user in the Target Database after recovery

SQL commands:

CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;

GRANT CATALOG READ TO <username>;

GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;

GRANT WORKLOAD REPLAY ADMIN TO <username>;

GRANT USER ADMIN TO <username>;

Option 3 : Reuse the Source Database user in the Target Database with granting replay privileges

SQL commands:

GRANT WORKLOAD REPLAY ADMIN TO <username>;

GRANT USER ADMIN TO <username>;

Option 4 : Create a new user in the Source Database before starting capture and backup

SQL commands:

CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE VALID UNTIL FOREVER;

GRANT CATALOG READ TO <username>;

GRANT SELECT on SCHEMA _SYS_STATISTICS TO <username>;

GRANT WORKLOAD REPLAY ADMIN TO <username>;

GRANT USER ADMIN TO <username>;

2) Source Database : Capture

To start capture, please go to the HANA Cockpit System Overview page of the source database and choose Start New Capture directly in the Capture Workload card.

To change the capture file destination (*.cpt file extension), choose Configure Capture on the bottom right of the Capture Management page.

The captured workload file is stored by default in the trace directory. (e.g.,/usr/sap/<SID>/HDB##/<hostname>/trace/)

In Capture Configuration, please select Usage and enter the name of the new capture. Please also customize other optional settings before you start the capture. Turning Create Backup on is recommended, the option could be used together with Synchronize Replay with Backup option for the replay.

In general, for most use cases, select Replay in Usage and enable Create Backup option is enough.

For other options, please refer to the SAP HANA Cockpit help document linked from the question mark icon at up right corner of the tool.

Once the capture is started, the information will be shown in the Capture Workload card, Capture Management Page and Capture Monitor page. Capture Monitor page shows the detailed information.

Please choose Stop Capture on the bottom right of the Capture Monitor page to finish the capture.

3) Control Database : Preprocess a Captured Workload

To preprocess a capture workload, the captured workload file should be copied or moved to the control database directory (e.g., /usr/sap/<SID>/HDB##/<hostname>/trace/) with proper file permissions.

Then please go to the SAP HANA Cockpit System Overview page of the control database and choose Start New Replay in the Replay Workload card.

Please choose Start in SAP HANA Cockpit Replay Management page to preprocess the capture file.

4) Control Database : Run Replayer

Once preprocessing is finished, please configure hdbuserstore entries and run replayer in the control database by accessing its OS level using <sid>adm.

Configure Secure User Store Keys

The following Secure User Store Keys should be configured to run the replayer and replay workloads.

Control Database user

This Secure User Store Key will be used for running the replayer.

OS command :

hdbuserstore SET <KEY> “<control_DB_Hostname:port>” <USERNAME> <PASSWORD>

Once control database user is registered in the Secure User Store, the key could be used to run the replayer.

OS command :

hdbwlreplayer -controlhost <control_DB_Host> -controlinstnum <control_DB_InstanceNumber> -controladminkey <USERNAME>,<KEY> -controldbname <control_DB_Name> -port <listenPortNumber>

Example:

<listenPortNumber> can be any port which is not in use.

Replayer should be up and running during until the end of the replay.

Replayer can be stopped by Ctrl+C or kill <pid> in OS.

Once replayer is running, it can be reachable from SAP HANA Cockpit Replay Configuration page by clicking the refresh icon.

Target SYSTEM user

This Secure User Store Key will be used for User Authentication in step 6.

OS command :

hdbuserstore SET <KEY> “<target_DB_Hostname:port>” SYSTEM <PASSWORD>

Target Database user

This Secure User Store Key will be used for Replayer Authentication in step 6.

As SYSTEM user can be used for this as well, if SYSTEM user will be used for Replayer Authetication during the replay, this step is not needed.

OS command:

hdbuserstore SET <KEY> “<target_DB_Hostname:port>” <USERNAME> <PASSWORD>

5) Target Databse : Recover from Source Database Backup

Recovering the target database with the source database backup is required to ensure that the source database and the target database are in a consistent state for capture and replay.

Performing a database backup right after starting the capture by using Create Backup option in step 2 together with using Synchronize Replay with Backup option in step 6 is highly recommended.

Please follow 10 steps and 3 steps in below 2 pictures to finish the target database recovery from source database backup. Before starting the recovery, source database data backup file should be copied or moved to the target database directory (e.g., /usr/sap/<SID>/HDB##/<hostname>/trace/) with proper file permissions.

For copy database steps explanation, please refer to the SAP HANA Cockpit help document linked from the question mark icon at up right corner of the tool.

6) Control Database : Replay

General Information

Please define the replay name and select Target Host Name. If Create Backup option in step 2 is enabled, enable Synchronize Replay with Backup option in step 6 is highly recommended.

Replayer Authentication

Replay Authentication is for assigning the running replayer that will be used to connect to the target system and facilitate the replay.

Replayer List: Select the replayer in Replayer List started in step 4.

Replayer Authentication: Target database User Name and Secure User Store key created in step 4 should be used. If the user credential is correct, green text “Validated credentials” will appear.

User Authentication

User Authentication is needed for including workloads from all required users.

System User: Secure User Store Key of Target database SYSTEM user created during step 4 should be used. If the key is correct, the text box color will become green.

Technical User: Enabled in case target technical user appeared in capture to authenticate the user (e.g., source technical user name is equal to target technical user name)

Database Users: For a stable replay, resetting the passwords of the database users is recommended and for this, you have the following options:

• Reset Password: Reset all passwords for all users except for the SYSTEM user and the technical user. On the Reset Password window enable the confirmation box and choose Confirm.
• User List: You can authenticate users manually by creating the Secure User Store Keys for the users in step 4.

Choose Start Replay once everything is ready.

Once the replay is started, choose Details to check the information in the HANA Cockpit Replay Monitor. Please choose Go to Report once the replay is finished.

7) Control Database : Replay Report Analysis

In the Replay Report, the first thing to check is availability of the Load chart menu. If it is missing, please choose Capture Workload card and make sure that the workload is loaded. Load chart is useful when comparing two replays.

Overview Tab

Result Comparison Card

In Result Comparison card, the functional correctness can be checked based on the number of record.

Identical: Row counts of individual SQL statement from source and target are identical

Different: Row counts are different

Failed: Execution has failed

Dummy Skipped: Execution has been skipped due to dummy request (result verification is also skipped)

Actual Skipped: Execution is done but result verification is skipped due to reasons other than dummy request

Different Statements Card

Statement: Individual SQL statement where the number of record is different. The full SQL statement can be checked by clicking More

Execution Count: The number of execution of the individual SQL statement

Deterministic: The number of execution that should be consistent

Difference Count: The number of execution with the different row count between source and target

Verification Skipped Card

In Verification Skipped card, details of skipped statements can be checked.

Multiple reasons can be appeared at the same time, in that case the reasons are concatenated with comma.

Performance Comparison Card

In Performance Comparison card, SQL execution time of individual statement can be compared.

Comparable: SQL performance is comparable between capture and replay

Faster: SQL performance is faster during replay comparing to capture

Slower: SQL performance is slower during replay comparing to capture

Failed: Execution has failed

Dummy Skipped: Execution has been skipped due to dummy request (result verification is also skipped)

Actual Skipped: Execution is done but result verification is skipped due to reasons other than dummy request

Comparable is related to Tolerance Ratio(10% by default) and Replay Report Threshold(100ms by default). Please refer to the examples below to know how SQL performance is categorize as Comparable.

Slower Statements Card

Statement: Individual SQL statement where the average difference is higher than the threshold. The full SQL statement can be checked by clicking More

Execution Count: The number of execution of the individual SQL statement

Average Difference Time: Average of elapsed time difference (between capture and replay) of the individual SQL statements

Maximum Elapsed Time: Maximum elapsed time of execution on target system of the individual SQL statements

Replay Failed Card

In Replay Failed card, details of failures can be checked.

Authentication failed: when invalid credential was given or failure during authentication

No connection to execute statements with: when only FetchCursor was captured without ExecutePrepared or ExecuteStatement

Invalid table name: FOUND_INIFILE_CONTENTS: line 1 col 12 (at pos 11): Missing table during CALL statement execution

Invalid table name: could not find table/view: missing table during SQL statement execution

Other reasons: unique constraints violated, out of memory, and any failure can happen depending on the situation

All error messages from replay can be shown here (any possible errors from the statement execution)

Load Tab

Load tab can be used to compare database KPIs between different captures and replays.

Performance Comparison Tab

Performance Comparison tab can provide detailed performance information of each captured and replayed SQL.

Result Comparison Tab

Result Comparison tab can provide detailed result verification information of each captured and replayed SQL.

Best Practice for Avoiding Issues

Always start with a small capture (e.g., 10-30 minutes) and test its replay

After clearing issues, then proceed with longer captures

If there are any issues, please carefully review the traces (e.g., authentication failures)

In case of authentication failure, check if credential is valid or database users are locked down due to the previous invalid login attempts

Don’t get confused with what has to be done on which system (e.g., control or target system)

Best Practice for troubleshooting

Raise a ticket with the collected traces and source/control/target revision information (component: HAN-CPT-CPT2-CNR)

It would be best if customer can provide the capture or preprocess file for investigation (we don’t need backup files in general)

Do’s and Don’ts