Attachment J-23 - QSRS Software Installation and Update Process.docx

Agency for Healthcare Research and Quality (AHRQ) Quality and Safety Review System (QSRS) QSRS Installation and Update Process

Required for QSRS External Dependencies An instance of Active Directory is required with the following groups set up and atleast one user in “QSRS_Administrator” group:

· QSRS_Supervisor

· Abstractors

· QSRS_Administrator

· QSRS_Reports Installation Pre-requisites

· Microsoft Windows Server 2008 or greater machine/VM (16GB of RAM, 4 VCPUs, 256 GB HDD recommended) Comment by Erin Kirley: Actually need 2 servers (one for app and one for database)

In order for the application and database servers to communicate, you need to run a command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'IP' IDENTIFIED BY ‘PASSWORD' WITH GRANT OPTION;

FLUSH PRIVILEGES; Comment by Erin Kirley: Changes needed when running multiple instances:

Running more instances required more RAM for the database server. 16 GB for 6 instances was not enough 32 seems to work;

vcpus might need to go up if more users are working in the system Is it possible to separate the data for multiple sites in a single instance instead of having a separate instance for each?

Buffer setting – This also relates to running more than one instance - The instructions specify that the server should have 16GB of RAM, which we have, but we received an error message saying the “InnoDB could not allocate memory for the buffer pool” – we needed to increase the RAM to 36GB and decrease the buffer pool (adjusts how much memory the java instance uses); adjusted down to 1000 accommodate more instances

Java from oracle for installation – will need to pay fees in future; Corretto from Amazon is free and worked for this installation

Is there any option to use MS SQL?

Likely user questions:

How much support will be offered? While MySQL is a free download, it is less frequently used than MS SQL so more support is needed and users may need to engage 3rd party contractor for support

When upgrading to next version of MySQL:

How easy will it be to upgrade security and the version of mysql?

Will existing QSRS instances be compatible with new MySQL versions?

When upgrading QSRS versions:

How will upgrades affect algorithms and existing data?

· Latest version of MySQL 5.6 Community Edition installed

· Latest version of Java (JRE) 8 installed

· Administrator privileges Creating the QSRSsetup.exe File Team Octo will package all software packages into a single executable file QSRSsetup.exe to be run by individual hospitals to perform a new installation or install an update. Section 3 describes through screenshots what a new installation will look like via the QSRSsetup.exe file.

Software update notifications Team Octo will send all software package updates and documentation to the pilot sites using a 3rd party secure file transfer solution called Hightail. All artifacts included in the software package updates will be password protected.

Running the QSRSsetup.exe File QSRSsetup.exe provides wizard based installation for QSRS application. The series of screens help install and configure QSRS application on the target environment.

There are two options available when you run QSRSsetup.exe:

1. Upgrade QSRS Software Only: To upgrade the existing QSRS application to a newer version. NOTE: It is recommended that you backup QSRS database before upgrade. You can backup QSRS database using MySQL workbench and use “Data Export” (Refer screenshot below for options to choose). This will export the existing QSRS database in a single SQL file: Comment by Erin Kirley: Specify that you should download this when downloading MySQL since it doesn’t automatically happen

Alternately, you can execute below command from Command Prompt:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --host=localhost --port=<port> -u root –p --routines --databases metadata qsrs_management abstraction_persistence qsrs_ods qsrs_cas > C:\MySQLData\qsrs_db.sql

2. Add a new QSRS Instance: To add a new instance of QSRS application on the target environment. If you have an existing instance of QSRS running and you want to install new instance of QSRS on the same machine which hosts QSRS database, then a new MySQL instance should be setup on a different port number before beginning QSRS installation. Please refer to Appendix – A on how to create additional instance of MySQL database on the same server.

Below screenshot demonstrates the use of both of the options above.

Run QSRSsetup.exe from an administrator account.

Step 1.1: For Upgrading existing QSRS application, select Upgrade option.

Step 1.2: After you hit ‘Next’, a confirmation screen appears.

Step 1.3: After you hit ‘Confirm’, all existing instances on the target environments will be. Click ‘Finish’ to complete the setup.

Step 2.1: For new instance of QSRS application, select the second option.

Step 2.2: Either choose to install a Tomcat Instance (If this a brand new environment) or use and existing Tomcat instance (If an instance of QSRS application is already running on the target environment). Also, enter the name the Website context root (must be unique on the same machine) and should not contain species or any special characters. Additionally, enter an Encryption Key and store at a safe place. This key will be used to encrypt database, Active Directory service account and other account passwords.

Step 2.3: Create a new database for the new instance. Select and enter User Name and password for QSRS database, additionally enter MySQL Root username and password. If you have a QSRS database not used anymore, select Existing database.

Step 2.4: Enter Active Directory instance details as show below.

Step 2.5: Enter Administrator username and map existing Active Directory groups to QSRS groups.

Step 2.6: After you hit ‘Next’, below confirmation screen appears with the details for the instance.

Step 2.7: After you hit ‘Confirm’, the new instance of QSRS will be installed. Click ‘Final’ to complete the setup.

Appendix – A Steps to Create new MySQL database instance

1. To create a new instance of MySQL database on a new port (e.g. 3307), create a new data folder (e.g. C:\ProgramData\MySQL\MySQL Server 5.6\data2). Copy MySQL configuration (my.ini) file from the existing instance into this new data folder and make below changes are to the new my.ini file. For e.g. Comment by Erin Kirley: Helpful to note that 3306 (first port), created in the step 2.3 above, is the first/default instance. It was not initially clear when creating the 2nd instance that 3306 was active

# The TCP/IP Port the MySQL Server will listen on port=3307

# Path to the database root datadir= C:\ProgramData\MySQL\MySQL Server 5.6\data2

# Server Id.

server-id=2

2. Copy “mysql” and “performance_schema” folders from an existing instance data dir (e.g. C:\ProgramData\MySQL\MySQL Server 5.6\data) to the new data dir for the new instance i.e . C:\ProgramData\MySQL\MySQL Server 5.6\data2.

3. To install this new instance as a Windows Service (e.g. MySQL_2) execute below command from the Command Prompt using Administrator Privileges:

C:\Windows\system32>"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --install MySQL_2 --defaults-file=" C:\ProgramData\MySQL\MySQL Server 5.6\data2\my.ini"

4. Start the new server (MySQL_2) Windows service from Windows ‘Services’.

5. The new instance uses the same ‘root’ password as the existing instance, since we copied ‘mysql’ folder from the original instance. If you need to change ‘root’ user password, then connect to MySQL instance from command line using the password for original instance.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql --host=localhost --port=3307 -u root –p

Once logged in change new password for root user:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('<new_root_password>');

6. Restart the MySQL service (MySQL_2) service for password change to take effect.

7. Depending on your environement, you may need to change Windows Firewall settings to allow incoming TCP traffic on the new port (3307) on the machine running MySQL server.

Appendix – B Steps to Restore QSRS MySQL database from backup In case, you need to restore QSRS databse from the backup taken ealier, follow below steps to restore the database from an existing backup.

1. Connect MySQL database from command line:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql --host=localhost --port=<db_port> --connect_timeout=10000 -u root –p<password>

2. Execute below statements to drop QSRS databases:

mysql> drop database `qsrs_cas`;

mysql> drop database `qsrs_ods`;

mysql> drop database `abstraction_persistence`;

mysql> drop database `qsrs_management`;

mysql> drop database `metadata`;

3. Command to be executed in shell to restore from Backup of QSRS database C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql --host=localhost --port=<db_port> -u root –p<password> < C:\MySQLData\qsrs_db.sql

Additional resources to include in material HTTP to HTTPS redirection – https://www.namecheap.com/support/knowledgebase/article.aspx/9953/38/iis-redirect-http-to-https

Reverse Proxy for IIS Frontend to localhost backend (URLRewrite) https://www.sapho.com/documentation/how-to-configure-microsoft-iis-http-server-as-a-reverse-proxy-for-sapho-server-running-on-apache-tomcat/

Note from Mehul on this that was helpful have: It is always a best practice to offload SSL negotiations by a webserver (e.g. IIS) fronting the Tomcat server. This has several benefits like application doesn’t have to deal with SSL handling for each incoming connections, which affects performance. Also, instead of using self-signed Certs by Tomcat, you are free to use any third-party CA signed certificates. These are some of the reason, I believe the original contractor (Edaptive) did not go with the option of setting up SSL with Tomcat.

· From JHU IT: Ideally this would all be done in Tomcat mysql auto backup https://www.redolive.com/automated-mysql-backup-for-windows/ image1.png image2.png image3.png image4.png image5.png image6.png image7.png image8.png image9.png image10.png image11.png image12.png image13.png