Voice of the DBA

Tag: DevOps

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
DevOps Basics–Getting Started with Git
Git is taking over the world as a Version Control System (VCS) and it’s actually fairly easy to use. This is a quick post on getting started using git.

Git is free and you can get it from: https://git-scm.com/

I use Chocolatey, and you can also download it for windows with this code:
```
choco install git
```
or
```
choco update git
```
In any case, this is a simple install on your machine. If you’re on OSX or Linux, you can download and install it in whatever way you want for those systems.

Once Installed

You can check that git is installed at the command line. There are also clients, but I like the command line, especially when getting into DevOps.

git version

This code tells you if git is installed. In this case, you can see my version (after I updated).

Now it’s installed, what do I do? Well, I first want to create a repository to track my code. A repository (or repo) is really a folder where all changes to code are tracked. I can do this with the “git init” command, but I need to do this in an empty folder.

I have some code in e:\Documents\GitHub\EndtoEndAlwaysEncrypted\SQLCode for a presentation I do. I can see lots of .sql files in here:

I want to track this code, so I’ll make a repo for it. On Windows machines, there is a Source\Repos folder under your user profile. For this machine, that is C:\Users\Steve\Source\Repos. You can see I have a few folders here:

Let me create a new folder, called EndtoEndAlwaysEncrypted. Once I do that, I’ll change to that folder and run
```
git init
```
This initializes a repository. There’s nothing in there bit a hidden .git folder, but that’s fine. I now paste in my code.

So far nothing is different with my code. However, if I check my status at the command line, I’ll see there are no files. I do this with
```
git init
```
This gives me some results.

Don’t worry about the master branch item. The thing to notice here is that all the files in red are new and aren’t being tracked by git. To add these to my git VCS, I need to track them and then commit them. To track them, I’ll “stage” them with
```
git add –all
```
This will add all the files. I could use git add with a filename after it, or use the dot (.) to add everything.

Notice now that the files are in a lighter color, as they are tracked, but not committed. A commit means that git now knows about this version of the code and will be able to return to this version if you need it.

To commit, use “git commit”. You need a message for the commit. Most GUIs make this easy, and git will pop up a text editor if you want. I prefer the command line, and use the –m parameter with a message. For the first commit, “initial commit” is usually a good message.
```
git commit –m “initial commit”
```
Once you do this, the status shows clean, which means everything is being tracked by git.

That’s it for getting started. This doesn’t seem like much, but it’s the basis for now tracking code. No more need for me to do something like “00_db_setup_old.sql” or “oo_db_setup_2.sql” for filenames. I can make changes, capture (commit) them, and then easily see what each version looks like.

I’ll cover more later.

If you want to see this as a video post, I’ve got it on my Voice of the DBA YouTube playlist here: https://youtu.be/hyyy9obHmw8
May 8, 2017
The Secret Password

As I work with more server systems that help developers run Continuous Integration and automated releases, one of the things I see used often are variables. These are values you can set for a particular process and reference inside of that process. Great for setting server names, paths, etc. In releases, these are great for specifying specific values that change for each environment, such as the instance name or IP address.

As with many developer based systems, security is not always set tightly on these systems and any developer can access the build server to kick off builds, reconfigure a process, etc. That makes sense in a CI process, but not so much in a release system. This is one reason I do recommend a separate release server from the CI server. You can use Jenkins or TeamCity to perform releases, but is it a good idea? Have you thought through the security?

In the release servers, one thing that most systems allow the admin to do is use a variable for a password and mark it secret, so the value cannot be recovered. In this way, if some developer is working on the release process, they can’t get the password to the production server. They can only click the buttons that deploy to that server.

However.

They can deploy to that server, and they don’t need the password. If I were to execute a script in the release system that executes the “CREATE USER” and a “ALTER ROLE ” commands to give me access to data, does it matter if I know the deployment password is “G4da%$2h#5f” or $(ProdPwd)? It doesn’t. The actual value isn’t relevant; I just need to be able to use it.

I think release systems are great pieces of software for reducing the risk of your deployments, but I do think the security models need to be carefully designed and easy to configure, especially when it comes to allowing arbitrary code to be submitted and executed by one person. Be sure that developers can’t necessarily deploy code directly to production servers, whether the password is hidden or not. If there is a way to use the value, someone will find it.

Steve Jones

May 2, 2017