ApexSQL DevOps toolkit FAQs about notifications, outputs and summaries

Applies to
ApexSQL DevOps toolkit

Summary
This article answers on frequently asked questions regarding ApexSQL DevOps toolkit including notifications, outputs and summaries.

Description

Q: How will I know when a pipeline has completed?

A: There are several ways that could be used as information system to inform you when a pipeline is completed:

1. PowerShell scripts:

   1. Once all set steps are finished, the following message will be shown in the PowerShell window, along with the pipeline level return code:

      Name: CI_Pipeline

      Started at 12/18/2017 20:19:11
      Completed at 12/18/2017 20:22:58 with result: Success
      Return code is: 0

      Output files path: F:\CICD\CI_Pipeline\
      12-18-2017_20-19-11

   2. The pipeline summary job log file is created into a date/time stamped folder on the previously set location:

      

   3. If the e-mail notification system is set up, the e-mail notification will be received, along with the attached output file

   4. The pipeline will make a sound, a beep, when it is completed. This is set to true, by default

2. In ApexSQL DevOps dashboard, under the Activity page, statuses for all pipelines can be reviewed:

   

3. In ApexSQL DevOps plugins, you can check the current progress and end status:

   

   E-mail notification system can be set up for both ApexSQL DevOps dashboard and ApexSQL DevOps plugins, so that information about finished pipeline is delivered via e-mail, the same as when using PowerShell cmdlets.

Q: How will I know the final status of the pipeline – success or failure?

A: There are several ways to find out the final status of the pipeline:

1. PowerShell console and output file – Once all steps from the pipeline are finished, there could be two messages:

   1. Success:

      

   2. Failure:

      

      In addition to success/failure messages and pipeline level return codes, return codes are shown for each step from the pipeline e.g. each application shows its return code.

2. E-mail notification – if the e-mail system is configured, an e-mail with the final pipeline status will be received by recipient. If everything was finished successfully, e-mail with the following information will be received:

   

   If any issue occurred during the pipeline execution, an e-mail with Failure result will be received along with the information which step failed and its return code, and with attached pipeline summary job log file:

   

   E-mail notification system can be set for all components of ApexSQL DevOps toolkit: PowerShell cmdlets, ApexSQL DevOps dashboard and ApexSQL DevOps plugins.

3. ApexSQL DevOps dashboard – apart from checking out statuses on the Activity page, results and details of all pipelines can be viewed on the Pipelines page:

   

4. ApexSQL DevOps plugins will also provide details about initiated steps wherever if it’s a success or failure:

   

   

Q: Is there a pipeline level return code or a step level return code?

A: There is a pipeline level return code and a step level return code after each step, so if any issue is encountered, a corresponding step application will show the return code, followed by the pipeline level return code.

For example, if a wrong CLI switch was entered for output name e.g. /of instead of /on, the following message is shown:

Starting execution of ApexSQL Doc

Switch ‘of’ is not recognized.

ApexSQL Doc failed.
The process is canceled due to failure return code: 2

Also, at the end of the output summary file, pipeline level return code will be shown:

Completed at 09/05/2017 13:37:48 with result: Failure
Return code is: 1

Failed step:
ApexSQL Doc with error code: 2

To check out full list with explanations for common return codes in applications, check out article about General usage and the common Command Line Interface (CLI) switches for ApexSQL tools

Q: Where can I find a file that shows the top line summary of the Pipeline including success/failure of each step and success/failure of the entire pipeline

A: The pipeline job summary log file, e.g. CI_Pipeline_job_summary.log can be found in the date/time stamped folder on location that was set before running the pipeline. By default, the date/time stamped folder will be created in the CI_CD_Pipeline folder that will be created at the same location where the ApexSQL_CI.ps1 or ApexSQL_CD.ps1 script is located.

Below are the date/time stamped folders:

The pipeline job summary log file name consists of the pipeline name e.g. CI_Pipeline, followed by the file name qualifier “job_summary” and the .log extension (e.g. CI_Pipeline_job_summary.log) and each one is located under different date/time stamped folder:

Q: Is the content in the e-mail notification also written to file?

A: Yes. The e-mail notification informs you when the pipeline started and that is written at the top of the pipeline job summary log file as well:

Q: Is there a specific location on disk where all job summaries and output are written to for each pipeline run?

A: Yes. As mentioned above, after setting up the location for the pipeline, all outputs will be written to a date/time stamped folder after each pipeline run.

Q: Can that folder location, name be customized?

A: Yes. You can customize folder location and name under the Global options at the top of both mentioned scripts, under the switches -PipelineName and -OutputLocation:

#Global options (pipeline name, output folder location and notification settings)
$options = New-ApexSqlOptions -PipelineName "CI_Pipeline" -OutputLocation "C:\CICD" -NotificationSettings $notificationSettings

Q: What is the full inventory of files/output by name, type and description?

A: The full inventory of files can be separated into two groups:

1. CI pipeline outputs:

   1. Build step – creates a SQL build script for creating a testing database from repository
   2. Populate step – creates a SQL script for populating empty tables in a testing database
   3. Test step – creates XML file that contains test execution results
   4. Review step – creates a HTML report with information about testing rules on a testing database

2. CD pipeline outputs:

   1. Schema sync step – four different outputs:

      1. Schema difference HTML report
      2. Schema comparison summary
      3. Schema synchronization warnings
      4. Schema synchronization script

   2. Data sync step – four different outputs:

      1. Data difference HTML report
      2. Data comparison summary
      3. Data synchronization warnings
      4. Data synchronization script

   3. Documentation step – creates PDF documentation of a testing database or its deferential (depending on how the user chooses)

   Package step – creates NuGet file consisted of all generated outputs during the executions of steps in both CI and CD processes.

   Check out the article about full Inventory of the CI/CD pipeline output files

   Also, you can check out the article about Manual review of CI pipeline outputs

Q: What determines the final success or failure status of a pipeline?

A: The final success or failure status for the executed pipeline is determined by the cumulative success of all individual steps. If all steps are executed successfully, without any failures, the job will be successful. Otherwise, if one fails, the job will return a failure status.

Q: What happens if schema or data between a source and destination database is the same, meaning essentially that there were no changes?

A: If both Schema and Data sync steps are included and schema or data is the same, the other one won’t be included in the Package and Deploy step, as the schema/data synchronization script won’t be created. For example, if there are schema changes, but there are no data changes, only schema sync script will be packaged and deployed to a production database.

The pipeline will abort if there are no schema and data differences, along with a return code of 102, indicating that there were no changes.

Q: Can I consolidate schema and data sync scripts into one script for the Deploy step?

A: Yes. You can just add the -Consolidate switch under the Package step and these two scripts will be merged into one script that will be executed in the Deploy step:

#Package step
Invoke-ApexSqlPackageStep -Options $options -nugetVersion "3.1.0" -nugetReleaseNotes "[changes]" -Publish -Consolidate | Out-Null

Q: Various tools produce output via the GUI for Summaries and Warnings. When running unattended, is that information written to file, and if so where?

A: Summaries and warnings are exported automatically to individual files for reviewing. Some examples are:

Check out more about exported files from article about Inventory of the CI/CD pipeline output files

Q: What is the name of the pipeline summary file and what general information does it contain?

A: The output summary file is named PipelineName_job_summary.log and it’s located in different date/time stamped folder each time when the pipeline is run. It contains the following information:

1. Individual step with success/failure message, along with application return code
2. Final notification information for pipeline success/failure
3. High level information about all execution process for each application, along with the number of objects/rows processed for each configured step
4. Verbose output from CLI is set by default. Check out Appendix A for examples
5. Detailed summary of each step

Q: Is CLI output set to verbose by default? Can I do it on a step by step level?

A: Yes. Verbose CLI output is set by default for all steps. All example scripts are open source and you can set each step based on your desires including verbose output.

For example, if you don’t want verbose output for the Test step (as test results are exported to an XML file), open the ApexSQL_CICD.psm1 script, locate the function Invoke-ApexSqlTestStep and under the $toolParameters variable, remove the /v switch:

$toolParameters = "$($Database.AsParameters()) /install_tsqlt" +" /or:""$testReport""$sqlCop$project$additional/f"

Appendix A

The following are examples of verbose outputs from different applications used in CI/CD pipeline: