SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository (4300313)

In this article, the second Build step of the CI workflow will be described. The Build step is a step in which a database is built using the latest changes in the source control repository and once the build process is finished, a feedback of success/failure is provided to developers.

The previous Commit step, is the initial (first) step in the CI workflow, in which all changes from one/all developers are committed to the source control repository and this step starts the CI workflow.

This is usually one-time step as database needs to be loaded into source control once. After that, updates to source control will be integrated in the development process, with a tool like ApexSQL Source Control which would keep the repository continuously up to date.

If source control wasn’t integrated, the Commit step could simply be re-executed, each time, to update the repository.

In either case, the Build step will be the next, and perhaps from now on the first, step in our CI workflow pipeline.

The next challenge is how to trigger the Build step. A trigger can be one of the following:

1. Manually – the Build master simply clicks a button or executes a script to initiate the process. This is helpful for scheduled, all-hands-on-deck builds as well as testing the build process or re-execution of previously failed builds

2. Scheduled – Builds can kick off on a certain frequency like nightly at midnight. Although this doesn’t provide for iterations that are as frequent as many CI purists would approve of, it does give us continuous builds and is relatively simple, easy and inexpensive to set up

3. On-commit – To iterate builds as quickly as possible, the best solution is to trigger the CI workflow pipeline on the most recent comment. In a way, an automated trigger mechanism should initiate the Build step

On-commit triggers for the Build step

One solution would be a monitoring Build server (like Jenkins) for the source control repository and each change that developers commit on the source control repository will automatically trigger the build step. Such a change (along with the rest of the latest changes) would be pulled from the source control repository and used to build a SQL database.

To create an automated, on-commit build trigger, with an existing ApexSQL toolset – see the article How to automatically trigger a SQL Server database continuous integration and/or delivery process on a new source control commit.

Let’s take a look at two tools/approaches for building a new database from source control.

For this job let’s utilize ApexSQL Build.

ApexSQL Build is a tool that can build a new SQL database, update an existing SQL database, consolidate scripts, and execute scripts on multiple databases. Additionally, it can deploy databases directly from the source control repository, create C# solution or executable installer, and build SQL databases from scripts, ApexSQL snapshots, or script folders.

In addition, ApexSQL Build has a helpful feature in that it can be customized to automatically build a new database for us, that we’ll utilize to create a new QA database.

To implement this step with ApexSQL Build, see the article How to build a SQL database from source control without dependency problems.

To include static data from the source control repository during the build process, just check the Insert static data option, under the Script tab, in the Options window:

The checked option will pull all of the static data that exists in the repository and insert in the build process.

Via the command line interface aka CLI, you would simply add this switch:

By adding this switch to the PowerShell project, static data from the source control repository will be included in tables for a built database.

Note: As an alternative to using ApexSQL Build for creating a SQL database from changes from the source control repository, using ApexSQL Diff, another 3^rd party tool, is described here.

In the next sequence article, the Populate step, the third step in the CI workflow will be described.

All articles in this series:

1. SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository
2. SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository
3. SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
4. SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes
5. SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script
6. SQL Server database continuous integration workflow DOCUMENT step - Creating database documentation

A complete SQL Server CI/CD solution

Please see the ApexSQL DevOps toolkit for a complete, end to end SQL Server CI/CD solution including full, open source PowerShell scripts. Learn more