Preparing Databases

SAP HANA Database Preparation

Warning: Before starting, review the page Managed Systems Preparation and Maintenance Guide page.

Task Actions
Preparation Tool (optional) In order to automate certain tasks, you can use the Preparation Tool
Install SAP HANA DB version 80 or higher

You need at least SAP HANA DB version 80, optionally install the latest SAP HANA and latest Patches from support.sap.com/swdc

Install SAP Note 2378916

Run Outside Discovery on the HostSet SAP HANA Network configurations according to SAP standard recommendations For example, host names of HANA-replicated DBs need to be unique.

SAP HANA SLD Parameterization

Correct SLDSYSTEMHOME SLDVIRTDBHOME (certain use cases) of HANA DB need to be sent with SLD

As SAP HANA <DBSID>adm user, at OS level:

Configure the HANA Technical System default.pfl profile parameters for SLDSYSTEMHOME and SLDVIRTDBHOME (only in case of replication scenarios) to match with the database identifier of technical systems using this HANA system. SLDVIRTDBHOME needs to be set on all SAP HANA DB clusters in the replication scenario to the same value as reported by the ABAP or JAVA system using this SAP HANA DB as database (virtualized DB host name). SLDSYSTEMHOME is to be set in order to ensure that it makes no difference which host is the master host in a SAP HANA scale out system and it needs to be set to the same value as reported by the ABAP or JAVA system using this SAP HANA DB as database (when replication and virtualized DB host name is used). If set incorrectly, SAP HANA needs to be restarted.

This should be a regular post-installation task, however, if it is not executed correctly at installation, it needs to be adapted. 

SAP HANA Replication Scenarios For SAP HANA revisions < 122.04 the handling of SAP HANA replication scenarios is not complete in the SAP HANA SLD data supplier and therefore manually adding of replication and virtual DB links in LMDB of Solution Manager is required. See section: manual adjustment of LMDB for SAP HANA replication scenarios.
Check if an SLD Data Router (SLDR) is needed As prerequisites, you need to choose, according to your landscape, if an SLDR is required see page Preparing System Landscape Data Router.

Check Needed Data Suppliers

The SAP HANA DB can be used in standelone or associated to an ABAP or Java Stack

The SAP HANA DB can be used in standelone or associated to an ABAP or Java Stack. Therefore, the corresponding Data Supplier needs to be setup. See the SAP NetWeaver Application Server ABAP Preparation or SAP NetWeaver Application Server Java Preparation pages.

Note: To ensure that the Database supplied by the ABAP Data Supplier fits to the Database reported by the HANA Data Supplier, you shall set the parameter SLDSYSTEMHOME on the ABAP / JAVA Stack to the value of VIRTDBHOME (or SLDSYSTEMHOME) of the HANA Database.

Configure Data Supplier

SAP HANA Database provides its own Data Supplier (SAP HANA Database Data Supplier)

HANA SLD Registration can be done in two ways:

1-Using HANA Studio:

Open the SAP HANA database lifecycle manager (HDBLCM) to configure the HANA SLD DS connection parameters (namserver.ini and Connection credentials) for the system registration in Focused Run. This can be done via command line interface or Graphical User Interface. Check the SAP HANA Administration Guide in the "Configuring HANA System to Connect to the SLD" section.

Note: The Payload can be send also by switching the "nameserver.ini" parameter "SLD Active" in the HANA Studio.

2- Using the "hdblcm" command command line. Exemple As <sid>adm user, or root user, at OS level:

  • Generate slddest.cfg file by running: /hana/shared/<SID>/hdblcm
  • Choose Option 5 "configure_sld" and provide SLD parameters
  • If hdblcm was executed the first time, the Datatransfer is triggered immediately. If an existing configuration is changed, the data will be send within the next 12 hours.
  • Check the slddest.cfg: /usr/sap/<DBSID>/SYS/exe/hdb> sldreg -showconnect /usr/sap/<SID>/SYS/global/slddest.cfg
  • Restart the service to send the payload /usr/sap/<DBSID>/HDB<inst>/exe> sapcontrol -nr <instance> -function RestartService
  • Data transfer result  can be checked in sldreg.log located in the trace directory of the HANA installation.

See also the HANA Troubleshooting Guide

Create SAP HANA monitoring user and provide connection credentials.

Focused Run uses SAP Host Agent Web methods to get DB monitoring data. 

Create a SAP HANA user on DB level with proper authorizations and maintain the hdbusertore with associated credentials.

You need to first confirm whether your SAP HANA Technical System it is based on a single DB, or part of a Multi Database Container (MDC) and this either on a system DB or on a tenant DB. If you are unsure, use one of the following ways to determine the type:

First of all in the HANA studio you see SYSTEMDB@<SID> for a system DB in a MDC, or you see <DBNAME>@<SID> for a tenant DB in a MDC.

If needed, you can also look-up the SAP HANA Technical System definition using the LMDB (Object Maintenance) from the Launchpad your FRun system, since the SLD Data Supplier registration was already performed (see previous task). When you visualize the Technical system in LMDB, press the Details button.

When selecting the tab Properties, search for the property IsSystemDB. The value will be false for a single DB (and also for a tenant DB in a MDC).

Also you can select the tab Associated Instances, and sort on the Associations. If there is an association SAP_HDBSystemSubDatabase, thenit is part of a MDC.

The SAP Host Agent provides a method to create this user and maintain the hdbuserstore within one single OS command. See also SAP Note 2023587.

 

Depending on the SAP HANA DB type:

  • For single DBs, or SYSTEMDB of a Multi Database Container (MDC):

Run OS command in context of user sapadm:

/usr/sap/hostctrl/exe/saphostctrl -dbname <SID>-SYSTEMDB -dbinstance HDB<instnr> -dbtype hdb -dbuser SYSTEM -dbpass <dbpassword> -function SetDatabaseProperty DBCredentials=SET -dboption User=SAPDBCTRL -dboption Password=<desiredPassword>

Note: In the hdbuserstore the following key will be updated: <SID>SAPDBCTRL

  • For tenant DBs of a Multi Database Container (MDC):

Run OS command in context of user sapadm: 

/usr/sap/hostctrl/exe/saphostctrl -dbname <DBNAME - Like displayed in HANA Studio>  -dbinstance HDB<instnr> -dbtype hdb -dbuser SYSTEM -dbpass <dbpassword> -function SetDatabaseProperty DBCredentials=SET -dboption User=SAPDBCTRL -dboption Password=<desiredPassword>

Note: In the hdbuserstore the following key will be updated: <SID>SAPDBCTRL<TenantSQLPort>

The <TenantSQLPort> is usually 30<instnr>41 for the first installed tenant, and 30<instnr>44 for the second, and so on.

In case this MDC was migrated from a single DB, then the <TenantSQLPort> for the first tenant DB is usually 30<instnr>15 as it keeps the port that had been used before the migration.

Options to Achieve Automation

Performed either manually or in OS script

  • Central SAP HANA user management
  • Local or remote call of SAP Host Agent function SetDatabaseProperty.
SAP HANA Webdispatcher Parameterization (Only since Focused Run 1.0 FP01).

Only since Focused Run 1.0 FP01, the RUM Application is able to minitor XS Traffic.

Configure the HANA Webdispatcher from HANA Studio like following:

  • Open the configuration tab
  • Select the webdispatcher.ini parameter
  • Drilldown to profile
  • Right Click and select "Add Parameter"

icm/HTTP/logging_0 = PREFIX=/, LOGFORMAT=%t %h %u - "%r2" %s %b %L epp[%{sap-passport}i] fesr[%{SAP-Perf-FESRec}i] fesropt[%{SAP-Perf-FESRec-opt}i],  LOGFILE=$(DIR_INSTANCE)/trace/http_fe.log, MAXSIZEKB=25000, SWITCHTF=day, FILEWRAP=on

Install SAP Note to fix monitoring issues

Install SAP Note if you observe many blue (grey) metrics without any measure value

You have connected a SAP HANA DB of revision 122.02 or 122.03 to a Focused Run system for monitoring and observe many blue (gray) metrics without any measure value and the text "nothing to report" shown in the system monitoring UI. Additionally you have installed SAP Host agent 7.21 SP18 on the SAP HANA hosts.

Manual Adjustment of LMDB for SAP HANA Replication Scenarios

This is required for SAP HANA revisions older than 122.04 due to errors in the SAP HANA SLD Data Supplier.

These errors may result in missing relations between the involved databases. In these cases you have to add these relations manually within LMDB and this sections explains how this can be done.

The following example shows the list of Systems expected in SSI for an ABAP on SAP HANA scenario with the SAP HANA DB replication:

  • <SID><CID> - SAP HANA Database (Primary) - "dbhost1" (SAP HANA Data Supplier)
  • <SID> - SAP HANA Database (Secondary) - "dbhost3" (SAP HANA Data Supplier)
  • <SID>00001 - Application Server ABAP Virtualized SAP HANA Database - "dbhost2" (RZ70 Data Supplier)
  • <SID><CID> - Application Server ABAP - "host" (RZ70 Data Supplier)

In LMDB, it must be shown as follows:

  • Technical System <SID> on Host dbhost1:
    Type: SAP HANA Database
    Database Virtualisation Type: Physical Database
    Role in Replication: Primary Database
  • Technical System <SID> on Host dbhost2:
    Type: SAP HANA Database
    Database Virtualisation Type: Virtual Database
    Role in Replication: None
  • Technical System <SID> on Host dbhost3:
    Type: SAP HANA Database
    Database Virtualisation Type: Physical Database
    Role in Replication: Secondary Database
  • Check that under the "Related Databases" / "Virtualized Relations of Selected Database" LMDB Section, the primary Database is linked to the Virtual Database.
  • Check that under the "Related Databases" / "Replication Relations of Selected Database" LMDB Section, the primary Database is linked to the Secondary Database.

If any of these is missing it can be manually added in LMDB via "Edit", then "Assign Virtual Database" and "Assign Secondary Database". Note: By assigning a Virtual Database, the Virtualization Type of the assigned Database will automatically be changed to "Virtual Database". By assigning a Secondary Database the role of the own Database, will be changed to "Primary Database" and the assigned databases "Role in Replication" will be changed to "Secondary Database". Additionally the backward link from the secondary Database will be added in the "Replications Relations" list of the assigned Secondary Database.

Handling Replication across Data Centers

For a replication scenario across multiple data centers the links between primary and secondary SAP HANA DB cannot be established within LMDB (due to the LMDB namespace separation for data centers). Therefore the systems will not show up as replicated within LMDB. The systems can still be setup and monitored separately but a manually set LMDB flag is required to tell the information that replication monitoring metrics shall be configured.

Set the custom attribute "ZHEC_REPL_TYPE" in LMDB for each SAP HANA DB involved in the replication scenario. This flag triggers Solution Manager to configure replication metrics also if the DB does not have the role "Primary Database" or "Secondary Database" in LMDB (which is the case if the replication partner is in a different data center).

Limitations:

  • Primary and secondary DBs will not explicitly show up in LMDB as such but appear like independent DBs.
  • As of now configuring monitoring of the secondary cluster will not automatically detect that it is a secondary site and if the same monitoring template is used this results in gray metrics. Using a separate custom templates for secondary clusters where all metrics are disabled avoids this problem.

After a switchover a manual reconfiguration of the monitoring with exchanged roles (assigned templates) is required. 

SAP Adaptive Server Enterprise (ASE) Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions
Install ASE DB 15.7 or higher

You need at least ASE DB 15.7, optionally install the latest ASE DB and latest Patches from support.sap.com/swdc -> Databases -> SAP Adaptive Server Enterprise

To check details which ASE DB monitoring feature is requiring which SAP Hostagent version please see SAP Note 2577046.

Check Security Guide and especially SAP Note 2236137 - SYB: saphostctrl/sapdbctrl - enable discovery for native ASE database installations.

Run Outside Discovery on the Host

As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in page Preparing Outside Discovery.

Be aware the hostname interpreted as  DB Hostname  by Outsided Discovery is the first entry set in file  in $ASE_HOME$/interfaces 

Check Needed Data Suppliers

The SAP ASE DB can be used in standelone or associated to an ABAP or Java Stack

The SAP ASE DB can be used in standelone or associated to an ABAP or Java Stack. Therefore, the corresponding Data Supplier needs to be setup. See the SAP NetWeaver Application Server ABAP Preparation or SAP NetWeaver Application Server Java Preparation pages.

Create DB monitoring user and provide connection credentials

Focused Run for SAP Solution Manager utilizes SAP Host Agent Web methods to get DB monitoring data from sapsa as in rsecssfs secure store with key DB_CONNECT/SYB/SADB_USER.

  1. Create the DB monitoring user for the ASE database with the right authorization. 
  2. Create an entry for the monitoring user in the secure store.

    Note: The SAP Host Agent provides a simple method, for 1 and 2, following SAP Note 1797040.

  3. Initialize (once) the ASE DB monitoring

If the ASE DB monitoring was not initialized with the Transaction "DBACockpit" do the following: 

For all ASE DB, the execution of the SAP Host Agent method 

saphostctrl -function SetDatabaseProperty -dbname <SID> -dbtype syb SETUP_LIST_METRICS=saptools is recommended (easier than to configure a remote DBA Cockpit Connection to a remote ABAP System).

Note: All SAP Host Agent methods can be executed and scripted local or remote by a saphostctrl or any other webservice client.

Alternative process:

The monitoring user can be created by ASE user management sqls using ISQL.
The secure store can be maintained by OS command rsecssfx.

SAP MaxDB Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions
Prerequisits Minimum requirement for the SAP Host Agent is 7.21 SP22
Install SAP Max DB 7.9 Client Build 40 or higher

You need at least SAP Max DB 7.9 Client Build 40, optionally install the latest SAP MaxDB Client and latest Patches from support.sap.com/swdc -> Databases -> SAP MaxDB

The installation of the MaxDB Client is described in SAP Note 649814.

Create the User Store
  • If a sapdbctrl user is already created on Max DB, remove it.
  • As root User, add Write permissions to the /usr/sap/hostctrl/exe folder
  • Create the User Store for sapadm: /usr/sap/hostctrl/exe/saphostctrl -host <hostname> -user sapadm <password> -dbname <dbname> -dbtype ada -function SetDatabaseProperty DBCredentials=SET -dboption User=SUPERDBA -dboption Password=<SUPERDBA Password>
  • Delete the Write permissions to the /usr/sap/hostctrl/exe folder.

See also how to set connection informations  in SAP Note 2018919

Note: can be automated by os script

Check created User Store

Check created User Store: /sapdb/programs/bin/xuser list

Note: can be automated by os script

Check Database status

Optional (eg. for debugging)

/hostctrl/exe/saphostctrl -function ListDatabases
/hostctrl/exe/saphostctrl -function GetDatabaseStatus –dbname <DBNAME> -dbtype ada

Run Outside Discovery on the Host As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in page Preparing Outside Discovery.

Microsoft SQL Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions
Install Database
Install MS-SQL 2008 or higher
You need at least MS-SQL 2008, optionally install the latest MS SQL DB and latest Patches from support.sap.com/swdc -> Databases -> MS SQL Server

Create DB monitoring user and provide connection credentials

Focused Run uses SAP Host Agent Web methods to get DB monitoring data. For MSSQL DB, the connection credentials are part of windows authorizations.

Check that your MSSQL installation follows the recommendations in SAP Note 1877727 and 1564275 Be aware the hostname interpreted as  DB Hostname  by Outsided Discovery discovery is the hostname as set in Windows registry as BackConnectionHostNames  described in this note

Note: can be automated by os script

Run Outside Discovery on the Host As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in page Preparing Outside Discovery.
Get DB metrics from the OS

Optional operation

hostctrl/exe/saphostctrl -function GetCIMObject -enuminstances SAP_ITSAMDatabaseMetric -properties "Name=<DBNAME>&Type=<DBTYPE>" -arguments "ID=TOTAL_DATA_AREA"

IBM DB2 for LUW Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions

Install database

Install IBM DB2 for LUW 9.7 or higher

You need at least IBM DB2 for LUW 9.7, optionally install the latest IBM DB2 for LUW 9.7 and latest Patches from support.sap.com/swdc -> Databases -> DB2
Configure Database for non-SAP NetWeaver systems Database configuration according to SAP recommendations; especially all MON_* configuration parameters. See SAP Note 1334923.
Check the "db2greg" executable availability

Optional operation

The DB2 plugin of the SAP Host Agent calls the db2greg executable to detect installed IBM DB2 databases. This Plugin needs to be present on the filesystem, otherwise no IBM database can be detected by the SAP Host Agent during Outside Discovery Execution.

Search sequence is

/db2/%3[A-Z0-9]/sqllib/bin/db2greg
/db2/db2%3[A-Z0-9]/sqllib/bin/db2greg 
/usr/local/bin/db2ls

 

Note: %3[A-Z0-9] is the pattern for <SID>. 

Check that the "db2greg" plugin is properly deployed to this directory by SAP and IBM Standard (depending on version and product) for managed Systems on IBM DB2 or Standalone IBM DB2 with FRUN.

Check returned database properties

Optional operation

From the hostctrl exe folder execute: saphostctrl -function GetDatabaseProperties -dbname <DBSID> -dbtype db6

Web method returned successfully:

Database/DBRelease=09.07.xxxx
Database/ConfigurationDirectory/Default=/<DBTYPE>/<DBSID>/sapdbctrl-config
Database/InstanceList=Name=0;Host=<FDQN>;Port=xxxx
Database/ConnectAddress=
Database/ManagementHosts=<hostname>

IBM DB2 bug fix to implement In case you are using SHA PL29, you might face a DB2 Defect related to db2gcf and the reporting of instances. Check the IBM DB2 Website
Run Outside Discovery on the Host As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in page Preparing Outside Discovery.

Check Needed Data Suppliers

The IBM DB2 DB can be used in standelone or associated to an ABAP or Java Stack

The IBM DB2 DB can be used in standelone or associated to an ABAP or Java Stack.

Therefore, the corresponding Data Supplier needs to be setup.

See the SAP NetWeaver Application Server ABAP or SAP NetWeaver Application Server Java pages.

LMDB database association

Virtualization relations of the database must be done manually

Optional operation

Open LMDB and search for your virtual DB. Check the assigned Physical / Logical Dadabase. 

Oracle Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions
Install Oracle Database Optioanally install the latest Orcale DB and latest Patches from support.sap.com/swdc -> Databases -> Oracle

Create DB monitoring user and set connection credentials

 

No specific tasks are to do be performed for Oracle database. The SAP Host Agent can manage the connection to an Oracle DB by itself without additional credential.

In the case of connection problems, see SAP Host Agent Troubleshooting Guide (Oracle DB) page.

Run Outside Discovery on the Host As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in Preparing Outside Discovery page.

SAP IQ Database Preparation

Warning: Before starting, make sure you studied the page Managed Systems Maintenance Guide page.

Task Actions
Install SAP IQ Database (formerly Sybase IQ)

You need at least SAP IQ DB 16.0, optionally install the latest version of the SAP IQ DB and latest Patches (please be aware there is an actual issue with 16.1, for more information check SAP KBA 2719876) from support.sap.com/swdc, Software Downloads, Search for SAP IQ 16.0. See also SAP IO Installation guide.

Operating Systems:  AIX 64, HP-UX on IA64, Linux on IA64, Linux on POWER, Linux on X86_64, Solaris for X64, Solaris / Sparc 64, Windows Client 64-BIT, Windows for X86_64

When configuring the database, do not use the option "-sb 0" or "-sb 1". While it might be tempting to hide the database from discovery by the dblocate tool, you also hide it from discovery by the SAP Host Agent and thus cut it off from monitoring. Also avoid to let the database listen on the default port 2638 as this may lead to conflicts with a utility database or a demo database on the same host.

Set DBA User and Configure SAP IQ DB Server

1. Create siqconfig.cfg

1.1. collect the following data:

  • DB-NAME - The filename of the database file without path and without extension
  • DB-INSTANCE - The value of the startup parameter "-n" – see the database configuration file
  • DB-PORT - The listen port of the database, see startup parameter "-x"
  • IQ-HOME - The parent directory of the directory IQ-16_* which is parent of bin64, lib64 etc.
  • IQ-DIR - The name of the directory IQ-16_*
  • DB-LOCATION - The full path of the database file
  • CFG-LOCATION - The full path of the database configuration file
  • DBA-USER - User ID with DBA privileges
  • DBA-PASSWD - Password of above user

1.2. From /usr/sap/hostctrl execute:

exe/saphostctrl -function  SetDatabaseProperty -dbname <DB-NAME> -dbtype siq -dbinstance <DB-INSTANCE> -dboption port=<DB-PORT> -dboption iqhome=<IQ-HOME> -dboption iqdir=<IQ-DIR> -dboption dblocation=<DB-LOCATION> -dboption cfglocation=<CFG-LOCATION> SIQENTRY=INSERT

Example: IQ is installed in /usr/sap/sybase/server, database and configuration are in /usr/sap/sybase/data/db and named SAPIQDB.db and SAPIQDB.cfg, respectively. The instance is named SAPIQ_SERVER and listens on port 45678. Then the data looks like:

  • DB-NAME - SAPIQDB
  • DB-INSTANCE - SAPIQ_SERVER
  • DB-PORT - 45678
  • IQ-HOME - /usr/sap/sybase/server
  • IQ-DIR - IQ-16_0
  • DB-LOCATION - /usr/sap/sybase/data/db/SAPIQDB.db
  • CFG-LOCATION - /usr/sap/sybase/data/db/SAPIQDB.cfg
  • DBA-USER - DBA
  • DBA-PASSWD - sql

execute: exe/saphostctrl -function SetDatabaseProperty -dbname SAPIQDB -dbtype siq -dbinstance SAPIQ_SERVER -dboption port=45678 -dboption iqhome=/usr/sap/sybase/server -dboption iqdir=IQ-16_0 -dboption dblocation=/usr/sap/sybase/data/db/SAPIQDB.db -dboption cfglocation=/usr/sap/sybase/data/db/SAPIQDB.cfg SIQENTRY=INSERT

1.3. Check work/siqconfig.cfg:

iqhost123:/usr/sap/hostctrl # cat work/siqconfig.cfg

#---
instance=SAPIQ_SERVER
port=45678
database=SAPIQDB
host=iqhost123
started=0
dblocation=/usr/sap/sybase/data/db/SAPIQDB.db
cfglocation=/usr/sap/sybase/data/db/SAPIQDB.cfg
iqhome=/usr/sap/sybase/server
iqdir=IQ-16_0

2. Create credential store

2.1. From /usr/sap/hostctrl execute:

exe/saphostctrl -function  SetDatabaseProperty -dbname <DB-NAME> -dbtype siq -dboption user=<DBA-USER> -dboption password=<DBA-PASSWD> SIQUSERCREDENTIAL=INSERT

Example: execute exe/saphostctrl -function  SetDatabaseProperty -dbname SAPIQDB -dbtype siq -dboption user=DBA -dboption password=sql SIQUSERCREDENTIAL=INSERT

Check DB status

Optional operation

Execute: dblocate

Note: In order to run this (and other IQ related programs) you need to source the following script first:

<IQ-HOME>/IQ.sh for sh, ksh, bash or

<IQ-HOME>/IQ.csh for csh

The output of dblocate lists all sybase database servers, IQ, ASE, SQL Anywhere, with server name, IP address and listen port that respond to a network broadcast.

From /usr/sap/hostctrl execute: exe/saphostctrl -function ListDatabases

From /usr/sap/hostctrl execute: exe/saphostctrl -function Get DatabaseStatus -dbname <SAPIQDB> -dbtype siq

Run Outside Discovery on the Host As prerequisites, you shall install the SAP Host Agent and run Outside Discovery as described in Preparing Outside Discovery page.