How to integrate Redgate with Octopus

Hello

Kindly guide me on how to integrate redgate with octopus deploy

do we need to manually install redgate as seperate software and then integrate with octopus

or we can use predefined redgate dpeloyment steps through octopus

Please suggest what all can be possible through redgate predefined steps in octopus

i have tried to create a table in alreday existing database , but ended up with errors and do not understand to proceed

Kindly guide ServerTasks-2144.log.txt (49.2 KB)
me

Hi @kakarlayogitha

The error in your task log states the error you are receiving:

The ‘Database package download step’ is not a ‘Deploy a NuGet package’ step: ‘Redgate - Create Database Release’

This indicates that you are trying to use a step name Redgate - Create Database Release to provide details of where the redgate database package has been deployed/installed, but that step isn’t the right one. In order for that step template to work, it requires an additional deploy a package step to deploy the database package files before the step runs.

I’d recommend taking a look at our documentation on database deploys using redgate here - Deploy to SQL Server using Redgate SQL change automation - Octopus Deploy

This guide walks you through which step templates are good ones to use (the one you are using isn’t worker friendly)

There is also an accompanying blog post - Database deployment automation using state-based Redgate SQL Change Automation - Octopus Deploy.

In answer to your other questions:

do we need to manually install redgate as seperate software and then integrate with octopus

Octopus doesnt come with built in steps for redgate but as you have already seen, there are a number of community provided step templates you can use. Each step template has it’s own requirements for the software it requires and the parameters and package(s) if any you need to provide.

Please suggest what all can be possible through redgate predefined steps in octopus

As above, these steps aren’t written by Octopus directly, but rather community members. I’d recommend browsing the community library and reading the descriptions to get a better idea of whats possible with each step template and then decide if the step matches your own requirements.

I hope that helps!

Best,

From below link ,

I do not find step named Package to upload package added in Octopus built in feed.
kindly suggest

Hi @kakarlayogitha

The step template used in the documentation is the Redgate - Create Database Release (Worker friendly) step, and not the one you initially shared (that was called Redgate - Create Database Release). You can download the step that has the Package parameter and install it from the community library from within your Octopus instance.

Then add that step to your deployment process, and the parameter named Package should be available.

Best,

Hello,

Do we need the build servers like teamcity, jenkins etc

, cant we make database automations only through octopus ? kindly suggets us

Now , I am getting a different error , .Please suggest if i need to give my parameters differently

PFA

WARNING: Did not find the metadata file C:\Octopus\Applications\TEST\table-author0\2_12\BuildArtifact.json. Using

November 25th 2021 14:38:06

Info

default values

November 25th 2021 14:38:06

Error

InvalidData: The file or folder is not a valid build artifact: ‘C:\Octopus\Applications\TEST\table-author0\2_12’

November 25th 2021 14:38:06

Error

At C:\Octopus\Work\20211125090747-2197-117\Script.ps1:363 char:26

November 25th 2021 14:38:06

Error

  • … portedBuildArtifact = Import-DatabaseBuildArtifact -Path $packagePath

November 25th 2021 14:38:06

Error

  • 
    

November 25th 2021 14:38:06

Error

at , C:\Octopus\Work\20211125090747-2197-117\Script.ps1: line 363

November 25th 2021 14:38:06

Error

at , : line 1

November 25th 2021 14:38:06

Error

at , C:\Octopus\Work\20211125090747-2197-117\Octopus.FunctionAppenderContext.ps1: line 189

November 25th 2021 14:38:06

Error

at , C:\Octopus\Work\20211125090747-2197-117\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 3061

November 25th 2021 14:38:06

Error

at , : line 1

November 25th 2021 14:38:06

Error

at , : line 1

November 25th 2021 14:38:06

Error

The file or folder is not a valid build artifact: ‘C:\Octopus\Applications\TEST\table-author0\2_12’

November 25th 2021 14:38:06

Fatal

The remote script failed with exit code 1

November 25th 2021 14:38:06

Fatal

The action Redgate - Create Database Release on a Worker failed

Hi

I have uploaded json file which include stps i have used in my project

Please suggestaws-rds-process.json (18.5 KB)ServerTasks-2197.log.txt (46.0 KB)

Hi @kakarlayogitha

To build a package you typically need a build (or CI) server such as TeamCity, Jenkins yes. Octopus is a deployment tool, and focuses on deployments and operation tasks (runbooks). Octopus does not include the ability to build packages from source control. If you don’t already have a build server, I’d highly recommend researching them and choosing one that matches your needs for building packages. Octopus has a number of plugins that work well with some of the common build servers:

Regarding your error:

Error
… portedBuildArtifact = Import-DatabaseBuildArtifact -Path $packagePath

Unfortunately, the Import-DatabaseBuildArtifact command is not something Octopus has knowledge of, it’s a RedGate PowerShell command part of the SqlChangeAutomation module and is not written by Octopus. Looking at the deployment process JSON won’t help in this case. I’d recommend getting in touch with their support team to help you understand why it’s not working correctly.

My best guess looking at the error is that your package is not in the expected format for the Redgate tool.

I hope that helps!

Best,

My best guess looking at the error is that your package is not in the expected format for the Redgate tool. —Please Can you give some sample examples on how it will be .

To build a package you typically need a build (or CI) server such as TeamCity, Jenkins yes. Octopus is a deployment tool, and focuses on deployments and operation tasks (runbooks). Octopus does not include the ability to build packages from source control. —> But to create a table or run a sql command , we can mention it in notepad and attach the zip file to Package in Octopus built in feed right ? Why cant we use those external feeds which octopus provides ?

Hi @kakarlayogitha

You can see examples of the Redgate steps in our Octopus Cloud samples instance here - login as Guest. There are a number of projects that start with the name Redgate.

Yes, you can execute SQL without using Redgate tools. You have a number of different choices:

  • You can use the standard Deploy a Package step but you would need to include all of the necessary tools, variables and other items needed to execute the SQL - the step is designed to deploy a package, not specifically execute SQL Scripts.
  • You can leverage one of the community library step templates that have been written. Here is a selection of different templates - https://library.octopus.com/listing/SQL%20-%20Execute. However, none of those step templates look like they support a package added to the built-in feed. Instead, they support a folder where scripts reside, or an individual statement added inline to the step itself.

To summarise, Octopus doesn’t have a step built in that will execute SQL scripts against a database You’ll either need to write the logic to execute your SQL, or deploy the package and then use one of the steps listed above from the community library, or write your own step that handles this for you.

I hope that helps!

Best

Hi

Thanks a lot for your immediate updates with lot of patience

Did we integrate any build tools in redgate projects in below link ??

https://samples.octopus.app/app#/Spaces-106

Can you please explain me the flow when ,why and how do we use this

Hi @kakarlayogitha

Unfortunately, looking at the built-in feed it looks like these were generated and uploaded manually.
The link I shared previously shows building a redgate automation compatible package using VSTS/TFS (now Azure DevOps) which you might find helpful:

Outside of that, my advice would be to research how to construct the package using the available Redgate documentation.

Best,