Voice of the DBA

Category: Editorial

  • A Technology Collapse

    This past week the Arecibo radio telescope collapsed, with cables and instruments falling into the massive dish. You can see images of the devastation, which saddens me. I doubt this will be rebuilt, though one can keep hope alive. there were reports of failures a month or so ago, with the intent to shut down the telescope, remove instruments, and institute a controlled demolition.

    The facility was featured in a few movies, notably GoldenEye and Contact. The latter is one that first showed me the telescope, which I first thought was a movie magic trick. When I found out this was a real facility, I was amazed that humans could conceive and build such a structure. Even more amazing is that it was built in the 1960s.

    That’s quite a lifetime for a piece of technology. While I’m sure lots of wiring, electronics, computer systems, and more were added or upgraded, the core of this telescope remained the same. The ongoing engineering effort, fitting new capabilities around legacy systems and structures, was likely a study that many would find interesting.

    I don’t know many database systems that have survived that long, but certainly there are some long lived legacy applications. The Sabre reservation system for airlines is one that stands out to me. The history of this one is fascinating, especially as it’s a system I’ve depended on in my travels.

    Many of us that work with databases likely feel that everything we do is a legacy system. The dependencies our objects have on various applications, scripts, reports, and more can limit how much repair, improvement, or replacement we can make to schema.

    There are good techniques for modifying our objects, and helping to ensure we don’t break systems, but we often do need some cooperation and collaboration with application developers to implement those changes. Much of DevOps avoids talking about the database, but we shouldn’t. Instead, we ought to embrace database refactoring patterns, both at the database and application levels, ensuring that our systems can survive for as long as we need them while adapting to the changing requirements of our clients.

    Steve Jones

  • Your Favorite IDE

    SSMS is, well, it’s what we mostly have from Microsoft. It’s been around a long time, but it is getting regularly released. There are new versions about every quarter, and there are bug fixes and minor enhancements. Azure Data Studio appears to be where Microsoft would like most of us to move, but I, and many others, somewhat resist this because it’s not quite as full featured as we’d like.

    Recently I was watching one of Brent Ozar’s Ask Me Anything recordings, and some people asked about SSMS and other IDEs. I think Brent is mostly an SSMS fan, and deals with issues, but he mentioned some other IDEs, like DataGrip.

    I’ve never used that one, but I used to use RapidSQL when it was Embarkadero, mostly because I had offline editing. Once Microsoft added that, I didn’t see a reason to buy it anymore. At Redgate, we’ve been asked to produce an IDE, but that’s a ton of work for limited commercial appeal. At least, that’s been what I think.

    Today, I’m curious. What’s your favorite IDE to work with SQL Server, or maybe the Microsoft data platform? I’d like to know what you use for SQL Server, but I’d also be curious what you use for other code. PoSh, C#, anything else. Are you a Visual Studio person? Do you like VS Code, and if so, is ADS a complement for databases or do you prefer SSMS?

    Let me know. Maybe you’ll convince me to try another editor.

    Steve Jones

  • Catching Minor Issues

    This seems like something out of a thriller that Hollywood made for a spy. Someone switches two cables in a rocket and then it crashes. However, it’s what happened recently, not from a spy, but an employee somewhere connected two cables to the wrong connectors, resulting in reverse commands being sent to the rocket.

    That sounds crazy. Who would plug cables in backwards, and how is this not caught? Surely we wouldn’t run into this in software would we? With all the unit testing? Well, we did once have an English->metric error.

    Actually, I’ve seen this in software. I’ve seen someone pass parameters in backwards to a method or stored procedure. I think I’ve done this before as well. While I hate typing out named parameters, I do appreciate SQL Prompt for ensuring I connect the right value to the right parameter by spelling them out.

    Do you type out parameter names, as in EXEC myProc @Param1 = @value1, @param2 = @value2? It’s a good habit to get into to prevent simple mistakes. In fact, this is where some simple, quick tests can catch errors. Not only will this catch some weird changes, this can ensure that your procedures are backwards compatible. That’s often a concern in refactoring, where someone might not think a parameter is being used, or wants to replace it and then breaks other code.

    Testing is something that has improved in software dramatically over the years, though not as much in databases as I’d like. I urge you to use more procedures, but also add more tests and ensure that you don’t make simple errors that should be caught.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Completely Encrypted Data

    I remember reading about, and doing some message exchange, with PGP, in the 90s. At the time I worked in a utility company and my boss and I were interested in whether encryption might be something we should implement. At the time, the integration with mail clients, and the relatively unsophisticated users limited our options, and we never moved forward, but I’ve always been interested in encryption and how it fits into our digital world.

    The technical bits have gotten easier, with https encryption automatically enabling for most of us, though perhaps only preventing limited attacks. We’ve gotten more options in the data platform, some that work well, some that require a decent coding effort, but they do work to some extent. At least, they make auditors happy and prevent silly leakage from something like a lost disk drive.

    One of the main areas where encryption has been controversial is in real time communications. Governments and law enforcement want to be able to eavesdrop on criminal activity, or maybe other activity, and individuals want privacy. This seems to be an ongoing battle between technical companies and lawmakers as to how to implement features and what limitations should be enabled. I noticed a story recently where Google is rolling out end to end encryption in its messaging apps.

    That got me to thinking. We capture and store data, and we may have some sort of communications in our system. If users demanded, or application developers built, end to end encryption, do we care as data professionals? Certainly we would need to allow for binary storage, and we’d lack insight or indexing into the actual data, but certainly could work with metadata like user, time, etc.

    There are also other considerations for us. If we store encrypted data, is this more of a hassle in dealing with legal requirements? Do we want to have another sort of PII in a key or have to constantly explain to management or legal staff that we can’t read the data because we don’t store the key? There are non-technical burdens that we might not want to shoulder.

    I do think that more systems ought to allow end-to-end encryption for communications, and user-managed keys are a capability that plenty of us might want in a world where no one physically sees the database server or disks. While I do like the idea of secure enclaves, which are catching on in computing, I also think that key management, especially for users, needs to improve. Perhaps we need a password manager for certificates, with backup included, to ensure our end users can properly manage their certificates across devices and in the event of any personal disasters.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.