Voice of the DBA

Category: Editorial

  • A Good Reason to use a VCS

    Imagine that you have some important year end procedures in your SQL Server database. Or maybe you have some important, but rarely run code. No matter what the circumstance, if this is a large amount of code, it might be difficult to remember how it’s structured or even how it works on different sets of data.

    Now, imagine that you have a malicious individual that thinks like Andy Mallon talks about in this post. They run an ALTER PROCEDURE and replace the entire procedure body with “RETURN 0”. Now imagine that you don’t find out about this until months later when the important code is run. That would be a bad day, and certainly one that might have you struggling to recover the code from backups. Do you have months of old backups? I used to, in a place where we kept an end of month backup from every month for a year. I always had the previous 12 months, but I wouldn’t want to restore them all to find out where code got changed.

    In most places, we don’t have that many backups, and we often assume we’d only go back a few full backups at most in the event of a disaster. I’ve also been a consultant for more than a few companies that found out that they had 2-10 full backups, all of which were corrupt and from the last few weeks. Sometimes there were older backups on dev/test machines, but in some cases, lots of data was lost.

    It’s important to run DBCC to catch corruption, but it’s also important to keep track of your code. There might not be maliciousness, but simple errors in code that’s changed without comprehensive testing. This can be especially problematic for rarely run code that we don’t test as thoroughly and won’t detect issues for a significant amount of time.

    Save your code in a VCS. Git is free. File|Save is in every editor on the planet. There’s no excuse not to use a VCS and save code. Redgate has tools to make this easy, but I worked in a file|save environment for years, and it worked. Learn about a VCS and build the habit to backup your code in a repository. It’s simple and easy, and you won’t know you need it until you do. At that point, it’s too late.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Beware, More Ransomware is Coming

    Criminals seem to take advantage of anything that will allow them to gain more money, power, or notoriety. It seems in the last year that ransomware has become more prevalent and widespread in all sorts of organizations, though a number of governments have had high profile attacks. They’ve often paid the ransom, with the help of insurers.

    While that might seem like the most expedient way to get back to working, it can be a problem, as this article points out. It can often be more expensive, in time and currency, to combat an attack than to pay a deductable and have the insurer cover the rest. When insurers start to pay for the attacks, then it provides an incentive for more attacks, especially similar attacks at governments or other corporate divisions where the security (or lack thereof) might be similar.

    This also can influence more organizations to purchase insurance, which might be part of the reason insurance companies are happy to pay out the policy. I expect that insurers can’t pay all policies, so I would hope they would start to require more proactive security measures and policies to prevent attacks against some companies. In fact, I hold out hope that insurance, not government, will drive more companies and organizations to implement better security practices.

    The downside is that sometimes paying the ransom doesn’t get the keys to decrypt files. In fact, I suspect that it’s as likely that the criminals don’t have the key as they just make a mistake in their “customer service” effort to provide the key. All sorts of organizations have trouble providing the right keys at times to customers, so I’d expect this happens to criminals as well. I also wouldn’t be surprised if some criminals aim to exact a second ransom, perhaps devised to be just below the insurance policy limit, before providing a key.

    The takeaway for most data professionals here is that we want to be very careful with our data, especially our backups. While others might lose their data to encryption, it behooves us to severely limit access to backup files to prevent a rogue account accessing them, and certainly we would want to to air gap backups wherever possible. If we find out that systems are encrypted, at least we can recover our data on new hardware. If our application code is likewise held in another system, like GitHub or BitBucket, then we might even get back to work quicker, at least for our data-driven applications.

    Security continues to be an increasing part of the data professionals job. As a way to ensure your career continues to advance and grow, pay attention to how well you secure your organization’s data.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • What You Should Know About Coding

    What are the important things you should know about writing T-SQL code for an organization or on a team? I’m sure that many of you have ideas, and please, feel free to share them with us with a comment. As you think about your answers, think about this as well: what do you wish you’d have known when you started writing code?

    I went through university quite some time ago, over 30 years ago, and some things have changed in this industry. Not so much in the way we structure the code we write, though we have gotten much better in some ways. Rather the way that organizations work has dramatically changed. It’s not just this new DevOps thing, but more that many organizations are starting to expect developers to do more than sit in a cube and react to a set of tickets or a specification written months ago.

    More companies are expecting developers to think and interact with others, knowing more about other parts of their environment and application. More developers work in teams, and perhaps very interesting to me, the once dreaded and heavyweight code review that seemed to die during the middle of my career has become a commonplace, quick task that developers do every day now.

    There’s an interesting piece, written by Ryland Goldstein, about the things he thinks developers should be learning in college or early on. I thought it was interesting to see him open with lines of code (LoC) as a metric, and show the growth in code for some large applications. To me, LoC hasn’t been important at any point in my career, but I know it was talked about while I was in school.

    He does talk about some things that good code should have, and I agree that most of the time the language doesn’t matter. I do think his points on reading code, and learning to work with other people’s code is good. To me, this really means we want to adapt and learn to write code in the style that our team uses. That can have a huge impact on group productivity, when we can write in a similar style, and then also read (and review) code quicker because it’s all the same style.

    Perhaps the best line is this: ” Every single member of our team (including me) would have a full blown panic-attack if someone ever suggested committing un-reviewed code.” I think too often the database side of things ends up committing (and deploying) un-reviewed code too often. We could to better, even if we need to teach someone how to review our code. Having a second set of inexperienced eyes might not prevent mistakes, but it certainly might help others learn to write better database code.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The Crazy Times of Year

    Do you have busy times of the year that recur regularly? In some positions I’ve held, we had certain events or processes that always caused additional stress and headaches for employees. As a bartender, certain holidays (New Years, Halloween) were extra crazy. In one company, every quarter was an adventure involving a late, or a complete, night of work. Different industries and organizations seem to have different schedules that regularly stress employees.

    Are there times that are tough for you? Or maybe slow times? In a few organizations where my wife worked, the week between Christmas and New Years was always so slow that they’d actually close the office and give people the week off, without it affecting their vacation schedules. That would be a nice perk to have.

    This is the busy time of year for me. Spring sometimes gets filled, but this next couple months is somewhat crazy. The first third of September I was in the UK. I returned home, and I have a week and a half off, which I need because then life gets hectic.

    The weekend of the 20th, I’m heading back to upstate New York to see my daughter play in a volleyball tournament. That’s exciting since I’ve never seen her play college competition live, and I’m looking forward to the trip. A few days after I return, however, I head to Australia for work. Just a couple days, because I can’t spare the time, which is disappointing to my wife. She’s like to go, but since I’m in Memphis the first weekend of October, there isn’t time for a week in the beginning of Spring down under.

    I am home the second week of October, but with a pre-con and SQL Saturday, it will be a busy week of prep and trying to get other things done since the third week of October my wife wants to go back to New York to see our daughter. I’m tempted, but torn since the last week of of the month I return to London before heading to Seattle the weekend after.

    I still have other work to do with SQLServerCentral and Redgate in addition to these weekends, and I have the feeling I’ll be ready to knock off travel and just coach kids after the PASS Summit. Right now I’m looking at being committed for part or all of every weekend except for this past weekend until Nov 9.

    A tough time, but really, it’s a great job I have with amazing opportunities to see friends and places all over the world. I know I’ll be tired, but it will be a good tired, even with the extra time spent preparing for travel and catching up on chores when I return.

    Are there any wild times in your work world? Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.