Voice of the DBA

Tag: DevOps

Building Helpful Commit Messages

Using a VCS (Version Control System) is important in any modern software development environment. I still can’t believe that both application and database developers are using a VCS at less than the 95% level, but surveys constantly show this. I’m not sure if this is because of laziness, ignorance, stubbornness, or something else, but it’s a bad idea. Start using a VCS. Talk to your management, and let them know Git is free and can be run on premises, much like a file share for code, except it’s better. Even if you hand craft scripts (or use the SSMS GUI), just start saving those scripts in a VCS.

There are lots of tutorials and examples of how to use git (or any VCS), and this is a skill you want to build over time. To start, just learn to build a habit of committing code to the repo as you make changes. This is fairly easy, but there is one skill you need to develop over time: how to write a good commit message.

When we commit code, we usually need to enter some comment. This is a bit of an art, and should be designed to quickly convey information to the next developer (who may be your own future self). While people may have their own view on what to include, my thought if usually to include the “why” of the commit. This helps me to go back later and understand what I was thinking. I don’t need “what” was changed, because a quick diff of the file will explain that. If possible, I like to relate a commit to a specific request, like a Work Item, a help ticket, or some place that describes the requirements in more detail.

I ran across a nice post that describes the art of the commit, and I think there are some good ideas here. Certainly being brief and understanding that often your message is a headline is good advice. The most important parts of the post, for me, are the need to understand that I’m working with others, and it’s important that my message help them and vice versa.

Learning to use a VCS is a critical skill for anyone working in the technology field. We know most application developers need this, and slowly I find more database developers doing the same. However, as we move to GitOps and Configuration and Code, as compliance becomes more impactful with more auditing requirements, as we try to do more with less, the infrastructure staff and others need to learn how to capture their changes in a VCS as well.

Do yourself a favor and start learning how to use a VCS. Git makes it easy or you can play with Azure DevOps in the cloud. Just start saving some files and building skills. Your career will thank you. Learn to write good commit messages and your team will appreciate you.

Steve Jones

July 8, 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
Microsoft, Think DevOps First

This post from Melissa Coates is a good example of not thinking through your product architecture early on. The short version of the post is that Power Bi Desktop is a better place, perhaps really the only place, to author your Power BI Reports. Melissa covers a few potential issues while using the Power BI online editor, but the big item is that you cannot easily prevent conflicts and track versions there.

Power BI started as the online service, with the Power BI Desktop tool seeming like a bit of an afterthought. At least from the marketing perspective as all of the early demos and media from Microsoft was about the online report service. Since then it’s evolved to become the primary way we will build reports in SQL Server moving forward, which I think is a good move.

While I can understand the developers at Microsoft not really thinking that the service would on-premises and maybe not considering the need to provide some text format for the reports, I can’t understand why they didn’t learn from the Integration Services team and realize that binary versions of programmable items don’t make sense. We need a format that can be easily versioned, and maybe more importantly, stored in a VCS and diff’d in a way humans can understand.

When building a format for storing code, please consider the need to work in a team and version the changes made. This means any format should not be a) binary and b) difficult to decode. Separate visual elements from logical elements and ensure a text version of this can be examined by developers. You can use XML, JSON, YAML, or any other text based format, but choose something that makes sense. Even if you add your own extension, ensure that standard tools that work with code can use this.

I do know the PBIX format is a ZIP file, but zip files don’t easily integrate with a VCS. We could use hooks to extract/rebuild out files on commit/checkout, but that’s cumbersome and silly. I’d rather that the PBIX were a folder with the files inside. Users, including my Mom, can zip a folder and email that if needed. To me, that would have been a better structure from the beginning.

Microsoft is supposed to be a company providing platforms that we build upon and use in our work. The decisions for the Power BI service seem to be poorly thought through with that in mind. I’d urge them to create a baseline set of rules for future products that consider DevOps, teams, and the need to track code.

Steve Jones

June 22, 2019
Practice Those Scripts

I’ve written lots of scripts that were deployed to production. I’ve often had another set of eyes look them over, and still, we made mistakes. In fact, a recent Salesforce outage was blamed on a poorly written database script that gave users more rights than they should have gotten. There wasn’t an actual outage caused by the script, but since customers might have been able to see data and change from other customers, Salesforce took its own service down to prevent anyone from doing so.

I’m a big fan of DevOps, and certainly including the database in a DevOps process to build a better software development flow. Part of that is ensuring that you can deploy by practicing the act multiple times. In a database world, this would mean that we run a script not just on a development server, but on a QA server, on a staging server, on any other environment we can find to practice and test the deployment. At that point, we should be confident of execution on the production system without issues.

Good in theory, but sometimes you can’t easily test scripts in intermediate environments. I think changing security is a place where it can be hard to actually test things, especially if specific accounts are referenced that might not need or have access in that environment. Certainly some data changes might be easily be tested in intermediate environments, especially when these refer to configuration differences, like email or messaging systems.

In this case, I suspect the “access changes” were data changes that updated values in certain tables in the Salesforce application. In that case, why wasn’t this tested? A restore of production to a staging environment would allow developers to test their script. It’s not multiple executions on intermediate servers, but it is better than nothing.

I’m sure many of you have had the need to execute scripts to change data, alter permissions, or something else in production. Could the same thing that happened to Salesforce happen to you? What precautions do you take, or what would you recommend to prevent this type of issue. Let us know today.

Steve Jones

Listen to the podcast at Libsyn.

June 21, 2019