Database Continuous Integration with SQL CI and Jenkins

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 Change Automation plugs 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 Change Automation has PowerShell cmdlets that 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.

The plugin is available for free. To install it:

1. Open the Jenkins Dashboard.
2. Go to Manage Jenkins > Manage Plugins > Available and search for RedGate.
3. Tick the Redgate SQL Change Automation plugin, and click Download Now and Install after a Restart

Then, instead of manually modifying the Jenkins file with the PowerShell cmdlets, you can use the Jenkins Dashboard to add the SQL Change Automation steps.

The database development process looks like this:

1. Work on your database in SSMS. Use SQL Source Control or SQL Change Automation, in order to write out your changes database and commit it to a version control system like git.
2. A Jenkins build is triggered by your commit.
3. SQL Change Automation builds the scripts to an artifact: a NuGet package. This step validates the database and produces a useful, transferable, versionable artifact.
4. SQL Change Automation runs your tSQLt test on the database
5. SQL Change Automation deploys the package to an ‘integration database’, so everyone can see the latest database state.
6. SQL Change Automation publishes the package to a NuGet stream.

You can watch the progress on the Jenkins Dashboard.

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://ift.tt/3k5Qt81

The post Database Continuous Integration with SQL CI and Jenkins appeared first on Simple Talk.

from Simple Talk https://ift.tt/3hg5bYf
via