Skip to main content

Multiple Database Catalogs and Schemas

OutSystems

Multiple Database Catalogs and Schemas

Enterprise applications often require thorough data separation down to database level, allowing for more granular storage management, database maintenance, or simply for organization purposes. The Multiple Database Catalogs and Schemas feature of the OutSystems Platform, allows you to meet these requirements by binding your eSpaces to specific database catalogs or schemas. The Platform supervises data accesses and generates all necessary scripts to move data between databases.

Introduction

OutSystems supports both Microsoft SQL Server and Oracle. While these database management systems use different terminology, for a matter of simplicity this technical note uses the term ‘‘database‘‘ to refer to an SQL Server Catalog and Oracle Schema. 

Why Use Multiple Database Catalogs and Schemas

Using Multiple Database Catalogs and Schemas improves the management and maintenance of your databases data, namely:

  • Improved database backup policies per application. For example: by establishing different backup schedules depending on database size and importance;
  • Improved database maintenance plans per application;
  • Optimize I/O performance by splitting the application‘s data across storage systems. For example, data with higher I/O needs distributed by more performing hard disks;
  • Smaller database sizes through data distributed by several databases, thus not having all information stored in the same place in big files;
  • The possibility of restoring non-BPM databases without affecting other applications, if already using a database that is independent from the Platform Database (Main);
  • The ability to have different database transaction logging policies.

Usage Scenario

The following diagram depicts a typical scenario with three distinct databases: one for Intranet applications, one for Internet corporate applications, and the OutSystems database.

The OutSystems database must always exist to store:

  • OutSystems Platform’s meta data;
  • System Components specific data;
  • Global data to support cross-application infrastructure such as: Single Sign-on, Permission Areas, etc;

1.png

Steps to Create Multiple Databases

Create a new Database (Catalog) in SQL Server

In SQL Server, the DBA must first create the database Catalog, and then set its permissions for both the Platform’s admin and runtime database users (set in the Configuration Tool).
The permissions should be set as follows:

Runtime User: must have read and write permissions for all tables.

runtime_user.png

Platform’s admin user: must have the permissions set for db_accessadmin, db_datareader, db_datawriter, db_ddladmin, and db_securityadmin.

platform_admin_user.png

Once the catalog is created in SQL Server, proceed to ServiceCenter Adminitration tab and select Database Catalogs.

database_catalogs.png

Then use the “Configure New Catalog” and add the catalog you previously created. Please test the configuration before saving it in order to ensure the permissions are correctly setup.

configure_database_catalog.png

Create a new Database (Schema) in Oracle

To create a new database Schema, go to Service Center, and in ‘Database Schemas’ select ‘Configure new Schema’.

Fill in the information for the new schema and download the configuration script. Then, execute the script (as DBA) in the Oracle database to effectively create the new Schema. To finish, test the newly created Database Schema in Service Center with the ‘Test Configuration’ button and save the schema.

The script to create the Schema already sets all required permissions.

configure_database_schema.png

Publishing new eSpaces

Publishing a New eSpace

In Service Studio, when you publish an eSpace for the first time using Multiple Database Catalogs and Schemas you are asked to specify the catalog/schema where to publish that eSpace. The eSpace is published in the Platform Server, its database objects created in the specified catalog/schema, and permissions are set for the runtime user.

After publishing an eSpace for the first time, you will no longer be asked about the catalog/schema for it: you can move an eSpace to another database in Service Center. Learn more about moving an eSpace to another catalog/schema in the “Moving an eSpace to Another Database Catalog/Schema” section further ahead.

In Service Center, the behavior of publishing a new eSpace is the same as in Service Studio, i.e., you’re asked to set
the catalog/schema where the eSpace is to be published.

Publishing a Solution with new eSpaces

When publishing a Solution with Multiple Database Catalogs and Schemas, either in Service Center or using the Solution Pack Tool, you are asked to set the catalog/schema for each one of the new eSpaces found in the Solution.

For eSpaces that were already published in the Platform Server, their catalog/schema is already set in the Platform Server, as for the others, the publishing proceeds as follows:

  • No catalog/schema is set for the eSpace in the Platform Server: the user is asked to set the catalog/schema for the eSpace, as if it were a new eSpace;
  • A catalog/schema is set for the eSpace in the Platform Server: the catalog/schema set in the Platform Server overrides the one that comes with the eSpace in the Solution.

Moving eSpaces to Different Databases

In a Multiple Database Catalogs and Schemas scenario, moving an eSpace from a database to another involves executing several steps which are described in this section.

SQL Server

Moving an eSpace to a Different Database Catalog

To move an eSpace to a different Catalog execute the following steps:

  1. Backup the source and destination databases
     
  2. Mark the eSpace to be moved
    1. Open Service Center;
    2. Open the edit screen of the eSpace, select the Operation tab, and in the Catalog drop down select the Catalog where the eSpace is to be moved to;
    3. Press the ‘Apply’ button: the eSpace won’t be moved right away but marked for to be moved, therefore, it will still be operating with its old Catalog;
       
  3. Copy the database data
    For Version 8 or lower For Platform 9 or higher 
    1. Once the eSpace is marked to be moved, go the eSpace details screen, click the link ‘Download Data Copy Script’ to download the script that will copy the eSpace data;
    2. Bring the application that contains the eSpace offline using the ‘Take Offline’ button, on the application details screen;
    3. Run the script in SQL Server Management Studio, to copy the eSpace data. You must have the same privileges as the admin user of the Platform;
    4. The script creates the source tables and views into the destination Catalog, copies their data, and creates the constraints;
    5. Ensure that the move operation was successful and the application is working properly.
    6. In the source Catalog, tables are kept for any eventual data recovery situation, but are renamed with a ‘OLD_‘ prefix. To definitely delete them, use the commented (not executed) SQL commands at the end of the script;
    7. In case of moving large amounts of data, require the DBA assistance to examine data copy steps on the script, and possibly make them more efficient.
    1. Once the eSpace is marked to be moved, take offline the application that contains the eSpace (using the ‘Take Offline’ button, on the application details screen);
    2. Create a SQL script to copy the eSpace data to the destination catalog (run it in SQL Server Management Studio);
    3. Ensure that the move operation was successful and the application is working properly.
  4. Finish the eSpace move

    1. In Service Center, publish the eSpace;

    2. The Platform Server first checks that all tables of the eSpace are created in the destination Catalog. Then it displays a ‘Use Matching Tables’ option that is to be selected, and continue with the publishing process;

    3. If the eSpace has Consumer eSpaces, republish them, or build a solution with all referenced eSpaces and publish it in a single step;

    4. Bring the eSpace online using the ‘Bring Online’ button on the eSpace details screen;

    5. The eSpace is now using the new Catalog.

Staging a Solution to Production with Moved eSpaces

To stage a Solution to a Production environment with eSpaces moved to a different Catalog, proceed as follows:

  1. Make backups of the databases.
     
  2. Stage the solution
    1. Open Service Center of the Production environment;
    2. In the Solution list screen, upload the solution;
    3. Because the solution contains moved eSpaces, a table detailing them is displayed during the upload, and their move should be confirmed.
       
  3. Copy the database data
    For Version 8 or lower For Platform 9 or higher 
    1. Once the eSpace is marked to be moved, go the eSpace details screen, click the link ‘Download Data Copy Script’ to download the script that will copy the eSpace data;
    2. Bring the application that contains the eSpace offline using the ‘Take Offline’ button, on the application details screen;
    3. Run the script in SQL Server Management Studio, to copy the eSpace data. You must have the same privileges as the admin user of the Platform;
    4. The script creates the source tables and views into the destination Catalogs, copies their data, and creates the constraints;
    5. Ensure that the move operation was successful and the application is working properly;
    6. In the source Catalogs, tables are kept for any eventual data recovery situation, but are renamed with a ‘OLD_‘ prefix. To definitely delete them, use the commented (not executed) SQL commands at the end of the script;
    7. In case of moving large amounts of data, require the DBA assistance to examine data copy steps on the script, and eventually make them more efficient.
    1. Once the eSpace is marked to be moved, take offline the application that contains the eSpace (using the ‘Take Offline’ button, on the application details screen);
    2. Create a SQL script to copy the eSpace data to the destination catalog (run it in SQL Server Management Studio);
    3. Ensure that the move operation was successful and the application is working properly.

     
  4. Finish the solution staging
    1. In Service Center, publish the solution;
    2. The Platform Server first checks that all tables (of the moved eSpaces) are created in their destination Catalogs. Then it displays a ‘Use Matching Tables’ option that is to be selected, and continue with the publishing process;
    3. All Consumer eSpaces should be republished. Republish them or build a solution and publish it in a single step;
    4. Bring eSpaces online using the ‘Bring Online’ button on the detail screen of each application they belong to;
    5. The solution is now staged and all moved eSpaces using their new Catalogs.

Oracle

Moving an eSpace to a Different Database Catalog

To move an eSpace to a different Schema execute the following steps:

  1. Backup the source and destination databases
     
  2. Mark the eSpace to be moved
    1. Open Service Center;
    2. Go to the eSpace details screen, select the Operation tab and select the Catalog where the eSpace is to be moved to;
    3. Press the ‘Apply’: the eSpace won’t be moved right away but marked to be moved, therefore, it will still be operating with its old Schema;
       
  3. Copy the database data
    For Version 8 or lower  For Platform 9 or higher 
    1. Once the eSpace is marked to be moved, go the eSpace details screen, click the link ‘Download Data Copy Script’ to download the script that will copy the eSpace data;
    2. Bring the application that contains the eSpace offline using the ‘Take Offline’ button, on the application details screen;
    3. Run the script to copy data in the Oracle SQL Developer with a dba user;
    4. The script uses the Oracle Data Pump to create the source tables and views into the destination Schema, copies their data, and creates the constraints;
    5. In the source Schema, tables are kept for any eventual data recovery situation, but are renamed with a ‘OLD_‘ prefix. To definitely delete them, use the commented (not executed) SQL commands at the end of the script, but assuring that the move operation was successful and the application is working properly;
    6. Usually Data Pump is efficient when moving large amounts of data, though the script can still be optimized by the dba for your specific case.
    1. Once the eSpace is marked to be moved, take offline the application that contains the eSpace (using the ‘Take Offline’ button, on the application details screen);
    2. Create and run a SQL script to copy the eSpace data to the destination Schema;
    3. Ensure that the move operation was successful and the application is working properly.
  4. Finish the eSpace move

    1. In Service Center, publish the eSpace;

    2. The Platform Server first checks that all tables of the eSpace are created in the destination Schema.
      Then it displays a ‘Use Matching Tables’ option that is to be selected, and continue with the publishing process;

    3. If the eSpace has Consumer eSpaces, republish them, one by one. Yet, if they are too many, it’s more efficient to build a solution with them and publish that solution in a single step;

    4. Bring the eSpaces online using the ‘Bring Online’ button on the application details screen that contain the eSpaces moved;

    5. The eSpace is now using the new Schema.

Staging a Solution to Production with Moved eSpaces

To stage a Solution to a Production environment with eSpaces moved to a different Schema, proceed as follows:

  1. Make backups of the databases in question.
     
  2. Stage the solution
    1. Open Service Center of the Production environment;
    2. In the Solution list screen, upload the solution;
    3. Because the solution contains moved eSpaces, a table detailing them is displayed during the upload, and their move should be confirmed.
       
  3. Copy the database data
    For Version 8 or lower  For Platform 9 or higher
    1. Once the eSpace is marked to be moved, go the eSpace details screen, click the link ‘Download Data Copy Script’ to download the script that will copy the eSpace data;
    2. Bring the application that contains the eSpace offline using the ‘Take Offline’ button, on the application details screen;
    3. Copy data by running the script in the Oracle SQL Developer with a dba user;
    4. The script uses the Oracle Data Pump to create the source tables and views into the destination Schema, copies their data, and creates the constraints;
    5. In the source Schemas, tables are kept for any eventual data recovery situation, but are renamed with a ‘OLD_‘ prefix. To definitely delete them, use the commented (not executed) SQL commands at the end of the script, but making sure before that the move was successful and the application is working properly;
    6. Usually Data Pump is efficient when moving large amounts of data, though the script can still be optimized by the dba for your specific case.
    1. Once the eSpace is marked to be moved, take offline the application that contains the eSpace (using the ‘Take Offline’ button, on the application details screen);
    2. Create and run a SQL script to copy the eSpace data to the destination Schema;
    3. Ensure that the move operation was successful and the application is working properly.
  4. Finish the solution staging

    1. In Service Center, publish the solution;

    2. The Platform Server first checks that all tables (of the moved eSpaces) are created in their destination Schemas. Then it displays a ‘Use Matching Tables’ option that is to be selected, and continue with the publishing process;

    3. All Consumer eSpaces should be republished. If the solution doesn’t contain them all, republish them individually or within another solution;

    4. Bring the eSpaces online through the ‘Bring Online’ button on the details screen of each of the application that contains the eSpaces moved;

    5. The solution is now staged and all moved eSpaces using their new Schemas.

  • Was this article helpful?