Deploying and running sql files

We are trying to run static scripts into SQL Server databases. We zip them into a package in TeamCity and push across to Octopus. We have tried a couple of approaches - deploy package and transfer package. Unfortunately we need to deploy onto a jump box which has a ‘database worker’ running.
Those 2 package types don’t let us select which worker to use.
How do other people run static scripts into databases using Octopus?
Thanks

Hi @richardboyce

Thanks for reaching out about running scripts on SQL server databases. If you haven’t read it already, we have some great documentation on database deployments here - https://octopus.com/docs/deployment-examples/database-deployments

This should help point you in the right direction, but please feel free to let me know if you have any further questions.

Regards,
Stuart

Thanks Stuart. I had seen that page, but I re-checked anyway and couldn’t see any reference to deploying static data/scripts.
I imagine this is a fairly common approach, so was surprised I couldn’t find any mention of it. Most of the links I have found are Redgate-based, which we don’t have.
All we want to do is run the scripts in a specific order from a powershell script, but it seems we don’t get the option of running on a worker thread if we don’t use the specific deploy task types
Thanks again
Richard

Hi @richardboyce

Thank you for your patience with me on this. Running PowerShell scripts does seem like the way forward here for what you’d like to achieve.

One change I’d suggest to your workflow would be instead of running this on a worker, try assigning that specific worker as a deployment target and then run the step on that deployment target.

Please try that and let me know how you get on.

Regards,

Stuart

Thanks Stuart, that’s sort-of what I’ve wound-up doing. However…

  1. I added a new tentacle to the same server as the worker (I don’t know how to “assign that specific worker as a deployment target”. If you can tell me, I would rather do that
  2. We’re trying to get the path a file is unzipped to, but nothing seems to work - all the variables shown in examples are empty. To clarify, we deploy a zip file, which gets unpacked to a folder under \Applications and we want to run a script in there against other files in there. How do we get the path to \Applications<the rest of the path>?

Thanks
Richard

Hi @richardboyce

Sorry, I should’ve been clearer in my previous reply. Instead of “assign that specific worker as a deployment target”, I should’ve said, “create a deployment target on the SQL server.”.

For your second question, you can add a custom installation directory that will allow you to control the path.

Please let me know how you get on.

Regards,
Stuart

Thanks Stuart. As usual, a follow-up question;-)

  1. The documentation says not to install tentacles on SQL servers, so we have created a jump box. Is that still the recommended approach?

Thanks
Richard

Hi @richardboyce

You are correct! I have confirmed with a team-mate, and using a jump box is a recommended approach.

Thanks
Stuart

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.