Voice of the DBA

Category: Editorial

  • Security Through Chaos

    This editorial was originally published on June 16, 2005. It is being re-run as Steve is away at training.

    I can’t really disclose who told me this, or at which company, but I found it very interesting. Recently there was a worm that rolled through a large number of Windows systems on the Internet. A few friends told me about it since it had rippled fairly quickly through their companies. These were all large organizations, with over 2,000 people employed in each of them. However, one company had almost no infections. The spread between their internal systems was almost non-existant.

    Now I’m sure that you are all wondering what great technique they used so you can deploy it in your environment. I was as well, until I heard the details. I dismissed it at first, but then thought it did make some sense. I’m not sure I’d recommend the technique, but it was interesting.

    Their defense was chaos. They don’t really have a central IT organization, standards are almost non-existent, no central AD setup, not even a standard platform. They do make anti-virus, firewalls, etc. available, but it is up to individual departments, people, and labs/data center areas to deploy them as they see fit. Need a resource from another group? Better start making friends. Want to breach a firewall? I’d recommend buying a Starbucks card or a 6 pack of Red Bull for the admin of that firewall.

    Now this is a technology company and most of the employees are fairly smart technologists. They are each responsible for the most part for their systems. If they break it, they need to fix it or find someone to help because a broken computer is not an excuse for work not being complete. But an individual can have a Mac, PC, Sparc, run Windows, Linux, whatever, as long they get their job done.

    Samba is in use as a file system in many places, but permissions control is distributed. Sarbanes-Oxley, secuirty, auditing, etc. all still apply, but there is no central group that ensures it’s performed in a consistent manner.

    As I mentioned, at first I was shocked. I thought this was ridiculous. But the more I thought about it, the more I realized that it made some sense. You couldn’t easily break into their network because what worked in one place wouldn’t necessarily work in another. Compromise one password and you might not get very far at all, even if you had an administrator password.

    I’m still not completely sure what to think of this, but apparently it works. Probably just like my life with three kids and a wife that works hard. You juggle all the balls and hope none of them drops.

    Steve Jones

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

  • Monitoring After Deployment

    Deployments of database and application changes have tended to be stressful in my career. I’ve seen the actual deployment of changes fail, installation programs crash, the startup of an application error out, or the famous blue screen of death from a .DLL change. I’ve been most wary, and worried, when the changes were out of my control, and I had no chance to review things.

    For most database upgrades, however, I’ve been able to examine scripts. Even when an installation program is making changes, I could run a test on a separate instance and use Trace to capture the SQL that would be executed. However even when the actual installation succeeds, what I’m often most worried about is a logic or performance problem that won’t reveal itself for hours, often not until the next business day when the system is under full load.

    This week I wanted to know what formal (or informal) checks people might have to detect issues. I’d like to know if you are you actually monitoring systems in a scientific way that allows you to determine if some database deployment has gone awry.

    What Monitoring process do you use to determine if a deployment is working?

    One of the customers for Red Gate software noted recently that they have a series of metrics being captured on their systems with SQL Monitor. They can compare the performance of two time periods, say the 4 hours before a deployment with the 4 hours after (or 4 hours the next day) to determine if any issues have surfaced. That’s similar to what I’ve done in the past for performance using other tools, or my own metrics. I’ve often kept baselines of common queries run during the day, and I can compare that to post-deployment metrics to uncover potential issues.

    Logical errors are more problematic, and I’ve often wanted to be sure I had a couple of backups stored, and at least one backup on a warm standby in case we find information is being incorrectly changed or calculated. Those are the worst errors, and since they is no way to determine how they might surface, I have usually ensured myself and other DBA staff were on standby to perform some manual ETL to move data around between databases in a hurry.

    Monitoring is an important part of being a DBA. It’s not as critical as backups or security, but it’s not far behind in letting you know when something is not right. Let us know this week what tricks you might have to ease the worries you have after database changes.

    Steve Jones

    The Voice of the DBA Podcast

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

  • QA for Data

    Here’s a typical DBA scenario. You’re at your desk, working on something, when you get a trouble ticket, phone call, or tap on your shoulder. There’s a problem in production and an order, or some other important piece of data, is incorrect. Can you fix it?

    Many of us are happy to oblige. Often we know what needs to be changed and can write the INSERT/UPDATE/DELETE statement to correct the issue. Even if we have to dig in further, we can usually find the data in a table and determine what should be changed.

    However, what do we do then?

    If you are asked the make data changes to production, do you QA the script? Do you save the scripts in a VCS or a log? Does anyone else examine the data or code? Do you scan the script yourself, have SQL Server parse it, or bother to look for triggers that might fire? Do you bother to put the script in a transaction with error handling?

    I have to say that in most of my career, I didn’t bother with any of that. I’d examine a script, perhaps have another DBA or developer look at it, but most of the time I’d run it in production if I thought it was ready. I did often wrap the code in a transaction, with a query to check the results, and an immediate rollback to prevent blocking. However if I thought the results looked good, I’d rerun the query without the transaction. Most of the time that worked well. A few triggers and fat fingers caused issues that I had to fix again, sometimes after quite a bit of work to correct my mistakes.

    It’s hard to prevent data change errors, especially if it’s something we do often, or we work in a small environment with limited staff. Ultimately, we might not even care because if we update the data incorrectly, we can run another update to fix the new issue. That’s not necessarily what most managers would want to hear, but it’s reality.

    The one thing I have insisted upon in my environments was logging any queries run. A simple cut and paste with a timestamp. That way if things were done incorrectly, at least I know what was done.

    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.