Voice of the DBA

Category: Editorial

  • Archive Every Day

    I regularly see questions posted online from people struggling to manage a large archive process. Often the inserts into an archive table and the deletes from the source require large amounts of resources that strain systems and block other activity. These types of processes can also require lots of transaction log space, something many people forget about.

    The best solution, in my mind, is to archive regularly. Decide how much data you need and then trim your working set back to that level on a regular, preferably daily, level. That way you limit the amount of resources you need at one time.

    However that’s not always easy to implement in a system. I’ve encountered many applications that were set up to read from a single table and archiving data means no one can access that data unless it’s inserted back into the main table.

    I wish that we would have better patterns and frameworks for building archiving into systems from the start. I almost wonder if we should be using a view built across a a regular table and archive table from the start for accessing data. This isn’t a complete solutions, and there are certainly problems here. Maybe we should have partitioning implemented early on, though the edition limits on that technology are problematic.

    Microsoft is bringing us “stretch” tables in SQL Server 2016. Not a bad idea for archiving cold data, but as Brent Ozar points out, are they going to do better partition elimination? Will Microsoft give us a good way to handle backups? There are certainly issues here, and likely room for improvement in later versions.

    Ultimately I think the complexity of archival data doesn’t matter for most systems. These days 10GB of data is a pittance, and since many applications (and their databases) are smaller than this, why bother. If you have a larger system, however, I’d encourage you to think about archival before you need it. When you actually have time to implement it well.

    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.

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