Databases and DBUPGRADE

A deployment of Planning Space uses two types of database to store all of the system, application and user data:

  • a single 'common database' (also known as the 'common data source') is used to store all of the system data for IPS Server and the common elements of the tenants
  • each tenant has its own 'tenant database' which stores tenant-specific system data, and users' data.

Database Recovery Model

The choice for Recovery Model will depend on the overall policy for backup and disaster recovery (DR) of the Planning Space deployment. The 'Simple' recovery model keeps the transaction log growth to a minimum and provides for best database performance. However this will entail a DR approach that is based on full and differential backups, in line with the required recovery point objective (RPO). If High Availability (always on, or mirroring) is a requirement, and/or point-in-time data restoration, then the 'Full' recovery model will be needed.

Database security

Important: There are two security levels of SQL Server account required for IPS/Planning Space deployment. For runtime access to the Common and Tenant databases, a restricted account should always be used (with server role 'bulkadmin' and the roles 'db_datareader', 'db_datawriter', and 'pes_datawriter' for the database). In a production environment a separate SQL Server account should be used for each database. For running DBUPGRADE programs during installation and upgrading, a more privileged account is required, with the role 'db_owner' for the database which is being upgraded. Note: 'pes_datawriter' is a custom role for databases that is added by the DBUPGRADE programs.

DBUPGRADE programs

Two programs are provided, IPS DBUPGRADE and DBUPGRADE, to prepare a database for use as the common database or as a tenant database. Each program can be used in two ways:

  • to start from a new, empty database and create the necessary table structures and configuration which are required for the installed version of IPS Server or Planning Space, or
  • to start from an existing database that has been used with an earlier version of IPS Server or Planning Space, and apply an upgrade process to transform the database tables to the required structure for the current version of IPS/Planning Space.

Detailed instructions for using IPS DBUPGRADE and DBUPGRADE are provided in IPS common database creation and IPS DBUPGRADE, and Create the initial tenant and database (DBUPGRADE).

New SQL Server requirement for DB Upgrades for version 20.3: The SQL Server user account that is used to run the 'DBUpgrade' programs must be granted extra permissions to modify the SQL Server 'tempDB' database. Here is the full Administrator SQL script for an example user account 'user1':

Copy 
USE [tempdb]
GO
ALTER ROLE [public] ADD MEMBER [user1]
GO
GRANT ALTER ON DATABASE::tempDB TO [user1]
GO

The DBUPGRADE programs automatically check the state of a given database and will determine the creation or transformation steps required. If a database is already in a ready-to-use state then DBUPGRADE will detect this and will not make any changes to it.

Command line programs

The IPS DBUPGRADE and DBUPGRADE folders each contain a command line program 'Palantir.DBUpgrade.Command.exe'.

Run the program as follows to see all of its input parameters:

Copy 
.\Palantir.DBUpgrade.Command.exe /?

Note that the program will run an upgrade immediately given valid inputs, therefore make sure that you have made full backups of databases before attempting to use this program.

If the parameter '/checkversion' is used then the program will only run a check for the need to perform an upgrade, without initiating the upgrade. The program returns value 1 if the upgrade is required and value 0 if not required (note you can get this value using the PowerShell variable $LastExitCode).