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. 

These are the steps to perform a data refresh :

1. Log into Control-M.
2. Click the "Order Service" button at the top of the page.
3. Select the desired environments to refresh.
4. Open the refresh job and confirm the first step by right-clicking on the step and choosing "confirm"

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 executes the cleansePIIData.sql script 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.  This script does not re-encrypt CLOB data.  A separate process is required for that.

** 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. 

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 Schedule

Ad Hoc refreshes can be performed at any time by contacting the KFS Dev team.

Scheduled refreshes are performed during the week after a release.  Specific schedules can be found in the Planned Releases checklists.

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