An ApexSQL DevOps toolkit plugin is available for the popular Atlassian Bamboo continuous integration server for creating flexible Continuous integration (CI) and continuous delivery (CD) workflows. The ApexSQL DevOps toolkit provides all-in-one solution for DevOps users to build, test, document and review database changes and deploy them to production. This plug-in contains a set of Bamboo tasks which combined in a set will create some form of toolchain and run ApexSQL developer tools in sequence which represent a continuous integration and continuous delivery workflow.
Providing that the Bamboo server is up and running on the build machine, ApexSQL DevOps toolkit plugin requires few easy steps to install. After logging on to Bamboo server, when initial screen shows, go to the Bamboo administration settings and choose the Add-ons option.
The view will directly switch to the Add-ons management page. On this page use the Upload app option to display the pop-up where the plug-in can be uploaded.
In the Upload app pop-up click on the Choose file button, browse to the previously downloaded ApexSQL DevOps toolkit – Bamboo plug-in installation file and open it then confirm the installation with the Upload button.
After the short installation process the newly installed plugin will be present in the User-installed apps list and the continuous integration configuration can begin.
Hint: Although it is not necessary before the installation process it is recommended to temporarily pause Bamboo server as itself suggests.
Creating Bamboo project
From the Add-ons management page go back to the initial Bamboo page by clicking on the My Bamboo option of the Bamboo main menu.
From this view click on Create your first build plan button and the view for the Bamboo project and plan initial definition and the source control repository settings will open.
For the project definition name and key need to be inserted as for the plan definition. The key name Bamboo server will use to create dedicated project outputs folder. Additionally, checking the “Allow all users to view this plan” option will enable all Bamboo login accounts access to this continuous integration plan.
Further on, the repository settings need to be defined with these steps:
- Choose the host for the desired repository
- Enter display name (the name that can be recognized when choosing the database build source later with the CI job setup)
- Choose the repository access type (public or private)
- Provide the authentication credentials. If there are shared credentials defined in the Bamboo administration settings it is possible to use them here, otherwise the username and password for the repository connection will have to be provided on this page
- When the connection settings are created click on the Load repositories button to load the list of available repositories on the selected host
- Set the authentication method for the repository connection, the credentials provided in previous steps can be used or a SSH key can be used instead
- Choose a repository project branch to use for the build
- Finally, set if all Bamboo users can access this repository definition which will be saved permanently in the Bamboo configuration
On the screenshot below the proper repository configuration is shown that can be used for ApexSQL DevOps toolkit continuous integration setup. To confirm all settings and continue with the setup click on Configure plan button.
Configuring Bamboo continuous integration job
The first page that opens will allow adding the build tasks for the new Bamboo job. Every task will represent one continuous integration step from building a test database though testing and reviewing to packaging for deployment.
Click on the Add task button and the pop-up with available configurable tasks will show up.
In the pop-up the ApexSQL DevOps toolkit plug-in configurable tasks can be seen among the ones that come in the package with the Bamboo server installation.
ApexSQL DevOps toolkit – Build
Choose the ApexSQL DevOps toolkit – Build task for the first continuous integration step.
This task will run the ApexSQL Build tool to build a test database from the source control database project that contains changes that need to be tested and deployed.
With this task selected the build configuration pane will open with several sections to be considered.
After filling in the task title in the Task description field, use the Include output in package option to create a NuGet package which will store the output summary of the Build task execution. With this option the package file name should be defined in the ID field with possibility to use ${bamboo.buildNumber} variable which will create the build number suffix and autoincrement it with every build attempt (Note that variable is case sensitive).
For the build source the repository definition created at the beginning will be used. In the Database folder select box of the Source section pick the Subfolder of build VCS root then type in the name of the desired project folder. Note that when repository definition is created, Bamboo build server will create working folder path based on the root of the repository and the user will have to append to that path with the desired project folder.
At the end of the Source section there is the option to Include static data in case there is static data in the project needed to be included in the build.
The Target section contains the fields to define the SQL Server and target database for the build which will be used for testing and review along with authentication type for the database connection.
At the end there is Additional options section which can be used for fine tuning the build. This can be achieved with the ApexSQL Build tool project file and/or additional parameters where CLI switches can be typed in.
For example, using the Build tool it is possible to create selection of objects to be included in the build (in case there is entire database in the repository and not all objects are necessary for this build), it is possible to set global build options like to include USE statements, ANSI nulls and ANSI padding which will influence the creation of the build script which will form the test database upon step execution. In case the proper target database for the build is configured in the project file, the Target section of the ApexSQL DevOps toolkit – Build task can remain blank because the build will use that information from the project file and if the Target section is populated then the build will override information contained in the project file. Note that in case that the project file is not included the Target section will be mandatory to populate it.
All these options used in the project file can be set manually with the CLI switches in the Additional parameters field.
A combination of project file settings and CLI switches can be used. For example, we failed to include object extended properties and foreign keys and constraints names options in the project file for the build and it is not convenient to update project file at the moment so we can update build configuration with the proper CLI switches. Switches used as additional parameters will always override the equivalent options in the project file.
Note: For detail information on available option switches for the Build task see the article ApexSQL Build Command Line Interface (CLI) switches
Combination of sections in the configuration of ApexSQL DevOps toolkit tasks, the project file settings and additional parameters will work like explained in every available task that is part of ApexSQL DevOps toolkit – Bamboo plugin.
ApexSQL DevOps toolkit – Populate
This task uses ApexSQL Generate tool to populate empty tables in a test database with synthetic data. Configuration for this task contains sections previously explained for the Build task except for the Source section.
The Include output in the package option will store the execution summary in the NuGet package when selected. The NuGet file name definition will be used from the Build task automatically.
In the Target section it is possible to define the test database where the tables to be populated are located.
In the Advanced options section, the ApexSQL Generate project file with target connection definition or details like the number of rows and which tables to generate data for can be included along with the CLI switches. For example, in a case where the project file setup will fill all tables, populated and empty, that can be overridden with the switch to fill only empty tables.
Note: For detail information on available option switches for the Populate step see the article ApexSQL Generate Command Line Interface (CLI) switches
ApexSQL DevOps toolkit – Audit
This task will use ApexSQL Trigger tool to implement triggers into tables that are sensitive to changes and require auditing.
The created test database where unaudited tables reside can be optionally defined in the Target section in case it is different from the one that is stored as information in a project file.
With the Include output in package option the task execution summary will be stored in the NuGet package file.
For this task’s configuration the ApexSQL Trigger project file will be mandatory because it contains the information about the existing auditing architecture and the name of the repository database where audit logs are stored. Since implemented triggers will be propagated to production upon deployment this information is needed so this task cannot disturb the existing configuration. In case there is no existing architecture this task will create one and prepare tables for auditing.
In case there are tables with existing triggers in the test database they will be excluded by default from further handling by this task thus preserving them as they are in a production database.
By default, this task will implement triggers in all tables that do not have them so there is the Exclude tables option in the Advanced options section to exclude specified tables from trigger implementation. To specify the tables, type in their names in the Name(s): field delimited with the blank space character.
In the Additional parameters field ApexSQL Trigger CLI switches can be used to add an option like to remove specified triggers from specified tables. In the example shown on the screenshot below we can see that the table named “Phone_book” is exlcuded from trigger implementation and that from the table named “products” the “update” type trigger will be removed.
Note: For detail information on available option switches for the Audit task see the article ApexSQL Trigger Command Line Interface (CLI) switches
ApexSQL DevOps toolkit – Test
This task can be used to install tSQLt framework and perform unit tests on test database to evaluate the test database for potential issues or poor coding practices.
For the unit tests a class created with the ApexSQL Unit Test tool can be used when the Class: field is populated with the unit test class name or use the Install SQL Cop tests option to install the set of 48 predefined unit tests and execute them.
When Include output in package option is selected the test result with success status for every executed test will be stored in the package file for later review.
ApexSQL DevOps toolkit – Review
Using this task will allow checkup of database changes against the custom rulebase created with the ApexSQL Enforce tool. With the rulebase file the criteria for enforcing the database best practices can be set and used as a method to review and approve changes for deployment or turn them back to development. In case the criteria is not met this task will fail and detailed report will be generated about which database object didn’t pass the checkup and why.
Note: for more details on how to select the set of rules to form a custom rulebase see the article “How to enforce best practices rules against your database”
In the Source section for this task’s configuration the Rulebase path: field has to be filled with the path to the created rulebase file.
Additional parameters field can be used for some ApexSQL Enforce CLI switches. For example the rulebase might contain set of checkup rules but they are not all necessarily going to be set as active and will not be applied in the task. In that case it is easy to activate all of them with proper CLI option without the need to update the rulebase.
Note: For detail information on available option switches for the Review task see the article ApexSQL Enforce Command Line Interface CLI switches
When Include output in package option is used the rulebase review report will be stored in the package file.
ApexSQL DevOps toolkit – Package
The Package task will create database script folder out of test database with implemented triggers included and audit architecture if created, but without the synthetic test data and tSQLt framework objects, and pack it in a NuGet package file with the name found in the ID field of the Include output in package option if set. For that process the task will use ApexSQL Script tool for the schema objects and ApexSQL Data Diff tool for the static data in case it was included with the ApexSQL DevOps toolkit – Build task.
For the process of creation of database script folder the Source section has to be populated with the connection parameters to the test database.
Configuring Bamboo continuous delivery job
After the successful execution of the continuous integration job and testing the database changes those changes can be deployed using several available tasks.
ApexSQL DevOps toolkit – Sync
This task will use ApexSQL Diff tool to compare database that contain changes which was tested and prepared for deployment in the NuGet package file against the database that requires changes to be implemented. The comparison will create a synchronization script which will later be executed to deploy changes.
In order to use a NuGet package as a source the From the package option in the Source section has to be selected. From there the location of the package file can be chosen. The package file can be used from a local storage where the NuGet file path is required,
or from a NuGet feed with the URL and API key provided.
To include specific database as a source instead of package file uncheck the From the package option and SQL Server and database connection parameter fields will be displayed.
The Target section contains the SQL Server and database connection parameters for the database that awaits changes.
In the Advanced options section the ApexSQL Diff project file can be included. This project file can contain the source and target database information as well as some options to ignore certain differences during comparison, like permissions, CHECK/NOCHECK constrains, etc. In case some differences are set to be ignored those objects that may have those differences will be compared but the synchronization script will not include them.
In the Additional parameters field those comparison ignore settings can be overridden with CLI switches and for example an option to include specific types of schema objects in comparison.
Note: For detail information on available option switches for the Sync task see the article ApexSQL Diff Command Line Interface CLI switches
When Include output in package option is used the comparison summary and synchronization script will be stored in the NuGet package file.
ApexSQL DevOps toolkit – Sync data
This task uses ApexSQL Data Diff tool to compare database with tables that contain static data changes against the target database that requires update and create synchronization script which will be executed later upon deployment.
Behavior and configuration of this task is similar to the ApexSQL DevOps toolkit – Sync task except that for the project file in the Advanced options section the ApexSQL Data Diff project file can be included. Along with the connection settings for the source and target database the project file can contain information about filters to exclude certain tables or rows from comparison and therefore synchronization. As for Additional parameters option there can be as example the switch to choose which object types will be compared i.e. tables or views or both.
Note: For detail information on available option switches for the Sync data task see the article ApexSQL Data Diff Command Line Interface CLI switches
When Include output in package is selected the comparison summary and generated data synchronization script will be stored in the NuGet package file which will be used later to deploy changes.
ApexSQL DevOps – Deploy
This task will deploy changes using synchronization scripts created in the Sync and Sync data tasks and execute them against the target database.
Synchronization scripts used as input source of the Deploy task can be picked from a NuGet package file if From the package option is checked and location of that package file is provided which can be on local storage or on a NuGet feed.
If from the package option is not selected, a direct path to schema and/or data synchronization scripts has to be provided.
If Include output in package is selected the SQL script execution summary will be stored in the output NuGet file.
Apart from task specialized for continuous integration and continuous delivery, there are two more tasks that can be used wherever they are needed.
ApexSQL DevOps toolkit – Document
This task will use ApexSQL Doc tool to create database documentation with the choice to create full documentation or differential, i.e. only changes.
As a source for generating documentation In the Source section SQL Server and database connection fields can be populated. Alternatively, the database script folder can be used in case there is a NuGet package created with that script folder stored in it. By checking the From the package option in the Source section the package file can be used from local storage,
or from a NuGet feed with provided URL and API key.
The source database information can also be stored in the ApexSQL Doc project file along with some options like which objects or types of objects and their properties will be documented. To use that information, include the project file in the Project path field of the Advanced options section.
When the Changes only option is selected, the task configuration pane will be expanded with the Target section. With this option the task will document delta between the source database (with the same source location options like previously explained) and the target database e.g. production database.
Additional parameters field in the Advanced options section allows using ApexSQL Doc CLI switches. For example, it is possible to include more details like object extended properties in generated. documentation.
Note: For detail information on available option switches for the Document data task see the article ApexSQL Doc Command Line Interface CLI switches
ApexSQL DevOps toolkit – Publish
When the NuGet package is prepared with the output summaries and reports from previous task executions it can be publish to a NuGet feed for later use.
The package file name in the Source section has to be provided and the URL and API key for the NuGet feed location
All the execution outputs as well as NuGet package file are expected to be in a Bamboo project folder located in Bamboo home directory. From there they can be used for review or troubleshooting.
