Voice of the DBA

Category: Editorial

  • The Challenge of Deleting Data

    We collect a lot of data in our databases. Not as much in bytes as a lot of the video/audio/TikTok/Instagram sites, but still enough that many of us are constantly adding storage to our systems. All this data is not only a challenge to manage, but it also means that we are regularly dealing with query tuning issues. Better code, indexes, and more become regular challenges with large volumes of data.

    I am a big fan of trying to reduce the data you manage where possible. Archive, delete, remove older data, do something. This not only makes your systems easier to manage and improves performance, but it reduces your risk. Any PII data you have that might store is an ongoing risk in the event of a data breach. I don’t pretend this is easy to do in any way, but it’s a good idea.

    If you can remove data (or must because of a regulation like the GDPR), how do you ensure that data is deleted? Most of us know how to submit a DELETE statement, but that just removes the data from an online system. What if you restored or recovered this database tomorrow, would you remember to delete the data again? What about losing a copy of the data or log backup? What about older dev/test systems that were refreshed from production? The data might be in there. If you work through the possible problems, deleting data from a system isn’t as simple as you might expect.

    This might be even more complex in the age of cloud computing, where we don’t control the hardware for primary systems, or for backups. There is an article on deleting data in the cloud that talks about the government standards that require that you not only delete data, but that you overwrite the physical hardware to ensure it can’t be recovered. This still doesn’t address backup systems, but it does help to clarify that many of us might start to demand cloud vendors not only de-allocate the disks we use (or the backup storage), but they also overwrite the storage with zeros.

    Data security and the risks of not taking this seriously is becoming a bigger issue all the time. I don’t know that poor security will cause your organization to fail, but there can be significant costs and possibly reduced employment opportunities. While you might not want to be overly paranoid or concerned about every possible issue, it is worth asking questions of vendors, working through likely scenarios, and trying to quantify risk.

    More and more systems are regularly under attack from malicious groups, which means we want to minimize simple mistakes, reduce human error, and limit the exposure we have from the data we have by storing only the data we need.

    Steve Jones

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

  • 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.

  • Knowing Your Technologies

    A while back I ran across a blog post that talks about the difference between database snapshots and database backups. There are certainly some similarities and some differences, as well as an overlap in the places where you might choose to use each of these technologies. Both might be useful as a way to recover from a bad code deployment, but both aren’t necessarily helpful for a DR situation where the primary server has a catastrophic hardware failure.

    It’s often the case that we learn only a bit about some of the technologies in SQL Server. That’s understandable as the platform has grown very complex, encompassing a vast array of technologies and options. There is often some overlap between them and possibly different places where you might choose to use one or the other to solve a problem.

    In plenty of cases, one technology will stand out, especially when you have gathered enough requirements to understand the entire situation. More information can help you narrow down your choices, and even make a decision, if you know both (or all) the technologies well.

    If.

    The key here is that as you look for solutions, you should be sure that you understand the technologies well. Dive deep into each of your choices and make an effort to determine the positives and negatives, the advantages of one over the other, as well as the limitations or holes that may be present. You can work often work around limitations, but you should be aware of what they are.

    We can’t learn everything today, or even everything that we need to know in a short period of time. However, when we are faced with a situation that has multiple solutions and unfamiliar technologies, we should ensure that we try to learn, ask questions, and do our best to understand the boundaries of the question and the technologies that might solve our problem.

    Steve Jones

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

  • Forgoing Tech Investments

    The US was hit with a number of storms over the Christmas holiday weekend. This disrupted air travel for many airlines and their customers, but one of the worst hit was Southwest Airlines. They accounted for most of the cancellations, over half of their scheduled flights at one point.

    A number of places reported talking with Southwest employees who blamed the lack of tech investment by Southwest over time, noting this caught up with them. The Chief Operating Officer disagreed, saying that their scheduling system is the best in the world, even as the CEO noted that their scheduling software couldn’t keep up and they fell back to manual operations.

    Most of us likely have no idea of how Southwest software works or the scope of the problem. This airline does tend to operate differently than many others in that they mostly fly point to point, rather than using hubs. Possibly they have the best point-to-point scheduling software in the world, but it still couldn’t keep up with the storms covering much of the US.

    There’s an interesting perspective on Facebook, supposedly from a pilot with 35 years of experience with SouthWest. If you don’t want to click, his view is the hands-on CEO retired years ago and accountants were appointed as CEO and COO. They improved the money flow, but neglected investments in tech and weren’t aware of how the business really runs day to day. The infrastructure and software deteriorated, and they’ve had many small issues, but issues that were bigger than other airlines. They’ve started turning around with a CEO that is more hands-on, but they’re digging out of a hole.

    Like many of you, I’ve built and operated software over the years. I sometimes realize just how hard it can be to keep up with the demands of customers for adjusting how our systems work. I also know that it’s easy to slow your investment in a system that appears to works and limit your efforts to just maintenance work. Allan Hirt wrote about this.

    This does bring up the issue of investing in systems and maintaining them over time. I see why many companies would prefer to purchase software and let someone else manage the investment in ongoing development. I also know that for companies that see software as strategic, likely there needs to be regular investment, upgrading and refactoring code, as well as finding ways to scale higher and use resources more efficiently. Especially for databases.

    The battle between enhancing software and reducing technical debt is a constant one. I see this struggle being one that project managers and developers never agree on, but in the companies that seem to thrive, there is a balance. Perhaps it’s splitting the sprints, perhaps it’s allocating regular time during each development period, or maybe there’s another way.

    One thing is certain. We need to find a balance. Otherwise we might get into the situation where a complete rewrite or replacement of software is warranted; a situation that is almost always very costly.

    Steve Jones

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