Document toolboxDocument toolbox

(2022.1) Database Maintenance and Data Refreshes

Owned by David Engblom
Apr 16, 2020
2 min read

Hosted Customers


For Hosted Customers of PCR-360, PCR acts as the Database Administrator. As such, PCR is responsible for all Database Administrative functions:

  1. Configuration and setup.
  2. Database upgrades and patches.
  3. Server upgrades and patches.
  4. Restoring the database after a system failure.
  5. Backing up the database.
  6. Refreshing Test system with Production data.

When the Test data is refreshed from Production data, the customer is responsible for changing any "Production" specific information. This includes, but is not limited to:

  1. Production-specific API keys.
  2. Custom Events, Custom API endpoints, or Escalations that contain Production specific URIs.
  3. Service Host entries for switch interfaces i.e. Cisco, Avaya, and SL100 settings.

PCR does not automatically refresh Test Systems with Production Data. This is done upon request and is limited to a single data refresh per quarter. Additional requests will be considered on a case-by-case basis. Typically, Test Data refreshes will only be scheduled for a weekday. Alternatively, PCR will also release a schedule of available weekend dates for data refreshes, with a limited number of slots. All weekend dates are first-come-first-serve.

Licensed Customers


Licensed Customers of PCR-360 are responsible for all Database Administration. This includes, but is not limited to, backups, modifying database settings, and refreshing Test data with Production data. 
 
PCR does have a few requests when it comes to copying Production data to the Test database. Abiding by these requests will help avoid any potential issues caused by the data restoration.

  1. PCR would like to be informed when data is copied from Production to Test or restored from a backup.  If the version of the data in the backup, or from Production does not match the patch installed on the system, the application will not function properly.
  2. If possible, coordinate the copying of Production Data to Test with new Patch Upgrades.  This should reduce the possibility of any unforeseen issues since PCR would be installing the upgrade right after the data is copied. This allows PCR to verify everything is in order after the data transfer.

Refreshing Test Data Guidelines

  1. Inform PCR before copying data from the Production system to the Test system, or restoring data from a backup.  If the version of the data in the backup, or from Production, does not match the patch installed on the system, the application will not function properly.

  2. If possible, coordinate the copying of Production Data to the Test system with new Patch Upgrades.  This should reduce the possibility of any unforeseen issues since PCR would be installing the upgrade right after the data is copied. This allows PCR to verify everything is in order after the data transfer.

  3. Backup the Test database (optional) - This is only necessary if you suspect you will need to restore any data from the Test system that is not in the Production data.

  4. Export the Production database, or prepare a backup copy of the Production database that you can import into the Test database.

    1. All PCR-360 installations require three schemas/databases; pcr360_prod, pcr360_prod_metadata, and pcr360_prod_archive.  The names may vary by installation, however, there should be a primary schema, metadata schema, and archive schema.

    2. All tables must be exported, including empty tables.  The application may not run properly if any schemas, or tables are missing.

  5. Disable the cron for the Test system by commenting out the entry in the crontab.  (This is typically found in the root crontab, but could be in the Apache crontab for some customers).

  6. Import the data into each of the schemas on the Test Database.

    1. Note: Always verify that you are importing the data into the test schemas, you may not be able to fix the production data if any data changes occurred since the data was exported.

  7. Save the output of the import process to a log file. You should review the log after the import has completed and verify no issues occurred during the import process.

  8. After the data has imported, you will need to rebuild the views.

    1. Rebuild the MT-Views (from the browser)

      1. <application url>/update/create/mt-views

      2. The mt-views will be automatically updated

    2.  Rebuild the standard Views (from the browser)

      1. <application url>/update/create/views

      2. The views will be automatically updated

  9. Restore any triggers, custom views, etc. on the Test system.

  10. Uncomment the Test system entry in the crontab to enable the cron.

  11. Log into the application to verify everything works and you're done!