Voice of the DBA

Category: Editorial

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

  • Increasing the Accuracy of Data We Rely On

    When I was a kid, we used physical maps and verbal directions. That, with a good memory and sense of direction helped me navigate across the US on my bike and through many cities in Europe on my bicycle.

    Near the turn of the millennium, we got a number of mapping sites, like MapQuest, which were really designed to replace a paper map with a route drawn in. Since then, the capabilities of GPS combined with the advances of mobile phone OS and app improvements, much of the world depends on GPS working correctly.

    The GPS systems, of which there are many run by different governments, have been in place for years, and have been upgraded at different times. The US government is looking to upgrade GPS (their system) with GPS III, with stronger signals and a longer lifespan. There are a few other items, better security among them.

    There is a lot of GPS data that many of us take for granted. We may not worry about the actual data values, but we may use apps or include integration with services like Google Maps or Apple Maps, and we depend on this data being accurate and available.

    There could be similar types of data that our applications and systems need, where availability, accuracy, and more affect the usefulness and value of some application. I think things like master data and data classification fall into these areas. The problem is that often those projects, or the idea of a project, don’t have a large government agency with a big budget working on them. As a result, they rarely move forward.

    Steve Jones