Voice of the DBA

Tag: DevOps

VSTS– Hosted Agent is the Default for Releases

In keeping with the idea I don’t want this to get out of date, this is VSTS as of Jun 1, 2017. If your screen looks different, find a different article.

I was setting up a test the other day using VSTS (Visual Studio Team Services) and once had something bite me (again). This has happened a few times this year as the service has evolved, but it is one of those things that I keep having to change.

Here’s a new Release definition, where I start with an empty process. I clicked “New Release Definition” and then selected an Empty task, hooking this up to my current CI process.

Usually I add tasks, and then create a release and execute it. Since I mostly work with databases, I’m usually releasing to a local SQL Server instance and want to use my local agents. I have separate ones running on my desktop and laptops, so I can easily demo releases in different environments.

However, when I’ve done this, usually things fail at first. If I’m not paying attention, I won’t notice my build agent looking like this:

That’s the screen showing nothing happening with my agent.

Grouping Tasks in VSTS

Earlier this year, the release process in VSTS changed to allow us to group tasks. I can now add different groups of phases, which I stumbled upon by clicking the arrow next to “Add Tasks”. This is a UI Fail because the arrow in other parts of VSTS is linked to the text to the left (like the Add environment area).

I stumbled on this one day, thinking the arrow let me add tasks, but I accidently added a new agent phase. By default, we have a single agent phase, but clicking the arrow allows more.

What’s also interesting, and not intuitive to me, is that if I click on the “Run on agent” area, I get settings to the right. This is different than the old release section, which had the properties as a global item at the top.

This is where I need to change to the agent queue, in this case, the default for my desktop.

This might not be a big deal, and I’m sure lots of people are happy to use the hosted agent. I’m always wary of costs, so I try to avoid spending compute dollars in Azure when I don’t need to, and this is a place I want to avoid spending $$.

Hopefully, after writing this, I’ll remember to set the queue for my future demos the first time. Now if I can just get the VSTS people to give me a “default” setting I can change…

June 5, 2017
DevOps Basics – Connecting to a Git Remote

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

As I work more and more with Git, I find myself learning little tips and tricks that are helpful. One of those tricks is adding a remote to my local repo so that I can sync with it, and start collaborating with others. In many cases, I find myself pulling from a remote repo and pushing back, and much of the work is done for me. However, if I create a local repo, how do I get this to a remote service?

Certainly some tools like VS or GitHub for Windows/OSX/etc help, but I like to understand the process myself, so I spent a few minutes practicing.

First, I create a new repo with some documents. This is basic git stuff that you should understand, but if not, I’ve written a few posts on git basics. Here’s the first steps I took in another post.

Create a Remote

The next step is to have a remote git repo, which is really a remote git init spot. For this post, I’ll make on at Github, but the process is similar anywhere. In my repositories list, there is a new button.

I click that and get a form. In my case, I’ll name this to match the repo on my local machine. Life is easier if you match names, but you don’t have to.

Once I do that, Github guides me along. I get a page with quick setup.

In my case, I want to push things from an existing repo on my machine. Again, tooling may do this for me, but from the command line, I need to add a “git remote”. I’ll use the “add” option, and I specify a name and URL. These are shown near the bottom of the image. I’ll run these locally.

Once I do this, my local git repo has a remote repo (called origin) that it can send code to (push) and get code from (pull). Git manages conflicts and versions and all that.

If I try to just push, what I’ll find is I don’t have enough config.

My push needs to specify the remote and then the branch. Let’s do that.

Once I do this, 7 objects got pushed, which includes some of the .git stuff. If I go to Github and look at the repo, I see this under Code.

I’ve connected my local repo to a remote, and copied my code up. Now I can push/pull as I make changes (or others do) to keep my local copy in sync with the remote.

I’ll look at other flows in a future post.

SQLNewBlogger

This was a fairly quick post, about 10 minutes, as I connected up the local stuff to the remote. I’ve done this before and learned some of this the hard way, but this post allows me to organize my thoughts and be sure I understand what’s going on.

I did end up spending a few minutes looking at the git docs to be sure I was describing things correctly.

May 18, 2017
DevOps Basics–Creating a local repo and committing files
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

A local repo is a repository, and is the version control system you will use locally. In a previous post I looked at cloning a repo. That’s a way to get code from others, but what if I want to start a new project?

That’s easy. This post will start a new project, save a few files, and show how to commit these to my git VCS.

Create a Repo

If you use tooling, there is usually a CREATE function somewhere, but at the command line, you can just do this:
```
git init
```
Assuming you’ve installed git, this will create a repo in your folder, and let you know it exists.

At this point I have an empty repo, and if I look in my folder, there’s a .git folder.

This folder will essentially control how this repo works on my system. Let me start by adding a couple text files. I’ll use a markdown file as a Readme, since I’ll eventually push this to Github and I like to have something there that makes sense. I’ve also got the contents of the text file here, which makes it easy to track what changes are being made and versioned.

Let’s now check my status:

I’ve saved files here, but they aren’t being versioned. There’s not automatic tracking here just because I’ve saved files. This is something I need to do. Some tooling will do this for you, but it’s good to understand how this actually works. I need to tell git to track these files, so let’s do that.

First, I’ll add the files. I could specify specific files, but for now, I’m adding them all (both of them). Then I’ll check my status.

Notice the files are in green now. These are being tracked, and they’re “staged” for commit, but they’re not committed. Git sees these are new files, but the changes haven’t been saved.

I’ll now save the files with a git commit. I use the –m option to specify a comment on the command line. In another post I’ll show you what happens when you don’t do this.

If I now look at status, I see nothing.

Why?

Git is concerned with changes and versioning. If everything is tracked, then git sees a clean directory and no files to commit. The files exist, but the version is not tracked in git.

Changes

I’ll make a change to a file and then we can see the effect. Here I’ll add text and save the file.

Now let’s check status. Below you’ll see I have a “modified” file, which I’ll then “stage” and add as something I want to commit.

Let’s now commit this.

I can see that things are clean again, and my folder looks like I’d expect. The two files, one of which has two lines in it.

That’s really it for now. If you want to play along, download git, create a repo, and make some changes and commits. In another post, I’ll look at how I see the changes and get back to a previous version.

SQLNewBlogger

This was a quick post, about 10 minutes, as I practiced and experimented with things I know about git, trying to ensure I get them straight in my mind. That’s a good way to learn or improve skills in an area.

The hardest part in this post is trying to focus and stop writing.
May 11, 2017
Shipping Database Changes–T-SQL Tuesday #90
This is a good T-SQL Tuesday topic from James Anderson: shipping database changes. It’s especially poignant for me since I talk and present often on this topic. Much of my work at Redgate involves helping people implement DevOps for Databases, deploying changes smoothly and efficiently to databases.

I’ve got lots of stuff here, but let me go back to a previous position, many years ago before TFS, when Visual SourceSafe (VSS) was in heavy use in development. We implemented DevOps before DevOps was a thing, and included our database changes.

Smoothing Deployments

I worked at a startup company and we were trying to respond to requests from management and sales. As we grew from 4 developers to 10, plus a DBA (me) and a QA person, we needed to smooth out our process. Over a few months we got into a particular schedule:
- Monday noon – Package up all completed changes for deployment to QA.
- Monday afternoon – Discuss and plan changes for the following week
- Tuesday-Wednesday – Bug fixes as needed
- Late Monday to the following Monday – write code for the next deployment
- Wednesday evening after 8 – deploy changes to production
I had a foot in both the development and operations world and had to reconcile the need for production to work and remain stable while also ensuring new changes could be deployed every week. We were a young, new company, and we often had database changes included each week in our package.

When I started, we would talk to developers on Monday, start to gather all the changes from VSS for our web application, script out all database code, and then deploy to QA. Inevitably, some code would be forgotten or wrong, and we’d track it down sometime between Monday noon and Wednesday noon. Then we’d (hopefully) have a good folder of changes that we could deploy to production on Monday night, manually running .sql files or copying web code to remote servers.

The Old Process

When I started deploying code with this group, I’d get some dinner Wednesday afternoon and then return to the office with 2-3 other developers to deploy code. I usually had notes from various issues that QA had discovered in their testing, sometimes altering scripts in real time to ensure they would deploy and work correctly on the production database.

Myself and the web developers would work to get code deployed in an hour or so each week, and things usually went well. We’d have hiccups and issues, but we worked through them and talent helped. It was a good team and we could usually solve our issues.

Getting Better

The lead developer and I both had little children at the time. Spending 12+ hours at work on Wednesday wasn’t an ideal situation for us, and we decided to get better.

The first thing we did was ensure that all code was tracked in VSS. We had most web code here, but there were always a few files that weren’t captured, so we cleaned that up. I also added database code to VSS with the well known, time tested and proven File | Save, File | Open method of capturing SQL code. This took a few months, and some deployment issues, to get everyone in the habit of modifying code in this manner. I refused to deploy code that wasn’t in VSS, and since our CTO was a former developer, I had support.

The other change was the lead developer and I started building a release branch of code each week. We’d move over the changes that were going to be released to this branch, which simplified our process. We could now see exactly which code was being deployed. This was before git and more modern branching strategies, but we were able to easily copy code from the mainline of development to the release branch as we made changes for this week.

Since some changes might be in development for a few weeks, we couldn’t just grab the latest version of every file. We needed to know which pages, and which database changes would be released and which were still in development.

Automation

Once we had the code tracked, we began to automate deployments. Both the web developer and I built separate scripting tools because we had different needs. Web code is easier, mostly just copying files to the correct locations. We had a few token replacement issues, but some creative scripting solved those.

For the database, I had a single application and a single database, which is a simple problem to solve. Deploying database changes were always taking the latest version of object code, which might be a CREATE, or it might be an ALTER. In my case, I separated code into folders (tables, views, etc.) and then added scripting to ensure that I deployed objects in order. For the most part, we could work in gross orders (Tables first, then views, then procs, etc.). We had a few items that were out of order, but hard coded deployment checks ensured these issues were handled.

My additional challenge was managing QA, which was my test environment for deployments as well as the application. I added processes that would automatically refresh the QA environment from production. This was a button click to start the process. Once this was done, I’d use my deployment process to deploy the database changes for the week from VSS.

If we found issues in QA, and code changes were needed, we went back to development, made the changes there, committed to VSS, and then repeated the process. With automation and a small database, we could rebuild the QA environment with a new package in about 15 minutes.

Going Live

The first couple times we deployed to production, the lead developer and I still went into the office and made sure the deployments ran. After two weeks of watching our process run in about five minutes, we realized this was silly.

For over a year, we would continue to deploy changes every Wednesday. The lead developer and I would get on the phone every Wednesday at 8pm. We’d VPN to the office and I’d deploy database changes (5 minutes or so) and then he’d run his application scripts (2-3 minutes). We’d check a few things and then sign off.

Life got much better, we built confidence in our ability to deploy, which meant the business trusted us to get changes out to customers every Wednesday. Perhaps most importantly, we started working more normal hours rather than crazy startup schedules. We could even make quick patch deployments on a Thur or Fri if needed, and because we could smoothly roll out changes, we were under less pressure to pile as much as possible into the next deployment. If something didn’t get done in time, it would just roll to the next week.

Modern Tools

I work for Redgate Software now, and we’ve spent a lot of resources (and brainpower) to try and help you deploy code in an easier way. Deploying database changes is hard, because we need to maintain the state of our system.

These days, I might still use a home-grown, scripted approach if I had one application and one database. However, I’d really consider using other tools, because they mean my developers don’t spent time doing simple scripting, they spend time solving application problems.
May 9, 2017