Inventory of the CI/CD pipeline output files

Applies to
ApexSQL DevOps toolkit

Summary
This article explains inventory of the CI/CD pipeline output files, their names, types, and descriptions.

Description

Since Database Lifecycle Management (DLM) processes are divided in two parts, CI and CD, we have similarly created example starter scripts, one for CI and one for CD, that can be downloaded from this link

Both, CI and CD processes create an output file, e.g. PipelineName_job_summary.log, that contains verbose information about all application executions that are involved in pipeline.

Quick tip: If any of the steps provided in example scripts is excluded from the CI/CD pipeline, the corresponding output files won’t be generated

CI pipeline outputs

The current example script for the CI pipeline process contains seven steps and five of them are creating the following output files:

1. Build step step (ApexSQL Build) – creates a SQL script for building of a testing database, including any static data, from the source control (Team Foundation Server in this case) repository. The file name template consists of the step name e.g. “Build”, the connection name of the source control system used and defined with the switch -ConnectionName switch under the $dsSC variable, e.g. tfs_source, followed by the connection name of the testing database used and defined with the switch -ConnectionName switch under the $dsQA variable e.g. qaDB_dest, and the file name qualifier ”BuildScript”. The file extension is .sql, as this is a SQL file:

   Build_tfs_source_qaDB_dest_BuildScript.sql

2. Populate step (ApexSQL Generate) creates a SQL script for populating of a testing database with test data in empty tables. The file name template consists of the step name e.g. “Populate”, followed by the connection name of the testing database used and defined with the switch -ConnectionName switch under the $dsQA variable e.g. qaDB_dest, and the file name qualifier ”PopulateScript”. The file extension is .sql, as this is a SQL file:

   Populate_qaDB_dest_PopulateScript.sql

3. Test step (ApexSQL Unit Test) – runs installed unit tests on a testing database and exports test execution results into an XML file. The file name template consists of the step name e.g. “Test”, the connection name of the testing database used and defined with the switch
   -ConnectionName switch under the $dsQA variable e.g. qaDB_dest, followed by the file name qualifier “TestResults”. The file extension is .xml, as this is an XML file:

   Test_qaDB_dest_TestResults.xml

4. Review step (ApexSQL Enforce) – runs rule testing on a testing database and exports results into a HTML file. The file name template consists of the step name e.g. “Review”, the connection name of the testing database used and defined with the switch
   -ConnectionName switch under the $dsQA variable e.g. qaDB_dest, followed by the file name qualifier “ReviewReport”. The file extension is .html, as this is a HTML file:

   Review_qaDB_dest_ReviewReport.html

One more output that will be created in the CI pipeline, if the Build step is included, is the script folder of a testing database. In this case, ApexSQL Script will be used to run “under the hood” to:

• Script schema of a testing database created in the Build step
• Exclude generated test data in the Populate step
• Include triggers from the Audit step
• Exclude unit tests from the Test step

The created script folder will contain scripts of all objects from a testing database, along with the static data that was committed to source control repository. The folder name template consists of the folder name qualifier e.g. DbScriptFolder.

CD pipeline outputs

The current example script for the CD pipeline process contains six steps and three of them are creating the following outputs:

1. Schema sync step (ApexSQL Diff) – generates four different outputs. The file name template for all four outputs consists of the step name e.g. “SchemaSync”, a source name defined under the -Source switch e.g. stageDB, followed by a target name defined under the -Target switch e.g. productionDB, and the file name qualifier “Diff/Sync” along with “Report/Summary/Warnings/Script”:

   1. Schema difference HTML report that contains information about differences between a source (e.g. stageDB) and target (e.g. productionDB). The file extension is .html, as this is an HTML file:

      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). The file extension is .log as this is a LOG file:

      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. The file extension is .log, as this is a LOG file:

      SchemaSync_stageDB_productionDB_SyncWarnings.log

   4. Schema synchronization script is generated based on the comparison of a source (e.g. stageDB) and target (e.g. productionDB). Its job is to synchronize objects in the target (e.g. productionDB) database by using the source (e.g. stageDB) database. The file extension is .sql, as this is a SQL file:

      SchemaSync_stageDB_productionDB_SyncScript.sql

2. Data sync step (ApexSQL Data Diff) – generates four different outputs. The file name template for all four outputs consists of the step name e.g. “DataSync”, a source name defined under the -Source switch e.g. stageDB, followed by a target name defined under the -Target switch e.g. productionDB, and the file name qualifier “Diff/Sync” along with “Report/Summary/Warnings/Script”:

   1. Data difference HTML report that contains information about differences in tables/views data, between a source (e.g. stageDB) and target (e.g. productionDB). The file extension is .html, as this is an HTML file:

      DataSync_stageDB_productionDB_DiffReport.html

   2. Data comparison summary output file contains the number of different/missing/additional/equal rows, between a source (e.g. stageDB) and target (e.g. productionDB). The file extension is .log as this is a LOG file:

      DataSync_stageDB_productionDB_DiffSummary.log

   3. Data synchronization warnings output file contains all warnings about potential issues during the execution of the data synchronization script. The file extension is .log as this is a LOG file:

      DataSync_stageDB_productionDB_SyncWarnings.log

   4. Data synchronization script is generated based on the comparison of a source (e.g. stageDB) and target (e.g. productionDB). Its job is to synchronize rows in the target (e.g. productionDB) by using the source (e.g. stageDB). The file extension is .sql, as this is a SQL file:

      DataSync_stageDB_productionDB_SyncScript.sql

3. Document step (ApexSQL Doc) – creates full PDF documentation of a testing database. The file name template consists of the step name “Document”, a testing database name defined under the -Database switch e.g. qaDB_dest, followed by file name qualifier “Documentation”. The file extension is .pdf, as this is a PDF file:

   Document_qaDB_dest_Documentation.pdf

4. Package step – this step is available in both CI and CD pipelines and it creates a NuGet file that contains all generated output files from included steps in the CI/CD processes. The file name template consists of the package name that is defined by the user under the variable $global:nugetId e.g. Package at the beginning of the example script for CI/CD process, followed by the version number that is defined in the Package step, under the -nugetVersion switch e.g. 3.0.7. The file extension is .nupkg, as this is a NuGet file:

   Package_3.0.7.nupkg

FAQs

Q: Can I exclude any of the output files from being generated?

A: Yes. All output files for specific step can be excluded by adding the switches below for corresponding steps:

• NoScript switch for steps: Build, Populate, Schema sync, and Data sync
• NoReport switch for steps: Test, Review, Schema sync, and Data sync
• NoSummary switch for steps: Schema sync and Data sync
• NoWarnings switch for steps: Schema sync and Data sync

Q: Can I customize file name templates of the output files?

A: Yes. As you can remove unneeded output files and you can also change their names by locating the desired ones in the same ApexSQL_DevOps.psm1 script for a specific step.

For example, if you want to edit output file name templates for the Data sync step, you’ll navigate to the function Invoke-ApexSqlSchemaSyncStep and under this function you’ll find the variables below and you can edit their names in any way you want:

$schemaSyncScript =
“$($Options.OutputLocation)\SchemaSync_$($Target.ConnectionName)_SyncScript.sql”