Update RedGate worker friendly templates with new functionality

[markgould]

Background

• Adds Trust Server Certificate to Create Database Release (Worker Friendly) and Deploy Database Release (Worker Friendly) to be consistent with Deploy From Package (Worker Friendly)
• Adds ability to pass a custom connection string primarily to allow for additional authentication options supported in Microsoft.Data.SqlClient

Results

Existing behavior still works

After

Pre-requisites

• [X] Id should be a GUID that is not 00000000-0000-0000-0000-000000000000
  • NOTE If you are modifying an existing step template, please make sure that you do not modify the Id property (updating the Id will break the Library sync functionality in Octopus).
• [X] Version should be incremented, otherwise the integration with Octopus won't update the step template correctly
• [X] Parameter names should not start with $
• [X] Step template parameter names (the ones declared in the JSON, not the script body) should be prefixed with a namespace so that they are less likely to clash with other user-defined variables in Octopus (see this issue). For example, use an abbreviated name of the step template or the category of the step template).
• [X] LastModifiedBy field must be present, and (optionally) updated with the correct author
• [X] The best practices documented here have been applied
• [ ] If a new Category has been created:
  • [ ] An image with the name {categoryname}.png must be present under the step-templates/logos folder
  • [ ] The switch in the humanize function in gulpfile.babel.js must have a case statement corresponding to it

[markgould]

Not sure why the formatting is so off - json files edited in VSCode. Any suggestions?

[markgould]

@twerthi @BobJWalker since you've both contributed here before open to your feedback.

The new connection string parameter bypasses the New-DatabaseConnection command and instead instantiates RedGate.Versioning.Automation.Compare.SchemaSources.DatabaseConnection to pass a custom connection string. New-DatabaseConnection supports a very limited subset of Authentication values, this seemed the best way to handle this.

[hnrkndrssn]

Hey @markgould it looks like it might be a spaces vs tabs issue causing the amount of changes. Hiding white space in the file view makes only the actual changes to show up.

[twerthi]

That sounds reasonable to me. As you're probably aware, Redgate is opting to invest in the Flyway database migrations product vs. the SQL Automation product so these templates may receive reduced usage.

[markgould]

That sounds reasonable to me. As you're probably aware, Redgate is opting to invest in the Flyway database migrations product vs. the SQL Automation product so these templates may receive reduced usage.

Yeah - we have it on our roadmap to transition at some point but have lots of projects using it today so it will take some time for us to migrate. Hopefully this can help others that may still be using the Change Automation tools.

[markgould]

Hey @markgould it looks like it might be a spaces vs tabs issue causing the amount of changes. Hiding white space in the file view makes only the actual changes to show up.

Could be, looks like some of the formatting may have been off previously too. Any changes you'd like me to make here?

[twerthi]

@markgould There is an error in the template Redgate - Deploy from Package (Worker Friendly) when not using the Connection String (Optional) parameter. The template is using the PowerShell variable $DLMAutomationDatabaseServer where I believe it is meaning to use the $DLMAutomationTargetDatabaseServer parameter?

I didn't check the others, would you mind checking the other two for a similar situation?

[twerthi]

Looks to be a similar issue with the database name, the code is using $DLMAutomationDatabaseName whereas the Parameter is $DLMAutomationTargetDatabaseName. After updating that one in my testing, I have encountered other issues. Did you update some Parameter names in the steps?

[markgould]

I see what's going on here - there are discrepancies with the parameter names in the deploy-from-package vs the other two, so when I updated that one there was an issue with it using the wrong variable names. Correcting now.

[markgould]

Should be good now. I also noticed an issue in how I was checking the Trust Server Certificate value across the 3 scripts, changed to use Convert.ToBoolean to handle empty values. Thanks for the feedback!