Voice of the DBA

Category: Editorial

  • Protecting Code

    There are so many data breaches taking place, that it’s hard to keep track of them. While I rarely find my email in any of the breaches loaded into haveibeenpwned.com, I do see Mr. Hunt regularly loading more data sets into the database. I don’t know how many you’ve been a part of, but I certainly hope you know, and I hope you’ve changed shared passwords and updated accounts.

    While we do need to protect data, we also need to ensure that we protect our code, as that might be where the vulnerabilities lie that others might discover. While I’m not a big fan of encrypting or hiding the code in a database from customers, I certainly don’t want that code, or the application code, to be visible to outsiders, especially potential hackers.

    While your team might not be are careless as the Boeing research team, I’d hope that you don’t expose your code on the Internet, as they did. Perhaps more importantly, I hope no one does a Black Hat talk about potential issues in your code. A researcher did this to Boeing, talking about potentially being able to jump from a customer network and application to a more privileged one to the command and control network. Boeing denies this is possible, and I’m not worried about my future Dreamliner flights, but I think Boeing should do more publicly here. Let the researcher have a few days on a plane and truly pen test the software.

    I’ve seen lots of “hidden” features in software that administrators use to get work done. I’ve seen sloppily written tooling that solves a problem and isn’t intended for general use. I’ve also see far too many of these features “discovered” by ordinary users. Even assuming your internal network is completely free of malicious users is a bad idea. We’ve seen plenty of viruses and trojan software that can be used maliciously.

    We don’t want to lock our networks and applications so tightly that we create impediments to work, but we certainly can do better jobs in limiting access, keeping tight privileges, ensuring administrative accounts are protected and more. Those might be hard to get done today, but you certainly can prevent simple things, like securing your VCS. Don’t use public places like GitHub, or ensure you have private repositories that only your organization can see. It’s not perfect, but it does stop the researchers and lazy criminals that are just scanning for easy targets on the Internet.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Growing and Changing

    When I started with SQL Server, I worked on OS/2 and SQL Server 4.2. Eventually I moved on to SQL Server 6.5 at a few places, one of which had heavy workloads. While I liked v6.5 much more than v4.2, there were issues with nested views, and certainly issues with heavy tempdb loads. In fact, at one job, I banned temp tables unless we had no other way to get queries to work. This helped stabilize some of our code, and we essentially had a review group that a developer had to convince to allow temp tables since there were situations where we needed them.

    I was reminded of this recently after Grant published his Best Practices and Band Plans editorial. There was this comment, which noted: ” I have talked to some people that state “our best practices is to never use…” because they tried it when it was first introduced in SQL Server 2005 and it didn’t work for them so now they won’t use it at all. ”

    I’ve encountered this in my career, and I’ve been guilty of it. It took me a few versions, and more complex code, to start thinking in terms of temp tables at times. I still try to avoid them, but I’m not afraid of them, and I know SQL Server handles tempdb loads better with each version. They are a tool I reach for sooner, but not immediately. I still try to get a simpler T-SQL query if I can.

    Many developers and DBAs have plenty of experience with T-SQL, but how often do they update their knowledge? How many people question that the way they first think to solve a particular problem is the best way? The T-SQL language has grown and changed across versions. Some language changes are great, like the OVER() clause for window functions. Some might not be so great (STRING_SPLIT among them) or have limitations that you might not expect. Without questioning the way you write code and periodically looking for new techniques, you might not be aware that the amazing code you wrote a few years ago could be better structured today.

    There is a balance between relying on your knowledge and trying new things. Certainly most of the time I’d expect a developer to write code based on their experience and what works well, but I do think that you also need to periodically investigate new techniques. Regular learning is part of what we hope to help you with at SQLServerCentral, with articles, the Question of the Day, and blogs in our daily newsletter. Maybe you don’t read every article, but go through one once every week or two. Whether it’s something you do every day or have never tried, you might be surprised what you learn.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Waits and Queues

    How many of you have read this article: SQL Server Performance Tuning Using Waits and Queues? (PDF) If you haven’t, you might look at a review of the paper from Jeremiah Peschka. There is good advice about which parts to read and which to ignore a decade later. There’s an updated version from Paul Randal and SQLSkills as well.

    This leads me to where you might expect this week, with a question for you. Do you use waits and queues to troubleshoot? (Survey)

    Not every performance problem needs you to dig deeply into the way that the database engine processes your query, but there are no shortage of times when understanding waits and queues can help you determine how to tune your system. This is a well known method of trying to determine where to focus your efforts, so I wonder how many of you have tried this.

    If you have, let us know what’s worked or what hasn’t. If you’ve never tried this, then drop a quick note as well if you have a reason you haven’t used waits and queues.

    Performance tuning is a skill everyone needs, and most everyone can work on. This is often one of the most popular topics for talks and classes, and any help you can get will grow your career. Both in your own self confidence as well as impressive to interviewers when you have an answer or a methodology to give when solving any problems you’re given. Whether in an interview or in your job.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Don’t Get On This Page

    The GDPR has been in effect for over a year. While the press has died down a little, outside of a few record fines, there is still plenty of activity. At Redgate, we have customers that still worry about compliance and are doing their best to ensure they properly handle and secure data. It’s a tough job, but one that many organizations need to continue to focus on.

    And there’s a good reason. Who among you would want their boss to come talk to them about mishandling data? Who wants their boss to come after reading this page, with your organization and fine listed? I’m guessing most of us would prefer to not be on that page, or at least not want our boss to know.

    There have been a lot of fines handed out, though most are relatively small. Still, every amount spent towards a fine is money that could be used for shareholders, investment, or even better security and systems to prevent future issues.

    What’s interesting is that many of these fines aren’t for data breaches, but rather for other issues. There are some security issues (unauthorized access), and some inappropriate storage. There are also quite a few consent fines, where data is used without the appropriate permissions from the data subjects.

    I find the list interesting, and I hope this is the type of thing that does drive change in organizations. Many of these fines might easily be eliminated with a few process and procedure changes and adding some security to prevent unauthorized access. While some companies might be willing to pay fines, I expect that subsequent amounts will rise, and it behooves organizations to change both their behavioral and technical practices.

    This might be building archival processes, which would be one of the few ways that might reduce the amount of data that we need to manage and query. Hopefully, something that might improve performance for your clients.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.