Topics

    Data Migration to New Schema

    Introduction

    This is intended to focus on the migration of data from existing applications to a new Database Schema in the MariaDB cloud database. The following sections of this article outline the recommended steps for migrating all or part of your existing applications from the existing DB schema to new schema (s). Before getting into the details here is a short background on the topic.

    Background

    Before version 21.3, Appian Cloud database didn’t provide the option or provision to create additional Schema with the phpMyAdmin tool for MySQL / MariaDB databases. This has been updated as of 21.3 and hence all new applications being built on top of 21.3 would have this feature to add new schemas in MariaDB via phpMyAdmin. In this article we would like to elaborate on the options which existing customers would have in order to migrate their applications from an existing single schema in MariaDB to one/multiple new schemas in MariaDB.

    We recommend creating a new schema only for applications that require segregated user access to the database. For common data between applications, all users should access from a common schema.

    Using new schemas for existing applications and migrating data to the new schema is a complex activity. Updating your application and writing the required SQL scripts requires careful planning, development, and database knowledge.

    High Level Overview

    The high level steps involved in the process are :

    • Use the new Data Source Connected System
    • Identification of candidate DB objects for migration. This includes tables, views, triggers, stored procedures etc.
    • Re-creation of DB objects in the new Schema
    • Data migration to the New Schema
    • Retrofit the DB and Datasource references in the Application
    • Test thoroughly

    Recommended Steps

    Create new schema and the Data Source Connected System

    1. Create a new schema by calling the stored procedure using "call AppianProcess.createNewSchema(<schemaName>);" from phpMyAdmin. Note down the credentials and the jdbc url.
    2. Create a new MariaDB Connected System Data Source(CSDS) object pointing to the new schema using the credentials .

    Identification of the DB objects to be migrated

    For the application you want to migrate to the new schema, identify the database objects

    1. Tables and Views
      1. Table definition
      2. Table data (if you are planning to use phpMyAdmin import instead of SQL INSERT INTO SELECT Statement to move data)
      3. Indexes
      4. Triggers
      1. Look at all the data stores in the application.
      2. From the data stores identify all the data store entities.
      3. The data store entity names get translated to table or view names.
      4. If data store entity name is not found as a table or view name then use the @Table annotation in the corresponding CDT's xsd.
      5. Use the phpMyAdmin Export feature to export the following database objects related to the application tables:
      6. Views cannot be exported from phpMyAdmin versions earlier than 5.5.61. For the earlier versions , export views separately as it creates a "create table" statement for the view in the exported file instead of "create view"
    2. Indexes
      1. Indexes are covered in the table export.
    3. Triggers
      1. Indexes are covered in the table export.
      2. Review the trigger code and prefix references to any objects in the common Appian schema with 'Appian.'.
    4. Stored Procedures (includes functions)
      1. There is no automatic way to identify which stored procedures are used by which Appian applications.
      2. Use show create procedure procedure_name; to view and export the definition of all stored procedures and functions that need to be moved to the new schema.
      3. Before exporting , make sure that the prefixes used in the stored procedures point to the migrated tables/views in the new Schema.
    5. Events: Events are database level objects. They are not tied to a schema.

    Re-creation of DB objects in the new Schema

    1. Import the exported file from the previous step using the phpMyAdmin Import feature.
    2. Drop any application specific stored procedures or functions from the Appian schema.
    3. Recreate any application specific stored procedures and functions in the new schema.
    4. After the database objects are created, populate the tables in the new schema with the data from the source schema. You could do this using SQL scripts or import functionality in phpMyAdmin. During the data migration to the new schema, you might need to:
      1. Populate the independent tables first.
      2. Disable foreign key constraints.
      3. Lock the source schema tables.
      4. Unlock the source schema tables after the migration is complete.

    Retrofit the DB and Datasource references in the Application

    Once the data has been migrated to the new Schema, the most important part of the process is to make sure that all the old references in the application objects are changed to point to the new Schema using the new Connected System Data Source. These objects include:

    1. Data store objects.
    2. Record type objects with data sync enabled.
    3. Process model objects with [query database smart service nodes](Query_Database_Smart_Service.md).

    Test

    Thoroughly test the new functionality on your lower environment before deploying the changes to higher environments.

    Example

    Adding a new Schema in phpMyAdmin

    Here is an example of how to use the new features of Appian 21.3 to create additional schema using phpMyAdmin.

    In the figure above a new schema is created by executing a stored procedure highlighted in Red. The result is a new schema named “Schemaone” in the example. The connection details are highlighted in Green.

    The newly created schema now appears on the landing page for phpMyAdmin as shown below.

    Configuring Data Source using Connected System

    Here are the details on how one can secure the credentials of a new DB schema using the feature of Data Source Connected System. As shown below in the figure, there are new connected system options for all the major DB systems like Oracle. MariaDB, MySQL, DB2. The connected system then ensures that the authorization and authentication parameters are securely stored in the Appian platform.

    Once the Connected system is configured the data source would be available in all the objects which refer to a data source such as: data store, Records while configuring a data source, Query Database smart service, etc.

    Sample Application Schema Migration

    Here are the set of steps which are carried out in a sample application in Appian for migrating the tables to a new DB schema.

    1. Create a new schema using the stored procedure "call AppianProcess.createNewSchema('newSchema');" from phpMyAdmin. Note down the credentials and the jdbc url.
    2. Create a new Connected System Data Source object pointing to newSchema.
    3. Use phpMyAdmin -> Export feature to export only the structure of the tables of the application in sql format. This script has four main parts:
      1. Create tables, without primary keys, foreign keys or indexes.
      2. Create primary keys and unique keys.
      3. Add auto_increment for applicable primary keys.
      4. Create foreign keys.
    4. At the top of the script, prepend "use newSchema;" statement.
    5. In between the first (create tables) and second (create primary and unique keys) section, insert the following statements:

    Lock all the source tables:

    LOCK TABLES Appian.table1 WRITE,

LOCK TABLES Appian.table2 WRITE,

LOCK TABLES Appian.table3 WRITE,

.....

LOCK TABLES Appian.tableN WRITE;

    Data population statements

    INSERT INTO newSchema.table1 SELECT * FROM Appian.table1;

INSERT INTO newSchema.table2 SELECT * FROM Appian.table2;

INSERT INTO newSchema.table3 SELECT * FROM Appian.table3;

....

INSERT INTO newSchema.tableN SELECT * FROM Appian.tableN;

    6. At the end of the script after section 4 (create foreign keys), drop all the source tables and then unlock them.drop table Appian.table1;

    DROP TABLE Appian.table2;

DROP TABLE Appian.table3;

....

DROP TABLE Appian.tableN;

UNLOCK TABLES;

    7. Run this script in phpMyAdmin.

    8. Update all design objects that reference a data source to the newly created CSDS object.

    9.Test.

    Sample Application Schema Migration to Production Environment

    Here are the steps to migrate the application to the higher environments

    1. Create the new schema in the higher environments from phpMyAdmin by calling the stored procedure "call AppianProcess.createNewSchema('newSchema');". Note down the credentials and the jdbc url.
    2. Create a package from the dev environment that includes the data source connected system object only.
    3. Download and update the custom properties file with the credentials and jdbc url of the higher environment.
    4. Import this package in the higher environment with the custom properties file to create the CSDS object.
    5. Create another package from the dev environment with all the updated design objects that use the new CSDS object.
    6. Compare and deploy this package with the database scripts option. Attach the same sql script created from the dev environment which migrates the data to the new schema.
    7. If not using compare and deploy then execute the database script first using phpMyAdmin. Then import the package with the data store and any other updated design objects that use the new CSDS object.
    8. Test.
    © 2024 Appian. All rights reserved.
    We want to hear from you! Submit a review on Gartner or G2.