Azure DevOps Reversal

The DATASTAR scripts encapsulate data and their related entities as a scriptable component. When these components are deployed they make the necessary changes to the target environment by inserting, updating and deleting records. This idea of encapsulating data as a component also facilitates DATASTAR to be able to automatically generate the scripts necessary to reverse the changes so that the data can be reverted to it's original state. The reversal feature enables the generation of reversal or rollback scripts at the time of deployment so that if necessary the changes can be reverted.

Reversal Feature Prerequisites

To take full advantage of the DATASTAR Reversal feature you should automate the build and deployment of the database scripts. DATASTAR comes with all the necessary components to incorporate DATASTAR into "Dev Ops" so that you can fully automate and audit your deployments. It is recommended that you implement the Audit and History tables which can then be automatically updated to store the deployment logs and hold the generated reversal scripts which enable the ability to reverse or rollback the changes after they have been committed.

Deployment History Table

The definition of the deployment history is below, please note that it is possible to change the name of table, but the columns must be as defined below:

CREATE TABLE [dbo].[ADS_DEPLOYMENT_HISTORY] (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [WORK_ITEM] [int] NOT NULL,
    [VERSION] [int] NOT NULL,
    [ARTIFACT] [varchar](50) NOT NULL,
    [DATE_STAMP] [datetime] NOT NULL,
    [LOG_OUTPUT] [varbinary](max) NULL,
    [REVERSAL_ID] [Int] NULL,
    [STATUS] char(1) NULL,
    CONSTRAINT [ADS_DEPLOYMENT_HISTORY_PK] PRIMARY KEY CLUSTERED ([ID] ASC));

Note: Upgrading

If upgrading from versions then the following script will apply the necessary alterations:

ALTER TABLE [dbo].[ADS_DEPLOYMENT_HISTORY]
ADD [REVERSAL_ID] [Int];

ALTER TABLE [dbo].[ADS_DEPLOYMENT_HISTORY] 
ADD [STATUS] char(1) NULL

Deployment Reversal Table

The deployment reversal table is written to during an automated deployment and holds versions of the scripts that reverse the changes made by that release in that environment:

CREATE TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL] (
    [ID] [int] NOT NULL,
    [SEQUENCE] [int] NOT NULL,
    [CATEGORY] [nvarchar](100) NOT NULL,
    [NAME] [nvarchar](100) NOT NULL,
    [SCRIPT] [varbinary](max) NULL,
    CONSTRAINT [ADS_DEPLOYMENT_REVERSAL_PK] 
    PRIMARY KEY CLUSTERED ([ID] ASC, [SEQUENCE] ASC))

ALTER TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL]
    ADD CONSTRAINT ADS_DEPLOYMENT_REVERSAL_FK1
    FOREIGN KEY ([ID])
    REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
    ON DELETE CASCADE

Deployment Summary Table

The deployment summary table is written to during an automated deployment and holds a summary of the data changes made by that release in that environment:

CREATE TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY] (
    [ID] [int] NOT NULL,
    [TABLE_NAME] [nvarchar](255) NOT NULL,
    [INSERTED] [int] NOT NULL,
    [UPDATED] [int] NOT NULL,
    [DELETED] [int] NOT NULL
CONSTRAINT [ADS_DEPLOYMENT_SUMMARY_PK] 
    PRIMARY KEY CLUSTERED ([ID] ASC, [TABLE_NAME] ASC))

ALTER TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY]
    ADD CONSTRAINT ADS_DEPLOYMENT_SUMMARY_FK1
    FOREIGN KEY ([ID])
    REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
    ON DELETE CASCADE

Metadata Tags

When setting up the static scripts they need to be tagged as being static, this ensures that the script will be included in the reversal scripts and remain unchanged. The static scripts are typically the ones that get triggered to enable and disable constraints. The example below illustrates how to apply the static metadata tag to your scripts:

-- Metadata: {"static":"true"} 
ALTER TABLE BUSINESS_RULE NOCHECK CONSTRAINT ALL 
ALTER TABLE BUSINESS_RULE_GROUP NOCHECK CONSTRAINT ALL

Define the tables in the DataStar Client

You can set this up in the Workspace settings by navigating to File → Workspace and add the Audit, Reversal and Summary table names.

DataStar Client Reversal Feature

The DATASTAR scripts encapsulate data and their related entities as a scriptable component. When these components are deployed they make the necessary changes to the target environment by inserting, updating and deleting records. This idea of encapsulating data as a component also facilitates DATASTAR to be able to automatically generate the scripts necessary to reverse the changes so that the data can be reverted to it's original state. The reversal feature enables the generation of reversal or rollback scripts at the time of deployment so that if necessary the changes can be reverted. This guide details how to use the reversal feature in the desktop application as well as the automated command line toolkit.

Deployment Run Scripts

When you open or create a deployment file the "Run Scripts" option becomes available so that the deployment file can be deployed from the DataStar Client. When clicking the button:

a) DataStar will display the database dialog box so that you can select the deployment target, you can also select the deployment mode either "Commit" or "Rollback".

b) Clicking the "Connect" button will connect you to the target database ready for deployment, DataStar will then ask if you'd like to generate the reversal scripts for the deployment:

c) Selecting "Yes" will then prompt you to provide the directory location where the reversal scripts should be stored. The DataStar client will then perform the following actions:

  1. DataStar will attempt to create the reversal scripts from the target environment. Scripts that are identified as "static" will be copied unaltered, scripts that are identified as being custom will have the reversal applied to the script and DataStar components will be generated from the target environment to crate a snapshot immediately before deployment. These scripts will be written to the selected output directory along with a manifest file.
  2. The deployment will then be executed and the logs displayed.

Deployment History

When the Audit and Reversal tables have been defined in the Workspace settings as described in the prerequisites then the "Deployment History" option will be enabled. Clicking this button will open a view of the deployment logs using the current workspace database connection. The deployments are listed in descending date order in a paginated results view. Clicking on a deployment history item will show the logs for that deployment in the bottom pane, likewise the reversal scripts will be shown in the right hand pane.

The reversals scrips can be exported to your local file system using the "Export" button, alternatively the changes can be applied to an environment using the "Apply Reversal" button which will prompt for you to choose the target environment.

Manifest Deployment

The manifest deployment enables you to deploy a set of scripts to a target environment using a simple manifest file. For example if you have exported or generated some reversal scripts the output directory will contain a manifest of the files and the order in which they should be executed. The "Manifest Deployment" allows you to select these files and execute them against a target database.

Custom Scripts

Custom scripts should be avoided were the data being modified is covered by a DataStar component, otherwise you loose the benefits of versioning such as audit history and managing merge conflicts in the event that more then one person is change the same set of data. To get the most from DataStar and to reduce the risk of overwriting data it is important to follow this principal. Note all custom scripts should be re-runnable, such that if the script has already been run then running it again has no adverse effect.

Where custom scripts are absolutely necessary it is possible to get DataStar to create the reversal scripts provided that they include the metadata tags to facilitate the automated reversal. To create custom scripts that DataStar can reverse following the instructions below:

Step-by-step guide

a) Mark the script as being custom by inserting the metadata tag:

b) Use --SWITCH comments to control which statements are executed, if the --SWITCH is at the start of the line then it will be swapped to be the end of the line in the reversal script and vice-versa. This allows you to control what will be executed in each mode:

Original Script
-- Metadata: {"custom":"true"}
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2');
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3', 'value4'); --SWITCH
Result of Reversal
-- Metadata: {"custom":"true"}
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2'); --SWITCH
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3','value4');

An alternative approach is also shown below:

-- Metadata: {"custom":"true"}
DECLARE @ReversalMode BIT = 0 
--SWITCH SET @ReversalMode = 1;
IF @ReversalMode = 0
BEGIN
    -- do stuff here
    PRINT 'doing stuff here';
END
ELSE
BEGIN
    -- do reversal stuff here
    PRINT 'reversing stuff here';
END