Deploying a database package to SQL Server through Octopus & TeamCity

I am implementing CI/CD for SQL Server Database through Redgate software and TeamCity. I manage to Build and push the NuGet Database Package to Octopus. I can see the NuGet package in Library section of Octopus. But I am facing issues in deploying that package to SQL Server. I cant find the Built-in Step Template "Deploy a NuGet package" in Octopus process section. I have also tried "Deploy a package" step template but it didnt worked.I am following this guide.

https://documentation.red-gate.com/sr1/worked-examples/deploying-a-database-package-using-octopus-deploy-step-templates

Any Help will be highly Appreciated.

Good question, to use Redgate's tooling with Octopus Deploy you will need to install the step templates they provided. I recommend create a database release and deploy a database release. When you are browsing the step template you might notice the step template to deploy directly from a package. The state-based functionality for SQL Change Automation works by comparing the state of the database stored in the NuGet package with the destination database. Each time it runs it creates a new set of delta scripts to apply. Because of that, the recommended process is:

1. Download the database package onto the jump box.
2. Create the delta script by comparing the package on the jump box with the database on SQL Server.
3. Review the delta script (can be skipped in dev and test).
4. Run the script on SQL Server using the tentacle on the jump box.

Let's go ahead and walk through each one. The download a package step is very straightforward, no custom settings aside from picking the package name.

The Redgate - Create Database Release step is a little more interesting. This is the step which generates the actual delta script that will be run on the database. What trips up most people is the Export Path. The export path is where the delta script will be exported to. This needs to be a directory outside of the Octopus Deploy tentacle folder. This is because the "Redgate - Deploy from Database Release" step needs access to that path and the Tentacle folder will be different for each step.

What I like to do is use a project variable.

The full value of the variable is:

C:\RedGate\#{Octopus.Project.Name}\#{Octopus.Release.Number}\Database\Export

The next step is approving the database release. I recommend creating a custom team to be responsible for this. My preference is to skip this step in Dev and QA.

The create database release step makes use of the artifact functionality built into Octopus Deploy. This allows the approver to download the files and review them.

The final step is deploying the database release. This step takes the delta script in the export data path and runs it on the target server. This is why I recommend putting the export path in a variable.

Some other general items to help get going. First, don't install tentacles directly onto SQL Server instances. In production, the typical SQL Server set up is a cluster or they have multiple nodes with always-on high availability. Access to SQL Server is handled via a virtual IP.

If you were to install tentacles on both nodes, Octopus Deploy would attempt to run the change script on both nodes at the same time (by default). That will cause a lot of drama. I recommend using a jump box because you will need something to sit between Octopus Deploy and SQL Server. When you get comfortable with that I'd recommend using workers (but that is a bit of scope creep, so I won't cover that).

If you would like to know more on how to wire this up, check out the blog post I wrote (and copied from for this answer) here.

I also have written an entire series on database deployments with Octopus Deploy, which you can find here.

Finally, our documentation covers jump boxes and permissions you will need for the user doing the database deployments.

Hope that helps!