Voice of the DBA

Category: Editorial

  • The Craziness of Code

    Every once in a while I find a fun post that makes fun of programming. I think because the author is struggling with some piece of code, they write about the craziness of software development, often with an analogy that compares building something in the real world to how software programs get written. Here’s one, with a bridge construction analogy, that degenerates into a rant on the disparate ways in which different programmers work together and how the entire world of the Internet is held together in a constant patch-fix loop.

    I do think that most large software applications have a decent amount of inconsistency. I’d suspect most large databases (in terms of object count), also have a variety of techniques, tools, and standards in use. That’s because the tooling that we use to build things change. We don’t have the constraints of the real world, which is why we could have a stored procedure using a subquery to calculate a running total and another one that uses the OVER() clause.

    We can be more consistent in SQL because the language doesn’t change as quickly, but in application code, frameworks, techniques, and even libraries come and go constantly. Different developers have favorites, and without strong controls that limit the experimentation of your staff, likely your code base has a variety of styles and algorithms that do the same thing. Static code analysis, linters, and unit tests can help ensure your code runs effectively, but it still likely has sections that no one wants to touch.

    This flexibility can be maddening to developers, especially when they start a new position. At the same time, each of them probably thinks there are a few places where they think they can write the code better, and they will attempt to do so, even if they are creating more inconsistency in the project. Multiply this by the 40% of programmers that are willing to make changes without trying to mimic how others write code and pretty soon the architect (if you have one) has no idea how anything works.

    In the real world, we can’t easily get things to work together if they are disparate. We don’t suddenly change from framing one wall with a 2×4 to another with a 2×6. We might use different size nails or thinner brake pads or any of a number of other changes from one place to another, but often those differences don’t matter. At least not enough that we find houses and bridges constantly falling down. Of course, the workers building those structures follow a set of plans and standards, using similar tools and techniques.

    That doesn’t happen in software, where you are often not limited by external constraints, but often your imagination. We can construct things in software that no one ever thought of before, we can grab materials, i.e. code from Stack Overflow, and use them in our system. Often builders don’t get to source materials from wherever they want, which inherently limits the variation between parts of the structure. That being said, lots of little mistakes get hidden in construction projects, just like they do in code.

    I don’t think programming sucks. It’s always an interesting challenge, and I do think that when you have a group of developers that care about their craft and are trying to work together to solve a problem, we get some amazing software built and deployed. It might not be consistent or pretty, but often it works very well. If it takes regular maintenance, that’s fine. It’s an ongoing challenge that keeps us employed.

    Steve Jones

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

  • Scripting Makes Mistakes Easier Than Ever

    A number of you likely use Atlassian products like Jira, Confluence, Opsgenie, or something else. You might have been affected by a large outage they had (post incident blog, Company Q&A, TechRepublic report) recently which lasted at least 9 days. I don’t know if all customers have their data back and are working, but this was a surprisingly poorly handled incident according to a number of reports from customers. There’s a great write-up from the outside that you might want to read.

    The bottom line in this issue is that Atlassian looked to deactivate a legacy product with a script, but they apparently didn’t communicate well among their teams. The script ended up using the wrong customer IDs and also marked the sites for permanent removal, not temporary removal (soft delete). While they supposedly test their restore capabilities, they weren’t prepared for partial restores of subsites. I’m guessing this is likely a partial database restore, which many of us know is way more complex than a full database restore.

    Leave aside the issue of a software-as-a-service (SaaS) company failing their customers, and the lack of communication with customers. The more interesting thing for me is the challenge of poor coding and communication internally. Clearly, the project to deactivate their legacy app wasn’t well planned or tested and the code used was probably executed at too wide a scale initially.

    When we deploy code changes to a large number of items, we want to test them at a small number first. Whether we are deploying to multiple databases, against many customers, or different systems, a standard method of making changes at scale involves working in rings. Azure DevOps describes this in docs, and they actually use rings to change the platform. We used the same pattern 20 years ago for software and database updates to many systems. We would internally deploy to a few users to look for issues. Then a week later we would deploy to a small number of systems to check for unexpected issues. Then typically to most systems in the third ring with a fourth ring a week later to catch up stragglers that needed more time to prepare.

    I find many customers, especially those with sharded/federated databases or many systems unwilling to spread out deployments in this manner. Often they yield to pressure from business users to ensure everyone gets the same update at the same time. I would never recommend this approach as we need to ensure we are looking at scripts in a controlled environment, or even two, before we deploy things widely. I’d be even more cautious about one-off administrative scripts that might make a change similar to the one Atlassian attempted. Those are often not seriously tested enough.

    At the very least, any of us working with multiple customers in a single database or in multiple databases ought to ensure we can backup and restore a single customer, but more importantly, can you restore a group of customers. If you make a mistake like Atlassian, which scripting allows us to do extremely rapidly, can you recover a partial set of data? Many of us don’t test this, but that’s likely something we ought to consider when we work with scripts that are designed to only change some data. Most of us don’t experience complete failures, but partial ones, usually because of human error. We ought to know how to deal with these situations.

    Steve Jones

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

  • Using T-SQL over PoSh

    Why would you use SSMS/T-SQL over PowerShell (PoSh)? When is T-SQL directly a better option than PoSh? That’s a question I ask myself regularly as I see articles and blogs that discuss how to accomplish a particular task using one tool or the other. There is plenty of overlap in the capabilities for each language when it comes to working with SQL Server, so this is a decision I think about regularly. This is especially true if you use dbatools.

    There also appears to be a bias towards one tool or the other for each individual. Many people traditionally have used T-SQL to accomplish most database tasks, and they tend to always look for a solution with a script in SSMS. Others are excited by PoSh and I have seen plenty of questions on the SQL Server Central forums asking how to structure their code in that language. In both cases, there is no shortage of people that argue that you should use T-SQL instead of PoSh or vice versa.

    Personally, I think that there are lots of development items where I’d use T-SQL. For any sort of schema change, most data changes, and a lot of database administrative tasks, I would use T-SQL first. Trying to alter a table in PoSh vs. T-SQL doesn’t make sense to me. Now the deployment of these changes is something where I’d use PoSh to run the T-SQL, which is what we do in the Redgate Deploy tools.

    I was with a panel recently and all the individuals on the panel said they wouldn’t use PoSh over SSMS for much of anything. The exception is where a task involved working with files or folders in the file system. PoSh excels here, and for work that might delete old files or move files from one folder to another, PoSh is preferred.

    I think the defining line for me is whether I need to accomplish a task inside of SQL Server or outside of it. When I cross instances or work with the file system, then PoSh is my preferred method. I can use xp_cmdshell or a linked server as well as anyone, but I prefer not to. Anything inside SQL Server, usually has me reaching for SSMS instead of VS Code.

    Steve Jones

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

  • A Third Space

    It’s 2022, two years into a pandemic, and it finally seems that many things are returning to the way they were in 2019. Life outside of work is fairly normal for me, and I have a number of trips. Work is starting to pick up and we’ve seen a few conferences and SQL Saturday events pop up on the schedule.

    Work is still mostly remote for many technology workers. Not all, and some companies are starting to require workers to come back to the office, part or full time. My son got a job as a software engineer and he has to go to the office every day for six months. After that, if he proves himself reliable, he can work at home 2-3 days a week.

    For many tech people I talk with, there is a still a mostly remote atmosphere. The feelings about this situation vary dramatically from person to person, and from role to role. I find as many managers love remote work as hate it. I find more tech people prefer remote work, but not all, and certainly not all like working at home. Depending on your environment, and how many others might be at home (and their ages), you might find remote work more stressful than commuting to an office.

    A friend pointed me to an article on someone that doesn’t love working at home and needs a third space. My friend feels the same way, as isolation and quiet can be hard to cope with for long periods. As much as I have loved working from home for nearly 20 years, I haven’t done this for 20 years. I’ve traveled regularly and when I have long stretches at home, I usually spend some periods in a Starbucks or another space just to be around people. Talking to my dogs isn’t bad, but they often don’t talk back in a way that satisfies my need for background noise.

    My friend’s company has a subscription to WeWork, which allows remote employees to get some social interaction, or just ambient noise, as needed. We’ve experimented with this at Redgate as well, and while I don’t know I’d go to WeWork over a coffee shop or restaurant, I do appreciate the ability to have a space where 5 or 10 of us could gather in an ad hoc manner periodically. If I had more employees in Denver, I would look forward to meeting them a few times a month.

    For those of you working at home, do you want a place to gather on a regular or periodic basis? Do you miss interacting with live people? Or do you love being alone at home. I certainly like the latter many days of the week, but I miss interacting with people on a regular basis. I look forward to regular trips to Redgate offices and more events where I can talk technology with people whose hands I can shake.

    Steve Jones

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