Add Sql update scripts to offline package

We have an Octopus deployment process with a database update step based on the dbup tool.

When deploying a specific release to an evironment the database update step makes a http request to an endpoint on our applicationserver passing the “Octopus.Release.Number” and “Octopus.Environment.Id” as parameters getting a list of SqlScripts as response. This works fine for all environments that have access to the internet to retrieve the list of SqlScripts.

Now however we have a new Environment that needs to be updated with a Offline package drop because of high security issues and no tentacle available. The target environment has no access to our application server to get the list of SqlScripts at install time. This means somehow the SqlScripts need to be part of the Offline package.

How could this be achieved?
So how can I add the SqlScripts to the Offline package at the time of creation so the SqlScript are accessible when the Offline package is executed in the target environment.

Thanks in advance,

Willy

Hi @vroenhoven,

Thanks for reaching out.

You should be able to achieve this by using DBUp. DBUp works by adding SQL Scripts as embedded resources to the compiled .exe. When DBUp executes, it looks into itself to retrieve the list of scripts to execute, comparing that list to the journal table in the database so that it only executes scripts once (unless otherwise specified). If it’s an air-gapped environment without Internet access, embedding the scripts in the .exe itself sounds like it could work as a solution.

Please let me know if you think this will work for you as a solution.

Thanks,
Jeremy

Hi Jeremy,

Unfortunately your solution does not work.

The SqlScripts depend on the combination of “Release” and “Environment”. Because the “Environment” is not known at compile time of the DBUp .exe the scripts can not be embedded in the assembly. Only as soon as the deploy of Release and Environment is selected in the overview the applicable SqlScripts can be determined. Thats why we use a http endpoint that takes these two parameters as input and returns the applicable SqlScripts. Instead of executing the SqlScripts directly against the target database I now need to store them in the Offline package drop so they will be available on the target when executing the offline install.

Any other ideas.

Greetings,

Willy

Hi Willy,

I believe to achieve this you will need to modify your build process. If the offline target exists only in one environment, you could add a build step that grabs the scripts for that environment and creates a second package and pushes it to Octopus. Then, you will create another new step in Octopus that only runs on the offline package machine(s) to deploy this new package with the bundled scripts. Finally, you would point DBUp at this package, rather than the http endpoint. You will have two separate packages, but you should be able to achieve the desired results with this method.

Please let me know if that will work for you or if you need any more help with it.

Thanks,
Jeremy

Hi Jeremy,

I thought about this but was not sure if it was feasible. How would I create a second package and push it to the same Octopus server we are on. Any sample code available

Greetings,

Willy

Hi Jeremy,

Sorry I didn’t read properly. You are taking about a build step not an Octopus step to create the second package. I will try that.

Greetings,

Willy

Hi Willy,

No worries, I was just getting you an example from our Azure Devops pipeline that does multiple packages.

I’m not sure if you still need it but I will attach it just in case. OctoFX.yml (2.1 KB)

Please let me know if you get it working or if you need more assistance.

Thanks,
Jeremy

Hi Jeremy,

I have evaluated the suggested approach but found that creating an additional Nuget package at build time was to early in the process. When the build is running, our http endpoint has no knowledge about the Octopus release going to be created.

The current build pipeline is like this.

  1. Build solution and create nuget packages
  2. Push nuget packages to Octopus
  3. Create Octopus release
  4. Our application has a subscription to the “Release Create” webhook event.
  5. Our application receives and evaluates the Webhook event information and relates Sql scripts to the release that was created. From this moment on the http endpoint will return the correct Sql scripts given a Environment.Id and Release.Number

So instead of changing the the build process. I changed the Octopus deploy process. I added a powershell script step that is executed on a Worker and calls the http endpoint with the Evironment.Id and ReleaseNumber. The web request returns a json string which describes the sql scripts that need to be executed. The json is saved in an output variable to make the json accessible to all further steps.

I changed the existing DatabaseUpdater step to read the Sql scripts from the output variable instead of doing a http request.

Tested the process with a deployment to an offline drop target. Checked the created zip file for its contents. All the json files in the Variables folder contain the output variable with the sql scripts.

Have not tested an offline install yet but i supose the sql scripts will be accessible since they are available in the json files.

Can you confirm this is a proper solution or do you foresee any problems with this approach.

Greetings,

Willy

Hey Willy,

I looked at is as well as a colleague and we both agree that if your JSON can be correctly de-serialized to extract the scripts without issue, this looks like you should be in a good place going forward. Do you have a test environment to run it against?

Thanks,
Jeremy

Hi Jeremy,

Thanks for the response.

Yes we do have three different Environment types to run it against.

  • Application hosted on Azure -> Has been tested an runs without problems
  • Application hosted on Commercial hosting provider with Tentacle installed -> Has been tested and runs without problems
  • Application hosted on premise without external connectivity -> Needs to be tested by a colleague. We have a test setup for that but I have to wait for the test result

Greetings,

Willy

Hi Willy,

Thanks for the update. Sounds like its a potential winner so far. Please let me know if the final test passes or if you need more help with the setup.

Thanks,
Jeremy

Hi Jeremy,

We have tested the offline install on an on premise machine without connectivity. The database updates are executed as expected.

So problem solved.

Greetings,

Willy

1 Like

Hi Willy,

That’s great news thank you for updating me. Have a great rest of your week!

Thanks,
Jeremy