Database Deployment (Powershell)

Curt's Avatar

Curt

08 Aug, 2017 03:59 PM

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

  1. Support Staff 1 Posted by Daniel Fischer on 09 Aug, 2017 04:09 AM

    Daniel Fischer's Avatar

    Hi Curt,

    Thanks for getting in touch! Have you considered one of the options from the following documentation page?
    https://octopus.com/docs/deploying-applications/sql-server-databases

    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.
    https://octopus.com/docs/how-to/get-the-raw-output-from-a-task

    Looking forward to hearing from you. :)

    Best regards,
    Daniel

  2. 2 Posted by Curt on 09 Aug, 2017 03:05 PM

    Curt's Avatar

    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.

  3. Support Staff 3 Posted by Daniel Fischer on 10 Aug, 2017 05:19 AM

    Daniel Fischer's Avatar

    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

  4. 4 Posted by Curt on 10 Aug, 2017 05:33 AM

    Curt's Avatar

    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\....

  5. Support Staff 5 Posted by Daniel Fischer on 17 Aug, 2017 01:07 AM

    Daniel Fischer's Avatar

    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

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac