Welcome to part 2 in this short series. In this post I’ll explain how to extend the Oracle DB continuous integration process I set up in part 1 by adding a release management process that deploys changes to staging or production databases at the click of a button.

Objective for part 2

At the click of a button, at a time of their choosing, a human can deploy any deployment artifact, created by a CI process, to another database.

Why’s this worth doing?

“…but it works on my machine?” explained the developer, moments after a failed deployment to production.

You see, the developer’s machine is configured differently, the deployment process was manual and no-one can say for certain whether the 57 steps were followed correctly, and no-one is really sure if the production database was consistent with source control to begin with.

It might take a while to unpick what went wrong – and, more importantly, how to fix it.

It is a principle of continuous delivery that you build once, creating a single deployment artefact which is tested/validated by a CI process. If the tests pass, the artifact may be deployed at the click of a button in a consistent manner to other environments.

Deployment artifacts and repeatable processes to deploy them provide environment consistency and reduce risk. They also simplify deployment processes and provide visibility and accountability, as well as documenting the deployment process.

This potentially allows you to deploy to production ten (or more?) times a day – if you want to. No longer will database deployments be the bottleneck that stop you from being responsive and agile.

This blog post is split into three parts:

1. Before you start…
2. Making it work from the command prompt
3. Automating the commands with Bamboo and PowerShell

Requirements

1. Redgate Schema Compare for Oracle
   You’ll need this to script out your schema objects.
2. Redgate Source Control for Oracle
   You’ll need this to get your schema objects into a source control system (eg TFS, SVN, Git).
3. Atlassian Bamboo
   This tutorial uses Bamboo, but I’ll try to explain the set-up in a way that lets you copy it on other release management tool (eg Octopus Deploy, Microsoft Release Management).
   I’m assume your release management tool is already set up.
4. NuGet
   You’ll need to install NuGet on your dev machine and on the build agent.
5. .NET 4.5 on the build agent

One of the deployment scripts requires .NET 4.5. If you do not have .NET 4.5 you will either need to install it or write a different script to achieve the same task for this section.

6. You’ve completed part 1 of this series

Or you have a similar process for creating NuGet packages in the correct format.

Before you start…

Ensure you have followed the steps in part 1 of this series.

Create an empty Oracle schema to represent a mock LIVE environment. This is the schema we’ll deploy to from Bamboo. In my case I created the schema WIDGETLIVE.

Deploying from the command prompt

Before you automate a task, you need to check you can complete it manually, so you understand the process and can troubleshoot errors.

Our deployment process will do the following:

1. Download and extract the NuGet package
2. Use Redgate Schema Compare for Oracle to deploy the code to a target schema

Follow these steps manually so that you understand the process that we intend to automate.

Download and extract the NuGet package

If you don’t use Bamboo for your CI server you should use whatever tooling you have to download the NuGet artifact from a passing build and skip to step 5.

1. Open Bamboo and go to the dashboard by clicking the Bamboo logo in the top left corner. You should see your build plans listed.
2. Locate your Plan that creates your NuGet package for your Oracle schema. Ensure that the last build was successful. (You should see a green tick in the Build column with a build number.) If the last build was not successful, you should probably attend to that first. Alternatively find a previous successful build.
3. Click on the tick. This will open a page with data about this particular build run including test results and build logs etc. Click the Artifacts
4. You should see a list of artifacts associated with this build. Download the NuGet package by clicking on it. 
5. Locate the NuGet package on disk and rename the file extension from .nupkg to .zip. Then right click on it and extract it to a directory. I’ll extract it to C:\deleteme. (This works because NuGet uses exactly the same compression technology as .zip files.)

Use Redgate Schema Compare for Oracle to deploy the code to a target schema

1. Use file explorer to explore your extracted NuGet package.
2. In the state directory you should find the xml file and a directory with the name of your schema, in my case WIDGETDEV. Inside the schema directory you should find scripts that define the state of your database, organised into directories according to object type. 
3. Open a command prompt window and enter the following commands:

C:\Users\Username> cd C:\Program Files\Red Gate\Schema Compare for Oracle 3

C:\Program Files\Red Gate\Schema Compare for Oracle 3>SCO.exe /source=C:\file\path\to\schema\scripts{schemaName} /target=dbUsername/dbPassword@tns{liveSchema} /deploy

Where the file path is to the directory that contains the DatabaseInformation.xml file. For example, I used:

C:\Users\AlexYates> cd C:\Program Files\Red Gate\Schema Compare for Oracle 3

C:\Program Files\Red Gate\Schema Compare for Oracle 3>SCO.exe /source= C:\deleteme\state{WIDGETDEV} /target=system/*****@localhost{WIDGETLIVE} /deploy

4. You should see from the logs in the command prompt that this deploys the objects to the target schema. Now open the mock LIVE schema in your IDE of choice and you should see that it has been updated in line with the contents of the NuGet package.

If you can’t get this deployment to work from the command prompt try firing up the Schema Compare for Oracle GUI as this will provide more feedback/visual cues and might help you to figure out the problem. You can also try using the /verbose command line switch or you could try saving artifacts like diff reports and upgrade scripts by exploring the various command line switches documented here. If you still can’t get it working, contact the Redgate support team (support@red-gate.com).

Assuming you managed to get the deployment to work, you are ready to move on to the next section.

Automating the commands with Bamboo and PowerShell

Now, let’s automate that process. Note that I don’t mean to automate the trigger, this I intent to be a human clicking a button, but I do intend to automate the process. I want a human to decide when to deploy to LIVE, but I don’t want them to have to follow 57 steps to get there.

This section is split into 5 steps:

1. Create a deployment project
2. Add a task to extract the NuGet package
3. Add a task to deploy the schema
4. Add one more variable
5. Trigger a deployment

Create a deployment project

This is exactly like creating a Project in Octopus Deploy.

1. Go to the Bamboo dashboard and, at the top, click Create > Create Deployment Project.
2. Give the deployment project a name, for example “WIDGET schema deploy” and link it to your existing build plan for the schema. Click Create deployment project.
3. Click Add environment. Provide a name, for example LIVE. Provide a description if you wish and click Continue to task setup.

Add a task to extract the NuGet package

This is exactly like adding a process step to deploy a NuGet package with a post deploy PowerShell script in Octopus Deploy.

1. Observe that by default your LIVE environment will contain two tasks: Clean working directory task and Artifact download. These steps will delete the contents of your working directory and then download all the artifacts from your build plan that have been marked as shared. This should include your NuGet package.
2. Click Add task and scroll down through the options to add a Script. 
3. Add a task description along the lines of Extract NuGet package.
4. Check the Run as Powershell script
5. Paste the following code into the Script body (also available on my GitHub.)

# NOTE: This script requires .NET 4.5

# Declaring the paths to the NuGet package and the destination directory

$nuget = '${bamboo.build.working.directory}\widget.0.${bamboo.buildNumber}.nupkg'

$destination = '${bamboo.build.working.directory}\extractedNuGet'

 

# Clean the destination directory.

If (Test-Path $destination) {

rmdir $destination -Recurse -Force

}

 

# Extract the NuGet package

# Code copied from http://stackoverflow.com/questions/24672560/most-elegant-way-to-extract-a-directory-from-a-zipfile-using-powershell

[Reflection.Assembly]::LoadWithPartialName('System.IO.Compression.FileSystem')

[IO.Compression.ZipFile]::ExtractToDirectory($nuget, $destination)

6. Note that the script above requires .NET 4.5. If this is not installed on the build agent, either install it or write your own script to extract the NuGet package to the directory ${bamboo.build.working.directory}\extractedNuGet.
7. Click save.

Add a task to deploy the schema

If using Octopus Deploy you should probably append this PowerShell script to the end of the first one or add both PowerShell scripts as child steps to your the NuGet step.

1. Repeat steps 1 to 4 above to add another PowerShell script step. This time give it a description along the lines of Deploy schema.
2. Paste the following code into the script body (also available on my GitHub).

# Configuration

$schemaComparePath = 'C:\\Program Files\\Red Gate\\Schema Compare for Oracle 3\\SCO.exe'

$targetSchemaName = "${bamboo.OracleDbLiveSchema}"

$schemaRelativePath = "${bamboo.schemaRelativePath}"

$tns = "${bamboo.OracleTns}"

$username = "${bamboo.OracleDbUser}"

$password = "${bamboo.OracleDbPassword}"

$sourceName = "${bamboo.SourceSchemaName}"

 

# Logging config variables for troubleshooting

Write-Output "Using the following variables:"

Write-Output "schemaComparePath: $schemaComparePath"

Write-Output "schemaRelativePath: $schemaRelativePath"

Write-Output "targetSchemaName: $targetSchemaName"

Write-Output "tns: $tns"

Write-Output "username: $username"

Write-Output "password: has been set to the bamboo variable OracleDbPassword"

Write-Output "sourceName: $sourceName"

 

# Deploy changes using Redgate Schema Compare for Oracle

Write-Warning "Upgrading $targetSchemaName"

 

& $schemaComparePath -source "${bamboo.build.working.directory}\${schemaRelativePath}{$sourceName}" -target "$username/$password@$tns{$targetSchemaName}" -deploy -scriptfile "${bamboo.build.working.directory}\artifacts\upgradeScript.sql" -report  "${bamboo.build.working.directory}\artifacts\diffReport.html" -reporttype Simple -includeidentical | Out-Host

 

# Logging the Schema Compare exit code and path to artifacts

Write-Output "Schema Compare for Oracle exited with code $lastExitCode"

Write-Output "UpgradeScript and diff report created and saved to ${bamboo.build.working.directory}\artifacts"

 

# Exit code 61 is simply telling us there are differences that have been deployed.

if( $lastExitCode -eq 61)

{

exit 0

}

3. Click Save.

Add one more variable

Octopus Deploy users will need to replace all the Bamboo variables with Octopus Deploy variables.

You’ll notice that the script above makes use of the same variables we set up in part 1 of this series, with one exception: ${bambo.OracleDbLiveSchema}. This replaces the ${bambo.OracleDbTestSchema} variable we used for our build. This post assumes that your variables were set as Plan variables and that they are inherited by this deployment project. If this is not true you may need to reconfigure your existing variables so that they do cover the deployment project.

Of course, while in my demo example I have all my schemas on the same Oracle database it is entirely likely you will be using different databases and passwords etc for the different environments so you may also want to create new variables for ${bambo.OracleTns }, ${bambo.OracleDbUser} and ${bambo.OracleDbPassword}. Alternatively you might like to override the Plan variables by setting Environment variables to override the Plan variables.

In either case, you’ll need to modify your Bamboo variables in a way that suits you to ensure that this deployment will be executed against the correct schema with the right credentials. Also, if you have more than one environment that you’d like to deploy to, for example UAT, Staging and Pre-Prod environments, or if you have multiple production databases to deploy to, you can add more environments or add more deployment steps using the same scripts and simply replacing/overriding the variables.

Also, watch out for your relative paths. There are various relative paths that are used in various scripts that are likely to be slightly different. For example, the path to your NuSpec package in source control, the path to your schema script files in source control and the path to your schema script files within your NuGet package. These are all likely to be similar but possibly slightly different – so you may end up creating/overwriting various variables for these, or just hard coding them.

Trigger a deployment

Your deployment project should work now. To deploy your project take the following steps:

1. Go back to the Bamboo dashboard.
2. Click on the tick for the last successful build.
3. Under the Build Summary tab, on the right hand side, you should see your deployment project. (This is because we linked the deployment project to this build plan earlier). Click the Create release On the next screen accept the defaults and click Create release again.
4. Press the  icon under the Actions column for your environment name.
5. On the next screen click the Start deployment
6. Follow the logs. You should see that after running the deployment the LIVE schema has been updated to match the state held by the NuGet package.

And you’re done.

Summary

As an exercise, try to complete the following as if your change was a critical bug-fix for an issue that was costing your organisation a squillion dollars a second:

1. Make a change to the dev schema
2. Commit it to source control
3. Watch the build run automatically
4. Create a new release for the new code and deploy it to your mock LIVE schema

Can you can do all that in less than a couple of minutes? If so that’s a pretty impressive cycle time if you ask me!

Resources

The completed scripts: https://github.com/Alex-Yates/Powershell-for-Oracle-Database-CI-with-Bamboo

Redgate Deployment Suite for Oracle (for your dev machine): http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/

Redgate DLM Automation Suite for Oracle (for your Bamboo build agent): http://www.red-gate.com/products/oracle-development/dlm-automation-suite-for-oracle/

Redgate tools come with a fully functional free trial period.

Schema Compare for Oracle command line switches documentation: http://documentation.red-gate.com/display/SCO3/Command+line+switches