Database - bcgov/PIMS GitHub Wiki

Database

The PIMS solution currently uses a Microsoft SQL Server hosted within a Linux container. This database provides the primary data storage for the API and contains all Property information.

Platform

Microsoft SQL Server 2022

Table of Contents

Additional Links

Architectural Design

The primary purpose of this datasource is to maintain an inventory of properties (Parcels, Buildings and Projects). User roles and claims are managed by keycloak, but are also contained within this datasource. There are also a number of supporting lists to support the front-end application user experience. MS SQL Server Enterprise edition is being used (this requires a license).

OpenShift Container Memory Consumption

It has been discovered that under load MS SQL Server will run out of memory within a container that has less than 6 GB maximum memory. A container is configured with a minimum and maximum memory setting. The minimum can be as low as you want it (i.e. 100 MB). The maximum however must be 6 GB or more. Presently MS SQL Server doesn't require more than 1 GB of memory, even under load, but for some reason it still requires the 6 GB being available.

Disaster Recovery

The database is backed up daily by a cron job service provided by a pod running in each namespace/environment (dev, test, prod). This service is fully configurable and also provides a way to adhoc backup, verify and restore.

Link Description
DevOps configuration How to configure database backup for each environment
Container image Detailed information about the database backup container and configuration
Disaster Recovery How to use the database backup and recovery scripts

Enterprise Relational Diagram

image

Object Information

As the current primary purpose of the PIMS DB is to manage inventory of properties, the structure revolves around the the two property objects Parcels and Buildings. A Parcel object represents land, and it can contain many Building objects. Additionally the DB provides a structure to support properties being owned by an Agency, so that the appropriate Users have authority to manage it.

Most objects will also include tracking columns to identify when it was created or updated and who created and updated it. Additionally objects will natively provide optimistic concurrency enforcement, which will ensure data is not overwritten in a multi-user concurrent solution.

Primary Tables

The following are the primary objects;

Object Description
Parcels Land inventory
Buildings Building inventory
Projects Project inventory (disposal, aquisition)
Users Users accounts within PIMS
Roles Roles that authorize abilities within PIMS
Agencies A ministry, crown corporation or entity that owns properties
Addresses A physical address to a location
Workflows Light workflow engine provide a way to control the lifecycle of projects
Notification Templates Manage notification templates that are used for sending email
Notification Queue A queue containing all notifications that have been generated

List Tables

The following provide a way to manage lists or collections within the solution;

Object Description
PropertyTypes List of property types [land | building]
PropertyClassifications List of property classifications
Cities List of cities
Provinces List of provinces
BuildingConstructionTypes List of building construction types
BuildingPredominateUses List of building predominate uses
BuildingOccupantTypes List of building occupant types
TierLevels List of project tier levels for projects
Project Status List of project status that represent stages a project will go through
Project Risks List of project risks
Tasks List of tasks to complete a process or stage. These are associated to project status and workflows

Connecting to OpenShift Database

"Last Editied By": "Brady Mitchell"
"Date": "May 9th, 2023"
  1. Download SQL Server Management Studio if you haven't already.
  2. In OpenShift, copy login command from the account dropdown in the top right corner of the dashboard.
  3. In a terminal with oc CLI tool installed, paste and run the login command.
  4. Be sure to use the namespace you want to connect to, such as production by executing oc project 354028-prod.
  5. In OpenShift, Workloads > Pods (in the namespace you wish to use), find the database pod and save its name for the next command.
  6. In terminal, execute oc port-forward <name-of-db-pod> 22220:1433. 22220 can be any number, it is the port on your machine to map to the 1433 port of the pod in OpenShift.
  7. In SQL Server Management Studio, in the Object Explorer on the left, click on Connect > Database Engine.
  8. Set the Server Name to 127.0.0.1,22220.
  9. Copy the username and password from OpenShift, Workloads > Secrets > pims-database.
  10. Connect!