How to organize a DevOps process using the dbForge DevOps Automation Jenkins Plugin

dbForge DevOps Automation Jenkins Plugin provides all the necessary features to configure Continuous Integration. It allows you to quickly and easily create a database on the server, test it and then deploy.

Table of Contents:

Pre-requisites

  • Download and install Jenkins server

  • Download and install Windows PowerShell plugin for Jenkins

  • Download and install dbForeg DevOpS Automation PowerShell for SQL Server from the PowerShell Gallery.

  • Download and install all required dbForge tools. Learn more…

Installation

The dbForge DevOps Automation for SQL Server plugin installation is performed in the same way as for the rest of the Jenkins plugins. To do this, select the Manage Jenkins option on the home page, and then click Manage Plugins as shown in the figure below.

Next, on the Available tab, select the dbForge DevOps Automation for SQL Server plugin and install it as shown below:

Creating a new SQL Continuous Integration job

After you have installed the plugin, you can create a Continuous Integration job based on it. This is as simple as any other job for Jenkins. To do this, on the Jenkins main page, select the New Item option as shown below:

Then, enter a new project name, select a project type, and click OK.

Next, a window for configuring your job opens. Provide a description of the project and the path to your working folder. It is anticipated that this working folder contains a subfolder with Scripts Folder. It is also assumed that you have already set up a versioning system in order to pull new changes for the Scripts Folder subfolder. Anyway, getting new changes can be arranged as a separate job in Jenkins.

Next, configure Build Triggers. You can, for example, set the job to be run every hour:

Now it’s time to customize your installed plugin.

Step 1. Building a database package

The first step comprises building a database package and serves to deploy your database from a Script Folder on a given server.

To do this, click Add build step and select the Build a database package step as shown below.

Next, in the dialog window that opens, enter the subfolder name of your Script Folder. Remember, that this subfolder should be relative to the path that you have already set above (Use custom workspace → Directory). Then, enter the package name, server name and the name of the database that you want to deploy to the server as shown below:

Step 2. Testing a database using tSQLt

Now you are ready to test your SQL Server database. This step involves running unit tests on a deployed database. To do this, click Add build step and select the Test a database using tSQLt step as shown below:

Provide the Package ID, the names of the server and the database that you want to test.

Step 3. Publishing a database package

Now you can move to publishing your package. This step entails packing the Script Folder into the package and then publishing it at the specified location.

To do this, click Add build step and select the Publish a database package step as shown below:

Specify the Package ID and the upload path for your NuGet package.

Trial run of your project

It is time to launch your project and check the results. To do this, run your project manually as shown below:

Then, open Console Output and view the result of execution.

Among the general information, the result of creating the database from the Script Folder is particularly significant:

You can also view the result of running unit tests:

The result of the NuGet package publishing can also be checked:

Conclusion

The dbForge DevOps Automation Plugin for Jenkins allows you to quickly configure the SQL Server database CI process, since the plugin steps already encapsulate all cmdlet commands in the correct sequence, and you do not need to enter them from the keyboard when creating a job.

Download dbForge DevOps Automation to implement Continuous Integration and Continuous Delivery practices in Jenkins.