Voice of the DBA

Category: Editorial

  • Practicing (Annoying) Better Security

    At Redgate Software, we’ve been looking to “level up” our internal security game. While we have had very good security during the 16 years I’ve been there, there have been a few security issues with our products. The speed at which we address things, as well as the communications with customers, has impressed me.

    We’ve had almost no problems with our internal systems, unlike a few other places I’ve worked. We haven’t had the phishing/virus/breach/ransomware issues that I’ve seen at other employers or heard about from friends. I do think our IT staff is diligent and careful, as well as forward-thinking. It also helps that we’ve had a relatively small employee staff that worked in physical offices for most of our existence.

    Recently, we’ve been on a security push to tighten up the way we deal with systems. As we grow our staff, and as we add more offices, there is a recognition that our attack surface area is growing. We also find more and more people using non-Redgate-owned devices. This year we’ve had a series of policies rolled out that we are supposed to adhere to in order to ensure strong security, as well as compliance with data privacy rules such as the GDPR.

    One of these is a bring-your-own-device (BYOD) policy. For years I’ve used my personal mobile phone for Redgate, with a few settings enabled to allow a remote wipe if I lose it. However, I’ve also had a personal desktop that I use for daily work in my home office. I’ve never enabled a lock on this as my wife occasionally uses it to get a picture or other document. Or send me something I forgot to sync in the cloud.

    Part of our new policy is that I need to enable a lock on my desktop, as there is privileged Redgate information on there. Not much Redgate data, but the machine does connect to our business OneDrive and SharePoint systems. This lock should be a 2-minute timeout, which means that I come back to my desktop after coffee, laundry, or something else to find it locked. After years of always locking my desktop in corporate offices, I somehow find this more annoying. Especially as I’ve gotten used to rarely typing my 15-character password. I mess this up regularly and have to (more slowly) re-type my password a few times.

    I know this is better security, and I am always conscious of locking my laptop in our various offices when I go in. However, I find it annoying at home. Especially when I pop in away from work to look up something on the Internet. I keep telling myself this is good security, and good for both Redgate and our customers. I’m still annoyed by the change, but I know it’s for the best. Like many who work in organizations, I’ve been lazy about some security aspects for years, and the change is a disruption. I’m sure some of you feel the same way about the rules and protocols that your employers have implemented.

    You’re not alone in desiring a more convenient workplace, but security is a series of overlapping measures that work together to protect data. Practicing and adhering to good security is a lot like a daily backup. Most of the time it’s something never need, but when there’s an issue you’ll be glad you followed the process that day.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • The Code Review Checklist

    Early in my career as a developer, I was required to follow a formal process to get the code I’d written deployed to production environments. Each time I’d written code, I had to document what I’d tested and then print out the relevant sections of the codebase. I needed to find two other developers to spend 10-15 minutes with me in a meeting, going over my code, each of us with our own paper copy. Almost like a dissertation, I had to answer questions and defend my work, with problems being marked on paper for me to go fix.

    Over time, I learned that different developers reviewed code in different ways. Some spent more time on standards and formatting. Naming and the visual structure were more important to them, so if I wasn’t confident in my work, I’d pick them and spend time ensuring the formatting was correct. Or sometimes, I’d mis-format it, so they would tell me to go fix that and not look at what the code actually did. Others were better at examining algorithms, and I often used them to help me learn, with them digging into my logic and helping me understand whether I’d included enough error handling, considered edge cases, or written code that performed well.

    Often the mood I was in, and the pressure to meet a deadline might have me leaning one way or the other. Of course, there were plenty of times I just had to go with whichever two developers had time to review the code.

    There was an inconsistency in code reviews, and I breathed a sigh of relief in future jobs where we didn’t formally review code. In fact, in quite a few positions where I wrote C++, VB, or FoxPro/Clipper, my code was never reviewed, nor was there formal testing. Other developers and I often had to rework sections of code regularly, which led me to implement better testing of my own code. I didn’t adopt formal frameworks for some time, but I did save off test scripts for code in our Visual SourceSafe repository to ensure I could test code.

    These days pull requests and code reviews are commonplace, at least among many software developers. Not so much in the database world, but I do find customers that believe in testing and I regularly preach this to others. I’m also glad that Redgate has built-in static code analysis and linting into its products, though I wish we had better (and easier) unit testing support for database code.

    For those of you out there writing code, do you go through any sort of code review process? Is it consistent? Is there a checklist of sorts? I have found that different people have their own internal checklists, but I rarely see anyone with a more formal checklist, or even a set of lists for what to check in different types of code. Even in unit testing, I don’t often see people approaching their tests in a methodical manner.

    Checklists have been shown to be beneficial in the healthcare field where staffers are overworked and handoffs are frequent. Using a set of checklists can improve patient outcomes. I suspect that a good set of checks for code might do the same thing. However, I find that a lot of database developers are reluctant to adopt any formal testing practices.

    A view that reminds me of .NET and Java developers in the early 2000s.

    Do you have a checklist (actual or mental) that you go through for your own code? For anyone else’s code? Or do you think formal testing of SQL code is even worth the effort. Let me know today.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • You Always Have a Software Pipeline

    I was working with a customer recently and they said they didn’t want to set up a pipeline. Somehow they wanted magically to see changes that developers made to their database appear in their production database without making any effort to build or configure a pipeline in a tool like Octopus Deploy.

    As I was thinking about how to find them a solution they would accept, I realized that I’ve always had a pipeline for software. Early on, this was a copy of all the files in a folder sent to the client for Clipper and dBase apps. Later, my pipeline was copying around all the VB6 files from a .zip file and executing a few SQL scripts that were sent to me in emails.

    At one point, I had developers just tell me the software pipeline was them copying files to a server while I had to move all the changes from the QA server to production. I didn’t like that one for two reasons. One was that the developers would sometimes forget to move a change from dev to QA, despite claiming they’d tested the application. Two was that the developers sometimes moved too much to QA, and had changes to the schema that they hadn’t finished in their app. SQL Compare couldn’t help me in either of those cases.

    Over the years, I learned that a slightly thought-out pipeline is better than nothing. I wrote my scripts to simulate what Flyway does today, executing the scripts I’d placed in a folder. I added logging that would capture output results and save them for later review. I learned how tools such as Team City or Jenkins could execute the same thing I could but in a more repeatable, reliable manner. After all, my reliability at 4:55 p.m. on Friday isn’t as good as it is at 2 pm on a Tuesday.

    DevOps is about learning and taking advantage of tooling. Look at what works in your pipeline and find repeatable, reliable ways of accomplishing those tasks without humans doing more than clicking a button to approve something. It’s about using your creativity to improve the process and codifying it in a way. It’s about being better than we were last week while ensuring the process isn’t dependent on any particular person. After all, I might leave for vacation.

    Or find another job.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • Modern Development

    I was browsing online the other day during a break and stumbled upon an article on the modern architecture stack for a startup. It’s not a bad read, and as someone who’s worked in startups, it’s interesting to see what others think. As with many of these articles, it has a lot of practical advice, and it’s also not relevant for many of us because we don’t often work in greenfield development.

    However, we could view adding something new to our application as greenfield. It’s not completely the same, but there are similarities when we start a new feature that doesn’t exist anywhere. Some of their advice, like docker-izing everything, won’t apply, but there is one thing that does apply: data.

    There is a quote, which I really like: “…what is the point of an API running on top of an empty DB? Manually entering necessary data shortly leads to depression (and the risk of increasing the duration of development cycles). Hence, we prepared a curated dataset that was inserted into the local DB to be able to play with.”

    That’s similar to the advice I give when speaking on DevOps. Invest in a curated data set for developers. As this group learned, you can start to use this in testing, CI, etc. and it makes life better for developers. Heck, if you’re using version control and you create a data set for yourself, save the insert statements, put them in a folder, name the script for the table, and share it with others.

    It’s a small change, but it’s one that pays big dividends over time. If others use your INSERTS, and you use theirs, all of a sudden you have a good curated set. If you maintain this as you find bugs or strange things customers enter into production, you don’t need a restore of production; you can add more data and run just those statements. Heck, if you add columns, add some data. You’ll do that anyway to test your new column so maintain this as a script. Or update your data and use SQL Prompt to create INSERT statements for all the data and replace the entirety of your data script.

    A big part of development is maintaining data. While there are virtualization solutions, like Redgate Clone, those can be cumbersome and expensive. They do solve problems, and they might be a good fit for your situation. However, I really like smaller sets of data that duplicate our problem domain. For those of you dealing with time-based problems, include scripts that “update” dates and times to simulate problems from today or yesterday rather than last week. The limit is the creativity of your team, and as you maintain this dataset, everyone benefits from small changes made over time.

    Version control has been a boon to sharing software projects between developers, but underutilized as a way of sharing data as well. Add some data scripts to your project and you might be surprised how much easier it is to work as a team.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.