Voice of the DBA

Category: Blog

  • T-SQL Tuesday #45–Follow the Yellow Brick Road

    tsqltuesdayIt’s T-SQL Tuesday time again, and this month Mickey Stuewe hosts the party. It’s an interesting topic, and one that I think is important, and will be more important, as we capture, store, analyze, and depend on more and more data.

    The topic this month is auditing, and you can read the invitation on Mickey’s blog. I think it’s amazing no one has asked us to write on this in the past, and I expect we’ll see some interesting stories that people will share.

    This is a monthly blog party and all you have to do to participate is write a post on the 2nd Tuesday of the month. The topic comes out about a week before, and you can schedule something to go out on the appropriate day. Be sure you leave a comment on the host blog or a pingback.

    Auditing

    I’ve never been bound by regulatory requirements for strict auditing. When I worked in industries that had bounds, they weren’t really updated for computer systems, and lots of our practices slipped by because no one thought to look. A few times that was scary.

    However I’ve implemented auditing in various systems, in various ways. The most common way to do this in the past was with triggers, though I had a situation where I had to build a unique solution that would capture changes being made to systems.

    I worked for a small company one time and we had a few critical systems our clients regularly accessed remotely for various functions. This was in the era of client server systems, and we delivered software to our clients that connected to our servers. We had lots of buggy software, and when we released new features, I’d cringe as I knew we would have calls and complaints for a few weeks as we patched things.

    I ran a team of 3 DBAs, and we had 5 or 6 developers working for another manager, including a few remote developers. We had agreed to limit changes to known times, and document the packages being deployed so that we were aware of changes and could better troubleshoot issues. Our environment was complex enough with multiple processes communicating with each other and SQL Server without adding the issues of unknown changes. We communicated things to our development staff, and though they grumbled everyone agreed.

    One day I came in to find that a process that had worked the day before had failed. We were struggling to get it working, and eventually discovered that a schema had been changed. This was in response to another enhancement, but it caused an import process to change. We hadn’t scheduled a change, which must have occurred overnight. No one admitted to making a change, and after a long day, I had to drop it.

    But not completely.

    I went back and set up a server side trace to run and capture information for object changes. I let this run constantly, rolling over the files periodically. It was a bit of a load on our system, but I felt it would be short term and it was important enough to understand what was happening.

    A few days went back and we were having an issue with another part of the system. Clients were screaming at my boss, who was coming to the development area to complain to DBAs and developers alike. As we tried to reproduce the problem on test systems, suddenly it started working.

    Relieved, but concerned, I decided to check my trace. Sure enough, I found a change to an object logged, by none other than that craft “sa”. Digging in further, we discovered that a piece of middleware had a timestamp of a few minutes before the system started working.

    It can be hard to track down who is using an “sa” account, which is why it is not recommended as an account to use by individuals. Cross referencing some information from the network team, we discovered our remote developers were slinging code changes on the system whenever they felt like it, using sa and “Administration” on Windows.

    They patched systems at times, but often these were patches to fix issues in poorly written software they’d deployed in the first place. They were also patching patches when thing didn’t work. Whether this was in reducing the time of problems or not was difficult to determine, but it certainly meant a lot of wasted time when we tried to troubleshoot issues with no idea what had changed. We also found lots of their changes weren’t being added to version control.

    My boss agreed this was an issue, and it allowed me to change all system administrator passwords, as well as revoke all rights to production systems from developers. Eventually we achieved more stability in our systems than the company had ever found. I’d attribute this to preventing cowboy coding changes to systems, which seem to cause as many issues as they fix.

    Auditing is something I’ve depended on, especially for myself. As I’ve worked with more and more systems concurrently, I find that it’s easy to forget about changes made in a hurry, or forget which change occurred on which systems. Logging and auditing allows me to retrace my own steps, as well as those of others.

  • I Hesitate to Commit

    At work my boss this week my boss gave me some quotes about me. We solicit feedback twice a year from co-workers and use that to evaluate how employees are doing. This goes up and down the chain, so everyone from the CEO down gets anonymous quotes.

    A few of mine were that I am pleasant and helpful and always looking to do things when people ask.

    My boss laughed, and said those obviously weren’t from her. They weren’t because she has to struggle with me to get me to do new things. I get a lot done and don’t need management, but I hesitate to tackle new projects or agree to get them done. She has to ask me more than a few times and remind me if I haven’t committed.

    Why?

    Here’s a great example of why:

    Hard Earned Lessons Spots – Raw

    I wrote an article recently on disaster recovery. One of the marketing people wanted some video ad spots to use. A 30s one and a 1 minute one. She asked me, and I said I might be able to. My boss asked, and reminded me a few times. I didn’t commit because of time. I think she, probably everyone, wondered how hard is it to make a 30s spot?

    During out meeting she reminded me that I had said I’d try to get it done by this Friday (today). I explained I’d try, but not promises.

    I took a 2 paragraph script, 10 things and an opening and closing and had to work with it for 30-45 minutes. I practiced it, timed it, realized it was long and had to cut it to a few versions. I finally felt comfortable and loaded it into the prompter.

    I went to shoot it, thinking this would be a 5 minute effort like my podcasts. It was 12+ minutes of video, along with some setup to move things around, and then a nice 30 minutes of editing to cut out the green corner, check audio, cut out the mistakes, etc. and just send the raw 3 versions over.

    With the issues in loading the video to the computer, this was 2+ hours out of my day and frustrating, but also funny.

  • Disaster Recovery Docs–Offsite

    Years ago I worked at an ISO certified organization, where we took our administration and planning seriously. We ensured everything was backed up, we had backup drills, we each practiced recovering systems, loading tapes, even calling the company that rotated our tapes from an offsite location.

    One of our administrators was very conscientious and documented everything. He had screen images, copies of vendor documentation, all linked and labeled in a great folder on our network. We used this documentation constantly while restore systems from mistakes or drills and updated it regularly. It contained vendor contact information and account numbers. Each of us could access is remotely, and I had used it a few times from home while working on a problem with a system.

    One day our auditor came in and in the course of examining our details remarked on the comprehensive documentation and said it was great. Then he reached over and turned off our very detail-oriented administrator’s monitor.

    “Now show me your documentation,” he said.

    Our administrator was a little shocked. All the documentation was on the network. We’d accessed it from home. We accessed it from other corporate locations. We had restored it from tapes.

    We’d never thought it would be unavailable.

    It was a sobering lesson, one that had interns printing off copies that afternoon, dating them and binding them into books, one of which was always rotated offsite to the tape vendor’s facility.

    As for the tape vendor information itself? After overcoming his embarrassment, our administrator made wallet sized cards with the vendor company, phone number, address, and account number. He laminated those and gave us each one to keep on our person at all times.

    Make sure you have copies of the stuff you need offsite. It’s easy not with the “cloud”, though you better secure the data with encryption and limit the key access to a few people. You never know what you’ll need to recover, but you never should  find yourself in a position of not being able to recover your documentation.

  • SQL in the City in the fall of 2013

    This fall I’ll be attending a number of SQL in the City events in the US. Once again we have a tour, though it’s a shorter tour than the 5 events in two weeks I was on last year.

    One of my feedback items last year was that we should be able to do a SQL in the City event that’s smaller. The big events are fun and amazing, but they’re also hard to put together and expensive. I’d rather do an event for a smaller audience, and at a much lower cost, and get to do 10 of them a year.

    Someone listened to me and we ended up scheduling 5 seminar events  (2 listed for now) along with 3 big ones in the US. We held a DBA oriented event in March of this year, and a development based one in Sacramento in July. Both went over well, and we have a few more scheduled later in the year.

    SQL in the City – The DBA Team, San Diego, CA, Sept 20, 2013 – We covered a number of items in Richmond, and we’ve scheduled a few sessions on backups, monitoring, and one TBD. This will be a half day on Friday, before SQL Saturday #249 on Saturday. I’ll be speaking there as well. If you can come, register for both.

    SQL in the City – The Development Seminar, Denver, CO, Sept 27, 2013. This seminar flows well as we present talks on putting your database under version control, automating your database build and test with continuous integration and smoothing out the deployment of your application and database. I’ll actually be presenting all three talks myself as Grant is other commitments this weekend. I’ll also be at SQL Saturday #190 on Saturday in Denver.

    SQL in the City – Pasadena 2013 in Pasadena, CA, October 9, 2013. The first of our large events in the US this year. We’re back in LA, near the Red Gate office in Pasadena. We’ll have a large staff for this all day event, which should be a great time for all. We hope to see you there for a mix of administrative and development topics.

    SQL in the City – Atlanta 2013, Atlanta, GA, October 11, 2013. We’re crossing the country for a Friday event in the South. My first time speaking in Atlanta, and I’m hoping to see something in the city before I leave. This will also be a mix of administrative and development topics.

    SQL in the City – Charlotte 2013, in Charlotte, NC on October 14, 2013. This is the final stop on our 2013 large tour, with us again covering administrative and development topics. If you can’t attend the PASS Summit and you live nearby, here’s a full day of free training. If you are attending the Summit, feel free to register for this on Monday if you are in town.

    We’ve tentatively scheduled one final Stairway to Development in Washington DC in December, just before the SQL Saturday there. More details to follow soon, and there’s a chance we’ll add a sixth one as well.

    Hopefully I’ll see some of you at one of these events. Whether we’ve met before, please feel free to say hi and shake my hand.

    SITC13_Banner_300x250_Speaker