Stephanie Herr

8 June 2020

• Deployment Suite for Oracle
• Database Builds and Deployments

Automating Oracle Database Deployments using the Hybrid Approach

The Deployment Suite for Oracle (DSO) helps teams version control their Oracle database changes and automate their deployment from version control. It integrates with your existing continuous integration and release management systems. The aim is to make these processes simpler, faster, more reliable, as well as more transparent to others in the organization, for governance and auditing.

The DSO gives teams flexibility to choose the way they want to work. We currently offer three different approaches to working:

• State-based approach with Source Control for Oracle – DDL CREATE scripts for each object
• Migrations-based approach with Redgate Change Control – change scripts that capture how objects change (e.g. ALTERs)
• Hybrid approach – using state for development and migrations for deployments

The overall goals of releasing safer and quicker are the same regardless. In this article, we’ll concentrate on the hybrid approach, which offers the benefits of getting started quickly and having full control over the scripts that will be used for deployment.

We refer to this as a ‘hybrid’ approach because it uses a ‘state’ workflow during development, which maintains in version control only the state of the database, and then a ‘migrations’ workflow to generate and adapt the scripts that will be used for deploying those changes to other databases. The following diagram depicts the overall DevOps pipeline, with the ‘state’ workflows indicated in orange and the ‘migrations’ in green. I’ll describe each section in more detail as we work along the pipeline.

Figure 1 – This diagram shows the hybrid approach, which uses Source Control for Oracle for the state workflow (orange) and Redgate Change Control and Automation for the migrations workflow (green).

Database development using Source Control for Oracle (state-based version control)

Source Control for Oracle creates and then maintains the DDL CREATE script for each object in version control. This provides teams with an audit of their changes (who, what, when, and why) and a safety net because they can easily get back to a previous version in their development database.

It also allows teams to have their own sandbox database to test database changes before committing them to version control. Source Control for Oracle is used to share these changes between team members. It allows teams to identify any conflicts for changes made to the same object.

Figure 2 – Developers use Source Control for Oracle to capture the state of the database as DDL CREATE scripts in version control.

For this work, teams should use Source Control for Oracle as they normally would. You can learn more about this at https://documentation.red-gate.com/soco/setting-up.

Continuous integration using state (optional) – Schema Compare for Oracle cmdline

Some teams may want to keep an integration environment up to date with the latest DDL scripts in version control to perform and test CI builds throughout the development process. To do this, the team would setup a CI process to get the latest version from the repository and then use the Schema Compare for Oracle command line with these scripts as a source and the integration database as a target. Schema Compare for Oracle will auto-generate a script and update the target integration database schema objects to match what it pulled from version control. This gives you an integration database that is always in the latest state. If the target is an empty database, then this process will build the new version each time and the team can import the required test data.

In some cases, if you’re using a permanent integration database, then you may lose data if you’re renaming tables/columns since a comparison tool won’t understand that a rename happened and just drop the old column name and create a new column. The state-based approach can also fail if adding new NOT NULL columns to an existing table that has data if a default value is not specified. These changes might be OK for an integration environment where data preservation is not key, but are an important reason for why you should use the hybrid model that uses migration scripts for your real deployments to downstream environments like QA, UAT, Staging, and Production.

Figure 3 -Triggering a Continuous Integration build to update the Integration database based on the latest state of the objects in version control.

There’s more information about using the Schema Compare for Oracle cmdline to perform this automated state-based deployment. If you have static data (e.g., lookup tables, configuration data) versioned alongside your schema changes using Source Control for Oracle, then the Data Compare for Oracle cmdline can also be used at this point to update the integration database with the latest static data as well..

Getting ready for release – generating migration scripts with Redgate Change Control

With a hybrid approach, teams will generate a migration script for deployment when the time is right for them. This could be when the work on a feature is complete, at the end of a sprint, or as part of a monthly/quarterly release process. At this time, a database specialist or a DBA will use Redgate Change Control to generate a migration script that will be used for deployment. The benefit of this is that the database specialist/DBA can see all the changes in version control that haven’t been included in a migration script yet. They can select the changes ready for this release. This is also known as cherry picking. The migration script will be generated by Redgate Change Control, which uses Redgate’s Schema Compare for Oracle technology behind the scenes. The database specialist/DBA can then modify/adjust the script as needed to have complete control over the what is run during the deployment. They can even add transactional data updates as well, if needed. This script is then saved in version control to be used for CI/CD.

To do this, a database specialist/DBA needs to do a few one-off steps to setup a Redgate Change Control project and prepare the database in the release pipeline. They should:

Create a Redgate Change Control project – one-off task

1. Get the latest changes that developers committed
   Update a local copy of the Git repository that the developers have been using with Source Control for Oracle. It’s important to always pull the latest changes from the repository so you can be sure you’re working against the latest database objects that the developers have committed/pushed.
2. Create a Redgate Change Control Project
   Follow these steps to create a Redgate Change Control project. You’ll want to use Source Control for Oracle as your Development Source which points to the location you created in the previous step.
   
3. Prepare the databases in the release pipeline
   Learn more about baselining your other environments at https://documentation.red-gate.com/dso/redgate-change-control/developing-databases/creating-a-new-project/creating-a-baseline. This is important in a migrations-based approach so that the baseline script isn’t reapplied to your downstream environments.

Generating migration scripts

Now that your project is setup and using the correct development source (the development team’s Source Control for Oracle project) and the downstream environments in your pipeline are ready, follow these steps to generate new migrations to be used for your deployments. This will be the main workflow to follow when you are ready to generate a migration script for deployment.

Figure 4 -Generate migration scripts from the state model.

1. Pull the developers latest changes into your local repository
   Use your version control system to update your local repository by pulling the latest changes. This makes sure that you have all the changes that the developers have committed/pushed that they feel are ready for release.
2. Generate a migration script
   Use Redgate Change Control‘s Generate migrations tab to review all the developers’ changes that have not been scripted out to the migration project yet. You can review how each object has changed in the differences panel at the bottom. Select which changes are part of this release and click the Generate scripts button. Review the script and give it a descriptive name. You can edit and add to the script as needed. Then click Save.
   
3. Update the remote repository
   Use your version control system to commit the migration script to your local repository. It is a good practice to include a detailed message. You can then push the migration script to the remote repository, or some teams create a Pull Request. A Pull Request can be useful because this is a chance for another team member to review the migration script before accepting it into the remote repository. If a developer generated the migration script, then this is a good time to get DBAs involved so they can catch any issues with the migration script that will be used for deployment early.

To learn more about generating migrations with Redgate Change Control, see our documentation site.

Continuous integration using migrations – Redgate Change Automation

Now that your migration script is in version control (maybe on a release branch), this is the script that you’ll use to test your database deployments. Your CI process should deploy this to an integration environment where automated tests and checks can be run. To do this, use Redgate Change Automation, which is a cmdline that simplifies automating your build and release process and adds additional checks and reports to help make sure your releases are successful.

Redgate Change Automation has a few commands that will help with CI:

• rca build
  • Checks what migration scripts have not been applied to your integration database yet and deploys them.
    Some teams will use an empty database, so the continuous integration environment is testing deploying a new database to the latest version from scratch. Other teams have an existing integration database, so it will just deploy any migration scripts that haven’t yet been run on the integration database. This quickly checks that the scripts in your repository are valid and there’s no syntax or dependency issues. This will give teams fast feedback if there is a problem with the migration scripts that needs to be reviewed and fixed.
  • Runs static code analysis checks
    These make sure best practices and naming conventions are being followed. This will also help you catch issues early before they go to Production. By default, all checks are set to warnings. If you want, you can customize which rules to run and which ones are errors that can fail the build. You can learn more about the rules and how to customize them at https://documentation.red-gate.com/dso/code-analysis-for-oracle.
  • Produces a build artifact
    This consists of a zip file that contains a copy of the scripts in the repository and the output from the code analysis checks. It is also best practice to build this artifact once and then deploy from it to guarantee a consistent deployment process.
• rca test (optional)
  • Runs any utPLSQL tests that are in your database
    If you have business logic in your database, then you may want to consider testing this at a database unit testing level using utPLSQL, the open source unit testing framework for Oracle databases that Redgate sponsors.

Automating database releases – Redgate Change Automation

Once your CI processes are successful, then your release system can be setup to automatically deploy to the next environment(s) in your pipeline. Use the following commands in Redgate Change Automation to help with this:

• rca release-prepare
  This command creates the release artifact. It will use the build artifact and a target database to create the release. The release is another zip file with a copy of the migration scripts, but it also contains additional reports that are useful for reviewing the database changes before going to Production. This includes:
  • Deployment.sql – the actual deployment script that would be run on the target to update it with the latest migration scripts
  • Changes.html – a detailed report that shows the before and after state of each object that will be changed as part of this release. This provides DBAs with an easy understanding of exactly what will be changing.
  • Drift report – if the target database has changed since the last deployment in this automated process, then you will get notified. This sometimes means that changes have been applied to the database outside of this release process. This might mean that your development environment isn’t a good representation of your downstream environment and might cause problems because you were developing against a database that’s in a different state. It’s usually best for a DBA to review these changes and see if it was a valid hot fix that needs to be incorporated back into the development environment.
• rca release-perform
  This command takes the release artifact that was generated in the previous command and executes the Deployment.sql script against the target database. This guarantees that the same script is applied to the target and there are no surprises. A lot of teams will set up their release pipeline to have a manual approval step before deploying to Production so that a Production DBA can review the reports in the release artifact and the script and sign-off on it as an additional check before it’s executed against Production.

See our documentation to learn more about the Redgate Change Automation cmdlines and worked examples to integrate Redgate Change Automation with CI/CD systems like Jenkins, Azure DevOps pipelines, and TeamCity and Octopus Deploy. These examples can be used to setup other CI/CD systems like GitHub Actions, GitLab CI/CD Pipelines, Bitbucket Pipelines, IBM UrbanCode, XebiaLabs, and more. If your system is not listed and you need help, please let us know.

Summary

We hope this helps you understand how you can use the Deployment Suite for Oracle to give developers a fast and easy way to version control their database object changes using Source Control for Oracle. Then, when the time is right, using a database specialist/DBA to generate a migration script that they have full control over for the release using Redgate Change Control. These migration scripts are versioned controlled and used by Redgate Change Automation in your CI/CD process to give you a repeatable and safe deployment to your downstream environments. The whole process can be customized to meet your needs and your release pipeline and can be setup with a manual intervention step that allows Production DBAs to easily review and approve changes as part of an automated pipeline.

If you have an initiative to automate your Oracle database releases in the next few months, we’d love to work with you to understand your needs and help with best practices. Please get in touch with the Redgate Oracle Team if you are interested in this or have any questions.