Refreshing the CAMIS Database (Draft)

• CAMIS is a protected B medical application.
  
  When a database refresh is performed precautions need to be taken to ensure that users connecting to acceptance cannot view unauthorized data. MERS themselves will not be deleted. Links to MER documents will be broken but the rest of the view will be viewable.
  
  Concerns that were identified were:

• Privacy/Security: medical information (especially documents)

• Portal trial setup would need to be redone

• CAMIS/eMER accounts - 2FA links would be broken

Possible actions

• Refresh from prod but delete assessments past a certain date (2000) and all documents

  • There is an old sanitize script that we can possibly use as a starting point: \\tc4s0a\Groups\aara\aarad\dba\camis\scripts

• MERs won't be deleted - if they click on the button to view the eMER file it will break but the rest of the MER info is still there

• Backup document info (blobs and others) for portal trial and then update all of it once refresh is done

• All the data in the account tables in CAMIS need to be backed up and then put back in once the refresh is done

PROPOSED PLAN (associated subtask placeholders are created).

Database refresh of CAMIS Acc (only) from Production

Before refresh

Reach out to Daniel Baptiste to get permission for the refresh of CAMIS Acceptance only.

On approval of the request

• Create an SM-GS change request ticket: it needs to take place asap to minimize the amount of CAMMS data that would be lost.

• Backup accounts/permissions and settings, as applicable (eMer and CAMIS) Identify and Backup CAMIS tables containing eMER and CAMIS account information, permissions for Acceptance

• Review / update sanitize script: Review "sanitize" script

Day before the refresh is to occur:
Check SM-GS approvals completed for CAMIS DB refresh

Day of Refresh:

Inform clients and AVRO Arrows / Medevac teams of refresh taking place

Once Refresh is completed on acceptance

• Restore backup of acceptance accounts, settings and permissions for eMER and CAMIS

• Verify that records identified for deletion / sanitization are removed/updated.

• Check for invalid objects that need to be recompiled.

• Verify that the db links still point to Acc (note: based on feedback from corporate DBAs these are not typically refreshed).

• Database compare: acc and prod.

• Testing, eMER and Acc by Avro Arrows and Medevac.

• Testing by Client.

Issues encountered during the refresh of CAMIS production to acceptance:
The amount of time it took: started on Tuesday May 23, 2023.
Not completed as of the morning of June 2, 2023

When the refresh fails the process has to be restarted from the beginning.

server ran out of space (occurred multiple times during the work to refresh it.

the database is > 200gb and the acceptance server won’t support it.

It is not currently possible to clone the CAMIS database from Production to Acceptance:

• the database is > 200gb and the acceptance server won’t support it.