Voice of the DBA

Category: Editorial

  • Finding Production Drift

    A few years ago when I started delivering presentations about automating database delivery with Grant Fritchey, I was surprised by something. We were rehearsing a talk and Grant mentioned that there were really only two places where we knew the state of our code: in a VCS and in production. I hadn’t really thought about that before, but realized it was true.

    While the development environments are always in flux, by using a VCS, you can determine the state of your code at any point in time. However since changes can occur in production (DBAs adding indexes, changing security, etc), you can’t count on a release branch of some other designation as being a guarantee of the state of your live environment. Production is always its own version of the truth.

    Ideally all of the changes being made on your production system are tested in development, QA, and pre-production environments before being deployed, but very few companies have a mature enough deployment pipeline to allow them to push hot fixes and patches rapidly enough, especially when they need to be made in an emergency.

    More than likely some changes will always be made in production directly, especially small ones that affect data. You can’t prevent this, but you should be able to deal with the aftermath and ensure that your system doesn’t destabilize from the enhancements. Without a doubt DBAs should log any scripts they run, but it’s easy to forget things, especially when changes are made in firefighting mode or at 3am.

    That’s why having some sort of monitoring system to watch production for any changes becomes an important logging mechanism for DBAs. Whether you build one yourself, or buy some software, you use the native tools or bolt something onto SQL Server, make sure that you put something in place. Work to push your ad hoc changes back to development, and ensure they get into a VCS at some point.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Your Job

    This quote is a great:  “I don’t really care if your code runs locally. If your code just runs locally, then my only option is to sell your computer so that our users can use our software, and that really doesn’t scale.” That quote is from an open letter to engineers and talks about what the job of a software engineer really is. It’s not just to write code, but to make sure our code and the entire system works for users.

    That’s important. It doesn’t matter how well you code if the application only works on your machine. We traditionally haven’t been great at getting code from one system to another and ensuring it works, at least not in a smooth fashion. We haven’t really followed an “engineering” process when we write, and deploy, software.

    The DevOps movement is changing this a big, bringing automation, scripting, and repeatability to both software development and operational deployment. All of these things should help us adhere to a more repeatable engineering practice. The commoditization of powerful hardware has also helped as many client environments might be nearly as powerful as development systems these days. It also means that our test systems can be good substitutes for production systems, at least if we can quickly build up and tear down large virtual environments.

    However we still need to be sure that we always remember that our code needs to work on other systems. Whether it has to scale down to meet the needs mobile devices or slightly older browsers and desktops, or our code needs scale up to handle thousands of times more transactions on a production database server than it processes on our laptops. We have to always consider the needs of the end user environment as we develop software. We need to test it in those situations, and broaden our skills to ensure that our applications satisfy the needs of the clients. And, of course, we should be able to quickly and easily deploy our changes to any other environment.

    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.

  • Statistical Analysis

    I ran across a book recently that is written to address some of the statistical issues the author has found in many scientific papers. It’s been released online for free, but if you like it and would like a more portable format, you can buy it for a reasonable price.

    I haven’t dug into the book much, and I certainly don’t consider myself to be a statistician. That wasn’t one of my stronger subjects in school, and it’s a weakness I’d like to correct at some point. However I have had to use some of the statistical functions in past applications, and I wonder if I was using them wrong.

    This week I decided to see how many of you are using more complex math in your systems. I’m hoping you understand how the functions work, but I wanted to ask what you’re using:

    What statistical functions have you implemented in a production system?

    I am thinking of functions beyond basic aggregates like SUM and AVG. Are you using standard deviations, linear regressions or some other complex functions? Have you made use of built-in functions in T-SQL, R, or some other language? Are you implementing custom functions in code or CLR Aggregates?

    I think this is one of the areas where our profession will grow more and more across the next decade. As we deal with lots of data of varying types, and our organizations look to gain some strategic advantage through deeper insight into their information, we will have lots of chances to experiment and learn more about complex data analysis.

    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

  • The Hidden Patterns in Data

    There’s a great TED talk on statistics, and one I’d recommend you watch. However, it’s not so much the topic and data presented, but the way in which it’s presented and broken down that becomes interesting. If we ignore the topic, and just look at the analysis, a few  things stand out that I’d apply to business.

    The first noticeable thing for me was the breaking down of the large bubbles into smaller ones. I think that all too often we try to boil down information to a single, or very limited number, of values. While the overall average or graph of a set of data can be helpful, it can also be distorted by outliers in the underlying data. Seeing the data broken part into quintiles helps to analyze how closely the overall average matches up with the data.

    The other item that I think sometimes get lots of analysis of business data is the change across time. We often look at year over year, or last period’s values, but we don’t dive much deeper into the changes over time. Seeing a series of numbers on a page works well for some people, but the animated visual can be helpful in discerning a pattern or making a decision based on data.

    This is a great look at statistics, and it makes me think many of these techniques can be handy in looking at something like server performance metrics and deciding if we need more (or less) resources, or even if we have good candidates for consolidation. I’m looking forward to trying to adapt these tools for my own uses.

    Steve Jones

    The Voice of the DBA Podcast

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