{"id":39220,"date":"2020-05-14T06:00:35","date_gmt":"2020-05-14T13:00:35","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/premier-developer\/?p=39220"},"modified":"2020-04-28T14:25:13","modified_gmt":"2020-04-28T21:25:13","slug":"using-azure-devops-pipelines-with-redgate-to-automate-oracle-database-deployments","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/using-azure-devops-pipelines-with-redgate-to-automate-oracle-database-deployments\/","title":{"rendered":"Using Azure DevOps Pipelines with Redgate to Automate Oracle Database Deployments"},"content":{"rendered":"

Application Development Manager Jafar Jaffery<\/a> demonstrates using DevOps build and release pipelines to streamline development changes to your Oracle databases.<\/p>\n

\n

In this post, I will cover a basic end to end-to-end example of taking a local Oracle database, setting up a project in a Git repository and using a Continuous Integration \/ Continuous Delivery (CI\/CD) pipeline to take your database changes from code through to production. I will use Azure DevOps<\/a> as the foundation for my CI\/CD pipeline, and will use Redgate Tools (Source Control for Oracle<\/a> \/ Schema Compare for Oracle<\/a>) to perform the build & release steps. Database development and deployment in Oracle is an involved effort with many moving parts, and using the products previously mentioned helps streamline the process.<\/p>\n

Below is a diagram of the pipeline that this post will cover. We won’t cover topics like unit testing or restoring data from other environment as part of the pipeline, but instead focus on the core components needed to get started with database deployments with Oracle databases. The process will start by committing changes to source control, which will kick off the build & publish artifact steps. Once that completes, our changes will be deployed first to a testing environment, and upon approval be promoted to a production environment. This can be extended to have as many environments as needed, but for simplicity we will stick to these two environments.<\/p>\n

<\/p>\n

Build Server \/ Agent Pool<\/h2>\n

In order to perform build and release tasks for an Oracle database, we will need to have a build server that has the requisite components. To achieve this, I started with Windows Server 2019. Additional components you’ll need to add are Oracle Client (I used the 18c Client<\/a> for Windows for my setup), and Redgate Schema Compare for Oracle. Once the build server is setup, you’ll also need to create a new agent pool using these steps<\/a>, and add a virtual machine as a new self-hosted agent<\/a> to that agent pool. We will refer to this agent pool as “Oracle Pipelines” for our example, and will later use this agent pool to perform our build and release steps.<\/p>\n

Source Control<\/h2>\n

<\/p>\n

For the source code, we will be using two different source control systems. The first one will be setup with an Azure DevOps Git repository (we’ll call it “OracleSandbox”) as our main repository for database code. It will also be used to initiate our CI Pipeline when a change is committed. The other will be Source Control for Oracle, which will be used to synchronize changes between local databases and our main Azure DevOps repository. Source Control for Oracle lets developers make changes directly in the database without having to manually script changes & organize them into a database folder structure. It also facilitates developer collaboration during database development.<\/p>\n

To create a new source control project, follow the steps shown here<\/a> on the Redgate site. Redgate also has a series of getting started videos<\/a> that cover a variety of source control topics like creating a new project, branching with Git, and conflict resolution. Once setup, the Git repository should look something like this:<\/p>\n

<\/p>\n

CI Pipeline<\/h2>\n

<\/p>\n

The build pipeline will perform the following tasks:<\/p>\n

    \n
  1. Validate the source schema in our Git repository.<\/li>\n
  2. Package the project files into an artifact. In this case it will be a .zip file, but it could also be a NuGet package or some other type as well.<\/li>\n
  3. Publish the artifact.<\/li>\n<\/ol>\n

    In this example, I’ve created the build pipeline using the classic editor, and started with an “Empty Job”. You you can do this using YAML pipelines as well. For more information on pipelines, you can start with this doc<\/a> as well as this guide<\/a>. As a note, set the Agent Pool to the one created earlier, namely “Oracle Pipelines”.<\/p>\n

    <\/p>\n

    The validation script uses SQLPlus & Redgate Schema Compare for Oracle to create a new temporary schema from the definition in our Git repository, and then removes that schema. This is done to ensure the schema definition in our Git repository is valid. As a reference, here is the PowerShell script I’m using for this:<\/p>\n

    # this script creates a target DB using the source schema (to validate it works), and then deletes that target schema\r\nparam (\r\n   $schemaCompareExePath,\u00a0\u00a0# location of Redgate SCO EXE\r\n   $sourceSchemaName, # source schema name from source control\r\n   $sourceSchemaRelativePath,\u00a0\u00a0# relative path of schema from source control root folder\r\n   $targetSchemaName, # target schema name to be created for validation purposes\r\n   $buildLocalPath, # local path where build files will be located\r\n   $sqlPlusConnStr, # connection string for SQL Plus commands\r\n   # connection \/ creds for Redgate Schema Compare\r\n   $tnsName,\r\n   $userName,\r\n   $password\r\n)\r\n$ErrorActionPreference = \"Stop\"\r\n$createQuery = \"CREATE USER $targetSchemaName IDENTIFIED by null; GRANT \"\"DBA\"\" TO $targetSchemaName; ALTER USER $targetSchemaName DEFAULT ROLE \"\"DBA\"\";\"\r\n\r\n# Create empty schema for validation\r\n$createQuery | sqlplus $sqlPlusConnStr | Out-Host\r\n\r\n# Deploy changes using Redgate Schema Compare for Oracle\r\nWrite-Warning \"Upgrading $targetSchemaName\"\r\n\r\ntry {\r\n   & $schemaCompareExePath -source \"$buildLocalPath\\$sourceSchemaRelativePath{$sourceSchemaName}\" -target \"$userName\/$password@$tnsName{$targetSchemaName}\" AS SYSDBA -deploy | Out-Host\r\n\r\n   # Logging the Schema Compare exit code and path to artifacts\r\n   Write-Output \"Schema Compare for Oracle exited with code $lastExitCode\"\r\n}\r\nfinally {\r\n   # drop the validation schema created\r\n   $dropQuery = \"DROP USER $targetSchemaName CASCADE;\"\r\n   $dropQuery | sqlplus $sqlPlusConnStr | Out-Host\r\n}<\/pre>\n
     <\/p>\n
    CD Pipeline<\/h2>\n
    The release pipeline will perform the following tasks:<\/p>\n
      \n
    1. Unzip\/unpack the artifact contents.<\/li>\n
    2. Deploy the changes using Redgate Schema Compare, wrapped in PowerShell in this example.<\/li>\n<\/ol>\n
      For information on how to create release pipelines, view the Microsoft documentation here<\/a>. Here are the things you need to set up in your pipeline:<\/p>\n
      <\/p>\n
      Note<\/strong>: be sure to set the Agent Pool (in the Agent Job) to the one created earlier, namely “Oracle Pipelines” — as shown here:<\/p>\n
      <\/p>\n
      Here is the PowerShell script I’m using for the release DB deployment:<\/p>\n
      param (\r\n   $schemaCompareExePath,\u00a0\u00a0# location of Redgate SCO EXE\r\n   $sourceSchemaName, # source schema name from source control\r\n   $sourceSchemaRelativePath,\u00a0\u00a0# relative path of schema from source control root folder\r\n   $releaseLocalPath, # local path where build files will be located\r\n   $targetSchemaName, # target schema name to be created for validation purposes\r\n   # connection \/ creds for Redgate Schema Compare\r\n   $tnsName,\r\n   $userName,\r\n   $password\r\n)\r\n\r\n# perform deployment with Redgate Schema Compare\r\n& \"$schemaCompareExePath\" \/source:\"$releaseLocalPath\\$sourceSchemaRelativePath{$sourceSchemaName}\" \/target:\"$userName\/$password@$tnsName{$targetSchemaName}\" AS SYSDBA \/deploy\r\nWrite-Output \"Schema Compare for Oracle exited with code $lastExitCode\"\r\n\r\n# exit code of 61 (differences in source\/target schemas) is not actually an error, but ends up being treated like one -- so adding logic to ignore that\r\nIf ($lastExitCode = 61) {\r\n   exit 0\r\n}<\/pre>\n
       <\/p>\n
      Closing Thoughts<\/h2>\n
      As mentioned earlier, the pipeline described in this post is not meant to be a comprehensive one. Rather, it is intended to be a starting point for automating your Oracle deployments. For additional considerations in your Oracle database DevOps process, Redgate has a good whitepaper here<\/a>.<\/p>\n
      Below are some additional references for your consideration:<\/p>\n