Voice of the DBA

Category: Editorial

  • 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

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