Command Line

Database deployments can be particularly tricky for a number of reasons:

  • Incremental: Usually, destroying a database and recreating it from scratch is not an option – at least in production as the business critical data inside would be lost.
  • Ordered: The changes to a database are ordered, it is very important to run scripts in the correct order to maintain data integrity.
  • Non-repeatable: Adding a record twice results in two records which will generally result in an error.
  • Non-reversible: Once a change that destroys data has occurred, the only hope of rolling it back is to restore from backup.

These characteristics are quite different from a typical software deployment which tend to moves a bundle of artefacts to some servers and installs them. DATASTAR addresses these issues by allowing you to create components that represent your data, once you have changed a component it is checked into your source control repository, subsequent changes to the same component also get applied as different versions.

DATASTAR will determine the changes that have been made to create a deployment file which contains the list of components that have been changed and their version number. All scripts are re-runnable so that they can be safely re-applied and using a the version history and the back out functionality allows to to reverse changes if necessary and generate a rollback script.The packaging process allows you to trigger the inclusion of other scripts and ensure that components are deployed in the correct order.

Using DataStar Command Line

DataStar.CmdLine is a utility that can be used within your automated deployment steps to generate and save reversal scripts and deploy your built deployment packages. In order to use the DataStar.CmdLine outside of the Azure Dev Ops Tasks you will typically have 2 or 3 artifacts that are required for the release:

  • The DataStar Command Line binaries and Powershell Scripts.
  • The DataStar component templates (only required if using the reversal feature)
  • The built deployment package.

DataStar Command Line Package

This is shipped with the main DataStar application, alternatively it is available via the Azure Dev Ops Marketplace packaged as a VSIX file. This can be installed into Azure Dev Ops or Team Foundation to provide access to a packaged task or alternatively unzipped to extract the DataStar.CmdLine binaries.

DataStar Component Templates

If you are using the DataStar Reversal Feature then you will need to package up the templates used to generate the components so that they can be used to generate the reversal scripts.

Command Line Deployment Mode

When a deployment file is created the information in the deployment is used to create a build. The build includes the versions of the files specified in the deployment file extracted from your version control system and a manifest file which contains a list of the relative file paths to be executed in the order listed. Typically the build is automated via you build system (for example Team Foundation Server).

Once a build has been created it can be deployed using the DataStar.CmdLine utility. This support many options which might be a simple deployment, writing an audit history to the target database and / or generating reversal scripts. If reversal is enabled then the deployment utility will generate an extract of the components immediately before executing the scripts which can then be used for a subsequent reversal of the changes.

The scripts can be deployed using the DataStar.CmdLine.exe utility, the options are show below (please note these options are case sensitive):

DataStar.CmdLine.exe --deployment [Options]
Switch Switch String Type Description
-D --deployment Flag Flag to indicate that you wish to run in deployment mode.
-S --DataSource String The SQL SERVER server and instance.
-d --InitialCatalog String The database name.
-U --Username String The username (if using password authentication)
-P --Password String The password (if using password authentication)
-m --ManifestDir String The deployment manifest directory.
-A --Artifacts String[] List of artifacts (manifests) to deploy, this defaults to wildcard.
-W --WorkItem Integer The work item (used for auditing purposes)
-V --Version Integer The version number (used for auditing purposes)
-x --Reversal Boolean Enable generation of reversal scripts (defaults to false)
-T --TemplateDir String The directory to the DataStar templates (the is required if generating compensation scripts)
-C --Commit Boolean Commit the changes to the database (defaults to true)
-O --OutputDir String The out put directory path (where compensation scripts are generated)
-k --key String The DataStar license key.
-b --Debug Flag Enable debug level logging.
-t --Trace Flag Enable trace level logging

Flag = No value expected after the switch

String = Argument after the switch is expected to be a string

Integer = Argument after the switch is expected to be an integer

Boolean = Argument after the switch is expected to be a Boolean ("true" or "false")

Executing With PowerShell

The DataStar command line tools are supplied with some utility PowerShell scripts which can either be used directly or customized if required. An example of executing the reversal using the DataStar.CmdLine.ps1 is shown below:

DataStar.CmdLine.ps1
    -BasePath "$(Basepath)"
    -ModeSwitch "$(ModeSwitch)"
    -ManifestDir "$(ManifestDir)"
    -DataSource "$(DataSource)"
    -Database "$(Database)"
    -Username "$(DatabaseUser)"
    -Password "$(DatabasePassword)"
    -OutputDir "$(OutputDirectory)"
    -TemplateDir "$(TemplateDirectory)"
    -WorkItem "$(WorkItem)"
    -Version "$(Version)"
    -LicenseKey "$(LicenseKey)"
    -WithReversal

Command Line Reversal Mode

When deploying your changes to an environment, DATASTAR can generate a the reversal scripts from the database you are deploying to and store these in audit tables in the database or to an output directory. The advantage of the reversal scrips is they provide the ability to revert the changes without having to prepare and maintain rollback scripts which may be out of date by the time you deploy.

If you have generated the reversal scripts in the database you can use the DataStar.CmdLine.exe utility to extract these scripts and execute, the options are show below (please note these options are case sensitive):

DataStar.CmdLine.exe --reversal [Options]
Switch Switch String Type Description
-R --reverse Flag Flag to indicate that you wish to run in reverse mode.
-S --DataSource String The SQL SERVER server and instance.
-d --InitialCatalog String The database name.
-U --Username String The username (if using password authentication)
-P --Password String The password (if using password authentication)
-m --ManifestDir String The deployment manifest directory.
-W --WorkItem Integer The work item (used for auditing purposes)
-V --Version Integer The version number (used for auditing purposes)
-x --Reversal Boolean Enable generation of reversal scripts (defaults to false)
-T --TemplateDir String The directory to the DataStar templates (the is required if generating compensation scripts)
-C --Commit Boolean Commit the changes to the database (defaults to true)
-O --OutputDir String The out put directory path (where reversal scripts are generated)
-k --key String The DataStar license key.
-b --Debug Flag Enable debug level logging.
-t --Trace Flag Enable trace level logging

Flag = No value expected after the switch

String = Argument after the switch is expected to be a string

Integer = Argument after the switch is expected to be an integer

Boolean = Argument after the switch is expected to be a Boolean ("true" or "false")

Executing With PowerShell

The DataStar command line tools are supplied with some utility PowerShell scripts which can either be used directly or customized if required. An example of executing the reversal using the DataStar.CmdLine.ps1 is shown below:

DataStar.CmdLine.ps1
    -BasePath "$(BasePath)"
    -ModeSwitch "$(ModeSwitch)"
    -ManifestDir "$(Manifests)"
    -DataSource "$(DataSource)"
    -Database "$(Database)"
    -Username "$(DatabaseUser)"
    -Password "$(DatabasePassword)"
    -OutputDir "$(OutputDirectory)"
    -TemplateDir "$(TemplateDirectory)"
    -WorkItem "$(WorkItem)"
    -Version "$(Version)"
    -LicenseKey "$(LicenseKey)"
    -WithReversal

Command Line Deployment History Mode

If you have enabled the database audit table and the optional reversal table, then the deployment logs and reversal scripts will be logged to the database. This data is stored as base 64 binary zipped files. This data can be extracted in two ways as described below.

Database Extract

If you are using SQL SERVER 2016 or above then you can select the data directly from the database as shown below:

SELECT CAST(DECOMPRESS(LOG_OUTPUT) AS VARCHAR(MAX)) 
FROM [dbo].[ADS_DEPLOYMENT_HISTORY]

Command Line Extract

The data cab be extracted using the DataStar.CmdLine.exe utility, the options are show below (please note these options are case sensitive):

DataStar.CmdLine.exe --history --DataSource "SERVER01\SQLSERVER" --InitialCatalog "DEV" --WorkItem 574632 --Reversal true --OutputDir "C:\Output"
Switch Switch String Type Description Required
-H --history Flag Flag to indicate that you wish to extract the audit history. Yes
-S --DataSource String The SQL SERVER server and instance. Yes
-d --InitialCatalog String The database name. No
-U --Username String The username (if using password authentication) No
-P --Password String The password (if using password authentication) No
-W --WorkItem Integer The work item (not required if History Id specified), this automatically select most recent entry for the Work Item. No
-x --Reversal Boolean Extract the reversal scripts (defaults to false) No
-l --LogOutput Boolean Extract the log output (defaults to true) No
-O --OutputDir String The out put directory path. Yes
-r --HistoryId Integer The audit history id of the record to extract (not required if the WorkItem is specified) No

Flag = No value expected after the switch

String = Argument after the switch is expected to be a string

Integer = Argument after the switch is expected to be an integer

Boolean = Argument after the switch is expected to be a Boolean("true" or "false")