Voice of the DBA

Author: way0utwest

  • Who Still Uses SP_ for Naming?

    An interesting post recently from Chad Callihan on the reasons why someone shouldn’t use sp_ to prefix their stored procedures. This is advice that I’ve seen for years, but I haven’t run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that’s good, and if you’re not sure why, read Chad’s post.

    I think my main reason for not doing this is that I sometimes create procs in master. Not because it’s a good idea (it isn’t) or I want to, but because I’ll make mistakes. I’ll accidentally connect to master and deploy a script, because I’m not paying enough attention. Usually, I’ll catch this right away, but if not, soon after I’ve told a client things are working, they’ll let me know they aren’t. Then I connect to the right database.

    In a vacuum, or even as you sit reading this today, it’s easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I’ve created it in the right one. However, all of us are human. We’ll make mistakes. We’ll be pressed for time or sick or tired or want to leave or some other situation where we aren’t using all of our faculties. Even if none of those are true, we might depend on muscle memory because we’ve done this 1,000 times (or may 12 times in the last hour) and then don’t notice we’ve connected to the wrong database.

    What might be even worse if when we’re updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren’t working as expected right away.

    Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like Flyway or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts.

    We’re human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs.

    Steve Jones

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

  • Breaking Biometrics

    In the movies, when someone needs to bypass some sort of fingerprint biometric system, they use tape to lift a fingerprint, or in the more gory films, cut off a body part. In any case, there have been a lot of attempts to replicate this in the real world. Sometimes these work, though the consensus is modern hardware isn’t as susceptible as older hardware. I don’t know if that’s true, but I did see an interesting look at the main three sensors used in most laptops.

    All the laptops had their authentication bypassed, though not with any fake biometric device, but rather with attacking the actual communications between the sensor and the OS. There’s a great report from two people who were asked by Microsoft to evaluate the security of the top three sensors. It’s a fascinating look at how the hardware and software of the OS are set up to provide secure authentication and how both can be fooled.

    Some of the problems are users not enabling features and some are that manufacturers aren’t understanding or implementing the secure protocols from Microsoft. I both get how this happens and I feel it’s not acceptable. Certainly someone might read a spec and not completely understand it, even across a team of people. However, for vendors, this can’t be something they don’t have researchers, pen testers, or some security professional examine. Hardware vendors spending money designing these devices ought to include the money for a few people to try and break the security before they are sold.

    At least for security features.

    I like the enhancements made with multi-factor authentication, and I appreciate biometrics as a convenient way to access things. At the same time, I want to be able to trust them. The efforts made by the researchers are pretty high, not something that someone in a coffee shop can do in 5 minutes while you go to the bathroom. At least, apart from the Surface. It appears that one can be quick.

    At the same time, if you lose your laptop, within hours someone can break past the biometrics with a man-in-the-middle attack. Even if you’ve encrypted the disk, your data can be accessed since this attack gets the OS to authenticate them.

    Security is tricky, but I don’t plan on disabling my fingerprint scanner. However, if I were carrying around data that was more important than the work I do now or data worth a lot of money, I don’t know I’d ever enable biometrics.

    Steve Jones

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

  • Executing One Line in VSCode–#SQLNewBlogger

    I wrote about arrays in PowerShell last week, but I realized one of the things I did while experimenting was look up how to run code a line at a time. I’m sure you can Google for that, but I decided to write a post to help me remember this in the future.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Running One Line

    Years ago I saw someone demonstrating either PoSh or Python in Visual Studio and they were walking through code a line at a time. I thought that was cool, like a debugger, but not being in the debugger.

    To do this in VS Code, we use F8. Here’s how it works. Create a new PoSh file in VS Code. You should see something like this:

    2023-12-01 14_01_11-● oneline.ps1 - Visual Studio Code_thumb[1]

    Now put the cursor on line 1, anywhere. I’ll put it after the = sign. If I press F8, I see this:

    2023-12-01 14_02_03-● oneline.ps1 - Visual Studio Code_thumb[1]

    Pretty cool. I just ran that line.

    One Hassle

    The one hassle is the focus is now in the terminal. However CTRL+Tab gets me back to the editor, though it would go to the Welcome tab here. However, supposedly CTRL+1 or 2 would work.

    My problem is those are mapped to ZoomIt, which is always running Sad smile

    I hit CTRL+Tab twice, however, and I was back in my editor. I could go down one line and hit F8 to run the second code.

    2023-12-01 14_02_52-● oneline.ps1 - Visual Studio Code_thumb[1]

    Try it for yourself.

    If you have a better method, let me know.

    SQL New Blogger

    After the last post, I spent less than 10 minutes setting up a new file and then capturing some code. A couple google searches helped me figure out the terminal/editor switching.

    This was fun, but it also showcased some learning and something that will help me in my work. You could easily do the same thing, with any tool you use at work. Show how you experiment and learn.

  • Focus on Growth in Your Career

    I find there to be a lot of value in actively managing your career. This is part of a series of editorials based on advice I’ve read from Raylene Yung (Facebook and Stripe) on your engineering career.

    I write a lot about growing your career. I think it’s important to find a good career, but also learn to be good at whatever you choose to do for employment. While I do think that learning is a skill and each of us ought to sharpen our saws, I sometimes find people chasing new things all the time. In this interview, I saw a great quote:

    “… while it’s important to be informed about the latest frameworks and best practices, learning this at the cost of developing fundamental skills is a bad tradeoff.”

    That’s something I imagine Jeff Moden saying about all the new technologies that get incorporated into database work. Often I find there are a lot of DBAs and database developers that want to implement PowerShell or MDX or Python because they can and not because it’s a good choice. Or perhaps more often because they aren’t as skilled in SQL as they should be.

    I am a fan of experimenting with different methods and determining if there is a better way to solve a problem. However, I am only a fan of using new techniques when they are better. Either they have to perform better (less resources or a lot less time to run), simplify the situation, or they save a ton of developer time. And not just your time, but for all other developers.

    One of the things noted in the interview above, as well as something I’ve seen, is that many younger people want to constantly try something new, without developing deep skills in any area. They often move in new directions because they can. I also see intermediate or senior engineers getting bored with their work or excited by something they read about and looking to shift everyone to a new platform/language/framework/etc.

    New can be better, but isn’t necessarily better. Instead, I might try to answer these questions from the article:

    • how can I make every code change great?
    • how can I be as good at X as someone on my team – For SQL, can you be as good as Jeff Moden or Itzik Ben-Gan? Or maybe closer to their level?

    The goal is to become better at your craft. Like the woodworker who improves their skills in shaping wood, or the chef becoming better at creating more tasty recipes, or maybe the weekend race car driver who learns to shave seconds off their lap times with improved driving technique.

    If it isn’t important to you to become the best in your team, maybe this will help. The cloud, AI, and competition from younger people will ensure that if you don’t improve your skills at all, you’ll slowly fall behind and your current manager, or the one that takes their place, might find a way to replace you with someone that does the job better, faster, and cheaper.

    Steve Jones

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