How to deploy SQL scripts to an AWS RDS instance

I’ve long been using Octopus Deploy to deploy SQL change to SQL Server instances hosted on EC2; however, I am now looking at doing similar with SQL Server instances hosted on AWS RDS. The executable part of the application is running on AWS ECS Fargate, and that part is all set up and running nicely.

So assuming that my build process has created a NuGet package that contains one or more SQL scripts that needs to be run against a SQL Server database running on AWS RDS, what’s the best way of going about this in 2022?

A few problems I’ve come up against:

  1. I can’t see how to create a deployment target, since I can’t create a new tentacle;
  2. When I add an Extract Package step, it wants to know where to execute the step, and since I couldn’t complete #1 above, I’m kinda stuck.

Hey @david.keaveny,

Have you used workers before? You can use a worker in Octopus to run your database migration scripts much like you would on a deployment target.

I’d recommend looking at this blog to give you an idea of how to set this up. The program to run scripts is DbUp, but it covers how to use workers to run the DbUp program.

If you have any questions, please let me know.

Thanks

Hi Adam,

Yes, I have used workers before, and that’s what I want to be doing, but my problem for now is that I can’t set a deployment target, because I can’t see any obvious candidates for AWS RDS. I’ve used worker pools before when deploying to a database hosted on EC2, but that’s never been a problem because I can install Tentacles on EC2; that’s not an option on a managed database server like AWS RDS. Octopus has built-in support for AWS ECS, so the serverless configuration for the other components of my application isn’t an issue.

In the Deploy A Package step, where I am extracting my SQL scripts from the NuGet package emitted by my build system, there is no option for deploying to a worker - only to deployment targets:

Other steps in the process (such as Execute SQL Script) do give me other options for execution location, including worker pools:

Install Tentacles and Workers for database deployments - Octopus Deploy says that if I want to extract a package, then I cannot use worker pools, and must set up a jump box, but it seems a bit extravagant having an EC2 instance sitting around waiting for deployments when the rest of our infrastructure is serverless. Are there any other approaches I can take? Are offline package drops any use?

Hey @david.keaveny,

Sorry, for the delay in responding!

Rather than using the deploy package step, you can use the run a script step and reference a package instead. Ideally, you would create a worker pool that contains workers for running database deployment scripts in your AWS ECS cluster.

This blog covers referencing packages in a run a script and here is a sample (you can sign in as a guest) of an Octopus deployment process running database scripts using referenced packages.

I think this does help you get around your problem but if you have follow-up questions please let me know.

Thanks