Quick Start Guide for SAP® HANA Database Replication Server® 15.7.
DOCUMENT ID: DC01974-01-1571100-02 LAST REVISED: July 2013 Copyright © 2013 by Sybase, Inc. All rights reserved. This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
Contents CHAPTER 1: Conventions ..............................................1 CHAPTER 2: How to Use This Document .....................5 CHAPTER 3: ASE-to-SAP HANA Database Replication Setup ........................................................7 System Architecture ..............................................................7 Prerequisites ..........................................................................7 Planning Your Installation .....................................................
Contents CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup ......................................................23 System Architecture ............................................................23 Prerequisites ........................................................................24 Planning Your Installation ...................................................24 System Requirements ..................................................24 Identify the Sybase Installation Directory ...................
Contents CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup .....................................51 System Architecture ............................................................51 Prerequisites ........................................................................52 Planning Your Installation ...................................................52 System Requirements .................................................. 52 Identify the Sybase Installation Directory .....................
Contents Resetting the Primary Microsoft SQL Server Database for Replication ..........................................76 CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup ......................................................79 System Architecture ............................................................79 Prerequisites ........................................................................79 Planning Your Installation ...................................................
Contents Creating a Replication Server Connection to the Primary Database .........................................100 Initializing the Replication Agent Instance ......... 100 Testing Replication ............................................................102 Resetting the Primary DB2 UDB Database for Replication ..............................................................103 CHAPTER 7: Glossary ................................................105 Index .................................................
Contents viii Replication Server
CHAPTER 1 Conventions These style and syntax conventions are used in Sybase® documentation. Style conventions Key monospaced (fixedwidth) Definition • • • • SQL and program code Commands to be entered exactly as shown File names Directory names italic monospaced In SQL or program code snippets, placeholders for user-specified values (see example below).
CHAPTER 1: Conventions Syntax conventions Key Definition {} Curly braces indicate that you must choose at least one of the enclosed options. Do not type the braces when you enter the command. [] Brackets mean that choosing one or more of the enclosed options is optional. Do not type the brackets when you enter the command. () Parentheses are to be typed as part of the command. | The vertical bar means you can select only one of the options shown.
CHAPTER 1: Conventions • • Replication Agent for Microsoft SQL Server Replication Agent for IBM DB2 UDB Quick Start Guide for SAP HANA Database 3
CHAPTER 1: Conventions 4 Replication Server
CHAPTER 2 How to Use This Document Use this document to set up and begin using a basic replication system with SAP® HANA database as the replicate. This document contains these sections: • • • • "ASE-to-SAP HANA Database Replication Setup" provides instructions for setting up an ASE-to-SAP HANA database replication system. "Oracle-to-SAP HANA Database Replication Setup" provides instructions for setting up an Oracle-to-SAP HANA database replication system.
CHAPTER 2: How to Use This Document 6 Replication Server
CHAPTER 3 ASE-to-SAP HANA Database Replication Setup Implement a sample replication system for replication from Adaptive Server Enterprise (ASE) to SAP HANA database. Use the tasks in this document to set up a proof-of-concept or test replication system. These tasks use the sample Replication Server instance SAMPLE_RS. If you have already installed Replication Server, skip to Configuring Replication Components on page 13.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup • • • You have a valid license for Replication Server with ExpressConnect for HANA DB. Adaptive Server is installed and configured to serve as the source (primary) database from which Replication Agent replicates transactions, and SAP HANA database is installed and configured as the target (replicate) database to which Replication Server with ExpressConnect for HANA DB replicates transactions. TCP/IP connectivity is available between all hosts.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Task Note: If you are installing Replication Server Options (RSO), this task requires you to perform several steps twice because you are downloading Replication Server and other products. 1. Go to https://websmp204.sap-ag.de/, and enter your login ID and password. 2. From the product listing, select: • SAP Sybase Replication Server, Option for HANA or • SAP Sybase Replication Server, HANA Edition 3. Select the software you need by version and platform.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Installing Replication Server Install Replication Server using the setup program. Prerequisites • • • Allocate a disk partition of at least 20MB for each Replication Server you are installing. You can add more partitions later, if necessary. Check each partition to make sure it is available and has write permissions. Allocate the entire partition to the Replication Server.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup 4. In the Introduction window, click Next. 5. Indicate where to install Replication Server: • To select an installation directory, click Choose, and browse for and select the directory. • To create a new directory, enter a new directory path. • To restore to the default directory, click Restore Default Folder. If the installation directory you chose does not exist, you see: The directory directory name does not exist.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup • • Use Previously Deployed License Server – for an existing license server, enter the host name of the machine where the license server is running, and the port number if the port number you are using is not default. Click Next. Continue installation without a license key – click Next to proceed without any license keys. The installer allows you to install and use the Replication Server components without a license for 30 days.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup 12. When the final window appears, indicating a successful installation, click Done to exit the installer. Editing the Interfaces File Edit your Replication Server interfaces file. 1. Log in to Replication Server: isql –Usa –Psa_pass –SSAMPLE_RS If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 2. Shut down Replication Server: 1>shutdown 2>go 3.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Configuring Replication Server Review the procedures for configuring Replication Server for ASE-to-SAP HANA database replication. Obtaining the SAP HANA Database ODBC Drivers Set up ExpressConnect for HANA DB for connections between SAP HANA database and Replication Server. You must add the SAP HANA database ODBC driver to the ExpressConnect for HANA DB installation.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup where myschema is your SAP HANA database schema, and muser is the maintenance user you just created. 5. Log in to Replication Server: isql –Usa –Psa_pass –SSAMPLE_RS If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 6. Create a connection to the replicate SAP HANA database instance using ExpressConnect for HANA DB. For a standard connection: create connection to rds.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Configuring Replication Server for Replication from the Primary Database Configure Replication Server for replication from the primary database. Adding the Primary Database to Replication Server Add the primary Adaptive Server database to Replication Server. 1. Add the Replication Server instance to the interfaces file of the data server that hosts the primary database. Note: Restart the Adaptive Server instance if the interfaces file has changed. 2.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Parameter Description Value rs.rs_db_maint_user User ID whose work is not re- _maint plicated when logged on the primary database. The user is Note: If the user ID does not exist, the script creates the user called maintenance user. ID on the database. The user ID cannot be the name of an alias. rs.rs_db_maint_password Password for the maintenance user. rs.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup The output from the command looks similar to: Spid Name State Info ---- ----- ------------32 DSI EXEC Awaiting Command 101(1) SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD 18 DSI 23 DIST Awaiting Message 101 SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD Awaiting Wakeup 101 SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD 29 SQT Awaiting Wakeup 101:1 DIST SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD 11 SQM Awaiting Message 101:1 SAMPLE_RS_ERSSD.SAMPLE_RS_ERSSD 9 SQM Awaiting Message 101:0 SAMPLE_RS_ERSSD.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup Note: By default, this DDL creates a table with the owner dbo. 3. Grant permissions to any new or existing object to be replicated in the primary database: grant all on ptab1 to public; 4. Mark the ptab1 table for replication: sp_setreptable ptab1, 'true' go 5.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup For information on using the create subscription command in direct load materialization, see Replication Server Reference Manual. 10. On the Adaptive Server database, insert data into the primary ptab1 table: insert into ptab1 values (3, "Tom Servo") go 11.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup exit go If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 8. Disconnect from the RSSD: 1>disconnect 2>go 9. Connect again to the primary database: 1>connect to pds.pdb 2>go 10. Reset the truncation point to the end of the database transaction log: 1>dbcc settrunc ('ltm','valid') 2>go 11. Start the Replication Agent instance: 1>sp_start_rep_agent pdb 2>go 12.
CHAPTER 3: ASE-to-SAP HANA Database Replication Setup 22 Replication Server
CHAPTER 4 Oracle-to-SAP HANA Database Replication Setup Implement a sample replication system for replication from Oracle to SAP HANA database. Use the tasks in this document to set up a proof-of-concept or test replication system. Only basic Oracle features are addressed in the example scenario. In particular, Oracle Automatic Storage Management (ASM) and Real Application Clusters (RAC) are not addressed in this guide, although Replication Agent does support these features.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Prerequisites Review the prerequisites before you begin setting up Oracle-to-SAP HANA database replication. Make sure: • • • • • • • You are familiar with Oracle and SAP HANA database. You have valid licenses for Replication Server with ExpressConnect for HANA DB and Replication Server Options.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Identify the Sybase Installation Directory Determine the directory in which to install the replication components. Replication Agent, Replication Server, and ExpressConnect for HANA DB database are installed in the same base directory, which is identified by the SYBASE environment variable. Select a path on a host drive to be the recipient of the Sybase installation and configuration activities.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Note: To generate a license, you must provide some information, such as the host name, MAC address, and number of CPUs. 5. Download your product licenses, and place them in your product license directory after installation: • $SYBASE/SYSAM-2_0/licenses (UNIX or Linux) • %SYBASE%\SYSAM-2_0\licenses (Windows) where $SYBASE or %SYBASE% is where you installed your product. 6.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup • If there is not enough disk space in the temporary disk space directory, set the environment variable TMP to directory_name before running it again, where directory_name is the full path to and name of the temporary directory to which the installation program writes the temporary installation files. (UNIX or Linux) If you downloaded the product, go to the directory where you extracted the installation image and start the installer: ./setup.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup • • • Specify License Keys – click Browse to select the license file. To select multiple license files, use Shift+Click or Ctrl+Click. The license pane displays the license information. Alternatively, copy and paste the license information directly in the license pane. Click Next. If you specify a served license key, the installer prompts you to install a new SySAM license server.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup • Yes – to create a sample Replication Server directory without starting the sample Replication Server, or, • No – to continue with the installation. See the Replication Server Configuration Guide for Windows or UNIX. Note: For a sample Replication Server instance, the rs_charset parameter must be set to utf8. Click Next. 12. When the final window appears, indicating a successful installation, click Done to exit the installer.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup The directory does not exist. Do you want to create it? If the directory you specified exists, you see: Warning: You have chosen to install into an existing directory. If you proceed with this installation, any older versions of the products you choose to install that are detected in this directory will be replaced.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup • • • Informational Warning Error If you choose not to have e-mail alerts or severity messages logged, select No. Click Next. 11. Review the product features or components listed on the Preinstallation Summary window. Click Install. The installation program installs the components in the installation directory you specified, and displays an installation progress indicator.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Configuring Replication Components Review the procedures for configuring each of the replication components in the replication system. Configuring Replication Server Review the procedures for configuring Replication Server for Oracle-to-SAP HANA database replication. Obtaining the SAP HANA Database ODBC Drivers Set up ExpressConnect for HANA DB for connections between SAP HANA database and Replication Server.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup hdbuserstore set rds myhost:xxxxx my_securestore_user my_securestore_pwd where • rds is the key for the secure store entry • myhost:xxxxx is the connection environment host name and port number • my_securestore_user and my_securestore_pwd are SAP Secure User Store credentials 2.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup where: • rds is the replicate SAP HANA database. For a standard connection, this must match the data server name in the interfaces file entry. See the Adaptive Server Enterprise • • • • Configuration Guide for UNIX > Set Up Communications Across the Network > Contents of the interfaces File and the Adaptive Server Enterprise Configuration Guide for Windows > Network Communications Using sql.ini.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Configuring Replication Agent Review the procedures for configuring Replication Agent for Oracle. Primary Oracle Database Configuration for Replication Configure the primary Oracle database for replication and to ensure that the Oracle redo log data is not overwritten before it is read by Replication Agent.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Configuring Oracle LogMiner Configure Oracle LogMiner on the primary Oracle database. 1. If you have not already installed Oracle LogMiner, go to $ORACLE_HOME/rdbms/ admin, log in as a “sys as sysdba” user, and execute the Oracle LogMiner installation script: @dbmslm.sql 2.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT SELECT
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup RESOURCE SELECT_CATALOG_ROLE The user who starts the Replication Agent instance must also have read access to the Oracle redo log files and the Oracle archive directory that contains the archive log files to be accessed for replication. If the Replication Agent is configured to remove old archive files, the user must have update authority to the directory and the archive log files.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Record the ORACLE_SID value for the instance you want to connect to. 4. (Optional) If your operating system has a Java 6.0.x-compatible JRE or JDK installed, you can use the Oracle iSQL (the browser-based interface to SQL*PLUS) demo items to connect to Oracle using the JDBC driver and to verify the connection information. See Verification of a JDBC Client Installation in the Oracle Database JDBC Developer's Guide and Reference Guide. 5.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup password information listed in the tnsname.ora file for the Oracle, Replication Server, and RSSD connections. • • • Set the value of rs_source_ds to the value you chose for pds when you created a Replication Server connection from the primary database. Set the value of rs_source_db to the value you chose for pdb when you created a Replication Server connection from the primary database.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Parameter Description Example Values rs_host_name Machine where Replication Server is instal- jdoe_host1 led. rs_port_number Port number where Replication Server is in- 11752 stalled. rs_username Replication Server user with connect SAMPLE_RS_ra source and create object permissions. This is the user used to create a Replication Server connection from the primary database. rs_password Password for rs_username.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Parameter Description Example Values initialize_instance Initialize the Replication Agent instance. yes pdb_include_archives Enables or disables the use of Oracle archive USE_DEFAULT log files. pdb_archive_path Identifies directory path where Replication Agent expects to find archived Oracle redo log files. A valid directory path on the machine hosting Replication Agent that points to a location where Oracle puts the archived redo log files.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Note: If, in your response file, you set start_instance to yes, your instance is also running. If you set initialize_instance to yes, your instance is also initialized. c) Results are returned as either: • • Response-file processing completed Response-file processing completed with errors See the Replication Agent Administration Guide. 9.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Note: These verification steps are optional because they were performed when you verified the resource file. 3. Verify the Replication Agent connection to Replication Server: a) Enter: test_connection RS go b) This result is returned: Type Connection ---- ---------RS succeeded (1 row affected) c) If the result indicates a failure, either the server is not responding or the connection properties (host, port, user, or password) are incorrect.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup set username muser set password mpwd with log transfer on, dsi_suspended go where: • pds is the value of the rs_source_ds parameter specified in Replication Agent. • pdb is the value of rs_source_db specified in Replication Agent. • muser is the maintenance user for the primary Oracle database. This user must already exist and have the necessary select permissions in the primary database. See the Replication Server Options Primary Database Guide.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup Command failed - Desired state could not be achieved. Current state: 3. The ra_status command returns the state of the Replication Agent. It is a good practice to verify that the Replication Agent remains in replication state, even after the resume command executes successfully.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup 22 62 SQM Awaiting Message 103:0 pds.pdb REP AGENT Awaiting Command pds.pdb Testing Replication When you finish setting up the replication system, test that replication works as intended. 1. Connect to the primary Oracle instance as a regular user, rather than as the maintenance user. 2. At the primary Oracle database, create a table named ptab1 to replicate: CREATE TABLE ptab1 (idno NUMBER PRIMARY KEY, name VARCHAR2(20)); 3.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 9. Alter the replication definition that you created earlier to point to the correct replicate table: alter replication definition with replicate table named .ptab1 go 10. Create a subscription against the replicate connection.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup go rs_zeroltm pds, pdb go exit go You see: Locator has been reset to zero. (return status = 0) 5.
CHAPTER 4: Oracle-to-SAP HANA Database Replication Setup 50 Replication Server
CHAPTER 5 Microsoft SQL Server-to-SAP HANA Database Replication Setup Implement a sample replication system for replication from Microsoft SQL Server to SAP HANA database. Use the tasks in this document to set up a proof-of-concept or test replication system. These tasks use the sample Replication Server instance SAMPLE_RS. If you have already installed Replication Server, skip to the configuration procedures.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Prerequisites Review the prerequisites before you begin setting up Microsoft SQL Server-to-SAP HANA database replication. Make sure: • • • • • • You are familiar with Microsoft SQL Server and SAP HANA database. You have valid licenses for Replication Server with ExpressConnect for HANA DB and Replication Server Options. You have valid licenses for Replication Server and Replication Server Options.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Identify the Sybase Installation Directory Determine the directory in which to install the replication components. Replication Agent, Replication Server, and ExpressConnect for HANA DB are installed in the same base directory, which is identified by the SYBASE environment variable. Select a path on a host drive to be the recipient of the Sybase installation and configuration activities.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Note: To generate a license, you must provide some information, such as the host name, MAC address, and number of CPUs. 5. Download your product licenses, and place them in your product license directory after installation: • $SYBASE/SYSAM-2_0/licenses (UNIX or Linux) • %SYBASE%\SYSAM-2_0\licenses (Windows) where $SYBASE or %SYBASE% is where you installed your product. 6.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup • If there is not enough disk space in the temporary disk space directory, set the environment variable TMP to directory_name before running the installer again, where directory_name is where the installation program writes the temporary installation files. When specifying the directory_name, include its full path.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup • • Alternatively, copy and paste the license information directly in the license pane. Click Next. If you specify a served license key, the installer prompts you to install a new SySAM license server. Click: • Next to install the new SySAM license server and follow the installation prompts. • Previous and select Use previously deployed license server if you have an existing SySAM license server on the same host.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup • No – to continue with the installation. See the Replication Server Configuration Guide for Windows or UNIX. Note: For a sample Replication Server instance, the rs_charset parameter must be set to utf8. Click Next. 12. When the final window appears, indicating a successful installation, click Done to exit the installer. Installing Replication Agent Install Replication Agent using the GUI wizard.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup after the end of the grace period, obtain a valid license from the Sybase Product Download Center at http://www.sybase.com/detail?id=1025266, and install it. Note: Replication Agent is licensed statically when installed with RSHE. If you are installing Replication Agent with RSHE, locate the file that contains a valid SySAM license for the Replication Agent component in RSHE. Specify that license file here.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Configuring Replication Components Review the procedures for configuring each of the replication components in the replication system. Configuring Replication Server Review the procedures for configuring Replication Server for Microsoft SQL Server-to-SAP HANA database replication. Obtaining the SAP HANA Database ODBC Drivers Set up ExpressConnect for HANA DB for connections between SAP HANA database and Replication Server.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup hdbuserstore set rds myhost:xxxxx my_securestore_user my_securestore_pwd where • rds is the key for the secure store entry • myhost:xxxxx is the connection environment host name and port number • my_securestore_user and my_securestore_pwd are SAP Secure User Store credentials 2.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup where: • rds is the replicate SAP HANA database. For a standard connection, this must match the data server name in the interfaces file entry. See the Adaptive Server Enterprise • • • • Configuration Guide for UNIX > Set Up Communications Across the Network > Contents of the interfaces File and the Adaptive Server Enterprise Configuration Guide for Windows > Network Communications Using sql.ini.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Configuring Replication Agent Review the procedures for configuring Replication Server for Microsoft SQL Server. Primary Microsoft SQL Server Configuration and Verification Review the procedures for configuring and verifying the primary Microsoft SQL Server database for replication.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Enabling Remote DAC Enable remote DAC. Prerequisites Task 1. Log in to the server: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -U username -P password -S serverName where username, password, and serverName are your user ID, password, and Microsoft SQL Server name. 2.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Verifying the Version of the Microsoft Filter Manager Library Confirm that the Microsoft Filter Manager Library version is 5.1.2600.2978 or later, to make the primary transaction log files readable for Replication Agent. 1. To determine the version of the library, in Windows Explorer, right-click c:\windows \system32\fltlib.dll, select Properties, and click the Version tab in the Properties dialog box. 2.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup 5. In Windows Explorer, navigate to %SYBASE%\RAX-15_5\bin, and double-click the sybfiltermgr.exe file to start the sybfilter driver management console. 6. To start the sybfilter driver, enter start at the management console. 7. Add the log file path to the sybfilter driver with the user manager or by modifying the configuration file: • User manager – use the add command in the management console.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Task 1. From the Windows Control Panel, select: Administrative Tools > Services 2. In the listed services, find the service named Microsoft SQL Server Analysis Service (SERVER), where SERVER is the name of your Microsoft SQL Server data server. 3. Stop this service. Enabling TCP/IP Enable TCP/IP. Prerequisites Task 1.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup The majority of configuration values required to create and initialize a Replication Agent can be recorded and stored in a resource file. Using a resource file provides a means to record or retain the configuration information for a Replication Agent instance, allowing an instance to be removed and re-created. The Microsoft SQL Server resource file template is at %SYBASE%\RAX-15_5\init \mssql.rs. 2.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Parameter Description Example values ltm_admin_pw Password for the user administering the Rep- The default password policy is lication Agent instance. that the password must be between 6 and 255 characters. For example: sa_pass pds_server_name Name of the primary Microsoft SQL Server. TEAMSTER pds_port_number Port number for the primary Microsoft SQL Server.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Parameter Description Example values rssd_username Valid user for RSSD. SAMPLE_RS_RSSD_maint rssd_password Password for rssd_username. SAMPLE_RS_RSSD_maint_ps start_instance Automatically start the instance that was cre- yes ated. initialize_instance Automatically initialize the new Replication yes Agent instance. 3.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Microsoft SQL Server(TEAMSTER) b) Stop the service. 2. Restart Microsoft SQL Server in single-user mode: a) b) c) d) Click Start > Control Panel > Administrative Tools > Services. Right-click your Microsoft SQL Server instance, and choose Properties. In the General tab, click Stop. Under Start parameters, enter: -m Click Start. 3. Execute the resource file to create the Replication Agent instance.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup isql –Usa –Psa_pass –Smyra 7. If your resource file has initialize_instance set to no, initialize the primary data server and the Replication Agent instance: a) Open a command window, and log in to the Replication Agent instance. b) Issue this command to create server xlog objects: server_xlog init c) Issue this command to shutdown Replication Agent: shutdown 8.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup Subsequent initialization Create another Replication Agent instance, after you have already initialized the primary data server. See also • Verifying the Replication Agent Replicating State on page 73 Creating and Initializing a Replication Agent Instance Execute the resource file to create the Replication Agent instance. 1.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup ra_admin init Your Replication Agent instance is now running and has been initialized. Creating a Replication Server Connection to the Primary Database Create a Replication Server connection to the primary database. 1. In Replication Server, create a connection to the primary Microsoft SQL Server database: create connection to pds.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup To detect an error that occurred after replication start-up, execute: ra_status go If the Replication Agent is in replicating state, ra_status returns: State Action ----------------- ----------------------REPLICATING Ready to replicate data. Replication Agent goes to the REPLICATION DOWN state if an error occurs.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup 24 25 23 22 62 EXEC DSI DIST SQT SQM SQM REP AGENT Suspended Suspended Awaiting Wakeup Awaiting Wakeup Awaiting Message Awaiting Message Awaiting Command 103(1) pds.pdb 103 pds.pdb 103 pds.pdb 103:1 DIST pds.pdb 103:1 pds.pdb 103:0 pds.pdb pds.pdb Testing Replication When you finish setting up the replication system, test that replication works as intended. 1.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup grant all privileges on .PTAB1 to public 8. Log in to Replication Server: isql –Usa –Psa_pass –SSAMPLE_RS If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 9. Alter the replication definition that you created earlier to point to the correct replicate table: alter replication definition with replicate table named .
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup ra_admin refresh Note: If you prefer to delete and replace all the information in the Replication Agent repository, issue the ra_admin deinit, force command followed by a normal ra_admin init command (without the force option). 3. Reset the locator stored in Replication Server: isql -Usa -Psa_pass -SSAMPLE_RS connect go rs_zeroltm pds, pdb go exit go You see: Locator has been reset to zero.
CHAPTER 5: Microsoft SQL Server-to-SAP HANA Database Replication Setup 78 Replication Server
CHAPTER 6 DB2 UDB-to-SAP HANA Database Replication Setup Implement a sample replication system from DB2 UDB to SAP HANA database. Use the tasks in this document to set up a proof-of-concept or test replication system. These tasks use the sample Replication Server instance SAMPLE_RS. If you have already installed Replication Server, skip to the configuration procedures. For the most current information on Replication Server and its components, see the Replication Server Release Bulletin.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup • • • • • You are familiar with DB2 UDB and SAP HANA database. You have valid licenses for Replication Server with ExpressConnect for HANA DB and Replication Server Options. DB2 UDB 9.5 or 9.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Replication Agent Installation Directory Replication Agent versions 15.5 and later use the RAX-15_5 directory name. If you have an existing Replication Agent installation of version 15.5 or later and do not want to overwrite it, install the latest version of Replication Agent in a different location. Accessing and Downloading Products from SMP Download replication components from the SAP Service Marketplace (SMP).
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup 6. Download your installation files, and uncompress and extract all the installation images to your local drive. Installing Replication Components Review the procedures for installing the replication components. Install all replication components on the same host where a DB2 UDB data server has already been installed and is running with both the primary and replicate databases. This simplifies the quick-start process.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup where setup.bin is the executable file name for installing Replication Server. If there is not enough disk space in the temporary disk space directory, set the environment variable IATEMPDIR to tmp_dir before running the installer again, where tmp_dir is the full path to the temporary directory to which the installation program writes the temporary installation files. 4. In the Introduction window, click Next. 5.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup • • • • Next to install the new SySAM license server and follow the installation prompts. Previous and select Use previously deployed license server if you have an existing SySAM license server on the same host. Use Previously Deployed License Server – for an existing license server, enter the host name of the machine where the license server is running, and the port number if the port number you are using is not default. Click Next.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Note: For a sample Replication Server instance, the rs_charset parameter must be set to utf8. Click Next. 12. When the final window appears, indicating a successful installation, click Done to exit the installer. Installing Replication Agent Install Replication Agent using the GUI wizard.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup any older versions of the products you choose to install that are detected in this directory will be replaced. If you continue and the older products were not installed with the current version of Replication Agent, the installation program overwrites the common files.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Click Next. 11. Review the product features or components listed on the Preinstallation Summary window. Click Install. The installation program installs the components in the installation directory you specified, and displays an installation progress indicator. If errors occur during the installation, the installation program displays error messages.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Setting DB2 UDB Environment Variables For UNIX and Linux, the DB2 UDB installation provides two scripts for setting up the DB2 UDB environment variables: db2cshrc for C shell and db2profile for Bourne or Korn shell. These scripts set the library path environment variable based on the bit size of the installed server or client. Note: On Windows, the installation sets all necessary environment variables.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup # force 32-bit libraries LD_LIBRARY_PATH=$HOME/sqllib/lib32:$LD_LIBRARY_PATH export LD_LIBRARY_PATH 4. Before starting Replication Agent, always source your file, if you have customized it. Otherwise, source the original db2cshrc or db2profile script. Node and Database Cataloging in an DB2 UDB Client If Replication Agent for DB2 UDB is installed on a different host than the primary database, it must use DB2 UDB client libraries.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Note: Each SAP HANA database installs with a unique instance number, and the port number is the instance number prefixed with 3 and suffixed with 15: 3in15 where in is the two-digit instance number. For example, the port number for an SAP HANA database with instance number 1 is 30115.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup set password mpwd go For SAP Secure User Store: create connection to rds.rdb using profile rs_udb_to_hanadb;ech set username auser set password apwd set dsi_connector_sec_mech to "hdbuserstore" go where: • rds is the replicate SAP HANA database. For a standard connection, this must match the data server name in the interfaces file entry.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup If you have not set up the sample Replication Server instance, enter your Replication Server instance name in place of SAMPLE_RS. 2. Enter: grant create object to SAMPLE_RS_ra go where SAMPLE_RS_ra is the value for the rs_username parameter you entered in the resource file. Configuring Replication Agent Review the procedures for configuring Replication Agent for DB2 UDB.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Setting the DB2 UDB Connectivity Autocommit Parameter Replication Agent requires that the DB2 UDB connectivity autocommit parameter be turned on (autocommit=1). The autocommit parameter is specified in the DB2 UDB call-level interface (CLI) configuration file for the primary database. If the autocommit parameter is not turned on, a deadlock problem may occur. 1. Make a copy of the sample db2cli.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Adding a Temporary Tablespace to the Primary Database Add a temporary tablespace to the primary database. 1. Start the DB2 UDB command-line processor (CLP): %>bash %>source /db2home/db2inst1/sqllib/db2profile %>db2 2. Connect to the primary DB2 UDB database: db2=>connect to pdb user db2_admin_user using db2_admin_password where db2_admin_user and db2_admin_password are the administrative user ID and password for the primary database. 3.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup 1. Locate the resource file template. The majority of configuration values required to create and initialize a Replication Agent can be recorded and stored in a resource file. Using a resource file provides a way to record or retain the configuration information for a Replication Agent instance, allowing an instance to be removed and re-created. The resource file template for a DB2 UDB instance is $SYBASE/RAX-15_5/init/ ibmudb.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Parameter Description Example values ltm_admin_pw Password for the user administering the Rep- The default password policy is lication Agent instance. that the password must be between 6 and 255 characters. For example: sa_pass pds_host_name Machine (host) where DB2 UDB is installed. NY pds_port_number Port number for DB2 UDB. 50007 Note: Make sure you know which port your DB2 UDB instance is using.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Parameter Description Example values rs_charset Character set that Replication Server is using. • • Windows: cp850 UNIX: iso_1 Note: The value defined for the rs_charset configuration parameter must match the RS_charset value in the Replication Server configuration file, $SYBASE/ REP-15_5 /install/.cfg (UNIX or Linux) or %SYBASE %\REP-15_5 \install\.cfg (Windows). rssd_host_name Machine where RSSD resides.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup %SYBASE%\RAX-15_5\bin\ra_admin.bat -vr myra.rs • On UNIX or Linux: $SYBASE/RAX-15_5/bin/ra_admin.sh -vr myra.rs where myra.rs is the path and name of the resource file. Validation results are returned as either: • Response-file processing completed, or, • Response-file processing completed with errors If any validation fails, the ra_admin utility returns an error message and information about the failure.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Verifying the Replication Agent Instance Installation Verify that the Replication Agent instance has been successfully installed. 1. Use dsedit to update the Replication Server sql.ini (Windows) or interfaces (UNIX or Linux) file to include an entry for the Replication Agent location. Note: You can use any Tabular Data Stream™ (TDS) client utility (isql, isqlApp, or SQLAdvantage) that you prefer.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup You see: Type Connection ---- ---------PDS succeeded (1 row affected) b) If the result indicates a failure: • • The server is not responding, or One of the connection properties is incorrect. Manually log in to the primary DB2 UDB database as the configured user to find which property is incorrectly defined. See "Testing network connectivity" in the Replication Agent Administration Guide.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup 1. Initialize the Replication Agent instance: ra_admin init go You see a message indicating that the procedure was successful. 2. Enter: resume go If the Replication Agent successfully transfers to a replicating state, you see: State ----------REPLICATING Action ----------------------Ready to replicate data. 3. The ra_status command returns the state of the Replication Agent.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup 24 25 23 22 62 DSI DIST SQT SQM SQM REP AGENT Suspended Awaiting Wakeup Awaiting Wakeup Awaiting Message Awaiting Message Awaiting Command 103 pds.pdb 103 pds.pdb 103:1 DIST pds.pdb 103:1 pds.pdb 103:0 pds.pdb pds.pdb Testing Replication When you finish setting up the replication system, test that replication works as intended. 1. Connect to the primary DB2 UDB instance as a regular user, rather than as the maintenance user.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup Note: SAP HANA database is not case-sensitive and uses uppercase characters if lowercase characters are provided. If an owner is not specified with the tablename, the owner of the table——is the user that is signed on at the time the table is created. 7.
CHAPTER 6: DB2 UDB-to-SAP HANA Database Replication Setup 2. Reset the locator stored in the Replication Agent: ra_locator zero go 3. Reset the locator stored in Replication Server: isql -Usa -Psa_pass -SSAMPLE_RS connect go rs_zeroltm pds, pdb go exit You see: Locator has been reset to zero. (return status = 0) 4.
CHAPTER 7 Glossary This glossary describes Replication Server Options terms. • Adaptive Server® – the brand name for Sybase relational database management system (RDBMS) software products. • • • • • • • • • • • • Adaptive Server® Enterprise manages multiple, large relational databases for highvolume online transaction processing (OLTP) systems and client applications.
CHAPTER 7: Glossary • • • • • • • • • • • 106 can have only one database connection in Replication Server. See also Replication Server and route. data client – a client application that provides access to data by connecting to a data server. See also client, client application, and data server. data distribution – a method of locating (or placing) discrete parts of a single set of data in multiple systems or at multiple sites.
CHAPTER 7: Glossary • • • • • • • • • • gateway – connectivity software that allows two or more computer systems with different network architectures to communicate. inbound queue – a stable queue managed by Replication Server to spool messages received from a Replication Agent. See also outbound queue and stable queue. interfaces file – a file containing information that Sybase Open Client and Open Server™ applications need to establish connections to other Open Client and Open Server applications.
CHAPTER 7: Glossary • • • • • • • • • • • • • • 108 Log Transfer Interface – an internal component of Replication Agent that interacts with Replication Server to forward transactions for distribution to Replication Server. See also Log Reader and Log Transfer Manager. Log Transfer Language – the proprietary protocol used between Replication Agent and Replication Server to replicate data from the primary database to Replication Server. See also Log Reader and Log Transfer Interface.
CHAPTER 7: Glossary • • • • • • • primary database – the database that contains the data to be replicated to another database (the replicate database) through a replication system. The primary database is the source of replicated data in a replication system. Sometimes called the active database. Contrast with replicate database. See also primary data. primary key – a column or set of columns that uniquely identifies each row in a table.
CHAPTER 7: Glossary • • • • • • • • • • • • • 110 replicated transaction – a primary transaction that is replicated from a primary database to a replicate database by a transaction replication system. See also primary database, primary transaction, replicate database, and transaction replication. replicate site – the location or facility at which replicate data servers and replicate databases are deployed to support normal business operations during scheduled downtime at the primary site.
CHAPTER 7: Glossary • • • • • • • table – in a relational DBMS, a two-dimensional array of data or a named data object that contains a specific number of unordered rows composed of a group of columns that are specific for the table. See also database. transaction – a unit of work in a database that can include zero, one, or many operations (including insert, update, and delete operations), and that is either applied or rejected as a whole.
CHAPTER 7: Glossary 112 Replication Server
Index Index A accessing and downloading from SPDC 8, 25, 53, 81 ASE configuring Replication Server for replication from the primary database 16 installing replication components 9 installing Replication Server 10 system requirements 7, 8 testing replication 18 ASE-to-SAP HANA database replication configuring Replication Server 14 C cataloging a node and a database 89 configuring Primary Oracle database for replication 35 Replication Agent for DB2 UDB 92 Replication Agent for Microsoft SQL Server 62 Replic
Index testing replication 102 verifying the Replication Agent instance 99 default directory 11, 27, 83 directories default 11, 27, 83 E installing Replication Server 54 resetting the primary database for replication 76 system requirements 52 testing replication 75 verifying the Replication Agent instance 70 Microsoft SQL Server JDBC driver 66 errors while starting installer 10, 26, 54, 82 existing installation directories 11, 27, 83 ExpressConnect for HANA DB configuring 14, 32, 59, 89 N I Oracle conf
Index replication components configuration 59, 87 Replication Server, adding primary database 16 resetting primary ASE database for replication 20 resetting primary Oracle database for replication 48 S setting up replication for DB2 UDB 79 setting up replication from ASE to SAP HANA database 7 setting up replication from Oracle to SAP HANA database 23, 51 setup prerequisites 7, 24, 52, 79 system requirements 24 ASE 7 DB2 UDB 79 Microsoft SQL Server 52 Oracle 24 Quick Start Guide for SAP HANA Database T t
Index 116 Replication Server
