As ApexSQL DevOps toolkit – Web dashboard is a standalone solution dedicated exclusively toward database-related continuous integration and continuous delivery project. Its base limitation is a lack of ability to execute some non-database related tasks during a working database automation cycle.
To overcome this limitation, the Custom step is available and can be introduced to pipelines which enable the execution of custom written PowerShell scripts within a pipeline designated for some database continuous integration or continuous delivery tasks. This can cover various non-database related use cases, for example, installing a client application on the test environment, executing a SQL script that reconfigures SQL Server default settings, updating software, etc.
Description
The Custom step is the equivalent of PowerShell tasks/steps common in popular continuous integration servers, like TeamCity, Bamboo or Azure DevOps.
The step has only one specific control for its configuration labeled Script path and it is designated for setting a PowerShell script path location and filename. This is where specific PowerShell script can be selected.
The rest of the configuration options are the same as with other steps. The Include output in package will allow selecting the NuGet package data source type to store execution outputs like the PowerShell console message summary. The Additional parameters field allows typing in a set of arguments if used in the created PowerShell scripts as parameters.
Example
The mechanism of the Custom step should be best explained with an example. The following example will show how to install an application on the target environment within a database build pipeline.
The example shows the situation where an application update should be executed on premise. In this instance, it will be the ApexSQL Build, the very same application that is used for database build execution in the following pipeline step. This is a plausible situation as enterprise system administrators have a tendency to push application updates through infrastructure in an automated manner.
The current version of ApexSQL Build:
Will be updated with a newer version:
To install an application via PowerShell, in this case, ApexSQL Build, a simple PowerShell command can be used that looks like this:
& 'C:\Update\ApexSQLBuild.exe' /VERYSILENT /SUPPRESSMSGBOXES
Where standard command prompt additional parameters:
/VERYSILENT – executes the installer as a background process
/SUPPRESSMSGBOXES – prevents any warning or confirmation messages to pop up and sets default action as confirmation
After this simple script is created in any plain text editor, it can be saved as .ps1 file. The .ps1 file should be placed in the designated ApexSQL DevOps toolkit – Web dashboard projects folder. This is the standard folder for any step’s related project file storage and input.
After previous operations, it is now required to simply add the Custom step to a pipeline and using the browser button to check the content of the projects folder and pick the .ps1 file containing installation script.
Along with the Custom step in this example the Build step is also placed in the pipeline and it is expected to be executed using the new ApexSQL Build version.
If PowerShell script is created without issues no error will be expected and the Custom step will display Success status and the Build step will proceed.
With the PowerShell script for the application update configured and executed the direct confirmation of success can be seen within the Build step’s execution summary where the new ApexSQL Build version was indeed used for execution:
Note that the script will be executed using PowerShell ByPass execution policy to temporarily circumvent current PowerShell security settings just for the targeted script execution.
In case, there are errors during the PowerShell execution, the Custom step will show Fail status and in execution summary standard PowerShell error message will be generated and displayed.
To illustrate this behavior used example will now have an incorrect path for the chosen installer. The PowerShell will return appropriate error pointing that there is no executable at the given path which can be seen directly from the Custom step execution summary. Also, the following steps of the pipeline will not be executed.
Conclusion
In conclusion, the Custom step increases the overall usability of the ApexSQL DevOps toolkit – Web dashboard solution by covering additional use cases for complex environments where database lifecycles are managed.
