Configure Enterprise Database

Update RapidDeploy to move from the internal HSQL database, to an enterprise database

RapidDeploy is designed to run with the internally shipped HSQL database, even for enterprise use and in a clustered environment without any further configuration. However, should you wish, you may configure an external enterprise database instead.

Shipped with the MidVision application are some sample scripts that allow users to quickly configure and create a database. The database instance creation is often carried out by a Database Administration function but the values used within the scripts could be communicated to that function.

1. Configuring DB2

These scripts make use of some Java classes that manage the database scripts and can determine if the database is up to date. Subsequent releases might require additional scripts to be run, using the ${MV_HOME}/web-apps/db/bin/maintain-db2-db.sh (or bat) scripts will ensure only new scripts are run in.

The following steps give an example of how a user with sufficient database privileges could create and populate the MidVision database.

NOTE: MidVision is not licenced to supply the DB2 connector library files db2jcc-3.4.65.jar and db2jcc-license-3.4.65.jar To run tomcat with a DB2 database, copy these Jar files from the DB2 installation into the directory ${MV_HOME}/web-apps/tomcat/lib. To populate the DB2 database using the scripts below then also copy these Jar files from the DB2 installation into the directory ${MV_HOME}/web-apps/db/lib.

  1. Create a database user e.g. on Linux create user rddbusr with useradd -m rddbuser
  2. Stop your midvision application (if it is running)
  3. Switch user (su) to a db2 admin user (db2inst1 by default)
  4. Ensure the db2admin user has write permission on the web-apps directory (chmod 777 if necessary).
  5. For first time database install run the ${MV_HOME}/web-apps/db/bin/install-db2-db.sh

    (or bat) script and fill in the details (leave as default unless you have a preference). This script creates the schema user and tablespaces. Note this option will drop any existing database of the same name so treat with caution.

  6. Next run the ${MV_HOME}/web-apps/db/bin/maintain-db2-db.sh (or bat) script. The first time this is run the schema is created. This script may need to be run again when you upgrade RapidDeploy, but only if the schema changes, which happens very infrequently. Subsequent runs will run in any newly added database scripts. This can be done as any user with access to the script file.
  7. Run ${MV_HOME}/web-apps/db/bin/reorg-local-db2-db.sh script
  8. As the owning user edit the ${MV_HOME}/bin/rapiddeploy.properties
  9. Ucomment all the DB2 settings
  10. Update the userid and password fields
  11. Comment out the HSQL settings
  12. Comment out the JNDI settings
  13. Start the web application

2. Configuring Oracle

Oracle does not require an operating system user. You do however need access to the Oracle sys account userid and password.

If your Databases are managed by a centralised database team it is unlikely you will have the necessary access to create the MidVision schema. In that instance the scripts can be given to your database administrators who may choose to use them as a basis for creating the schema. The procedure below can then be followed from ignoring Step 3 by the application owner running as the MidVision service id.

  1. Stop your midvision application (if it is running)
  2. Set the following variable export JRE_HOME=path to your Sun 1.8 version of jre
  3. For first time database install run the ${MV_HOME}/web-apps/db/bin/install-oracle-db.sh (or bat) script and fill in the details (leave as default unless you have a preference). Note this option will drop any existing database of the same name so treat with caution.
  4. For subsequent database data updates run the ${MV_HOME}/web-apps/db/bin/maintain-oracle-db.sh (or bat) script. This will run in any newly added database scripts. This can be done as any user with access to the script file and enclosing directory. Only run this script again if the schema changes, which happens very infrequently.
  5. As the owning user edit the ${MV_HOME}/bin/rapiddeploy.properties
  6. Uncomment all the Oracle settings
  7. Update userid and password as appropriate from step 3 and optionally the URL as per your RAC requirements (see below)
  8. Comment out the HSQL settings
  9. Start the web application

Considerations for Oracle

XA Driver Considerations

When connecting to Oracle or any other enterprise database, you must use an XA Compliant driver. You must also configure the RapidDeploy user that connects to the database with the following grants:

grant select on pending_trans$ to <user>; 
grant select on dba_2pc_pending to <user>;
grant select on dba_pending_transactions to <user>;
grant execute on dbms_system to <user>;  (If using Oracle 10.2.0.3 or lower JDBC driver)
grant execute on dbms_xa to <user>; (If using Oracle 10.2.0.4 or higher JDBC driver)
RAC Considerations

When using Oracle RAC (Real Application Cluster) a different URL format is required for installing the database and using the MidVision extensions. This requires some alteration to the maintain-oracle-db.sh script and/or the ${MV_HOME}/bin/rapiddeploy.properties files.

Standard URL (find in ${MV_HOME}/web-apps/db/bin/maintain-oracle-db.sh script):

        jdbc:oracle:thin:@$ORACLE_HOSTNAME:$ORACLE_PORT:$ORACLE_SID

RAC Format:

        jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = RD_DB_HOST_NAME)(PORT = RD_DB_PORT_NUMBER))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME=RD_DB_SERVICE_NAME)))

Where:

  • RD_DB_HOST_NAME = The host where the orcale database in installed
  • RD_DB_PORT_NUMBER = The listener port to connect to
  • RD_DB_SERVICE_NAME = The service name

For example:

        jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.245.229.108)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME=rapiddeploy-db.mycompany.co.uk)))

        jdbc:oracle:thin:@(DESCRIPTION=(load_balance=on)(ADDRESS = (PROTOCOL = TCP)(HOST = rddb1a.test.midvision.com)(PORT = 1521))(ADDRESS=(PROTOCOL = TCP)(HOST = rddb1b.test.midvision.com)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = rddbdb.test.midvision.com)))

NB: Note, this requires you to enter the SERVICE when prompted for the SID when running the script. These can be the same but contact your DBAs for the SERVICE name.

Alternate Script Runner Factory

Occasionally there are problems connecting to ORACLE using the default SQLPlus mechanism. JDBC can be used to execute database updates as follows

Within the ${MV_HOME}/web-apps/db/bin/maintain-oracle-db.sh script change the script runner factory change:

        -Dcom.midvision.rapiddeploy.db.script.runner.ScriptRunner.factory=com.midvision.rapiddeploy.db.script.runner.SqlPlusScriptRunnerFactory

to

        -Dcom.midvision.rapiddeploy.db.script.runner.ScriptRunner.factory=com.midvision.rapiddeploy.db.script.runner.JdbcScriptRunnerFactory
Importing the schema manually

It is possible for the DBA to run the scripts manually. Typically you will need to run 2 scripts, in order, these being:

  1. Create the schema user and roles, for which you will need to replace the necessary variables inside the script:
            ${MV_HOME}/web-apps/db/bin/create-oracle-db.sql
  2. Import the complete DDL:
            ${MV_HOME}/web-apps/db/scripts/oracle-migration/new-installation/V4.1.05__NewDataModelFromScratch.sql