Voice of the DBA

Category: Editorial

  • 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

  • Moving Fast at Redgate

    One of the pushes at Redgate Software over the last year or so is to move faster. We aim to develop software quicker and get the changes and improvements into the hands of our customers quicker. A new feature doesn’t help anyone if it sits inside of a VCS, undeployed, for months. Customers and clients need to see the software changes.

    There are various ways to achieve this. The SQL Prompt team manages themselves, and being a small group, they have been successful at not only writing code quickly, but releasing often. In fact, they’ve released code to customers over 70 times in the last year. As a result, a few other development groups are looking to work in a similar, lightweight, rapid development manner.

    However, that’s not an appropriate process for all developers. In fact, in most companies I’ve worked in, there is always someone that needs to manage and report on milestones and efforts to build software with some sort of plan. In addition, most products have a wider scope than something like SQL Prompt, which means more developers, more coordination required, and everyone must deal with more friction throughout the process.

    One of our project managers wrote an interesting piece on how he worked to get the SQL Monitor team, one of our larger pieces of software, to meet their goals, and also move to a more rapid development and deployment process. It’s a look at the changes made in process and the adaptations to reality that occurred last year. I found it fascinating to read since I’d see the team dramatically improve the product in a fairly short period of time.

    As with most of the stories about successful software development teams, buy in and support from management, especially project management is important. Without your leadership wanting and being willing to change, little will. Developers have to believe as well, but I suspect that if management truly supports improvement and is willing to change the way they work with developers, the people that write code are usually willing to go along for the ride

    Steve Jones

    The Voice of the DBA Podcast

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

  • Passwords Under Pressure

    What should we do about passwords? They’re a thorn in the side of administrators trying to keep systems secure, but they’re also an issue for users. Not for most of our users, but certainly for some. In hospitals, or other high stress environments, there are all sorts of issues with users sharing passwords, writing them down, leaving systems logged in, and more. Studies have shown this to be the case, which is something that drives IT people crazy. Why can’t people just use strong passwords and learn to remember one?

    I’ve dealt with this in many situations across the last couple decades. I’ve had management get upset that the local LAN password didn’t match their mainframe password, which was apparently, a big problem for managers. I’ve seen warehousing groups not want to have systems logged out, so a single login was used for all users to speed up access for data entry. Applications grown far and wide in another company, with embedded passwords, so that a single password for a domain admin account was unchanged for at least 5 years.

    It’s not just normal users. I’ve had separate administrator level and regular user accounts (as did others in my group), yet far too many of us (myself included), managed to use our privileged account for everything from email to web browsing, mostly because it was “too hard” to run as administrator for tasks. A great example of both hypocrisy, as well as the ways that technical people can get around many restrictions that might frustrate other users.

    More recently I’ve seen a dramatic growth of Two Factor Authentication (2FA), where logins often result in a text message or other verification scheme in addition to a password. What I usually see here is frustration and irritation from users having to wait to log in. Until something happens. A friend had a virus recently on their machine, and all of a sudden was worried about their finances being secured. They then decided to add 2FA to their various banking logins.

    The idea of a password seems simple and easy. At least to many of us that log in a few times a day at our desks. Log in on your phone, or use 2FA everywhere (like I do), and it’s more complex, but still acceptable. Usually a 30-60s delay isn’t a big deal for me. However, if I were in time sensitive situations, like a hospital or law enforcement situation, or worse, at war, I might feel differently. The balance between security and access usually tips to the latter when time is a factor.

    Is there a better way? Should we move to an audit method for security in high pressure situations? Just monitor and review later, allowing easier access? Or should we try to find ways to integrate simpler systems into our computer devices? Maybe a physical key in some situations to log someone in? I dislike having physical fall backs, especially given that I’ve seen computer systems, keycards, and more fail at inopportune times.

    Ultimately, I think the ideas of trying to authenticate a person quickly might really require rethinking how we can best handle the situation. Maybe staffing should be different in crisis situations with a person monitoring access to an open system (along with auditing). Maybe we should build better embedded systems that might perform authentication quicker, or even use video and machine learning to try and audit in real time, allowing for quicker review and response rather than attempting to stop access initially.

    There are likely few situations where this is a big problem. I think for most of us, in many corporate and work environments should just get used to having to log in, potentially with 2FA more often. And we should get used to better using password managers to manage our various name and password combinations.

    BTW, if you want to see just how bad developers can be with authentication, check out Troy Hunt’s post.

    Steve Jones

    The Voice of the DBA Podcast

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

  • How Far Have You Traveled for a SQL Saturday?

    A few of you might have seen the recent announcement from PASS about SQL Saturdays. I don’t like the decisions, especially the new 600 mile radius limitation for events on the same day (or consecutive weeks). Kevin Feasel performed an interesting analysis, looking at the potential conflicts with events. It’s worth a read, and if you want to run your own analysis, please feel free to do so.

    I know lots of you have gone to a SQL Saturday. I’m sure most of you that have attended one have gone to a local event. However, what I’d like to know is the answer to these questions:

    • How far have you traveled to each event? (multiple numbers in mi or km)
    • where were the events? (city, year if you want)
    • Are you a speaker? (yea, nay)

    Please, please, take a minute and drop a comment. Or send me an email (sjones{at}sqlservercentral[dot]com). I’d really like to get some data on travel. Note, I’m asking how far you have traveled. Not what you’re be willing to do. I thnk plenty of people think they’d drive a long way, but I’m not sure many of you would.

    My contention is that most people wouldn’t drive 50 miles, much less 100, for a SQL Saturday (or Code Camp, etc.). I know some of you would, but 50 miles is probably an hour+ in most places, plus an hour back, plus 7-8 hours of conference talks. Is that worth your time?

    Sure. Sometimes. If you don’t have family commitments, or you’re not too tired, or the weather isn’t too bad (or good). Even if the event is once a year, I see no shortage of people that can’t, or won’t, attend. Lots do, that that’s great. I love seeing SQL Saturdays grow, especially with lots of first time attendees.

    I have seen people go a few hundred miles, even one guy riding down from the mountains west of Denver to Albuquerque, but that’s rare. That’s the exception. At least, I think so. Let me know today. Actually, let us all know. I think the data will be really interesting.

    As an aside, I’d like to get more people performing some analysis of how limitations affect the growth of SQL Saturday and also how we might actually grow to 200+ events a year in the US. I am open to listening to arguements for the changes, but I’m more interested in finding ways to continue to grow SQL Saturdays around the world.

    Steve Jones

    The Voice of the DBA Podcast

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