Choral Installation of AWS Oracle RDS and Fargate

    This document describes the necessary tools and steps needed for the installation of JChem Choral in AWS RDS and Fargate environment.

    Architecture

    Prerequisites

    • Oracle RDS with Admin password

    • AWS rights

    • Local or EC2 Instance (AppServer) with

      • Java (Java 17 jre for JChem Choral versions above 23.5)

    • AWS CLI

    • Docker

    • Terraform

    • SQLDeveloper

    • AppServer connection to Oracle RDS

    • Familiarity with

      • AWS RDS, Fargate, ECR, ECS, Security groups, CLI
      • Terraform
      • Docker

    An RDS database running with a user that the Choral service can use to create its backend data. In this docs this user will be referred to as <RDS user> and <RDS password>.

    Fargate service CPU and memory capacity is limited, you can check if the suggested values do not exceed Fargate’s capacity. If memory seems insufficient, you can uncheck caching options to lower memory requirements. If it still exceeds Fargate’s capacity then our recommendation would be to run Choral service in an EC2 instance as it is described in this documentation.

    Installation steps

    The installation is twofold, we fake the application service for initialization using a local or EC2 instance. We create the application service in Fargate which will serve the database and replace the local fake application service with this one.

    During the installation we make the following steps:

    • Local initialization of the service without SQL script execution
    • Create a docker image for Fargate service
    • Upload image to ECR
    • Setup AWS environment
    • Finish Choral configuration steps in AWS RDS

    1. Local initialization of the service without SQL script execution

    The first (partial) installation of Choral will be executed here. RDS is set as database and a local machine or an EC2 instance is applied for the application service.
    RDS instance is accessed by the local machine or by the EC2 instance.
    Use SQLdeveloper. The application service is initialized by the without SQL script execution (8b) method.

    • Have Oracle RDS in place

      • have its ADMIN password and SID

    • Create indexuser with indexuserpassword passwd

      CREATE USER <RDS_USER> IDENTIFIED BY <RDS_PASSWORD>;
GRANT connect, resource to <RDS_USER>;
GRANT unlimited tablespace to <RDS_USER>;

      Example:

      CREATE USER indexuser IDENTIFIED BY indexuserpassword;
GRANT connect, resource to indexuser;
GRANT unlimited tablespace to indexuser;

      ​

    • Local or EC2 (AppServer) installation of the service

      # write back to oracle
com.chemaxon.jchem.psql.scheme=gcrdb
com.chemaxon.jchem.psql.gcrdb.isSingleTable=true
com.chemaxon.jchem.psql.gcrdb.singleTableName=engine_data 
com.chemaxon.jchem.psql.gcrdb.sqlBuilderProvider=ORACLE
com.chemaxon.jchem.psql.gcrdb.jdbcUrl=jdbc:oracle:thin:@localhost:1521/test 
com.chemaxon.jchem.psql.gcrdb.user=<RDS_USER>
com.chemaxon.jchem.psql.gcrdb.password=<RDS_PASSWORD>
      • and config/application.properties

      localhost is set below since the RDS is tunneled

      choral.init.oracleUrl=jdbc:oracle:thin:@localhost:1521:<SID>
choral.init.grantingUser=ADMIN

      • Memory settings

      • License file, see step 5

      • Molecule type, see step 7

      • Initialize server without SQL script execution

      ./init-choral-service

      ​

    2. Create a docker image for Fargate service

    This step serves for the docker image creation. Please take care of the configuration of the docker file and of entrypoint.sh. Copy the Choral installer - referenced in docker file - also into the directory where the docker build will be issued.

    • Create docker image

      Please see the provided docker file and entrypoint.sh example. These can be used as customizable templates to build a docker image usable for AWS Fargate service. entrypoint.sh is used by the docker image to initialize Choral application properties via fix values and environment variables.

      <RDS_USER> and <RDS_PASSWORD> should be set as JDBC_USER and JDBC_PASSWORD environment variables along with the RDS database endpoint as JDBC_URL.

      The default values of the caching properties are the same that can be found at JChem Engines cache and memory calculator by default. A container created from this image also starts the Choral service and can write the logs in AWS if e.g. Cloudwatch is configured and LOG environment variable is set to true. Time between sending log messages can be set with SLEEP_TIME_WHEN_LOG environment variable.

      For setting the license

      • use CHEMAXON_LICENSE_URL environment variable
      • or use CHEMAXON_LICENSE_SERVER_KEY environment variable
      docker build --build-arg="CHORAL_VERSION=<CHORAL_VERSION>" -t <LOCAL_IMAGE_NAME>:<LOCAL_TAG>

      Set the same Choral version as the one used in step 1 (Local initialization of the service without SQL script execution).

    3. Upload docker image to ECR

    AWS CLI is applied.

    Create a repository for your docker image in ECR <ECR_REPOSITORY>

    • Log into ECR with Docker:

      aws ecr get-login-password --profile <AWS_PROFILE> --region <AWS_REGION> | docker login --username AWS --password-stdin <ECR_ADRESS>

      Example:

      aws ecr get-login-password --profile 123456789012_Access --region eu-west-1 | docker login --username AWS --password-stdin 123456789012.dkr.ecr.eu-west-1.amazonaws.com

    • Tag your local docker image on ECR:

      docker tag <LOCAL_IMAGE_NAME>:<LOCAL_TAG> <ECR_ADRESS>/<ECR_REPOSITORY>:<ECR_TAG>

      Example:

      docker tag choralrds:latest 123456789012.dkr.ecr.eu-west-1.amazonaws.com/choralrds:latest

    • Push your local docker image to ECR:

      docker push <ECR_ADRESS>/<ECR_REPOSITORY>:<ECR_TAG>

      Example:

      123456789012.dkr.ecr.eu-west-1.amazonaws.com/choralrds:latest

    4. Setup AWS environment

    In this step the communication between ECR and RDS will be ensured.

    • Setup terraform files ​ An example terraform module is available here

      configuration ​ in terraform files you should use the same configuration as above JDBC_USER=indexuser JDBC_PASSWORD=indexuserpassword provide the JDBC_USER as db_user, RDS access url as db_url in terraform.tfvars file:

      db_url = "<RDS_URL>:1521:<DB_NAME>"

      Example:

      db_url = "jdbc:oracle:thin:@choral.abcdefgh1234.eu-west-1.rds.amazonaws.com:1521:TEST"
db_user = "indexuser"

      ​ the JDBC password is provided in ecs.tf file

      "JDBC_PASSWORD" : "indexuserpassword"

      The fargate route name, the Fargate service name are also set in the terraform files. The security groups are also created as defined in the terraform files.

    • Start Fargate cluster using terraform ​ AWS credentials should be in place (was already required for logging into ECR with docker)

      • terraform init
      • terraform apply

    • Allow module-test-sg security group to access ChoralSecurity security group on Oracle port (1521), this will allow the choral service on the Fargate cluster to access the database

      • Open ChoralSecurity security group
      • Add inbound rule
      • Type: Oracle-RDS, Source: Custom - module-test-sg

    • Allow ChoralSecurity security group to access module-test-sg security group on port 8128, this will allow the database to access choral service on the Fargate cluster

      • Open module-test-sg security group
      • Add inbound rule
      • Type:Custom TCP, Port: 8128, Source: Custom - ChoralSecurity

    • Later if needed destroy Fargate cluster (if not used any more)

      • Security groups: Remove the module-test-sg inbound rule from ChoralSecurity security group to automatically destroy module-test-sg security group as well
      • terraform destroy

    5. Finish Choral configuration steps in AWS RDS

    During this step SQL scripts for finishing the Choral configuration must be executed.
    RDS is tunneled.
    Use SQLdeveloper. The SQL scripts to be run are available in <choral_home>/sql/ folder.

    • Find the name of the router (Route 53 -> Hosted Zones -> Records) that handles the Fargate cluster (you can also find information about which target group the Fargate service was registered to in the Fargate service’s Deployment > Events tab), this will be referred to as Fargate_Route_name

    • Modify 2_http.sql

      host => 'Fargate_Route_name',

      Example:

      host => 'choral-fargate-internal.jcb.cxcloud.io',

    • Execute as Admin user in RDS (scripts work on SQLDeveloper but not in SquirrelSQL or DBeaver)

      • install_system.sql

      or

      • 1_createUser.sql
      • 2_http.sql

    • Modify 5_indextype.sql

      call set_choral_setting('HOST','Fargate_Route_name')

      Example:

      call set_choral_setting('HOST','choral-fargate-internal.jcb.cxcloud.io')

    • Execute as CHORAL_OWNER user in RDS (scripts work on SQLDeveloper but not in SquirrelSQL or DBeaver)

      • install_choral.sql

      or

      • 3_choral_settings.sql

      • 4_logger.sql

      • 5_indextype.sql

      • 6_choral_idxstat.sql

      • 7_choral_assoc_stats.sql

    • Test the installation by running 8_smoke_test.sql as CHORAL_OWNER.