Voice of the DBA

Tag: development

  • The Types of Changes

    I would guess that most of you reading this piece are database professionals in some sense. Either you work on databases as a developer of some sort (app and/or database) or manage them as a DBA or sysadmin. In working with databases, I’m sure that you need to alter object structures. Maybe you perform a lot of changes, or maybe you just alter the schema occasionally, In either case, you need to make decisions on which changes to make.

    I would guess that the most common changes are altering stored procs/views/functions. Often these are to handle new or updated business rules. In those cases, how do you ensure you haven’t added regressions? I would hope you had a set of test scripts to verify that your results are correct. If I were very optimistic, I’d hope that you had a set of automated tests, such as those you could write with tsqlt or the Microsoft Unit Test Framework.

    I’m sure many of you add new columns to tables. What are these types of changes? Capturing new data? Splitting existing columns or de-normalizing data? In these cases, are you re-examining the data model to ensure the changes make sense?

    Maybe even more importantly, if you are capturing new data, do you consider vertical partitions for the table? This can be a good way to separate out data in columns that isn’t queried as often. Despite most recommendations, lots of developers will still SELECT * from tables, which can flood the buffer cache with data that isn’t often needed by an application. Vertical partitions can reduce the impact of these types of queries.

    For those of you that add new data, do you consider using different data types for your new data, or do you have a standard string/numeric/date type that you use? SQL Server (and other platforms) often have a variety of different data types that can save space, while still accommodating the requirements for a change. As an example, many people use datetime as a standard type when they just need a date. In this case, we could save 5 bytes per row by choosing a more appropriate type.

    If you add status values or flags, have you considered bit storage for your values? Whether you use the bit datatype or bits in an int, you can dramatically save space in each row. Newer versions of SQL Server include T-SQL functions to help you work with bits, though I would hope most developers can handle simple AND/OR logic against an integer to test if a particular bit is set.

    Most software under active development has a need to continually alter and modify the database to meet new requirements. While lots of us have experience in making these changes, and do so often, once we make a change it often becomes enshrined in our production system for years. I would hope that we feel confident in refactoring our code as needed, but the reality of what I often see is that people are loathe to change the database for fear of breaking our software. Even when the refactoring actually improves the performance of the application.

    I’m curious what types of changes you often make, and how you go about deciding on the specifics of the change. Let us know about your approach in the discussion. I’m especially interested in how you might handle the various options for changes. Do you assume defaults or are you explicit in how you structure your ALTER code?

    You might have a novel way of examining requests and implementing changes that can help others in learning to ensure they make better changes in their own system.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • A Test Client for Zero Downtime Deployments

    I’ll be at VS Live in Las Vegas this March to discuss zero downtime deployments. If you want to come and join me for this session, or any of the other great ones, register today and save $500 with the promo code “Jones”. You can use this link to register.

    To simulate the effects of deployments on a workload, I built a small client. It’s nothing great, and likely some of you will laugh at my C# skills, but it works well enough. It’s a simple Windows form application that writes to a text box. However, it’s valuable to determine if there are any issues when you’ve made a deployment.

    This post looks at the rough design of the client. Code is available in this repo: https://github.com/way0utwest/ZeroDowntime

    Using WPF

    I wrote a small app a few years ago to test and present on Always Encrypted. This was a basic WPF app that added the proper values to the connection string for Always Encrypted and let you query encrypted data (or not).

    Like all mediocre developers, I copied and pasted that project into a new folder and set about modifying it. In this case, I set up a loop that continues to run and execute some database code, essentially using this loop:

    while (iRunQuery > 0)
    
{

    I set this value to 0 initially, and when a button is clicked, it’s 1. This then runs a bunch of lines to decide which DB code to run. I’ve mostly made this stored procedures to make it easier to adjust demos without touching C# code.

    It’s not pretty.

    At the bottom, I have this (outside the loop)

    Application.DoEvents();
    
System.Threading.Thread.Sleep(100);

    This is designed to catch me clicking a “stop” button that will set the variable back to 0. I added the delay because otherwise this runs a bit fast.

    I have a few option buttons that adjust what code I’m calling, so I can simulate toggling feature flags on and off. I also log results to a window so you can see them, and I catch errors and log those. Errors are also counted, so we can see the impact of “non zero-downtime” changes.

    It’s not a great example of software, but it does work.

  • Minimize Typing

    It couldn’t have happened to a worse set of people, and I’m glad it did, but I’ll still take a positive lesson out of this. A hacker sent a typo in a command to a botnet and lost control. That’s kind of funny, and I’m glad it happened. The less botnets, the better, IMHO.

    The coding in this software didn’t have good error handling, which is a lesson in and of itself. Overall it seems many developers do a good job of error handling, but I still encounter more pieces of software that allow problematic input than I’d like. While we don’t have great error handling in T-SQL, you can make some checks, and you should.

    That’s not the big lesson for me. The bigger lesson is that we ought to do less typing in much of our daily work. The last decade has had me work often with companies looking to implement DevOps software pipelines and driving automation wherever possible. We want to limit the chances humans can make mistakes, which means we want to limit their typing. Or clicking, as is the case in much of today’s software.

    Instead, we want to ensure all our code or commands are reviewed by someone, they are submitted to an automated pipeline, and they are validated or practiced on some system ahead of production execution. We ought to do this for no other reason than we want to ensure we have an audit trail, but preventing typos is good as well.

    I don’t know if you can completely get away from typing, but we can reduce the number of human error mistakes if we include some static code analysis (including for commands), some peer review, some sort of unit testing, and pre-production deployment. A lot of mistakes I find are fairly simple ones. Common human error that occurs because we’re busy, we’re stressed, we’re moving too fast, or we just miss something.

    Use the computer for one of its strengths. Tediously checking the simple things that humans do wrong.

    Steve Jones

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

  • The Programming Languages We Use

    Many of you reading this probably work primarily in SQL. Even if you are a developer whose main language is something else, you write a lot of SQL. Even if you have an ORM writing the SQL that goes into production, I bet a lot of you are writing queries against a database to check that the data coming back in your application is correct.

    As for me, I mostly work in SQL, with PowerShell and Python being second and third. I tried R for a while, but I think Python does everything R can do and it’s much cleaner. I find R very cumbersome. I rarely write C# or experiment with anything else, but that’s the nature of my job. PowerShell is important, as I do a bunch of DevOps and PoSh is a good choice to work with on the command line for gluing processes together.

    There was a set of the top articles on programming languages from 2022 that I saw recently. I found it interesting to see what was popular. The top one was about Python being the most popular, but it shouldn’t be. This one feels like clickbait, and I find many of the conclusions not making an argument against python in a meaningful way.

    There are some other links on the “hotness” of various languages. I think these are clicked on as many developers are just curious about what others are doing, and what they might experiment with. While I like curiosity and experimentation, I do think that many of our important systems in organizations need to be built with mainstream technologies. Support and staffing are a challenge, and while Golang might be great, finding people to read and code in it is hard. I don’t know how to balance the growth of new tech with the safety of old tech, but I wouldn’t stray too far from the mainstream for anything important.

    I do find it interesting that COBOL makes the list. I know there are still lots of COBOL systems, and while there aren’t a ton of jobs, there are jobs and little competition. If I were 10-15 years younger, this would be tempting. Of course, I’d have to be willing to adapt to the jobs, but it is tempting. I know a few people making well into the six figures because of COBOL jobs.

    It’s nice to see SQL is one of the top 10 languages in use, according to this survey.. It was #9 in 2021 and #8 in 2022. I don’t know it grew in popularity so much as assembly declined compared to other skills. I certainly can’t see SQL going away, but it’s not as popular, clickbait-y, or exciting as other languages. Instead, it’s a core, required skill for any serious software development. Whether you use relational or NoSQL databases, likely you need some SQL skills.

    Steve Jones

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