Voice of the DBA

Tag: software development

  • Is Rollback Feasible for Database Changes?

    One of the things I used to show people in SQL Compare was the ability to generate a quick rollback script by switching the source and target. We can generate a deployment script going from Dev to Production, switch the source and target, and then generate the rollback script, from Production to Dev. This was the automation of a manual process I used to go through, examining changes made in deployment scripts and producing the reversing changes for various schema items.

    Really the only object that causes us problems is the table. We can easily grab previous versions of views, functions, stored procedures and other code objects, applying them on top of our deployment without worrying about maintaining state. We can go back and forth with different versions of code. Certainly our applications might have issues, but the database itself works fine and deployment is quick.

    However, that’s not the case for tables. If I add a column to a table in a deployment, how do I remove it? Certainly I can drop the column, but what happens if there’s been a data transformation, or users have entered data? I might need to preserve the data that’s been added, which is a process I might not want to script. What if I split a column or refactor data to a new table? The movement of data back to it’s original spot, while preserving any changed data, is not a trivial task. Add up enough changes in a deployment and it wouldn’t be worth running any rollback scripts. A database restore might be more efficient.

    Recently I was discussing these issues with a developer, who remarked that they never built rollback scripts. It wasn’t a good use of time. If there were issues, developers or DBAs would need to make decisions about how to handle the issues. These days, as we’ve become better at architecting database applications, we might even decide to leave the actual table alone and use view or stored procedure change to effect a virtual rollback for the application, erasing the changed schema with code rather than actually returning the table schemas to their previous versions.

    As I see our organizations depending on databases to a larger and larger degree, requiring no more downtime than actually needed, I think that rollback scripts for tables might be a thing of the past. Certainly rolling back object code is still possible, with an understanding of the impact on both the database and application when old code is used. That’s definitely a decision that needs to be made on a deployment by deployment basis.

    My view is that whenever possible, both the application and database should try to be forward and backward compatible by one version to allow changes to be made to one or the other without breaking the system. That’s not always easy, but in many cases it is with a little forethought, practice, and awareness of the architecture techniques that allow this, it can be relatively easy. In some cases, especially with smaller databases or discrete, limited changes, rollback scripts might make sense. However, I think for most of us this is a software development pattern that’s not worth considering most of the time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Designing for the Public

    I’m a bit believer that better data, and better software can help our governments around the world operate more efficiently, and better work for our citizens. I appreciate what Tim O’Reilly has said where he notes that we want governments to “specify less and do more.” Whatever your political leanings, I think that most of us would like to have our governments work better, whatever that might mean for us.

    I ran across a short piece on the design principles of the GDS (Government Digital Service) in the UK. The details are inside of a set of tutorials that you get when you subscribe to Safari Books Online, but I did find a short list of their basic principles. The first seven are:

    • Digital by default
    • Putting users first
    • Learning from the journey
    • Building a network of trust
    • Moving barriers aside
    • Creating an environment for technology leaders to flourish
    • Don’t do everything yourself (you can’t)

    There are more, but I think that these are overall good design principles for anyone that builds digital services. These are agile ideas, or DevOps ideas, Six Sigma ideas, the ways in which we can be better by learning and evolving as we move forward. Certainly I think governments struggle with the pace of the digital transformation of the world, and trying to solve problems in one large effort doesn’t work. We need the ability to debug, and modify our systems on a regular basis. Both in public and private sectors.

    The last item might be one you don’t think about in some private industries, but certainly the idea that you can’t provide everything for customers. Perhaps you may to include an API or integration method to let customers build on what you do, and learn from what your customers might build, including those features in your next iteration. Designing for a diverse audience is hard, and certainly requires flexibility, but also the ability to make hard decisions and focus on the things you can do well, rather than trying to meet every goal and every need.

    Hopefully we’ll see more governments doing this in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • What’s a Database Pipeline?

    One of the things I ran into a few years ago was the idea of a software pipeline. A set journey across machines and stages that your software takes. In modern, DevOps style development shops, this usually means:

    • A development machine(s)
    • A Version Control System (VCS)
    • A CI/Build server/system
    • An artifact repository
    • Test server(s)/system(s)
    • Production server(s)/system(s)

    Usually software flows along this path as code is written, compiled, and moved from one stage to the next. You may have more or less stages in your environment.

    For databases, this should be similar, with databases in

    • development
    • test/QA
    • staging/pre-production
    • production

    You could also have other stages like integration, beta, user acceptance, etc. I’ve seen people with 7, 8, or 9 databases in their environment, all a part of their entire development and operational areas.

    The database development pipeline is the set of databases that your code flows through. We’ve represented this with visually in DLM Dashboard as a flow, allowing up to 5 environments to be defined. You can see one of my pipelines in this image:

    2016-08-01 22_16_05-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    Keeping code flowing smoothly along this pipeline is the goal of any development effort. We know this at Redgate, and our mission is to build tools to help you do this efficiently.

  • 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