Manual review of CI/CD pipeline outputs

Applies to
ApexSQL DevOps toolkit

Summary
This article shows the elements involved in manually reviewing the results and files generated during the execution of the CI/CD processes.

Description

CI and CD processes are generally separated by a period that allows for manual review as well as testing. As part of this process various artifacts, summaries, documentation and even reports specifically detailing the acceptance level of a particular build, generated by the ApexSQL DevOps toolkit can be reviewed.

Manual review inventory

There are several categories of outputs from a processed CI/CD pipeline of results files and artifacts that can be part of a manual review process:

1. Email notifications – this will provide the immediate result of the build, success or failure, along with some summary information
2. Other – the following artifacts may be useful for additional analysis or re-creating a failed job
   • The pipeline job summary file that contains verbose information about each step, along with the return code for each failed step
   • Any reports generated from individual steps e.g. Schema change report, Data (static) change report
   • Documentation – this is full documentation in various potential output formats e.g. chm, pdf, html of the database changes
   • Any scripts generated as part of the process
   • Any output files for summary, warnings, etc. that aren’t included in CLI output
3. Review outputs – these outputs are created specifically to evaluate the acceptance of particular build and will render a Boolean determination, of success or failure:
   • Summary of unit test execution from ApexSQL Unit Test
   • Best practices enforcement report from ApexSQL Enforce

E-mail notifications

As part of a review, the e-mail notification will contain information of the pipeline’s completion and result:

If any of the pipeline steps fails, the following e-mail notification will be received with information which steps failed:

File output

The rest of the items are all file based output.

All of this output will be written to a single folder created with a date/time stamp under the and configured pipeline name folder, that makes it unique for each run of a particular pipeline:

Each of the date/stamp folder can contain specific output files, depending on which steps are included in the CI/CD pipeline. Here is an example of all files that can be created before package step or if package step is excluded:

If there is a need to review any of these, let’s say build script for a created database in the CI process, navigate to the desired date/time stamped folder and for example, and locate the file named Build_tfs_source_qaDB_dest_BuildScript. Once open, its content can be reviewed:

A full inventory of all output files can be seen here

Pipeline job summary log file

As the pipeline executes, it processes various steps via the command line and each step will write summary information (normal or verbose detail) into the pipeline job summary log file for that particular pipeline, so that all steps can be reviewed from one file. The file name consists of the pipeline name e.g. CI_Pipeline, the file name qualifier “job_summary” and the .log extension (e.g. CI_Pipeline_job_summary.log) and it can be found in the date/time stamped folder under the pipeline named folder.

If it was determined that a particular step failed, the pipeline job summary log file can be opened and the execution process for the failed step can be reviewed. At a minimum, it will show the failure return code and reason:

Here is an example of return codes from ApexSQL Diff:

Check out more about ApexSQL Diff Command Line Interface (CLI) switches

Step specific reports

Certain steps may allow for the creation of reports. Examples include the schema change report in ApexSQL Diff and data change report for ApexSQL Data Diff:

These reports can be very helpful to get detailed information about the results of a particular step.

Documentation

Full documentation can be created in a variety of formats including compiled help, PDF, Word document, and HTML, as its own step in the ApexSQL DevOps toolkit, using ApexSQL Doc.

By default, our example script for CD pipeline, that can be downloaded from this link, generates PDF documentation and here is part of the generated documentation:

Documentation can be created for the entire database that was created as part of the CD process or just the changes, which allows users to focus on just that particular build.

Misc files

As a byproduct of processing, many files will be created that are archived in the execution log folder for the particular run of the pipeline. This would include any SQL scripts that were executed as well as any summary or warning files.

Refer to documentation for the particular product to determine what files may be created, when the product is executed via the CLI, what they are named, etc.

For example, ApexSQL Diff will create the following files:

1. Schema difference HTML report that contains information about differences between a source (e.g. stageDB) and target (e.g. productionDB) database and it’s named:

   SchemaSync_stageDB_productionDB_DiffReport.html

2. Schema comparison summary output file contains the number of different/missing/additional/equal objects, between a source (e.g. stageDB) and target (e.g. productionDB) database and it’s named:

   SchemaSync_stageDB_productionDB_DiffSummary.log

3. Schema synchronization warnings output file contains all warnings about potential issues during the execution of the schema synchronization script and it’s named:

   SchemaSync_stageDB_productionDB_SyncWarnings.log

4. Schema synchronization script is generated based on the comparison of the source and target database. Its job is to synchronize the target (e.g. productionDB) database by using the source (e.g. stageDB) database and it’s named:

   SchemaSync_stageDB_productionDB_SyncScript.sql

In determining the cause of failure, or attempting to replicate the step, these files may be helpful to review.

Review

Some outputs are specifically designed to provide a review of the pipeline and determines whether it is a “Green build” or not.

Unit test report – this report will show the result of all executed unit tests and a final summary of the entire job, including a Pass or Fail result. It is generated via ApexSQL Unit Test in the “Test” step.

Unit test reports can be exported to XML file, SQL script file, or to a database. By default, in our example scripts for CI pipeline process mentioned above, test execution results are exported into an XML file:

Best practices enforcement report – this report will show a full summary of any and all violated rules, along with a violation score and final summary of a Pass or Fail result. It is generated via ApexSQL Enforce in the “Review” step.