Initializing the Reference Database (REFDB)

Overview

For Oracle, database objects are contained in a schema. Applications will reference one or more schemas.

For SQL Server, DB2 and EDB Postgres, database objects are contained in a database. Applications will reference one or more databases.

To simplify discussion in this section, we will refer the schemas (in Oracle) and databases (in the other platforms) as Reference Databases.

Create the Reference Database

Each Datical project will have one or more pipelines - a pipeline represents a release.

• Most commonly, a project will have two pipelines - a current development pipeline and a hotfix pipeline (for production patches).
• Some projects will also contain pipelines to allow for multiple streams of development with different release dates.

Each pipeline will require its own reference database. The preferred approach is to setup a shared set of environments that can be used to hold the reference databases across all projects. The number of shared environments is equal to the maximum number of pipelines that could exist in a project. For example, if all projects have either 2 or 3 pipelines, then you would need to have 3 new environments available to setup reference databases.

Set Up Reference Database Credentials

Once the reference databases are setup, Datical will need database credentials to these new environments. In addition to the typical credentials needed to deploy database changes, Datical needs additional permissions so the packaging process can backup and restore the Reference Database as part of the build process. Documentation on the specific credentials needed can be found in the following database-specific documents:

• Oracle Roles and Permissions for Datical DB
• Oracle on Amazon RDS Roles and Permissions
• SQL Server Roles and Permissions for Datical DB
• Azure SQL Database Roles and Permissions
• Postgres EDB and PostgreSQL Database Setup
• DB2 LUW Roles and Permissions for Datical DB

Setup To Use the Reference Database the First Time

The above image represents a release pipeline in a Datical project. Once this pipeline has been created in a Datical project, the next step is to add a Datical Reference Database.

Before adding this database as the first step of your pipeline, you will need to create it and populate it as described in the Create the Reference Database section above. Once the database has been added to the pipeline, you will need to follow the process below to handle any sql scripts that have not yet been deployed into production.

1. Package the sql scripts that have not yet been deployed to production using Datical Packager
   1. Developer will check-in scripts to be packaged
   2. Datical Packaging Process will add the changes into the Datical changelog and update the Reference Database
   3. Build job will create the deploy artifact and load it into the artifact repository
   4. After all of the sql scripts have been processed, you will be ready to move to the next step
2. Tell Datical which sql scripts have already been deployed manually in the DEV through STAGE environments
   
   1. For each environment, perform the following Datical command
      • Run Datical ChangeLogSync to "fake-deploy" the sql scripts (using the sql script names as labels) that have already been deployed manually into the environment.
        • This can be done using the GUI or running the ChangeLogSync job in your deployment automation platform (the job might need to be created if it hasn't already been setup by your Datical PSE as part of the implementation).
        • This activity is typically done by the on-boarding engineer to ensure this operation is performed correctly and access is limited to the ChangelogSync job.
      • Repeat the above step for each environment
   2. After this has been done, everything will be tracked correctly and everyone on the team will be able to package and deploy scripts using Datical from this point forward.