Database Deployment (Powershell)

Currently we manually maintain a database deployment script. This is a sqlcmd script with references to many other sql files that are run in the order inside the script. I know this is not ideal but this is not something we want to change at the moment. Although we are willing to tweak it a little if necessary.

My first attempt at releasing a project involves the following

  • 1 sqlcmd file (.sql) that is our current deployment process.
  • 1 powershell script that sets a path variable and then calls the sqlcmd file

Running the powershell script manually, does correctly call the sqlcmd file and deploy the scripts as expected

I have packaged the above 2 files using Octo.exe pack.

The error I get while trying to deploy is that the powershell script call to the sql file, using Invoke-Sqlcmd, cannot find the sql file. The file is referenced with a full path name to the file.

Notes

  • I am doing everything on my desktop
  • This software is confusing for database deployments

Hi Curt,

Thanks for getting in touch! Have you considered one of the options from the following documentation page?

Specifically DbUp, there is a small video that runs through the basics.

Past that, to troubleshoot your issue, I will need to get a full deployment log where you are having your issue.

Looking forward to hearing from you. :slight_smile:

Best regards,
Daniel

Thanks for your response. I’m afraid moving our process to DbUp is not an option at this time. I have looked into it, maybe sometime in the future.

I have attached the deployment log, hopefully this helps troubleshoot the current issue I’m having.

ServerTasks-7.log.txt (10 KB)

Hi Curt,

Thanks for the logs! There are a couple of things that could be happening here. The error looks like your script can not find the following path:

U:\TFS Local2\IntegratedProduct\DatabaseRelease\REL_2017_09\RDBMS\IntegratedProduct\RS2.sql'

Does the server you are deploying to have the drive U mapped? Octopus packages and run everything on the target server, using the account that is used to configure the Tentacle. You may need to map U or specify a hostname/IP

If that is not the issue, would you be able to split the script from the package and add it individually to Octopus as a script step, then testing the deployment?

Looking forward to hearing from you.

Best regards,
Daniel

Thanks Daniel,
Everything is running on my desktop, the server and the tentacle are both just on my desktop pc. I had first tried to deploy as a script step before trying to package it up, I had the same error both ways though, can’t find the file "U:\TFS Local2…

Hi Curt,

Thanks for getting back! I’m sorry for the delay here. With the information provided here, I’m still not able to tell what could be going on here. It definitely looks like Powershell is having trouble finding that U:\ drive.

When you run this script locally, assuming the script is exactly the same, are there any error messages or warnings that are displayed in the script?

The next step in troubleshooting this would be to get a copy of this script. However, with the logs provided previously, the script may not give me much more relevant information. I believe it comes down to the Octopus Tentacle not being able to resolve the address:

U:\TFS Local2\IntegratedProduct\DatabaseRelease\REL_2017_09\RDBMS\IntegratedProduct\RS2.sql

Could you try the script with a more direct URL, directly put the IP or HostName and see if Octopus can resolve it.

Also, something I will note is that desktop environments are not officially supported by Octopus. It is entirely possible that there is something environmental at play due to the desktop installation.

Let me know how you go.

Best regards,
Daniel