Hello, and welcome to this ApexSQL CI/CD toolkit web dashboard demo introduction. The ApexSQL CI/CD toolkit web dashboard is a customizable build agent with a web-based user interface used for creating and executing continuous integration and delivery pipelines. The ApexSQL CI/CD toolkit web dashboard enables users to create flexible CI and CD pipelines with highly customizable pipeline steps.
The dashboard utilizes the ApexSQL CI/CD toolkit PowerShell code via a web-based user interface, and provides an easy method for database developers to build, review, document, and deploy changes for SQL server projects with full customization. For the creation of customized CI and CD workflows using the web dashboard, there is a selection of steps displayed in the pipeline configuration view. Steps are represented as interactive icons within this view, and grouped based on their common usage.
The first group of steps are used for creation of CI pipelines. The build step is the first in line and used to build a new database for testing with committed changes, including both schemas and static data from a source control repository. To configure the build step, use the Source dropdown menu, and choose a data source that has previously been created as source controlled data source configured to the desired repository. In the database selection menu, pick a destination database, where the test database will be built from source control.
Alternatively, if the data source was not previously created, use the Add option to switch to New data source view. Then configure the data source, and with its creation, it will immediately be available to use within the step configuration. If the repository contains scripted static data that needs to be included in the test database, use the option, Include static data.
The expected output with the execution of this step will be a SQL build script. It can optionally be included in a NuGet package. To package the script in this way, check the include output in the package option, and pick the previously created NuGet type data source from the dropdown selection menu. The output build script can be reviewed and reused later from this package.
Include a project file in Project path field to import a set of options from a previously created project file created with the ApexSQL build application. Set additional parameters if needed to override existing automation options or append with additional options. For example, some of the default options for the build step are output destination as a database, with full recovery mode and without author label. Adding the set of switches, db recovery, output, type, and author, it is possible to change the output to be an executable installer that will be executed against a SQL server later for the build with simple recovery mode and author name label.
Adding the populate step to your pipeline will fill empty tables in the target database with synthetic data for testing purposes. In this step, a target database needs to be selected and optionally, a NuGet package for storing output in the form of the script inserts test data can be specified. Also, an ApexSQL generate application project file can be included and additional parameters with option switches can be set. The audit step will integrate trigger-based audit trails for monitoring data changes to sensitive tables.
Although data change auditing is the primary requirement, schema changes can be audited as well. To configure the audit step, choose a target database and optionally, a NuGet package to store the output script that implements the triggers. An ApexSQL trigger application project file can be included along with any additional automation parameters. The document step will be used to document the newly built database, or if you prefer, just changes to the database made in the most recent build.
In the configuration view for this step, the target database needs to be added in optionally a package for storing the output documentation. For additional customization, ApexSQL dock application project file, and additional command line parameters can be added. When the document changes only option is checked, only differences between the source and target databases will be documented.
The test step will run unit tests on the target database. The step has a specific option to install a SQL cop static code analysis process that will check the database against a set of best practices. In the configuration view, a target database is needed. There's also an option to specify a NuGet package to store test report output.
A field is present for the ApexSQL unit test application class, which will include set tests created with the application. And just like in other steps, a field for additional command line options exists to provide any command line override instructions. Add the review step to process the test targeted database against a set of SQL coding best practices, enforce standards, and fail the pipeline if the standards aren't met. The step configuration view includes the standard selection option for a target database and a NuGet package for review report output files.
In order for this step to work, a rule-based file has to be included, so the path to a rule based created with ApexSQL in force needs to be defined. The package step will create a script folder of the tested database using the ApexSQL script application without any synthetic data. The script folder will be used in the CD process for comparison with the production database and its contents packed into a NuGet package with the output results of the other executed steps, which will then be ready for the review and delivery process.
The second group of steps will be commonly used with a continuous delivery pipeline configuration. The schema sync step is the initial step for deployment of changes to a production environment. In this step, the tested database that has committed changes will be compared with a target or production database. And the result will be a schema synchronization script for updating changes.
An ApexSQL SQL diff application project file can be included in this step, along with optional additional parameters. The optional data sync step compares static data of the source database to the destination database, and creates a data synchronization script which will later update the target or production database with any static data changes. An ApexSQL dat diff application project file can be included in this step, along with any optional additional command line parameters.
This deploy step executes the previously created schema and data synchronization scripts, and updates the selected target database with changes. As a data source, it is possible to directly pick synchronization scripts from their location. Alternatively, by checking From the package option, it's possible to choose a NuGet package, which includes the sync scripts created in previous steps. With seamless sync, data sync, and deploy steps added to the CD pipeline, after execution, the production database will be updated with all the committed database changes previously tested and verified.
There are two additional steps that can be used with pipelines. The notify step can be configured to send email notifications about pipeline, and step execution results. The message can be constructed with a combination of static text and execution parameters. In the Send to field, recipients' email addresses can be specified. This publish step publishes the created NuGet package with outputs results to the designated new get feed. For this purpose, the URL for the NuGet feed and proper API key have to be included. When the setup for the pipeline is completed with all the steps configured and arranged, the pipeline is ready for execution.
This concludes the overview video of the continuous integration and delivery steps of the ApexSQL CI/CD toolkit web dashboard. Thanks for watching. For more information, go to ApexSQL.com.