Process Overview

The UITS DBA team developed and maintains a series of scripts to refresh the non-prod Kuali databases from the most recent (nightly) production schema level export file.  This process allows the Kuali non-prod environments to contain a highly relevant set of data for development, testing, training, and production trouble-shooting.  The scripts are automated by environment in Control-M jobs. Refreshes are initiated by submitting a Control-M job request form to scheduling@uconn.edu, The requester simply enters the common name of the environments to refresh, in the order desired. The automated jobs contain all of the actions necessary to take the application servers offline, perform the refresh, and then restore the application servers to an available state.  The refresh process also includes scripts to restore the test version of the encryption key and java object, and to falsify and re-encrypt the contents of all sensitive data fields.

** Due to the size of the production attachments table, we do not populate the attachments in the refreshed environments.  The empty table structure is created but not populated. 

Sample request form

Feb 3 2015 Refresh KFS Environments Request.docx

Refreshes are performed at the start of each month (following a successful month end process).  The above form is an example of a recent request to refresh all of the non-prod environments.

Unique environment:

DR Environment

This environment is a "dual purpose" environment.  It is used as an isolated test environment on an as-needed basis but additionally, DR is utilized to perform quarterly testing of the DR "fall back" process.  When used for DR testing, it is maintained as a production-like environment, which includes a full copy of the production database including the document attachments. In addition, the data is maintained in it's productive state which includes production key encryption of the sensitive data. This data is also real (no falsified).  Access to the DR environment (when in true DR mode) is controlled by role assignment in KFS (KIM).  This provides a fully functional prod-like environment enabling full business process testing by authorized users, while protesting the sensitive data contained within.

Typical Issues and remediation

DR Environment & prod encryption key

When DR is used for true DR testing, the encryption key is the one from production.  If a refresh is requested for this environment with the production key and production java object in place, the result will be a successful refresh but an unusable environment. The reason for this is that part of the cleansing process involves copying the java object from the database before the data is overwritten and then restoring the "saved" object after.  This is done to ensure that the resulting object is the test object that was in place prior to the refresh as opposed to the prod object from the production export.   

Remediation

Immediately following completion of a DR event:

• the java object in the DR schemas must be replaced with the test version of java object. (Dave Raines)  
  • *** Tip: to determine if the java object in the database schema is the test version or prod version, follow these steps:
    1. using SQL Developer or Toad, connect to the schema in question
    2. under the java node, confirm that the status field of the java object is "valid"
    3. View the source of the encryption class
    4. The value of the instance variable 'secretkey' identifies if it is prod or non-prod
• the encryption key in the security file on the application servers is replaced with the test key. (Ben Daniels)

Refresh Log

Requirements

*Backdoor access in all environments is now controlled by permissions in KFS. Only those granted this permission via management authorization have the ability to utilize backdoor access. All sensitive data is encrypted in all instances so only those with the proper credentials or permission for backdoor access will have visibility to encrypted data.

DBA Procedure Document

Kuali Refresh Copy Procedure.doc