Voice of the DBA

Tag: Redgate

SQL in the City Streamed Next Week

I’m off to the UK this weekend, for a few commitments next week. One of those is SQL in the City Streamed, which is taking place on Wednesday September 4, 2019.

This is a special broadcast, with some technical content, but some fun as well. This is the 20th birthday party for Redgate Software, and we’re looking forward to celebrating online.

We’ll be talking some DevOps, some products, some tips and tricks, and more, so register today and join me on Wednesday.

August 30, 2019
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
Getting Started with Spawn

I’m excited that the Redgate Foundry is working on some cool projects. The Future of DevOps is one and part of that work is with Spawn. I’ve played with this a little, and I wanted to write about a few adventures I’ve had.

First, you need an account as Spawn is hosted and there is a login. The idea here is that the service (hosted now, but could be on premise) handles all of the complexity of databases in containers, spinning them up and managing them, with you just connecting to an endpoint to use the database. We can spin up SQL Server, PostgreSQL, MongoDB, MySQL, and Redis in this fashion. It’s truly a Database-as-a-Service (DBaaS).

Spawn on Windows

For once, this is easier, or I think it is. I downloaded the spawnctl.exe CLI and then put it in a folder in my path. For me, I have a “utilities” folder where I drop stuff and that’s where this lives. I can check this working with a version parameter.

So far, so good. Now we need to log in. I like integrated authentication schemes, and this one works well. I start with a “spawnctl auth” call from the command line.

This pauses and opens a web browser window. It’s here I need to log in. We support Github and Microsoft, but have mostly been using Github for our demo work.

Once I click that, since I have a Github session elsewhere, I’m logged in and I get a code back.

I paste that back in the cmdline and I am authenticated. From here, I can then see what images I have. As you can see, there are both MSSQL and PostgreSQL images for me.

I also have a few containers from my demo work.

Let’s make a new container and connect to it. I’ll use Azure Data Studio, since I can connect to both SQL Server and PostgreSQL.

Logging In

To get a database, I’ll use a CREATE verb and the DATA-CONTAINER noun, along with an image. When this runs, I get a note this container is being created, then I get a response with login information.

Let’s put that in ADS. I can copy/paste my credentials into a connection dialog and then hit Connect.

As you can see, I’m connected.

I see the demo database we’ve been using and the tables in there. How this works is worthy of more posts, but for now, this is pretty cool.

I can do the same thing with PostgreSQL. Create a container.

Then connect (need to specify the port in Advanced)

And query:

Pretty cool. Simple, easy, and I think about as fast and easy as any other cloud database. Here, though, I am spinning up a container from an image template. That’s valuable for dev work, and I’ll talk about that in the future.

July 1, 2019