Voice of the DBA

Author: way0utwest

  • What is SQL Server not good at?

    I was reading a post that had this quote: ” The MongoDB docs tell you what it’s good at, without emphasizing what it’s not good at. ”

    This isn’t to pick on MongoDB, but the post did make me wonder what SQL Server isn’t good at? Should our docs, and BOL specifically, have warnings about when there might be problems with code? In addition to the remarks, should there be warnings about various features, functions and code? I image that the CREATE FUNCTION page might have some warning about scalar UDF performance in many situations, and I’m sure many of you would think of warnings that might be added for other features.

    I understand Microsoft might not like to point out flaws, but in the interest of building better code and applications, shouldn’t various versions of BOL, and perhaps all docs, warn the client about potential issues with using a feature in a certain way? Documenting the potential problems with using a feature in particular situations isn’t a flaw; it’s guidance about how misuse might introduce other issues.

    It’s not likely that we’ll start seeing more warnings in the official documentation, but for those of you that would like to improve the situation for others, there are always the “Community Additions” sections on all BOL pages. I don’t know if Microsoft would post your warnings there, but if they allow them, we might be able to help others understand the pitfalls of using a particular technique.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 15.6MB) feed

    MP4 iPod Video ( 18.3MB) feed

    MP3 Audio ( 3.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Backups and Consistency

    I wrote about transactional consistency recently. Why do we need this?

    Imagine that I have an orders table and an ordersdetail table in my database. The orders table has the order header (date, customer, etc) and the detail table includes information about the items purchased (product, qty, price). Let’s say my orders table has 10 orders and my detail table has 5 items for each order for a total of 50 rows in the detail table.

    If I want a backup that is consistent, I need to have all of the orders and details included in the backup. I don’t want partial orders, so all my inserts for orders and details are included in a transaction. That means my backup needs to contain a complete, new order, or no new order at all. Anything else wouldn’t be consistent.

    The backup process you choose needs to account for this. It cannot include partial orders from a transaction inside of it. Could that happen?

    Sure, and let’s see how. Backups proceed linearly and it takes time for them to complete. Imagine that I have a process that starts backing up my Orders table. It reads orders 1 and 2.

    While this is happening, someone inserts a new order, #6, into the system. If my backup system is reading pages, it might continue through orders 3-6 and then start on details. Imagine now the user rolls back the new order. When my backup completes, it will include (and restore) an order #6. That could be a problem.

    The other problem could be that the backup process gets to the details table and starts backing up the first 5 details from order#1. While this is happening, a user inserts a new order and order details in a transaction (order #7), and it completes. The backup process moves through the details table and gets all the details, including the new detail records inserted during the backup. However the orders table has already been backed up. The restore will include details for order #7, but not the header (Orders table).

    That’s bad.

    Don’t use file backups, no open file managers, no exports, no BACPACs as backups for your data. You might have serious data issues on restore, which get worse as your workload is busier on the system.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

  • It Starts with Version Control

    As a software developer, I learned about the value of version control over time. My first forays into development were as a child, where I made a single backup of my code on a separate floppy disk in the event I had an issue. This was my one-man-wolf-pack version control system, and it served me well, although it wasn’t efficient. In a few of my early development positions, we zipped up all our code each night to a separate folder, giving us the pentagram-version-control system. This worked as long as we didn’t need to go back over a week, but it still wasn’t very efficient. I think that because we worked on fairly small projects and could hold most of the code in our minds, we muddled through issues.

    Later I was introduced to a real version control system (VCS), Visual SourceSafe. Some of you cringe, but that system worked well. We could see the differences in code, and over time I learned to cut loose with my development, just trying new things, knowing that I could easily revert code backwards without much effort. Since then I’ve worked a bit with Subversion and Git, both of which seem to be very capable, mature systems. I still plan on getting some work done with Mercurial and TFS at some point, just to compare the features and functions.

    I started source controlling my databases after reading an article by Bill Wunder. This was over a decade ago, and it seemed to make sense to me. All of the things I had learned about VCS worked with databases as well, and I learned that having a database under VCS provided me with some stability in development, including the ability to roll back to previous versions of code when we found bugs. Trust me, checking out a previous version of a stored procedure is much, much less stressful than restoring an old backup just to look at stored procedure code.

    These days I see more and more people putting their databases under version control, which is comforting. It makes me think that we, as data professionals, are maturing in our industry. However there are still lots of people out there that don’t use a VCS for their database (DDL) code. Some don’t even use a VCS for their application code.

    There’s no excuse these days. Subversion and Git are free and open source. Download them, spend a few days, and get control of your code. All of the ways in which you can improve the efficiency and quality of your software start with more knowledge, and a bit of version control on which to apply your craft.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 23.4MB) feed

    MP4 iPod Video ( 27.2MB) feed

    MP3 Audio ( 5.7MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Testing Skills

    This editorial was originally published on May 11, 2009. It is being re-run as Steve is on vacation.

    How can you measure someone’s skills? Is there a good way to actually assess how well a DBA or developer will do in your company? If you have one, there is some money to be made by passing the information along.

    Traditional interviews where you ask lots of questions typically haven’t necessarily proven to be a great way to do this.  Some people give tests, but they often are thrown together, and might contain multiple choice answers to make them easy to grade. However the other day someone posted a technique they used that I liked.

    This person suggested that a VM be configured with Windows and SQL Server and then with a certain number of things wrong with the install. Or at least things that would be seen as wrong in that particular  environment. You then give it to a candidate and ask them to tell you what’s wrong. You could let them work through it in a stream of consciousness way, telling you what was wrong as they found it. You could also give them some time, say an hour, and then come back and let them explain what is wrong.

    Actually I’d recommend you give the candidate the choice since some people work better alone.

    I’ve thought about asking them to accomplish certain tasks before, but after considering this technique, I like it a bit more. Just asking them “what’s wrong?” or “what would you change?” is a good way to see how people think. Interacting with them, getting them to dig into a real system, is a great way to assess not only technical skills, but also some interaction. See how they debate, argue, or question a co-worker is important, at least it’s important to me.

    Is this is a better way to interview people and assess skills? Would you like to be interviewed like this? Maybe this would even be a fun competition at somewhere like the PASS Summit.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.