Version: 2022.1.2

Restricted database access

Overview​

New way of deployment on systems, where changing the database objects is restricted. It is required to have 3 inPoint environments:

Test system (T)​
  • System with all required database rights described in this section.
  • Contains copy of the production database.
  • System for preparing the deployment for the Q and P systems.
  • Each DDL (Data Definition Language) command is logged.
  • Each DML (Data Manipulation Language) command on system related data is logged.
  • DML (Data Manipulation Language) commands on the user related data executed by the inPoint.Wizard/inPoint.Admin are logged too , but only the first X changes (by default it's the first 10 changes).
    User related data can be for example:
    • folder or document in the global site
    • inPoint users
    • inPoint groups
    • etc.
Quality system (Q)​
  • Restricted database access.
  • Contains copy of the production database.
  • Executing any DDL (Data Definition Language) command by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data by the inPoint.Wizard/inPoint.Admin is allowed. The first 10 commands of this type are reported in the script package file during the export and can be reviewed by the admin.
Production system (P)​
  • Restricted database access.
  • Executing any DDL (Data Definition Language) command by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data by the inPoint.Wizard/inPoint.Admin is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data by the inPoint.Wizard/inPoint.Admin is allowed. The first 10 commands of this type are reported in the script package file during the export and can be reviewed by the admin.

Database script execution modes​

In the inPoint.Wizard it's possible to define the database script execution mode, which defines whether to execute any DDL (Data Definition Language)/DML (Data Manipulation Language) commands during the deployment.

Possible database scripts execution modes:

Ausführen​
  • Default settings, with no logging.
Ausführen und protokollieren​
  • Each DDL (Data Definition Language) command is logged.
  • Each DML (Data Manipulation Language) command on system related data is logged.
  • Logging the first 10 DML (Data Manipulation Language) command on the user related data.
  • Used only on the T system without any database restrictions.
Nicht durchführen​
  • Setup is deploying the assemblies.
  • Executing any DDL (Data Definition Language) command is prohibited.
  • Executing any DML (Data Manipulation Language) command on system related data is prohibited.
  • Executing any DML (Data Manipulation Language) command on the user related data is allowed.
  • Used only on the Q and P system with database restrictions.

IMPORTANT
In the case of fresh install it is mandatory to keep the database script execution mode in "Ausführen" (which is the default settings) in the inPoint.Wizard.

It is allowed to change the database script execution mode in the inPoint.Wizard only in upgrade.

setup_update_default

setup_update_dbmodes

Process flow diagram​

The process flow diagram in restricted database access:

processflowdiagram_restrictedaccess

Deployment Life Cycle in restricted database access​

Phases of the Deployment Life Cycle (DLC) in restricted database access:

deployment_life_cycle

Prerequisites​

Prerequisites on the T system​

  • Before the Phase 1 of the DLC the T system already contains a fresh or running inPoint environment.
  • In the case it's a fresh installation, it is mandatory to set the database script execution mode in the inPoint.Wizard to "Ausführen".

Prerequisites on the Q system​

  • Before the Phase 1 of the DLC the Q system already contains a fresh or running inPoint environment.
  • In the case it's a fresh installation, it is mandatory to set the database script execution mode in the inPoint.Wizard to "Ausführen".

Prerequisites on the P system​

  • Before the Phase 1 of the DLC the P system already contains a fresh or running inPoint environment.

  • In the case it's a fresh installation, it is mandatory to set the database script execution mode in the inPoint.Wizard to "Ausführen".

  • Before the Phase 1 of the DLC all settings are configured in "Admin" Window of the inPoint.Client connecting to the the production system environment (source system), including:

    • Settings in the Menus tab
    • Settings in the General Settings tab
    • Settings in the E-mail templates tab
    • Settings in the Templates tab

  • After starting the Phase 1 of the DLC it's prohibited to create/change/delete any inPoint system objects (for example: creating Tenant/GSR/Flat archive unit/etc... ) on the production system till the last phase Phase 10 of the DLC is completed. If this condition cannot be met, then the whole DLC process has to be cancelled and rolled back.

Phase 1. Cloning the production database to T and Q systems​

The very first step of the DLC is to clone the production database to the T and Q systems.

  • The structure/declaration/body of the database objects must be the same between the P, Q and T systems.
  • The content of the inPoint system objects must be the same between the P, Q and T systems.
  • The clone must be applied on all databases used by inPoint.

Phase 2. Executing the wizard or creating/modifying objects in inPoint.Admin on the T system​

Execute the inPoint.Wizard in upgrade mode in the case it is done the first time on the T system. In the inPoint.Wizard choose the database script execution mode: "Ausführen und protokollieren"!

phases2_setup_update_saveddl

Once the upgrade with inPoint.Wizard was processed, executing the inPoint.Wizard in another DLC is optional, necessary only if the inPoint server upgrade is required.

Before doing any change on the T system, the following server settings needs to be modified in the "Admin" Window of the inPoint.Client connecting to the T system environment, including:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
    • Elasticsearch Index mappings
  • Workflow related:
    • HSService url
    • Workflow Server url
    • PortalConnector url
    • Apps
  • Others:
    • LocalWatchPath
    • LocalMirrorPath
    • LocalPluginsPath
    • Branding Hyperlink
    • Branding Logo Name
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Email restore search environments

These settings are excluded from logging and are not transferred to the P and Q systems.

After adjusting the server settings mentioned above, it's allowed to:

  • manipulate with the inPoint system objects using the inPoint.Admin including:
    • Tenants
    • GSRs
    • Flat archive units
    • Jobs
    • etc...
  • manipulate with the inPoint objects using the inPoint.Client/inPoint.Web including:
    • Documents
    • Folders
    • Workflows
    • etc...

IMPORTANT
The export executed later in Phase 4 of the DLC will fail if:

  • there is any failed script occuring in the current phase
  • there is an inconsistent state in the logging tables
    • not committed scripts
    • not committed setup
    • etc...

In this case the whole DLC process has to be cancelled and rolled back in order to get back to a consistent state.

Phase 3. Testing on the T system​

Before exporting the changes implemented on the T system, it's important to test the changes.

IMPORTANT
The export executed later in Phase 4 of the DLC will fail if:

  • there is any failed script occuring in the current phase
  • there is an inconsistent state in the logging tables
    • not committed scripts
    • etc...

In this case the whole DLC process has to be cancelled and rolled back in order to get back to a consistent state.

Phase 4. Export from the T system (creating script packages)​

If the test succeeds, it's allowed to export the changes into the script package. The export is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the export with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Export            ^
  /ArchiveConfig "C:\Program Files (x86)\H&S Heilig und Schubert Software AG\Pam.Archive\Pam.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\HybridStoreSv.exe.config"            ^
  /PackageRootPath "C:\ScriptPackages"          ^
  /PackageName "MyFirstScriptPackage"

Response

Package created (PackageName: 'MyFirstScriptPackage', PackageRootPath: 'C:\ScriptPackages').
Elapsed Time is 00:00:01.894

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

During one export as many script packages are created as many databases are used by inPoint.

For example:
If inPoint is using separate database for HybridStore and the inPoint server then 2 script packages will be created with the following naming convention:
"<package id>_<package name>_(<database hosting server name>_<schema name>).zip"

, where:

Package id​

<package id>
Is a unique sequence number generated by the system.

Package name​

<package name>
Name of the package entered in the command line arguments for the export.

Database hosting server name​

<database hosting server name>
Specifies the name of the host server, where the database is running.

Schema name​

<schema name>
Name of the database schema, used by inPoint or HybridStore.

In this phase of the DLC it is possible to execute the export multiple times. Each export contains the delta from the previous export. If there is nothing to export the script package is not created.

After the export/exports, copy the created script package files to the Q system which must be located in the same folder structure like on the T system.
This version of the ScriptPackageManagerTool is not transferring the config file changes yet, therefore it's necessary to override the "Pam.Archive.config" and the "HybridStoreSv.exe.config" files on the Q system with the config files from the T system.

Phase 5. Importing the script packages to the Q system​

After the Phase 4 of the DLC is finished, the database changes done on the T system (in the future also the config file changes) needs to be imported to the Q system using the ScriptPackageManagerTool.

Before running the import:

  • Stop each inPoint service.
  • Stop the Pam.Web application pool.
  • Copy the created script package files from the T system to the Q system, which must be located in the same folder structure like on the T system.
  • This version of the ScriptPackageManagerTool is not transferring the config file changes yet, therefore it's necessary to override the "Pam.Archive.config" and the "HybridStoreSv.exe.config" files on the Q system with the config files from the T system.
  • Create a global environment variable with the following command in administrative command prompt, which sets the database script execution mode to: "Nicht durchführen":
setx inPoint_DatabaseRestriction "DbNoDdl" /M

IMPORTANT
IN THE CASE OF MULTIPLE SCRIPT PACKAGE FILES IT IS IMPORTANT TO IMPORT THEM IN THE SAME ORDER THE WERE CREATED!

The import is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the import for each script package file created on the T system with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Import            ^
  /ArchiveConfig "C:\Program Files (x86)\H&S Heilig und Schubert Software AG\Pam.Archive\Pam.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\HybridStoreSv.exe.config"            ^
  /PackageFile "C:\ScriptPackages\<package id>_<package name>_(<database hosting server name>_<schema name>).zip"           ^

Response

...
Executing script: 'C:\ScriptPackages\....sql'
Elapsed Time is 00:00:01.860

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

IMPORTANT

  • After the import is finished it's required to:

    • Edit the 'web.config' file!
      Path: %installdir%\H&S Heilig und Schubert Software AG\Pam.Storage\Web\web.config

      Change the following pamSettings key from:

      <add key="ReinitializeTempTablesOnStartup" value="false" />

      to:

      <add key="ReinitializeTempTablesOnStartup" value="true" />

      temporarily, in order to fill the temporary tables with the newly created tables during the import.

    • Start the Pam.Web application pool and open the inPoint.Client in order to initialise the temporary tables.

    • Close the inPoint.Client.

    • Change back the following pamSettings key from:

      <add key="ReinitializeTempTablesOnStartup" value="true" />

      to:

      <add key="ReinitializeTempTablesOnStartup" value="false" />

  • After import, a restart of each inPoint service and restart of the Pam.Web application pool is required! The command line tool will not do that!

  • During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 6. Executing the wizard on the Q system only if it was executed also on the T system​

When all scripts package files are imported execute the inPoint.Wizard in the upgrade mode in the case it is done the first time on the Q system. In the inPoint.Wizard the database script execution mode will be already set to "Nicht durchführen"!

phases6_setup_update_noddl

Once the upgrade with inPoint.Wizard was processed, executing the inPoint.Wizard in another DLC is optional, necessary only if the inPoint server upgrade was required in Phase 2.

Before testing on the Q system, the following server settings needs to be modified in the "Admin" Window of the inPoint.Client connecting to the Q system environment, including:

  • Fulltext related:
    • Elasticsearch User
    • Elasticsearch Password
    • Elasticsearch URL
    • Elasticsearch Index mappings
  • Workflow related:
    • HSService url
    • Workflow Server url
    • PortalConnector url
    • Apps
  • Others:
    • LocalWatchPath
    • LocalMirrorPath
    • LocalPluginsPath
    • Branding Hyperlink
    • Branding Logo Name
    • Report portal url
    • ArchiveDialogDirectItemUri
    • Email restore search environments

After adjusting the server settings mentioned above it's allowed to start the tesing phase Phase 7 mentioned below.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 7. Testing on the Q system​

Before switching to the P system, it's important to test the changes on the Q system.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 8. Importing the script packages to the P system​

After the Testing phase Phase 7 of the DLC is finished on the Q system, the database changes done on the T system (in the future also the config file changes) needs to be imported to the P system using the ScriptPackageManagerTool.

Before running the import:

  • Stop each inPoint service.
  • Stop the Pam.Web application pool.
  • Copy the created script package files from the T system to the P system, which must be located in the same folder structure like on the T system.
  • This version of the ScriptPackageManagerTool is not transferring the config file changes yet, therefore it's necessary to override the "Pam.Archive.config" and the "HybridStoreSv.exe.config" files on the P system with the config files from the T system.
  • Create a global environment variable with the following command in administrative command prompt, which sets the database script execution mode to: "Nicht durchführen":
setx inPoint_DatabaseRestriction "DbNoDdl" /M

IMPORTANT
IN THE CASE OF MULTIPLE SCRIPT PACKAGE FILES IT IS IMPORTANT TO IMPORT THEM IN THE SAME ORDER THE WERE CREATED!

The import is done by the ScriptPackageManager tool ("ScriptPackageManagerTool.exe") , which is included in the "inPoint_Tools_<current release>_Release.zip" package.

Copy this tool to any location, and execute the import for each script package file created on the T system with the following command:

TIP: Use "^" at the end of a line, allows to continue the command in the next line to make it more readable!

ScriptPackageManagerTool.exe /Import            ^
  /ArchiveConfig "C:\Program Files (x86)\H&S Heilig und Schubert Software AG\Pam.Archive\Pam.Archive.config"    ^
  /HybridStoreConfig "C:\Program Files (x86)\HS Europe\inPoint.HybridStore\HybridStoreSv.exe.config"            ^
  /PackageFile "C:\ScriptPackages\<package id>_<package name>_(<database hosting server name>_<schema name>).zip"           ^

Response

...
Executing script: 'C:\ScriptPackages\....sql'
Elapsed Time is 00:00:01.860

For the help of all commands execute

"ScriptPackageManagerTool.exe /?"

All settings are explained in this documentation.

IMPORTANT

  • After import, start each inPoint service and start the Pam.Web application pool! The command line tool will not do that!
  • During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 9. Executing the wizard on the P system only if it was executed also on the T system​

When all scripts package files are imported execute the inPoint.Wizard in the upgrade mode in the case it is done the first time on the P system. In the inPoint.Wizard the database script execution mode will be already set to "Nicht durchführen"!

phases9_setup_update_noddl

Once the upgrade with inPoint.Wizard was processed, executing the inPoint.Wizard in another DLC is optional, necessary only if the inPoint server upgrade was required in Phase 2.

IMPORTANT
During this phase it's NOT allowed to manipulate with the inPoint objects using the inPoint.Admin.

Phase 10. Testing on the P system​

Before publishing the production environment, it's important to test the changes on the P system as well.

Missing implementation​

The following parts of the "Manual execution of database scripts" feature are not implemented yet in the 2021.1 release:

  • Transferring flat archive units from T to Q/P system

  • Logging in inPoint.Admin user management, which should generate an "Info" script containing only part of the executed DML commands (by default it's the first 10 changes)

  • Logging the following settings configurable in "Admin" Window of the inPoint.Client

    • Menus tab
    • E-mail templates tab
    • Templates tab

  • Logging the changes in the user related data in the following external tools executed by the setup:

    • AddTenantAdminToGlobalMyGsr
    • SetItemStatus
    • RepairOfficeTempContents

  • Logging the changes in the user related data in the following external tool:

    • inPoint.Migration

REMINDER
The generated script packages by the ScriptPackageManager tool must be located in the same folder structure on the Production/Quality/Test systems.