Tag: sql server

I saw (and wrote myself) thousands of lines of Transact-SQL code that interacts with something outside of the SQL Server instance box: be it an executable file, or a remote query to a different SQL server, or something as simple as running a Powershell statement. I still remember how excruciatingly painful it is to wrap up simple OS commands into a string variable, feed it into xp_cmdshell, create tables to retrieve results, parse them using T-SQL… The list can go on and on, but the main idea is: such code is hard to read, even harder to maintain and a simply a nightmare to scale.

Nine times out of ten these scripts were triggered by a SQL Server Agent job; mostly, because, when it comes to scheduling, in 95% of the cases SQL Server administrators rely on SQL Server Agent – it is our ‘go-to’ solution. However, even while SQL Server Agent has many positive traits, it also has certain limitations in some of the scenarios. Most notable examples include (but not limited to):

• Email interactions: basic notifications after job completion are usually not detailed enough. sp_send_dbmail in advanced use-cases as an attempt to overcome this deficiency forces us to write complicated logic inside our code to accommodate for various scenarios.
• Interactions with remote SQL Servers: mostly relies on Linked Servers, which come with an overhead of additional maintenance. Requires advanced logic (and maintenance) if Availability Groups are involved – to decide whether the AG database is active on the current server.
• Other remote interactions: Powershell remoting through CmdExec task running a Powershell script…? Writing results to a temporary table to pass it on to the next step? Using proxy accounts to interact with the network shares? There are many workarounds, but all of them require a wrapper of some kind, which, in addition to the complex syntax, are in most cases not overly secure.
• Running code that comes from a different programming language: requires code interpreter to be installed on a SQL box, which I rarely consider a best approach, knowing how easy it is to forget about ‘custom‘ configuration. Powershell was an exception to that rule, except until SQL 2016 it only came as ‘SQL Server Powershell’ that had certain quirks and limitations. Debugging such steps was/is a nightmare of its own due to limited exception handling capabilities.
• Capturing output (and storing it somewhere on the disk) has different flavors, but usually one of the following occurs:
  • There are thousands of log files in your log folder
  • All the logs are stored in msdb; comes with additional headache of retrieving them when needed, cleaning them up and including into the email notifications
  • Log file is configured to append output to the existing file and is now several megabytes long
  • You only have access to the latest execution log
• Execution timeouts – ability to stop the job after certain duration can only be achieved by having yet another job to control that.

Now, I am not trying to say that SQL Agent should never be used. It is convenient and utterly reliable at some tasks, like running stored procs or native T-SQL on that same server; it can easily be managed by native SQL Server tools (SSMS, T-SQL); it has advanced options, such as multi-server management and so on. What I’m trying to say is: there are other options.

Jenkins – work smarter not harder

Jenkins is a well-known DevOps automation solution, and is widely used across the globe in all kinds of automation tasks. Once you familiarize yourself with Jenkins basic concepts, it becomes a powerful tool in your hands that allows you to build almost any chain of automation events, linking together tasks coming from different programming languages, applications or even operating systems.

More importantly here, Jenkins is completely independent of SQL Server, meaning that Jenkins jobs will continue to trigger and execute even when SQL Server is not running; jobs will be able to talk directly to Availability Group listeners instead of deciding whether or not to run; and last but not least – scalable architecture will allow you to run any number of jobs, and security configuration will be flexible enough to provide all kinds of access to end-users.

Here are some of the tasks that can easily be achieved by using Jenkins instead of SQL Server Agent (and relevant examples):

• Maintenance tasks across the whole environment: run your code on any number of servers with the help of Powershell and parametrized jobs.
  • Recurring backup-restore operations – achieved by using dbatools Powershell module.
  • Deploying scripts or database projects from source-controlled repositories – can be done with dbops Powershell module
  • Initiating failovers for planned maintenance operations – T-SQL or Powershell
• Reports: invoke scripts, generate output files, send out notifications
  • Collect data from your servers using tools from Brent Ozar’s first responder kit and feed it into PowerBi reports
  • Run tests against your environment to ensure stability using dbachecks Powershell module
  • Running tSQLt tests against your development databases after deploying the code
  • Put together a notification email and send it to multiple groups of users
  • Notify admin about recent changes to the job that led to failure.
• Chaining jobs together: jobs can trigger other jobs, wait for the child jobs to complete and aggregate the output.
  • Deploying code in multiple environments – one following another  – with the help of the dbops Powershell module
  • Setting up custom pipelines with manual or automatic approvals and notifications
  • Include database deployment as a part of application delivery pipeline
• Working with artifacts: Jenkins will keep all the files and logs (called artifacts) produced by the task and grant the ability to view or download files in one click.
  • Keep execution logs and errors for an extended period
  • Publish report files
• Interaction with other systems – any Jenkins control element can be called using a rich REST API
  • Trigger Jenkins jobs execution from SCM webhooks, Powershell or Python call
  • Retrieve job status information
  • Retrieve job artifacts using a simple web call
• Integration with source code management systems: Git, SVN, TFS, you name it.
  • Deploy code by submitting commits to SCM
  • Run automated tests for your branches using multi-branch pipeline

Jenkins Setup

The installation steps are very simple:

• Download latest Jenkins from the Jenkins website. You will be able to select between an installation package, Docker image or a simple jar file.
• Start Jenkins using a method appropriate to a chosen distribution. More info here.
• Go to http://localhost:8080/ and follow the instructions.

Jenkins Architecture

A simplified schema of Jenkins components might look like this:

Jenkins Master – your primary Jenkins server that keeps all the metadata, provides the interface and primary functions.

Jenkins Agents – Jenkins processes running on a computer that can be used to execute Jenkins jobs. Each Jenkins master has a built-in agent by default, but can easily be extended by adding more agents. Agents are communicating through network and can be configured to run as a certain OS user.

Executor – an execution space inside a Jenkins Agent that provides a single job queue to execute Jenkins jobs – one at a time.

Jenkins Job – a set of predefined activities executed one after another. A job execution attempt triggers a Build that can either fail or succeed and will contain relevant artifacts and logs. Each Build runs in a Workspace – dedicated working folder in Jenkins home directory.

Jenkins Plugins allow Jenkins to extend its capabilities by using free community-driven extensions. The extensions usually add new functionality or modify existing behavior. Actually, let’s talk more about Jenkins plugins, as they are an integral part of Jenkins setup.

Jenkins Plugins

One of the best reasons to use Jenkins is that, while it’s distributed with bare minimum of the components, its functionality can be (and should be) extended by picking from thousands of plugins coming from a huge Jenkins community. Actually, there are so many of them that sometimes it’s really confusing where to start. Here’s the list of plugins that I find very useful for all intents and purposes:

• Active Directory – enables authentication through Active Directory
• Blue Ocean – beautiful interface to build and control pipelines
• Build Pipeline – adds a dashboard that can show jobs chained together into a build pipeline
• Build Timeout – terminates the build after predefined amount of time
• Conditional BuildStep – adds conditions to the job steps if necessary
• Copy Artifact – copies artifacts from previous builds/other jobs
• Email Extension – enhanced email experience
• Folders – allows you to create folders for jobs
• Git – integrates Jenkins with Git
• Parametrized Trigger – allows to trigger jobs with parameters
• Powershell – allows to run Powershell scripts or ad-hoc commands
• Promoted Builds – promotes build status based on certain criteria and adds a possibility of manual interactions in the job chain (aka manual promotion)
• Timestamper – adds timestamps to the log
• Workspace Cleanup – allows to cleanup the workspace before/after build

Conclusion

Jenkins can become an important asset in your day-to-day work as a SQL Server professional and bring a whole new level of possibilities. However, it will only make sense to use it for a certain kind of tasks, which can be characterized as operating outside of SQL Server instance scope. It will not be able to completely replace the functionality and convenience of SQL Server Agent, yet in certain scenarios it can become your faithful ally.

PS

If I was asked, in which situations I would use Jenkins instead of Agent, the answer would probably be ‘it depends’, and it really does! I tried to come up with some kind of generic scenarios, in which one of the two tools will be a more logical choice based on my experience. However, these are only recommendations and, after all, there is always more than one right answer, so – feel free to experiment!