Voice of the DBA

Author: way0utwest

Local Database CI Builds with Jenkins in a Container
In two previous posts (Jenkins in a container, Adding SCA to Jenkins), I’ve showed how to configure Jenkins in a container as well as how to add the SQL Change Automation plugin. This post looks at an initial database build in Jenkins. I will build from my local repo, which gives me a bit of a pre-push check on my code.

Note: I already have a SQL Source Control (SOC) project committed to Github that contains the database I want to build. If you haven’t done that, see this post on a basic SOC->GitHub link.

Creating a Jenkins Job

Jenkins calls these build processes jobs, and you see that if you set up Jenkins in a container. By default, there are no jobs, and Jenkins lets you know this.

When I click this, I get a choice of different projects. You can read about these in the Jenkins documentation, but I am just going to use a Freestyle Project. Give it a name and click the type and then OK.

At this point, we need to configure the job. I do like to provide a description, and normally I would set this as a Github project, but in this case, this is really just a local build..

I do want to restrict which agent will run this, since I have a master agent in the Jenkins container. That one can’t see my SQL Server (and doesn’t have tools), so I’ll restrict this to the local agent on my machine.

There are tabs at the top for quick navigation, but this config is really just one long page. You can scroll down as needed. Below the agents, I find the VCS section. I’ll choose git, and then enter the path the agent will need.

When I scroll down to the Build area, I need to add a step. CI is just a step engine to assemble your software, and in this case, I see my Redgate SQL Change Automation plugin.

When I click this, it is added as the first step. There is an implicit “git clone” checkout of code. I need to configure this step with just a few items. First, build in the local folder, rather than some specific area. We’ve already specified an agent location when we configured the Jenkins agent.

Also, you need a package ID, which will be the place where the current state of all objects is stored. Unlike application code, the artifact is really the current version of the objects in source control, not any .exe or .dll.

The only way to build a database project in SQL Server is with an actual SQL Server. In this case, I don’t have any code that would error on LocalDB, so I’ll just use that. I coudl specify my local SQL Server development database if I had the need.

This is a test build, so I also don’t need any SQL Compare options or other switches.

That’s it. I click Save, and I get to the main project page. Now, the test. I’ll click Build Now.

This kicks off a build on my agent. If your agent isn’t connected to the container, you’ll need to do that now. At the bottom left of the page, I see my build working.

The build number is a link, so click that to go to the details of the build. When you get there, you can see the build working by clicking the Console output. As you see, the output is on the right side of the screen. As you can see, my local path worked fine, without any error.

I also got to this part of the process on a plane, with no Internet. While there were some warnings as the plugin and other parts of the process tried to contact the PowerShell Gallery, things worked.

At the end, I see success, which means that the code I wrote in my database, and that was committed to my VCS, is valid. It’s a low bar, but you might be surprised how often someone breaks code by making a strange change to their project.

This is a basic build process, to which I can add other steps. Testing and releasing are further steps, and Jenkins can support you in these.

Now I can move forward with additional development, knowing I have my own build system in my own environment, using Jenkins in a container.

This Series
- Jenkins in a container
- Adding SQL Change Automation to Jenkins for CI
- Local Database CI Builds with Jenkins in a Container (this post)
August 26, 2019
Growing and Changing

When I started with SQL Server, I worked on OS/2 and SQL Server 4.2. Eventually I moved on to SQL Server 6.5 at a few places, one of which had heavy workloads. While I liked v6.5 much more than v4.2, there were issues with nested views, and certainly issues with heavy tempdb loads. In fact, at one job, I banned temp tables unless we had no other way to get queries to work. This helped stabilize some of our code, and we essentially had a review group that a developer had to convince to allow temp tables since there were situations where we needed them.

I was reminded of this recently after Grant published his Best Practices and Band Plans editorial. There was this comment, which noted: ” I have talked to some people that state “our best practices is to never use…” because they tried it when it was first introduced in SQL Server 2005 and it didn’t work for them so now they won’t use it at all. ”

I’ve encountered this in my career, and I’ve been guilty of it. It took me a few versions, and more complex code, to start thinking in terms of temp tables at times. I still try to avoid them, but I’m not afraid of them, and I know SQL Server handles tempdb loads better with each version. They are a tool I reach for sooner, but not immediately. I still try to get a simpler T-SQL query if I can.

Many developers and DBAs have plenty of experience with T-SQL, but how often do they update their knowledge? How many people question that the way they first think to solve a particular problem is the best way? The T-SQL language has grown and changed across versions. Some language changes are great, like the OVER() clause for window functions. Some might not be so great (STRING_SPLIT among them) or have limitations that you might not expect. Without questioning the way you write code and periodically looking for new techniques, you might not be aware that the amazing code you wrote a few years ago could be better structured today.

There is a balance between relying on your knowledge and trying new things. Certainly most of the time I’d expect a developer to write code based on their experience and what works well, but I do think that you also need to periodically investigate new techniques. Regular learning is part of what we hope to help you with at SQLServerCentral, with articles, the Question of the Day, and blogs in our daily newsletter. Maybe you don’t read every article, but go through one once every week or two. Whether it’s something you do every day or have never tried, you might be surprised what you learn.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 26, 2019
VSCode Tips–Remove the Highlighted Line

VSCode was making me crazy as I was writing a script. I like a light theme, and every time I went to type, I saw this:

The highlight was distracting for me. I could see why people might like it, but I don’t. I tried the command palatte, but couldn’t find a setting that was helpful. After scrolling back and forth a few times, I decided to search.

This SO post was helpful, and here are the steps I followed to change the setting.

First, I opened up the Default settings from the command palette, like this:

Next, I searched for the “editor.renderLineHighLight” setting. I copied this line into the clipboard.

Now, go back and open the User Settings.

Paste in the line and change “line” in the value to “none”.

Save this, and annoyance removed.

August 23, 2019
Waits and Queues

How many of you have read this article: SQL Server Performance Tuning Using Waits and Queues? (PDF) If you haven’t, you might look at a review of the paper from Jeremiah Peschka. There is good advice about which parts to read and which to ignore a decade later. There’s an updated version from Paul Randal and SQLSkills as well.

This leads me to where you might expect this week, with a question for you. Do you use waits and queues to troubleshoot? (Survey)

Not every performance problem needs you to dig deeply into the way that the database engine processes your query, but there are no shortage of times when understanding waits and queues can help you determine how to tune your system. This is a well known method of trying to determine where to focus your efforts, so I wonder how many of you have tried this.

If you have, let us know what’s worked or what hasn’t. If you’ve never tried this, then drop a quick note as well if you have a reason you haven’t used waits and queues.

Performance tuning is a skill everyone needs, and most everyone can work on. This is often one of the most popular topics for talks and classes, and any help you can get will grow your career. Both in your own self confidence as well as impressive to interviewers when you have an answer or a methodology to give when solving any problems you’re given. Whether in an interview or in your job.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 23, 2019