Voice of the DBA

Category: Blog

A Memory-Optimized Roundup – T-SQL Tuesday #117

The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic.

Either everyone is on vacation or not many people think about them. Or maybe they aren’t interesting. In any case, here’s the roundup.

Rob Farley has a win with user defined table types that are used in MOT objects. Never knew this was a possibility. Extra points for mentioning source control and DevOps.

Kevin Chant notes he studied them for his MCSE, but hasn’t really found them useful. Mostly because of older versions and restrictions on the feature. Kevin likens this to filtered indexes, which are a nice feature, but rarely used.

Jess Pomfret writes about some of the limitations for a feature she hasn’t used. A good summary of what they are, but don’t let her turn you off of the feature. It has its place.

Ken Fisher has a few notes about why he hasn’t used them. Not enough memory in his systems, older versions, and he doesn’t write the code. Apparently I’ve given him some things to think about and play with, and I’m sure I’ll see more blogs from him soon.

Johan Ludvig Brattås explains how he improved performance with a MOT table.

August 20, 2019
Actual Kubernetes

This post continues looking at my process of learning more about Kubernetes. I’ve been working through the 50 days of Kubernetes (K8s). I completed the first 3 sections (Days 1-15). This starts the look at Days 16-20

Disclosure: I actually ran a kubenetes cluster in Azure Kubernetes Services (AKS), but just using scripts from Andrew Pruski.

Starting to work with Kubernetes

Setting up Kubernetes is cumbersome. The setup and configuration is a little daunting but this look gives you a quick start. Microsoft is working with Katacoda here to run some things in the browser. I’m guessing they have some virtual environment you can use to get things running here.

When clicking the Day 16, we start with the welcome course that launches a single node Kubernetes Cluster.

When you start the scenario, you get the course on the left and a terminal on the right. If you click a section of code (like minicube version), it will run this in the terminal.

I typed most of the commands to get a feeling for the work. It’s definitely some work here, and lots of commands. I think I’d prefer to let Azure or AWS run this for me, but I guess I’ll still admin some of it.

The first course gets you a cluster with minikube. The second course lets you set up a two node cluster, which is a little more interesting. After all, the idea here is that you want a whole series of machines to act as a cluster of nodes. The process to join nodes is with a token, at least here it is. There can be other authentication if you really do this.

Continuing through the course across a few days, I got a feeling for how to join new nodes and change the configuration of the Kubernetes cluster. For the most part, I have a general idea of how to set up new nodes, access tokens, etc. While I don’t really know how or why to make changes, I do at least understand what changes are possible.

I did run the “get nodes” or “get pods” a few times, seeing the different messages as new containers/pods were being created, noting the delays as things are deployed. I also learned that the containers distributed on the nodes are shown under the Docker command CLI. That is interesting and unexpected to me, though it shouldn’t have been.

One thing to be aware of when trying to access the dashboard is that the token copy needs to be with a right click. At least for me, CTRL+C didn’t work.

I even re-ran a few deployments, adding new pods and web servers to the deployment, which was interesting to me.

Working through scenarios

In this course, there were 17 different scenarios. Adding volumes, networking, stateful services, building deployments, etc. Even using Helm.

For most of these, I’d click the code to run it, and read through the results, seeing how this works. The actual details of creating a secret and retrieving is aren’t likely things I’ll remember, More, I’m just trying to understand how things work.

The different scenarios do give you an idea of the breadth of Kubernetes capabilities. Certainly if you need to do these things, it would to go through the course, maybe even changing around some parameters. I’d suggest you go through this if you’ll be deploying systems and applications.

August 19, 2019
Changing Git Credentials in Windows

tl;dr For Windows when you need a different account.

I had to work with a new GitHub account recently and needed to add a separate account. I worked through that process and managed to get things to work with a Personal Access Token (PAT). Since I have 2FA (2 Factor Authentication) on my GitHub account, my user/password doesn’t ever work in the command line.

Once I was done, I wanted to change back. I had cloned a new repo, made a couple changes, and then when I went to push, I got:

sjonesdkranch is the wrong account. I want to use my way0utwest account, but despite entering a new PAT, it wouldn’t save. Since I use SQL Source Control, this was a pain as the terminal access isn’t there. I googled around and eventually realized that my generic git credentials are stored in the Windows Credential Manager, not the Git config files.

This is in the Control Panel –> User Accounts –> Credential Manager (shown here)

I first went to the Web Credentials, as I saw two https://www.github.com entries. This was incorrect, because I don’t want the web, I want the git:github.com entry, which is under the Windows Credentials section.

You can see I have a lot of Git stuff, but I want the GitHub.com one.

I expanded this down and then clicked Edit.

This had been created by the PAT I’d entered awhile ago. I had saved the one I generated today in my password manager and entered it in this dialog.

I saved it and my git push worked, both from the CLI and SQL Source Control.

Good reminder. Windows, especially Win10, has started to save and use credentials in new ways and lots of software is integrating with it in those ways.

August 16, 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