Continuous integration (CI) is the process of ensuring that all code and related resources in a development project are integrated regularly and tested by an automated build system. Code changes are checked into source control, triggering an automated build with unit tests and providing early feedback in the form of errors returned. Potential problems with the build can be fixed prior to deployment and developers are freed from traditionally manual, repetitive and error-prone processes.
Although it is best practice to use a continuous integration tool like Jenkins to build and test application code, database code and its accompanying business logic is often overlooked. Database code comes in the form of stored procedures, functions, views, triggers, and CLR objects. If it is deemed important to test application code as part of a CI process, the same must apply to the database.
SQL CI is part of DLM Automation, a suite of tools that plug into your build server or release management tool letting you apply continuous integration processes to Microsoft SQL Server databases. When used from within a continuous integration tool like Jenkins, you can create a smooth process where you can build and deploy databases alongside your application code, thus enabling both your application and your database developers to enjoy the benefits of continuous integration.
SQL CI is a command-line utility, so can be invoked directly from a Jenkins Execute Shell step. This method can be rather tricky and error-prone, so we’ve made a plugin for Jenkins to make life easier. It provides four database build tasks: build, test, sync and publish. Add one or more of these to your job, fill in a few boxes, and you’re set to go.
To use SQL CI effectively, you need to source-control your database using Redgate’s SQL Source Control.
You’ll end up with a database-development process like this:
- Work-on your database in SSMS.
- Use SQL Source Control to commit the creation-scripts to your source-control system (e.g. Subversion). These scripts, automatically created by SQL Source Control, describe the current state of your database schema.
- A Jenkins build is triggered by your check-in
- 4) SQL CI builds the scripts to an artifact: a NuGet package. This step validates the database and produces a useful, transferable, versionable artifact.
- SQL CI runs your tSQLt test on the database
- SQL CI synchronizes the package to an ‘integration database’, so everyone can see the latest database state.
- SQL CI publishes the package to a NuGet stream.
The plugin is available for free. To install it:
- Open Jenkins.
- Go to Manage Jenkins > Manage Plugins > Available and search for RedGate.
- Tick the Redgate SQL Ci plugin, and click ‘Install Without Restart’.
Now in your build configuration, click Add build step to add SQL CI build, test, sync and publish steps. You will also need to install Redgate’s DLM Automation, which has a 28-day free trial.
Like all Jenkins plugins, this plugin is open-source. You can clone the repository and work on it yourself. This may be useful if you want to make customize it, fix a bug, or just investigate how it works. If you think your change is an improvement that will benefit everyone, feel free to submit a pull-request. If it looks good, we’ll merge it in. Go to: https://github.com/jenkinsci/redgate-sql-ci-plugin