How to upgrade Orchestrator with one or more temporal tables enabled?
Description
In attempting to upgrade Orchestrator, the installer fails with the error, "System.Exception: Migration failed: Cannot insert the value NULL into column 'RowVersion', table 'rpa_ema_dev.dbo.ProcessSchedulesHistory'; column does not allow nulls. UPDATE fails."
The cause of the failure is the implementation of system-versioned temporal tables in the UiPath database, which is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis.
Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record the period of validity for each row, whenever a row is modified. The main table that stores current data is referred to as the current table, or simply as the temporal table.
In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema, called the history table. The system uses the history table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. During temporal table creation users can specify an existing history table (which must be schema compliant) or let the system create a default history table.
The update fails because Direct modification of the data in a history table isn't permitted, preventing the insertion of NULL into the ‘RowVersion’ column. Temporal table considerations and limitations
Resolution
In order to retain the history table through an upgrade, the process must include an additional step to temporarily remove SYSTEM_VERSIONING as an immediate precursor to the upgrade, then restoring the history table relationship before restarting the Orchestrator server.
This is the list of operations that requires system-versioning to be set to OFF:
- Removing unnecessary data from history (DELETE or TRUNCATE)
- Removing data from current table without versioning (DELETE, TRUNCATE)
- Partition SWITCH OUT from the current table
- Partition SWITCH IN into the history table
This example temporarily stops SYSTEM_VERSIONING to allow you to perform specific maintenance operations. If you stop versioning temporarily as a prerequisite for table maintenance, we strongly recommend doing this inside a transaction to keep data consistency.
Note
When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so results in a new history table being created and associated with the current table. The original history table can still exist as a normal table but won't be associated with the current table.
SQL
BEGIN TRAN
ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF);
TRUNCATE TABLE [History].[DepartmentHistory]
WITH (PARTITIONS (1,2))
ALTER TABLE dbo.Department SET
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.DepartmentHistory)
);
COMMIT ;