Introduction

There are scenarios where you need to schedule the SQL Server scripts without using SQL Server Agent, like when you use SQL Server Express edition. Mostly, the DBAs in this case is will choose to use the Windows task scheduler. However, Jenkins tool can help you to achieve the same result more efficiently than the Windows Scheduler, specifically in the area of logging and versioning the scripts.

This article will give you the insight to understand how you can schedule a SQL Server script using the DevOps Tool called Jenkins.

What is Jenkins?

Jenkins is an open-source automation server that could accelerate the software development process by automating it. Jenkins basically manages and controls the software delivery process throughout the entire lifecycle, including build, document, test, package, stage, deployment, static code analysis.

There are a few prerequisites in order to use Jenkins.

• Java 8 or above is required
• Install Jenkins for Windows from the Jenkins downloads website

For the scope of this article to schedule the SQL scripts, the below utilities are required.

• Microsoft ODBC Driver 11 for SQL Server – Windows (download link) – Pre-requisite for installing SQLCMD.exe
• Microsoft Command Line Utilities 11 for SQL Server (download link) – This gives you SQLCMD.exe

If you already have a SQL Server installed on your machine, then the last two prerequisites will be already available on your system. Other versions of these utilities can be chosen if needed.

Let us use the below SQL script to initiate a backup of a database and schedule it in Jenkins daily.

DECLARE @MyFileName varchar(200)
SELECT @MyFileName='C:\PS\SQLScript\Backup\' + REPLACE(cast(cast(convert(nvarchar(20) as nvarchar(max)) as nvarchar(max)),cast(cast(GetDate() as nvarchar(max)) as nvarchar(max)),cast(cast(120 as nvarchar(max as nvarchar(max))))),':','-') + '.bak'
BACKUP DATABASE Branch TO DISK=@MyFileName

The installation of Java and Jenkins is covered in my previous article. Refer to that article if you don't have a working Jenkins server.

Create the SQL Script

Next, we create the SQL script that needs to be scheduled using Jenkins. This script will initiate a backup of the database named Branch to the location C:\PS\SQLScript\Backup with a timestamp in the file name.

DECLARE @MyFileName varchar(200)
SELECT @MyFileName='C:\PS\SQLScript\Backup\' + REPLACE(cast(cast(convert(nvarchar(20) as nvarchar(max)) as nvarchar(max)),cast(cast(GetDate() as nvarchar(max)) as nvarchar(max)),cast(cast(120 as nvarchar(max as nvarchar(max))))),':','-') + '.bak'
BACKUP DATABASE Branch TO DISK=@MyFileName

Saving the script in the location C:\PS\SQLScript as backupscript.sql. This .sql file will be called by Jenkins using the SQLCMD utility.

Schedule SQL script with Jenkins

Login to the Jenkins URL and supply the credentials. This will display the Jenkins home page. Click on New Item option on the left hand side.

Type a job name that makes sense to the requirement. I named the task as 'RunSQLScript_Task' for this example. Click on the option 'Freestyle project'. Other options are projects with multiple other features for implementing the DevOps tasks. For our scope, we need the 'Freestyle Project' only.

On clicking OK, the below page is displayed. Fill in the description text field with some meaningful sentence(s) depicting the task we are going to create.

Scheduling the task is handled in the 'Build Trigger' tab. In the schedule text field, mention the frequency in the crontab format.

Crontab format can be easily built using the crontab.guru website.  In our case, I am creating a crontab schedule to run the job every day 12 AM. Hence I need to use  00 00 * * *  crontab format as shown below.

Fill the schedule text field with the crontab format schedule.

In the Build tab, select the 'Execute Windows batch command' from the dropdown options.

In the Windows batch command text box, type the command to change the directory of script execution to the  SQLCMD root location. Then, specify the SQLCMD command with parameter -i for passing the SQL file in the next line.  Click OK.

The other tabs, like 'Source Code Management', Build Environment, Post Build Actions, etc.,  can be skipped in this article.

This script will use the credentials with which the Jenkins services are configured for connecting to SQL Server.

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
SQLCMD -S CENTRALSERVER -i C:\PS\SQLScript\backupscript.sql

The alternate option is to specify the username and password of the SQL authenticated credential to connect to the SQL Server as follows. In this case, I am including the user name, appuser, and the password, mypa$$word.

Once the Jenkins task job is created, the Project management page for the task will be displayed as follows with the project(task) name at the top.

For adhoc execution of the job Click on the 'Build Now' option on the left side else it will run on the schedule defined.

The job execution status of every execution comes under the Build history section.  The job execution outcome can be identified with the color of the bubble against the execution number as shown below. A successful execution shows blue and the failed one displays the bubble in red. You can see this in the lower left of the image below.

To view the details of the job execution, click on the Console Output option of the respective job execution link  In this case, the execution link is #8. This will take you to the below page.

Verifying the Script Execution

Successful script execution can be verified by checking the backup file created in the C:\SQLScript\Backup location, which is the expected result of this Jenkins task.

Conclusion

This article explained how to schedule a SQL script using the Jenkins automation tool which will help the administrators to schedule their daily tasks. The advantage of using Jenkins over the Windows task scheduler is that there is an inbuilt log capturing mechanism with Jenkins which stores the execution logs in a structured way without much administrative overhead.