Voice of the DBA

Category: Editorial

  • What’s Your Test Plan?

    I ran across a post on upgrading a SQL Server instance, where the original poster (OP) was asking about a document to upgrade from SQL Server 2008 to 2014. That’s a big ask, especially as not many documents tend to be written to go across three versions. The official ones, or the people that document well, tend to upgrade every version, and so they have a document to go from 2008 to R2, and from R2 to 2012, etc. However, given the pace of Microsoft releasing things, perhaps we ought to build a document and checklist across every 2-3 versions since many people may be upgrading from 2005/2008 to 2014 (or soon, 2016).

    Apart from having the document, one of the questions was a list of what to test. That struck me, as I’m big on testing, and have tried to keep comprehensive plans when I had important systems. However, many of you might be like me and don’t consider most systems to be “important”. For those systems, a patch, a Service Pack, an application upgrade was really tested by applying the change to a test server and asking users to see if they could use the application. I’m not confident that there was any sort of comprehensive look at the system in these cases, but this system worked most of the time.

    There were some instances that we deemed important, usually measured as such because a failure would mean some high level manager would call my manager’s boss and smelly things would slide in my direction. In those cases, we had a list of the functions and processes that needed to work. These could be application functions, queries, ETL packages, reports, or anything that would cause a user to complain. This list became our test plan, and it was kept up to date. Usually back dated, since we weren’t sure what new things were important until they failed for some reason, but once we received a ticket on an item, we added it to our list. We went through the entire list for upgrades, ensuring each item worked.

    I’m wondering, do many of you have a test plan for your systems? Any system? It doesn’t matter if it’s automated or manual, but if you had to patch/upgrade instance X, are there a list of things you’d verify? Or is the system not that important, so you’d just make sure the database service was running? Let us know what your test plans look like.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Complexity of Branches

    Branching code is hard.  Well, branching isn’t hard. Just right click in some GUI or or type “git branch” and off you go. The actual branching of code is pretty simple and painless. In some sense, branching might be too simple in today’s tools as developers are almost encouraged to branch in a VCS and “test something” quickly without a lot of thought.

    Even merging has become easier, with tools to visual code changes, and automatic merging in many cases. Despite the hassles of merging, it often isn’t too difficult, which can mask the complexity of branching.

    However branching can easily devolve into a nightmare of multiple versions of code, with various developers unsure of the impact of their changes on a merged code base. This is especially true in a large code base where many developers each have their own branch where they work. With pressure from multiple individuals to patch bugs, add features, and streamline performance, I’ve met developers that end up building changes in three or four branches at the same time.

    Merging all the work together, into the correct main branch, becomes tricky.  We’re all human, and I’ve seen developers merge a change into the wrong branch. Tools try to limit this, but it still happens. The more branches that are in use, the more time that ends up getting spent on the administrative action of reconciling code differences rather than building new code (or fixing old code).

    Understanding our code is hard enough, without adding the additional complexity of versioning multiple files and objects. My philosophy has always been to limit branches as much as possible, usually only allowing one, or at most, two, per team. We have also endeavored to merge back changes every few days, just to limit the amount of time spent performing merge reconciliations. I’d rather have developers have the pain of merging daily on a shared branch than dealing with branch merges less frequently (usually more complex merges).

    We’ll always have mistakes made, but using automated testing and continuous integration builds on all branches can help limit the number of obvious mistakes that break code. This won’t prevent bugs, but our tests can help reduce the number of bugs, especially regression bugs, over time. At least, they can if we use testing on all branches, all the time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Half Baked Features

    I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

    The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

    However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

    Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

    There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Software Maturity with Release Management

    For most of my career, releasing new versions of software that I or others had written consisted of manually running scripts or copying files, maybe using an installer once in awhile, but that was rare. In many cases, the process of actually deploying the software involved a developer sitting with an operational person, especially if the operational person had been burned by previous deployments that were poorly executed. Once burned, you usually require the person building the scripts to be available when they are run.

    That’s a poor way of managing the release of software. We write code to allow computers to repeat the instructions we’ve given them over and over. Computers are good at that, and they don’t make mistakes the third or fifth or hundredth time they execute the instructions. We might not give them the correct instructions, but computers don’t make mistakes and do execute what we code. If we find mistakes, we fix them and let the computer then execute them again.

    However it seems that for many individuals that release software, they want to do it manually. Especially when the release is to just one machine (or database) inside their organization. And time and time again, I find that these people spend time building scripts, correcting them, and still finding mistakes it production deployments that they need to fix. Often these fixes are made on the fly, with no testing.

    There’s a good mantra in software that if something is painful, do it more often. Make it easier with automation and execute it multiple times. Learn to code it better. There’s a whole industry that has grown up to help you release software more often, multiple times, and in a controlled manner. These are release management (RM) tools like Octopus Deploy and TFS Release Management. I’ve been using both of these internally, and I couldn’t imagine not using an RM tool at any point in the future. It should be easy to deploy software from one machine to another, and the process should be repeatable. That’s a sign of mature software development.

    If you’re not using a RM tool, I’d urge you to check one out. Most let you get started for free, and a small Proof of Concept (POC) project is the way to get started. Once you learn the ins and outs of how you really deploy software, you will never want to do it manually again.

    Steve Jones

    The Voice of the DBA Podcast

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