Voice of the DBA

Category: Editorial

  • Keyboard Hardlines

    A few years ago, I had a keyboard die. At the time, I needed something quickly and ended up with a Logitech wireless model that included a mouse. I’m not sure of which model, but I’ve ended up getting two or three more Logitech devices over the years. I think I go through a keyboard every 18-24 months, though the mice seem to last longer. For me, having devices available without cords is more important for the mouse than the keyboard, but since Logitech will bundle them, I get both at the same time. I’m currently with a K350 keyboard and an M510 mouse, both of which seem to hold up well.

    However, I’m at home, usually working alone, so I haven’t been concerned much about security. In corporate environments, I remember playing jokes by moving someone’s mouse to my desk, and giving them a disconnected replacement, trying to mimic their movement. Or doing the same with a keyboard. That’s a great typing challenge if you ever try it.

    I thought about those times when I read this piece on the security of wireless keyboards, or maybe the lack of security. Apparently a relatively simple device can intercept and replace, or just record, keystrokes made on a variety of keyboards. These devices use their own dongles, not a Bluetooth connection, and security is non-existent. Perhaps I’ll take one to the Redgate office this fall and see who’s actually vulnerable.

    Actually, I think this is an issue, especially in large companies that may regularly hire consultants. Who knows what temporary workers might do with information they can gather by just sitting in proximity to a domain administrator. Or a DBA. We might be disclosing passwords to all our systems. Even malicious insiders may end up with access they shouldn’t have, including the ability to run queries or commands under another individual’s account.

    Since keyloggers are cheap and simple, perhaps this seems like an unwarranted concern. However making the move to actually touch someone else’s machine, and add a device that could be noticed is much more dangerous than just sitting, or even standing with a tablet, by someone’s workspace. Even a simple conversation, asking an admin to look up information could result in a breach of security.

    Fortunately this hack targets a few devices, and doesn’t work against Bluetooth devices. However, there are attacks against those, and against wireless access. I know some environments have very sensitive information, or even tight security regulation. I really hope we don’t find our administrators going back to hardlines for administrative access from specific machines, or even only using secured wired because we can’t strong wireless security for our devices.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Mevio .

  • Data Overload

    Data rich and information poor. I think that describes most of the companies I’ve worked for. It’s a theme in this piece from Forbes, noting that many companies use only a fraction of the data they have to make decisions. Certainly I think that many of us that work as data professionals might note that most queries access a portion of our data, often the newest data, with older data sitting on storage systems, constantly powered on, but rarely included in a report.

    Retrofitting archival processes into an existing application can be hard. Even more so when there is rarely queried data that clients want to be sure is still accessible somehow. The Stretch Database feature in SQL Server 2016 might help, but I bet it’s a long time before most of us have all our systems on SQL Server 2016 or later versions, let alone finding someone to pay for this feature.

    There are real costs to keeping this data around, first and foremost of which is the stress for us as developers and DBAs as we try to tune queries the must run against larger and larger data sets. Actually, I’m only somewhat kidding. Management and clients might not care about this, but having to work against larger and larger can be stressful for technical professionals.

    There are other, more concrete and measurable costs to keeping this data around. The cost of power and larger storage systems. With many companies keeping multiple copies of production systems around for different purposes, these can be noticeable costs. There’s also the time factor. If our systems run just 10% slower, that’s potentially 10% less business we can handle. Or maybe all that extra data means more annoyance and frustration from our customers due to slow systems.

    We are going to get more and more data in our systems. While much of this data may be useful, if we’re overloaded, we may not be able to take advantage of the information. We also might get erroneous results if we don’t recognize that data gets old, and the value we might have from a row today might not exist in a few years. We should also realize that at times we have lots of data that isn’t useful at all for our organization.

    I really would start thinking about the ways in which we can actually remove older data from our systems, with archival to cold systems, or even deletion if we’ve moved copies of data to other applications, such as data warehouse systems. Or maybe just deleting data we know isn’t going to provide any information. Above all, remember that warehouses will fill up at some point, unless you buy more and more (expensive) storage. Keeping all data accessible might not be the best decision for your organization.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Mevio .

  • Making Complex Table Changes

    Tables are a problem for anyone trying to grow and modify software. Views, stored procedures, functions, all of these objects are easy to modify, but when we start to deal with actual data, where we need to maintain state, we start to have issues with concurrency, performance, and more. These challenges can really stress the developers and administrators that work on large tables.

    As an evangelist for Redgate Software, I talk with a lot of customers and potential customers about their database work. Almost all of the problems and difficulties they experience revolve around tables, especially as we seek to deploy changes with little to no downtime. In fact, one of the hardest problems Redgate has been trying to solve is how to make these changes easier for DBAs and developers. It’s hard, especially because the impact becomes greater as the table gets larger.

    I ran across a post from Michael J Swart that looked at ways to alter a large table while keeping the data available for querying. It’s the start of a nice series that examines a technique, looking at the pros and cons. This seems complex, but there isn’t any magic to altering tables and ensuring the system continues to run. There really are a limited number of ways to perform alterations to tables.

    These are hard changes, and need to take place across time, meaning you won’t get this all done in an hour, sitting at your desktop. Some of these changes might take hours, or even take place across days. Really complex changes that must synchronize with an application (or many), might actually sit in some limbo state where triggers keep data in sync for months.

    There are different ways to make these changes, but how do you test which one works best? How do you ensure the correct steps run in the right order? You want an automated process that can be repeated. If I need to restore and retry my idea, I won’t want to depend on humans to execute the correct steps in the correct order. That’s a recipe for disaster. Especially when repeat the deployment to production.

    As the changes you make to your systems become more complex, with multiple steps, and the repercussions for problems grow, I think it’s important that you have an automated process. A human might need to kick off parts, but really an entire batch of items should run automatically, in a reliable, repeatable fashion, without requiring a human to execute each step. This doesn’t mean some things are too difficult to completely automate, or that human’s aren’t involved (maybe for smoke test) but people should be involved as little as possible.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Mevio .

  • Defensive Programming

    At SQLServerCentral, we have a book on Defensive Programming in T-SQL. However not a lot of people have read it, or at least they haven’t downloaded it. That’s disappointing, as I think this is an area many of could improve our programming skills. Many of the techniques are simple things, or items to be aware of when writing code in order to make it more robust and reliable.

    I ran across a piece on the Enterprise Craftsmanship blog als covering this topic. That post is geared more towards C# and other OOP languages, but some of the concepts are valid for any software system. Certainly the need to guard against invalid values, especially NULLs is important in much code, and especially important in SQL code. I hope all of you understand the impact of NULLs on aggregates as well as other T-SQL commands, operators, and expressions. If you have any doubt, be sure you read about the impact of NULLs in BOL and perhaps write tests that inject NULLs into your code.

    There are places in our T-SQL constructs where we might want to develop code defensively. Checking parameters, using defaults, and watching out for ambiguous updates, and more. However, the place where I really think we want to be defensive is when we deploy changes to our production systems.

    I’ve written about idempotent code before, and all of you that develop software should be familiar with the concept. Perhaps more importantly, you should practice writing code that is idempotent to ensure that you really can write code this way. While not all deployment code needs to be written this way, there are plenty of cases where we want to include some sort of guard clause to prevent unexpected changes during our deployment.

    I’d encourage you to take a bit of time and examine the idea of programming defensively, practicing some of the techniques and making an evaluation of where this might improve your own code base. Who knows, maybe you can prevent some future bugs by learning a bit more today.

    Steve Jones