Voice of the DBA

Category: Editorial

  • Ransomware vs. AI

    Ransomware has been a growing and shrinking problem in the modern world. Every time I think that some new defenses and protections are preventing ransomware from being a problem, I see another issue. Recently, I saw Subway got hit with with an attack and a few friends have recently noted their companies were restoring systems after a portion of their network was locked down.

    With the advent of Rasnsomware-as-a-service, where criminals deploy software and then sell access to others, better detection and protection become more important. As with any software, criminal human operators will use the ransomware software in different ways. That means that we don’t necessarily have a simple threat that can be easily programmed against with anti-virus technology.

    Microsoft has been using AI technology to help them track and combat ransomware campaigns. Since there are similarities between how ransomware is used by different individuals and how it appears in systems, AI technology can be helpful here. There aren’t the same simple signatures on files that we’ve seen in the past with viruses, but rather more complex patterns. Humans might discover how ransomware appears in their environment with lots of knowledge on what their network ought to look like, but this pattern matching across many different networks and organizations is something that AI/ML might do quicker and at scale. Once successful, ransomware can be hard to recover from, so early detection is important.

    In the article, it seems that Microsoft is capturing lots of traffic and analyzing it for patterns, with multiple types of anomalous activity, and then aggregating this across devices to guess whether this is an attack or not. In some of their testing, they find the ability to stop an attack with only a few percent of assets getting encrypted. That’s not perfect, but better than finding 90% of your nodes are encrypted over morning coffee.

    I suspect this is just the latest escalation in cyber attacks and defenses. I’m sure that hackers will come up with new and novel ways to cause problems, but I do think that this is a place where AI, especially ML, technology can be useful to provide better security. I also think that database technology, especially graph queries, is particularly helpful here. I hope we learn more about how they are building protections as I think this is likely a great data analysis story.

    Once again, the database is critical to making software better because all of that data has to be stored and queried somewhere.

    Steve Jones

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

  • Serious Software Glitches

    Recently Robert Sterbal pointed out a podcast to me. This link is for Apple Podcasts, but it’s for the Journal, which is on other platforms (I listened on Spotify). It’s the story of a computer glitch in UK post office software, which resulted in quite a few local postmasters being criminally prosecuted, many convicted, and even a few committing suicide. It’s a sad story, and it’s complex, but there are some technology-related elements.

    First, the overall story is Fujitsu sold the UK a point-of-sale system for post offices. There was a computer glitch here, which incorrectly calculated lots of totals and showed postmasters owing more money than they should. They were upset, called support, got nowhere and many were liable for paying money they didn’t owe. The UK postal management hid information about the widespread nature of the problem, while prosecuting many local postmasters. Fujitsu support didn’t disclose to callers how others were experiencing this same issue. This also coincided with a (an unrelated) law that changed saying computer systems were presumed correct and anyone accused of a crime had to prove the computer was wrong.

    Without a doubt, there are human failings here with support people, management, a vendor, and likely others. I don’t want to minimize those, and I do think quite a few people involved, especially management, should face charges. However, since this is a database-related site, I wanted to focus on the code quality here. I don’t know the exact nature of the calculation issue, but there is clearly a bug somewhere in the system. Do we, as technologists, think we’re better developers or database people than those at Fujitsu? Would we not produce calculation bugs that might be hidden in aggregations? I have to say that I see this stuff all the time and not just in development. I run into these bugs in production, and I think this is often because we don’t embrace enough testing. I see this in all sorts of systems, with developers of many different experiences.

    While application developers have gotten very good at unit testing, that same habit hasn’t gotten as widely deployed among database developers. What’s more, I often find that people writing aggregation queries for reports often use lots of live data, and they don’t write tests or even perform calculations to ensure complex formulas are correct. If you’ve ever done complex aggregations in SQL or DAX, you might find there can be strange effects from filters, from NULLs, and even from the way a window or range of rows is processed. It’s easy to say that a report on 1,000 rows of data out of 100,000 is roughly correct with some total, when you haven’t actually verified that calculation manually.

    I certainly think Fujitsu deserves a lot of blame in this case. Ultimately, they are the source of issues. Those that covered up the problems, both at the UK government organization and at Fujitsu should be prosecuted and held liable, but the programmers and testers are also at fault. They didn’t do a good job testing their software, and worse, didn’t do the job of tracking down the bugs, finding issues, and correcting them. I hope those issues are fixed now, but they weren’t addressed promptly as this situation took place across years.

    I often work with companies trying to build software better, but I find it hard to get them to test database software. I know the testing frameworks are immature, the tooling is poor, and honestly, too few of us have a good test data management process in place. However, we can start to learn to add unit tests to our code. At the very least, we ought to write some repeatable, automated test when a bug is reported. Clearly, in that situation, we (as a team) didn’t write good code if a bug was found. Either because of tech skills or we didn’t get the specification correct. In either case, we need to improve and automated tests to ensure we don’t make this mistake again are a way to start getting better.

    Much of the software I’ve worked on isn’t directly related to affecting human lives. That’s probably true for most of you unless you write software that controls some sort of vehicle movement or medical device that dispenses care or drugs. My son works on rocket flight software, and he takes that seriously since people will be riding those, but for most of us, the work we do isn’t critical to anyone living or dying.

    However, this story shows that we might still affect human lives. We ought to take that responsibility seriously and ensure we are doing the best job we can to produce quality software. Having some testing (and good test data), is a way to double-check ourselves and our team. It’s worked well to raise the quality level of mobile software dramatically. We database people ought to learn from that and adopt better testing.

    Steve Jones

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

  • How Often Do You Update SSMS?

    I got a message recently that SSM S19.3 is out. I am wary of major versions, especially with a few add-in tools, but I have tended to try and update SSMS regularly when it patches, which is about once a quarter. As I checked my desktop, I saw I was still on 19.1 (my laptop was 19.2), so I downloaded and updated both machines.

    I wonder what the rest of you do. I know many of you have corporate rules and restrictions and you may not be able to update regularly, but in many places, I’ve worked, once software was installed, I could update it. Are you doing the same thing out there? Let us know in the comments, or better yet, include the version you are running.

    Some of you might use VS Code or Azure Data Studio as well, which have more of a nag screen to update. I find myself updating ADS/VS Code more often, though not with every version. I tend to prefer software working and am less interested in gambling on potential issues unless there is a bug I need fixed.

    My work at Redgate brings me into contact with lots of customers, and I find a number of them view software like I do. They prefer working versions over the latest and greatest. In fact, we’ve added options to many of our products so people who want the latest have a frequent updates channel. Others can get notified less often; we even have a 7/14/28 day switch in Flyway Desktop.

    I tend to prefer updating a week or so after most releases, though depending on how busy I am, I might end up a week or two after the next release. I want to see if there are reports of issues or problems before I upgrade. However, even that is more often than what I hear from many others. Lots of people want to upgrade every few months, often quarterly, to minimize any disruptions to daily work.

    Are you the same? I’m interested in SSMS, but what about other tools as well? Is this the same for patches of Windows/SQL Server? Do you upgrade some software more or less often? Let me know today with a comment in the discussion.

    Steve Jones

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

  • Mind Over Milkshake (Thoughts Matter)

    Last year I went to THAT Conference in Wisconsin. It was a fun event, very community and family-friendly, and I enjoyed it. So much so that I recently went back to the Texas event in January. It’s more developer-focused, but it does have some data related sessions. I recommend this conference if you’re looking for some fun training and want to combine that with a family vacation. Your kids will love it.

    In any case, I watched a keynote talk that referenced an NPR article, called Mind over Milkshake. It’s an interesting look at how the food labels affected people’s bodies. It’s not definitive and I wouldn’t make any drastic changes based on this, but it is an interesting read on the idea your mind and thoughts can influence your body. I’ve heard about the effect of placebos in the past, we well as attitude on healing, so this makes some sense.

    I don’t know to what extent this would change how I manage my health and medical care, but I do think a similar idea is important in my work with databases. I see lots of people who have a negative attitude towards learning, change, or even adopting new/better ways of doing things. So many people, whether workers or management, get stuck in a rut and want to stay there. Or they don’t feel empowered to change. It’s why I find the cultural part of DevOps way more challenging in organizations than the technology part.

    In some of my management positions, I’ve often challenged workers not to bring problems, but to find solutions. I want them to view the issues we face, the things that go wrong, as opportunities to improve, not set-in-stone problems that we gripe about. I know many chronic issues recur regularly. I also know that in any organization, there can be resistance to change, and a “we’ve always done it that way” attitude. However, adopting that for yourself is how things continue to linger on (or get worse).

    Your mindset can make a huge difference in how you approach situations, including how much stress you feel from the environment. I don’t advocate change for change’s sake, but I do look to critically evaluate if something works well or can be improved. I’ve also learned to ask for change and that being turned down doesn’t mean that nothing changes. Or nothing ever well, or even this thing won’t change. It often means that this particular thing can’t change, or that it can’t change now. I have also learned to separate this request from another request for a different change. The key is often to analyze the solution, prepare a good reason why something should change, and present this un-emotionally.

    I know when I approach things as an opportunity life is better. Even if I don’t make a difference, or I don’t like the outcome, I feel better about it. Try it, and you might feel the same way.

    Steve Jones

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