Voice of the DBA

Tag: software development

  • Idempotent

    I ran into the word idempotent in the Stairway to Integration Services. I had heard the word, but I hadn’t really considered how important it can be for a DBA or developer until that time. It’s a term used in computer science, as well as other sciences, but I think it’s one that many of us don’t consider when we’re writing code, especially code used to deploy software to other systems.

    Most of us have written scripts like this:

    if exists (select object_id from sys.objects where name = 'uspGetSales')
  drop procedure uspGetSales;
create procedure uspGetSales
as
...

    At the end of running this code, we have the system in a state. If we run this over and over, we’ll regularly return to the same state, which is often exactly what we want to occur. That works great when deploying code, but what about this:

    insert into states select 'CO', 'Colorado';

    If I run that multiple times, what will happen? Either I’ll get errors if one of these fields is a PK, or I’ll get multiple inserts. If I’m running this as part of a software deployment, do I want either of those conditions? Do I want my end user to experience either one?

    No, I don’t. Certainly if I’m manually making changes to systems I can probably avoid issues, but that’s not what I want to do. Maybe you do, but I don’t. I’d like to be able to restart my deployment if something fails, without causing other issues. I’d like idempotent code, like this:

    if not exists (select abbrev from states where abbrev = 'CO')
  insert into states select 'CO', 'Colorado';

    That way if I happen to run this code twice, or ten times, I arrive in the same place.

    I realize that building scripts and deployment processes that are idempotent is a pain. It’s work, but it’s also scriptable and repeatable work that is easy to automate over time with a few patterns. I also realize that a little extra work to prevent issues is often an investment that’s worth making for both my customers, and my reputation.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Dig Out the Root Cause

    Early in my career in technology, I worked as a system administrator on a Novell network. Periodically we’d have crashes of servers on our network, or application failures, and while we understood that sometimes these were caused by transient errors, we often invoked a root cause analysis process when an issue repeated itself. I’m sure that the environment in which we worked, a nuclear power plant, contributed to the idea that we should always understand why some system failed.

    I was reminded of this while reading Grant Fritchey’s “Understand the True Source of Problems” in which he relates a few “best practices” that are likely folklore stemming from ignorance rather than actual good ideas. I’ve seen a few of these rules at various places in the past, often implemented because they appeared to work. However, we need to remember this:

    Correlation != causation

    Just because you perform some action and observe an effect doesn’t mean that one caused the other. If that were the case, I’d never install new hardware or applications on my computer systems as I’ve had crashes during various installations. However I often back out a change, or retry it and realize that I had a coincidental result, rather than a related one. The same thing has occurred in more complex systems where an action appears to cause an issue, but in reality, the two items are unrelated, or loosely related.

    We often don’t take the time to determine the root cause of many issues, which is disappointing. While it often doesn’t seem to be a worthwhile use of resources, I bet that often we’d learn there are actions we are taking (or code we’ve written) that actually is the cause. If we learned from our mistakes and could avoid making the same ones again, we’d greatly improve the quality of our technology systems, with many fewer issues over years.

    Unfortunately, “good enough” is often good enough, even if it does result in a bit of downtime.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Code Reviews

    When I first started to be paid money to write software, I worked on my own. I had to test my own code, decide when it could be deployed, and make the decision to deploy it. I had to notify users, and over time, I had to delay deployments because my mistakes would stop a business from getting work done. Fortunately I didn’t cause any serious loss of revenue for a small construction company due to my coding errors.

    Later I worked at a large company and was surprised when I finished a piece of work and had to submit to a code review. My boss told me to get two other programmers, at least one a senior level developer, and have them review my code. In those days we reviewed code on paper, with a red pen reminiscent of my school days. The amount of ink expended on my work was a bit overwhelming, but fortunately I recovered and was able to fix most of the issues quickly.

    I’ve talked to a number of developers over the years and it seems that most of them make their own decisions about how ready their work is for deployment. Code reviews seem to be the exception rather than the rule, but I wasn’t sure if that was the case for SQL Server professionals. Are we more conservative and formal in our approaches? Here’s the question this week.

    Do you perform code reviews?

    Do you review just application code (C#, Java, etc)? Are stored procedures and functions included? Do you include DDL and DML changes in reviews? Are DBAs able to apply changes to production without a second employee checking them?

    In many ways, despite the stereotype that DBAs want to be careful with their systems, I’ve seen many DBAs rashly apply a change to a production server without any oversight or second opinons. It has seemed to me that having the control to decide what changes are made to a SQL Server instance has led to the execution of that power without any oversight for many individuals.

    Let us know this week if you review code, and what things matter. I certainly thing naming and structure matter, though I’m willing to let formatting go as many tools these days will reformat code in whatever structure an individual developer prefers.

    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.

  • Embrace Stored Procedures

    I’ve read a lot of pieces on stored procedures. Most of the articles were for using stored procedures, quite a few were against their use, and a handful with extreme opinions. This piece from Rob Conery, while perhaps NSFW, is one of the latest, and more entertaining, pieces I’ve read that discusses the value stored procedures bring to an application.

    For the most part I agree with the presentation, though not completely. It’s an interesting discussion about business logic v data logic, and while I do think that some logic isn’t really central to your business, there are ways you manipulate data that might be necessary in multiple parts of your business. Those manipulations might make more sense in the database as a stored procedure rather than trying to ensure every application implements (and updates) the logic in its own code.

    I do like Mr. Conery pointing out the fact that ORMs or custom applications might be extremely wasteful in implementing a process with multiple calls, and stored procedures handle these functions more efficiently. We have fast networks and powerful hardware, but those calls do add up, especially if your system gets popular and has lots of users.

    Ultimately I think the last sentence sums things up nicely: let’s wake up to the power of our relational systems. That’s something that developers should embrace. Why only use half the power of a tool you have? I do think the judgment of how much business logic to include in the database is worth debating, but not at an application by application basis. Think about whether each call, page, form, whatever makes sense as having the programming in the front end or the back end. Whatever you decide, you should be able to explain and justify your choice to the others you work with.

    Steve Jones

    The Voice of the DBA Podcast

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