Administer > Configuration > Database Administration > Install the CSA Database Schema

Upgrade or Install a Fresh CSA Database Schema

The schema installation tool is used to upgrade the existing CSA database schema or to install a fresh database schema without re-installing CSA. Use this tool if you did not install CSA database components onto the database during installation, did not upgrade the database schema during an upgrade, or if you want to drop the existing schema and install a fresh CSA database schema.

Note If you do not install CSA database components during an install or upgrade, and you are going to run the schema installation tool, you need to run the org migration tool manually after that. For details see the Cloud Service Automation Upgrade Guide.

You can also use this tool to complete an upgrade if the upgrade failed, the database schema was not updated, the failure was not due to a database problem, and the problem can be fixed without rerunning the upgrade installer. For example, if the upgrade failed but can be completed successfully by manual configuration but the database schema was not updated, you can simply make the manual changes to complete the upgrade and run the schema installation tool instead of reverting CSA back to the previous version and running the upgrade installer again.

Note Do not run the schema installation tool if you installed the database components during the installation of CSA or if you upgraded the database schema when you upgraded CSA.

If you run the schema installation tool on an existing schema (where CSA has been upgraded but the database schema was not upgraded), the schema is upgraded and no data in the database is lost. However, if you drop the existing schema and run this tool, all data in the database associated with the dropped schema is lost. Once you run the tool, a fresh schema is installed and you cannot revert back to the dropped schema.

Caution Once you drop an existing schema and run the database schema installation tool, you cannot revert back to the dropped schema.

Upgrading or Installing the Database Schema

To upgrade or install a fresh CSA database schema, do the following:

  1. If CSA is running, stop CSA.

    To stop CSA on Windows, complete the following steps:

    1. On the server that hosts CSA, navigate to Start > Administrative Tools > Services.
    2. Right-click on the CSA service and select Stop.

    3. Right-click on the HPE Marketplace Portal service and select Stop.

    4. If you installed an embedded Operations Orchestration instance, right-click on the HPE Operations Orchestration Central service and select Stop.

    5. If you enabled global search, do the following:

      1. Right-click on the Elasticsearch 1.6.1 service and select Stop. You do not need to stop this service if global search is disabled (by default, global search is disabled).

      2. Right-click on HPE Search Service and select Stop. You do not need to stop this service if global search is disabled (by default, global search is disabled).

    6. If Elasticsearch is enabled (by default, Elasticsearch is enabled; refer to the csa.provider.es.exists property in Appendix: Cloud Service Management Console Properties for more information), right-click on the Elasticsearch 1.6.1 service and select Stop.

    To stop CSA on Linux, complete the following steps:

    1. On the server that hosts CSA, type the following commands:

      service csa stop
      service mpp stop
    2. If you installed an embedded Operations Orchestration instance, type:

      <embeddedHPEOOinstallation>/central/bin/central stop

      For example, type: /usr/local/hpe/csa/OO/central/bin/central stop

  2. Change to the CSA_HOME/Tools/SchemaInstallationTool/ directory where

    CSA_HOME is the directory in which CSA is installed

    .
  3. During upgrade or installation of CSA, a file named db.properties was generated in CSA_HOME/Tools/SchemaInstallationTool/. Verify the property values in this file. If you changed any database property values in the CSA_HOME/jboss-as/standalone/configuration/standalone.xml file after installation, the values in db.properties may not be up‑to‑date.

    If you have dropped the existing database schema and are installing a fresh database schema after upgrading to CSA 4.80.0002, you must update the driverFiles property value. The properties defined in db.properties are described in the table.

    Property Name Description
    dbUrl

    The JDBC URL. When specifying an IPv6 address, it must be enclosed in square brackets (see examples below).

    Examples

    Oracle (TLS not enabled): jdbc.databaseUrl=jdbc:oracle:thin:@//127.0.0.1:1521/XE

    Oracle (TLS not enabled, using an IPv6 address): jdbc.databaseUrl=jdbc:oracle:thin:@//[f000:253c::9c10:b4b4]:1521/XE

    Oracle (TLS enabled, CSA does not check the database DN): jdbc.databaseUrl=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCPS)(HOST = <host>)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = ORCL))) where <host> is the name of the system on which the Oracle database server is installed.

    Oracle (TLS enabled, CSA checks the database DN): jdbc.databaseUrl=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = <host>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL))(SECURITY=(SSL_SERVER_CERT_DN=
    "CN=abc,OU=dbserver,O=xyz,L=Sunnyvale,ST=CA,C=US")))

    where <host> is the name of the system on which the Oracle database server is installed and the values for SSL_SERVER_CERT_DN are for the DN of the Oracle database server.

    MS SQL (TLS not enabled): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=request


    MS SQL (TLS not enabled, using an IPv6 address): jdbc.databaseUrl=jdbc:jtds:sqlserver://[::1]:1433/
     example;ssl=request


    MS SQL (TLS enabled): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate


    MS SQL (FIPS 140-2 compliant): jdbc.databaseUrl=jdbc:jtds:sqlserver://127.0.0.1:1433/
     example;ssl=authenticate


    PostgreSQL: jdbc.databaseUrl=jdbc:postgresql://127.0.0.1:5432/csadb

    dbUserName The user name of the database user you configured for CSA after installing the database.
    dbPassword

    The password for the database user. The password should be encrypted (see Encrypt a password for instructions). An encrypted password is preceded by ENC without any separating spaces and is enclosed in parentheses.

    While you may enter a password in clear text, after you run the tool, the clear text password is automatically replaced by an encrypted password.

    If you have configured CSA to be FIPS 140-2 compliant on Windows, encrypt this password after you have configured CSA to be FIPS 140-2 compliant (that is, you should use the updated encryption tools to encrypt the password).

    Example

    dbPassword=ENC(fc5e38d38a5703285441e7fe7010b0)

    driverFiles

    The database driver files used by this tool. If you are running a fresh installation of CSA 4.80.0002 (you did not upgrade to CSA 4.80.0002), you do not need to change these values.

    If you have upgraded to CSA 4.80.0002 and want to upgrade the existing schema, you do not need to change these values.

    If you have upgraded to CSA 4.80.0002, have dropped the existing database schema, and are installing a fresh database schema, you must update this value to the following:

    Oracle (upgrade and dropped schema only)
    driverFiles=CSA_HOME\scripts\schemainstallforupg\
    create-oracle-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\
    create-oracle-topology-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\oracle\
    seed_data_driver.sql,
    CSA_HOME\scripts\reporting\oracle\
    install_views_driver.sql,
    CSA_HOME\scripts\reporting\oracle\
    grant-reporting-user.sql

    PostgreSQL (upgrade and dropped schema only)
    driverFiles=CSA_HOME\scripts\schemainstallforupg\
    create-postgres-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\
    create-postgres-topology-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\postgres\
    seed_data_driver.sql, CSA_HOME\scripts\reporting\postgres\
    install_views_driver.sql,
    CSA_HOME\scripts\reporting\postgres\
    grant-reporting-user.sql

    Microsoft SQL (upgrade and dropped schema only)
    driverFiles=CSA_HOME/scripts/schemainstallforupg/
    alterdb.sql,
    CSA_HOME\scripts\schemainstallforupg\
    create-mssql-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\
    create-mssql-topology-schema.sql,
    CSA_HOME\scripts\schemainstallforupg\
    mssql\seed_data_driver.sql,
    CSA_HOME\scripts\reporting\mssql\
    install_views_driver.sql,
    CSA_HOME\scripts\reporting\mssql\
    grant-reporting-user.sql

    Note Add the grant-reporting-user.sql file only if you have created the reporting database user for CSA.

    jdbcDriverClassName

    The JDBC driver class. Do not change this value.

    Examples

    Oracle: jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
    MS SQL: jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
    PostgreSQL: jdbc.driverClassName=org.postgresql.Driver

    jdbcDriverDir

    The location of the JDBC driver(s) used by this tool. Do not change this value.

  4. Run the following command:

    Oracle (TLS not enabled), MS SQL, and PostgreSQL

    • Windows:
      "CSA_JRE_HOME\bin\java" ‑jar schema‑installation‑tool.jar
    • Linux:

      CSA_JRE_HOME/bin/java ‑jar schema‑installation‑tool.jar

      where CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed. .

    Oracle (TLS enabled, CSA does not check the database DN, client authentication is enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java"
      ‑Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar schema-installation-tool.jar
    • Linux:

      CSA_JRE_HOME/bin/java
      ‑Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar schema-installation-tool.jar


      where certificate_key_file is the same keystore file defined by the certificate-key-file attribute in the ssl element of the CSA_HOME/jboss-as/standalone/configuration/standalone.xml file (for example, CSA_HOME/jboss-as/standalone/configuration/.keystore), certificate_key_file_password is the password to the keystore file (for example, changeit), certificate_key_file_type is the keystore type (for example, JKS or PKCS12) and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed. .

    Oracle (TLS enabled, CSA does not check the database DN, client authentication is NOT enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java" ‑jar schema‑installation‑tool.jar
    • Linux:

      CSA_JRE_HOME/bin/java ‑jar schema‑installation‑tool.jar

    Oracle (TLS enabled, CSA checks the database DN, client authentication is enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java"
      ‑Doracle.net.ssl_server_dn_match=true
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar schema-installation-tool.jar
    • Linux:

      CSA_JRE_HOME/bin/java
      ‑Doracle.net.ssl_server_dn_match=true
      -Djavax.net.ssl.keyStore="<certificate_key_file>"
      -Djavax.net.ssl.keyStorePassword=<certificate_key_file_password>
      -Djavax.net.ssl.keyStoreType=<certificate_key_file_type>
      -jar schema-installation-tool.jar


      where certificate_key_file is the same keystore file defined by the certificate-key-file attribute in the ssl element of the CSA_HOME/jboss-as/standalone/configuration/standalone.xml file (for example, CSA_HOME/jboss-as/standalone/configuration/.keystore), certificate_key_file_password is the password to the keystore file (for example, changeit), certificate_key_file_type is the keystore type (for example, JKS or PKCS12), and CSA_JRE_HOME is the directory in which the JRE that is used by CSA is installed.

    Oracle (TLS enabled, CSA checks the database DN, client authentication is NOT enabled on the Oracle database server)

    • Windows:
      "CSA_JRE_HOME\bin\java" ‑Doracle.net.ssl_server_dn_match=true -jar schema-installation-tool.jar
    • Linux:

      CSA_JRE_HOME/bin/java ‑Doracle.net.ssl_server_dn_match=true -jar schema-installation-tool.jar

  5. Prepare icons and java scripts to be loaded to the database:

    1. Change to the CSA_HOME/images directory.

      If there is a single file named icons-backup<time_stamp><ip_address>.zip then unzip this file into the current directory. When CSA is started the images will be migrated to the database.

      If the icons-backup<time_stamp><ip_address>.zip file is missing or the directory contains these directories: catalog, categories and library, then skip this step 5 completely.

    2. Change to the CSA_HOME/propertysources directory.

      If there is a file named js-backup<time_stamp><ip_address>.zip then unzip this file into the current directory. When CSA is started the java scripts will be migrated to the database.