Voice of the DBA

Tag: continuous integration

Continuous Integration with Local Agents and Azure DevOps
Abstract:

One of the fundamental tools for modern DevOps software development is continuous Integration (CI). This process provides an automated independent validation and verification of your code, with quick feedback for developers that are under pressure to write higher quality software at a rapid pace. This session examines how Azure DevOps or TFS can be used to manage this process and run a database CI process inside of your data center, on your own instances.

We will explain CI and Azure DevOps as an orchestrator and see a live DevOps flow of the process running on local instances, but controlled from the cloud. Learn how this can start improving your database development teamwork immediately.

Goals:
- Understand continuous integration (CI)
- Learn how Azure DevOps can run inside your local data center
- See how a database build process can work with your code changes
Length: 60 minutes

Slides: AzureDevOps_LocalHostedAgents.pptx
February 8, 2021
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
Adding SQL Change Automation to Jenkins for Database CI
In a previous post, I explained how to get Jenkins running in a container for your local CI work. In this one, I’ll expand on the process for database CI with a quick look at getting the SQL Change Automation plugin added to Jenkins

The first step here is to click the Manage Jenkins menu item from the home page.

This gives you a number of items on the right. Scroll down and you’ll find the Manage Plugins option.

Once you click this, you see a list of the installed plugins with updates in the right. You also see a few tabs for managing and adding plugins. Click over to the Available tab for plugins that you can add, but don’t have installed.

In the upper right is a Search box. I entered “redg” and you quickly see the list shrink to the one you want: the Redgate SQL Change Automation plugin.

Check the box next to the plugin (in the Install column) and then click “Install without restart”. I restart isn’t needed in this case to get the plugin to work. Once you click this, you will see the plugin status appear.

It should install quickly and let you know it’s done. I did this 4 or 5 times and every time it just worked.

At this point, you can add this plugin to your builds. When you have a project, this will be one of the possible options. In a future post, I’ll look at actually performing a build with the SQL Change Automation Plugin.

There are other posts in this series:
August 21, 2019
Jenkins in a Container
One of the things I needed to do recently was get Jenkins running as a demo for a customer. We have some pre-built VMs to do this, but I wanted to experiment with a container.

This post covers what I did to get this running.

There are other posts in this series:
- Adding SQL Change Automation to Jenkins for Database CI
- Local Database CI Builds with Jenkins in a Container
Getting Jenkins Working

The first stop was to download a container. A search on the Docker Hub first brought me to jenkinsci\blueocean. This is a nicer interface for Jenkins, for some things, and that’s fine. Really, I need the server and decided to try the blueocean project interface. First things first, get the image:
```
docker pull jenkinsci/blueocean
```
Next, we need to run this. I know how to run a container. Here, I glanced at the docs, which note this runs on 8080 by default.
```
docker run --name blueo -p 8080:8080 jenkinsci/blueocean
```
Once things are running, you get output at the console. I left off the –d so I could see this start. This is a Linux image, so you do need WSL running on Windows. Once things were running, I popped over to the address in a browser, and saw this:

I’m impatient. If you wait a minute or so, you will actually see this when the browser reloads.

This password is in the console output, in this area:

If you don’t see it, or you ran the container as detached, this command will get the output:
```
docker logs blueo
```
Paste in the password, and you’ll get to install plugins. I just let the normal plugins install, since I wasn’t sure what I might need. I know I’ll be adding some Redgate ones, but for now, get the typical ones.

Next, create a new user. If you don’t create the user, you’ll need admin and that password to log in again. Create a user; you’ll appreciate having a user. Click Save, not continue as admin. I made that mistake.

Last it’s the port. We know the default, I just left this.

Save, continue, and you ought to see this. Note BlueOcean isn’t the default. This is a new way of building pipelines.

I know I need the Redgate plugin, but I’ll drop that in a different post on a database build.

Now, I need an agent. Why? I don’t want to customize the container with SQL Server and other stuff, plus it’s Linux. I could configure things in another container, but it’s not simple. The simplest is using the SQL stuff on the host, so I’ll drop an agent there.

The Agent

I had to have java on the host, which was what I wanted to avoid, but I can’t. The container can’t see the host, and I didn’t want to do some crazy networking between containers. That ought to be my next project, but for now, I wanted to build with local resources.

One note here. You need a second
port for the container. If you didn’t start the container as below, you’ll need to stop it, remove it, and then create a new one with two ports.
```
docker run --name blueo -p 8080:8080 -p 50000:50000 -d  jenkinsci/blueocean
```
I won’t go through Java, but once it’s there, you can set up a node. The first step for me was to “manage nodes”. Click Manage Jenkins and scroll down.

Click this, and the “Add Node” on the left menu.

Give this a name. I chose the name of my host machine, and I want this to be permanent. Meaning, I’ll save this container as an image, so I can start it when I want Jenkins running.

There looks like a lot of configuration now, but there are really only a few things to do. Don’t let all the red bother you. First, leave executors at 1. This is for my personal setup, not a team.

Next, I need a place for Jenkins to work. I created a c:\Jenkins folder, then added a subfolder for the agent itself. I’ll use the subfolder as the agent location. This does look weird, but the server is in the container, so the host is really remote to the server.

For the agent connection, I don’t want this running all the time. I also don’t want this as another service. You can set up Jenkins agents as Windows services, but here I’ll just let the agent connect when I start it. I do the same thing with Azure DevOps. I’ll save this config.

I have two nodes, one of which isn’t running.

If I click the Agent, I get what I need. There is a command line to run and an agent.jar file I need. First, download agent.jar and drop it in your c:\Jenkins folder. Then, copy the command line into a .cmd file as is. Put that in the same space.

I named my file runagent.cmd. When I run this, my agent will start.

And the agent is connected and data shown:

That’s it. Now I can build. That’s the subject of another post.
August 14, 2019