How to migrate a Persistent State DB used by multiple independent services

With future Runtime releases, you will not be able anymore to use the same persistent state database/database schema for multiple independent services (see Roadmap below).
Before you update to such a Runtime version, you need to resolve this! We recommend to perform the migration as soon as possible.

Currently it is technically possible (though not recommended) to share one persistent state database/database schema between different independent services. With future Runtime releases, this will not be allowed anymore.

This means that you need to rework services that share the same persistent state database/database schema. This article describes what you need to do.

Roadmap

As of PAS 24.2 , we are providing a Runtime beta version with the changed persistent state behavior. PAS 25.0 then will come with the changed behavior included as per standard.

When using the Deployment Wizard in the PAS Administration to deploy a service, you can select the xUML Runtime version to be used for deployment:

For this to work, your installation needs to have multiple Runtime versions installed.

What this is about

To keep it shorter and improve the readability of this text, we are using the term database schema as a synonym to database. Which of the two is the correct term in your case depends on your database system.

You have set up a persistent state database schema of which the database tables contain data of multiple (2 or more) independent services:

As this is not allowed anymore, you need to

1. create dedicated database schemes for every single service

2. move (first copy, then delete) the data from the shared database to the dedicated database

You can keep one of the services in the original database.

To do this, follow the procedure described below.

Prerequisites

To follow the procedure described below, you need to have at least RUNTIME 2019.9 installed. If your current Runtime is older than 2019.9, please update your installation to 2019.9 (at least) before performing the migration steps.

Step 1: Stop the affected services

Stop all services that are affected by this rework.

Step 2: Create a database backup

Before starting with this migration, we strongly recommend to do a database backup. This way you can rollback your changes if anything goes wrong during the migration.

Step 3: Create a new database schema for every service

For each service you need to create a separate database schema. This is done by database commands that depend on the database system you are using. It may be e.g. something like

• CREATE SCHEMA <name of new schema> (Oracle)
  or

• CREATE DATABASE <name of new database> (SQL Server)

Keep in mind, to also assign the needed database users that the services you are going to migrate use to connect to the database. They need read and write access to this new schema!

Next, you need to create all necessary persistent state tables to the database schema. To help you with that, the Runtime comes with scripts for every supported database (MySQL/MariaDB, Oracle and SQL Server).

1. Go to your Runtime directory, to <path to Runtime directory>/<your OS>/resource/sql/<your DB system>/pstate.

If you are using an older Runtime (before Runtime 2023.1), these scripts reside in <path to Runtime directory>/<your OS>.

2. Find the script CreateSchema.sql for your database.

4. Run this script for each newly created schema to create persistent state tables.

Step 4: Copy the data

Copy the data of each service to the corresponding database schema. To help you with that task, we have prepared some scripts, or you can execute the steps manually.

The owner concept

To perform the next steps, you need to be familiar with the owner concept of the PAS persistent state implementation.

• Services are owners of persistent state objects.

• You need to know the owner to distinguish the persistent state data.

• The owner name is the service name as per default.

• The owner name can be changed in the service configuration/settings:

This is why owner names are coded into owner IDs in a dedicated table OWNERS, e.g.

Database items like persistent state object data is coded to the owner ID, some other items are coded to the owner name. Keep this in mind when doing the migration manually - the migration scripts (see below) already cover that.

Use the scripts

The following scripts are available:

Rework the scripts to fit your setup

At the top of each script, you'll find three variables with sample values: @old_schema, @new_schema, @owner_name. You need to replace the sample values with the actual names according to your setup.

For how to lookup the owner name, refer to The owner concept above.

Split the data manually

For each service, copy the related data from the old to the new database schema. To identify the data, refer to the owner name or owner ID.

UPDATE <new schema>.VERSION
SET MAX_OWNER_ID =
(SELECT MAX_OWNER_ID FROM <old schema>.VERSION)

INSERT INTO <new schema>.OWNERS
SELECT * FROM <old schema>.OWNERS
WHERE OWNER = <your owner name>

INSERT INTO <new schema>.OBJECT_DATA
SELECT * FROM <old schema>.OBJECT_DATA
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.EXTERNAL_ATTRIBUTES
SELECT * FROM <old schema>.EXTERNAL_ATTRIBUTES
WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM <new schema>.OBJECT_DATA)

INSERT INTO <new schema>.TOKENS
SELECT * FROM <old schema>.TOKENS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.EVENTS
SELECT * FROM <old schema>.EVENTS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.OBJECT_IDENTIFIERS
SELECT * FROM <old schema>.OBJECT_IDENTIFIERS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.HISTORY_STATES
SELECT * FROM <old schema>.HISTORY_STATES
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.CONVERSATION_LOCKS
SELECT * FROM <old schema>.CONVERSATION_LOCKS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.OBJECT_KEYS
SELECT * FROM <old schema>.OBJECT_KEYS
WHERE OWNER = <your owner name>

INSERT INTO <new schema>.CONVERSATION_EVENTS
SELECT * FROM <old schema>.CONVERSATION_EVENTS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.OBJECT_KEYS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.CONVERSATION_EVENTS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.EXTERNAL_ATTRIBUTES
WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM <new schema>.OBJECT_DATA)

DELETE FROM <old schema>.CONVERSATION_LOCKS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.HISTORY_STATES
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM %OLD_schema%.OBJECT_IDENTIFIERS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.EVENTS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.TOKENS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.OBJECT_DATA
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.OWNERS
WHERE OWNER_ID IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

Step 5: Change the service configuration

After the data migration, you need to update the service configuration (settings) of the migrated services. You need to update the configured database to the new one the data has been migrated to.

You can do this in the PAS Administration or on the Integration Component (Bridge), or you can change the definitions in your MagicDraw model and redeploy it.

Step 6: Restart the services

Reminder

Have you already assigned the needed database users that the migrated services use to connect to the database? They need read and write access!

Now you can restart the migrated services.

We recommend to test the new setup carefully.