Voice of the DBA

Category: Editorial

  • When Should We Be Cleaning Data?

    I was reading Grant Fritchey’s update from the Business Analyst Conference  and noticed this quote: ” There were lots of sessions on the need to clean data, which implies that we need to do a better job at data collection in order to support this wild new world.” Grant was talking about the fact that many of the sessions seemed to imply that it’s mostly the processes around data that cause issues with data, not the people.

    However is that really what we should do? Do we want to do more and more cleansing at the point where people enter data? I’m not so sure that’s the case. The more that I deal with various applications and forms, the less I want to see too many fields and values required. Certainly there are pieces of data that are necessary for action, but I often find there are lots of additional fields that analysts want, but are more of a burden than a necessity.

    Most of us as data professionals design tables to handle the needs of an application. We put fields in tables that we expect to fill with data, based on a specification. However the real world is messy, and often the data we want to collect and store isn’t always available. The question is, do we force failures in the application or do we handle missing data?

    I don’t want to eliminate DRI, or go to an EAV models for lots of data. However I do think that we need to ensure we allow defaults and work with developers to allow data in our systems that might not be complete now, but perhaps will be in the future. We should work with analysts to help them build reports that can handle defaults or missing fields. We can work with developers to allow applications to request updates to data later and then design ETL that can efficiently fill in the updated information.

    Applications and software need to be flexible to work with problematic data. We have the ability, as data professionals, to help our clients still find meaning in data that might not be as complete as we’d prefer, or they’d prefer. However we can still find valuable information in what data they have..

    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.

  • Architecture for Auditing

    In the analog world, when we need to provide an auditing system for checking on physical activity, we usually separate out the roles of people that might record or track activity from those that perform activities. The better separation we have between individuals, the better we can usually ensure our auditing records are intact, complete, and trustworthy. We certainly have issues in the real world achieving these goals, but overall things work well.

    In our digital systems, however, auditing seems to be bolted on to the systems and not designed with the need to separate duties and protect our auditing records from tampering. I would have hoped that many of the software platforms (Oses, RDBMSs, etc) over the years would have recognized this (along with security) and enabled auditing as a function separate from Administrator/root/sa/etc, but it hasn’t happened. Far too often, we’ve operated under the assumption that administrators can, and should, be trusted.

    In SQL Server, most all of the auditing mechanisms are enabled, managed, and reviewed by administrators. While viewing is certainly helpful, and perhaps necessary, for good administration, there ought to be a way to enable auditing that doesn’t require DBAs to manage it. There ought to be some role for auditors, one that allows an independent individual (or group), to access the record of actions taken on the instance.

    Perhaps it’s not as big a concern for the world as I think it is. In most cases, auditing data exists to help solve problems, not to ensure a legal record of activity for sensitive actions. If that’s the case, then SQL Server provides lots of choices and options for the auditing of your system. Extended Events, SQL Audit, and more are valuable tools for the DBA. I’m just not sure their value extends beyond being useful tools.

    Steve Jones

    The Voice of the DBA Podcast

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

  • What Not To Say

    This editorial was originally published on Jan 26, 2011. It is being re-run as Steve is out of town.

    This article on 8 things your boss doesn’t want to hear made me smile. There are a few phrases listed that I would definitely recommend not using if you want to succeed in your current place of employment.

    Having managed a few DBAs in my career, I thought of a few more that I thought might fit the data professionals out there as “things not to say” to your boss:

    I’m not sure if more memory will help: You could substitute CPU or disk drives for memory in this one, but it’s your job to know. If you don’t, learn how to tell what your bottlenecks are and make appropriate recommendations.

    That additional memory didn’t help: The only thing worse than not being able to make a good recommendation is making one that doesn’t improve performance after your boss has approved the purchase. Now you look bad, and you’ve made your boss look bad.

    Company X has offered me $yyyyy. Will you match it? You can get away with this once at a job, or maybe once a decade. If you want to go to your boss with this one, be ready to quit if you don’t get it, and be ready to shine if you receive the raise. No one wants to be put in a corner, and I would be likely to let you move on if you came to me, unless I felt you deserved it. Even then, the second time you ask, I’m showing you the door.

    I downloaded this software off the Internet and it crashed the server: You can’t trust stuff you download, no matter what the Open Source crowd says. Test, test, test. Preferably on a test machine.

    It works on my machine: I have always wanted to answer this with a “Who gives a <insert four letter expletive here>?” My actual responses haven’t been that far away if it’s an important project and you give me this excuse.

    Feel free to add your own “things not to say” to the comments below.

     

  • 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.