Voice of the DBA

Category: Blog

  • Roadtrip! SQL Saturday #53 – Kansas City

    kansas_city_mo After speaking this coming weekend in Denver at SQL Saturday #52, I’m heading to SQL Saturday #53 in Kansas City the following weekend, to give my Modern Resume presentation again and meet some SQL pros in another city.

     

    It’s not just me, however, that is making the trip. Chris Shaw (blog | @sqlshaw), Marc Beacom (LinkedIn | @marcbeacom), and Carlos Bossy (LinkedIn | blog | @carlosbossy), all will be speaking with me in Denver (our home) and then making the Roadie to KC.

     

    No, there’s no tour bus (hmmmm, maybe next year), but we will all be heading to KC along with some great out of towners. Arie Jones (blog | @programmersedge), Wendy Pastrick (LinkedIn | blog | @wendy_dance), and Jorge Segarra (blog | @sqlchicken) are coming from out of town. And for a long plane trip west, @TheSQLGuru, Kevin Boles (LinkedIn, @thesqlguru), is flying his plane from Alambama to come and share some great T-SQL tips with the crowd.

    I’m excited for two reasons. One is that I get to meet a whole new group of people in a city that I’ve only driven through, never spent any time in. It’s always great to meet new SQL professionals, and I’m looking forward to Kansas City, and finally shaking hands with a few people that I correspond with on a regular basis.

     

    kansas_city_royals_field-9435 The second is baseball. The speaker’s dinner on Friday night is at the Royal’s game, and I’ve never been to that stadium. My goal is to attend them all one day, and I’ve been to a lot (NYC, San Diego, Seattle, Denver, Baltimore, Arizona, Boston), and this will be one more.

     

    I’m looking forward to the trip, and looking forward to meeting a few people, enjoying some BBQ, and learning about SQL Server.

  • ACID

    What’s ACID in databases? I was asked this in an interview a long time ago and I couldn’t remember the exact meaning. For a (supposedly) senior DBA, that’s bad. I could, however, tell what it means and what it enforces in the database, I couldn’t remember the actual words.

    In building a presentation recently, I needed this, so I looked it up on Wikipedia. Isn’t everything on Wikipedia? And isn’t it true?

    In this case, they do a good job of explaining the various terms. ACID stands for

    • Atomicity
    • Consistency
    • Integrity
    • Durable

    I won’t repeat their explanations, but try to give my own take on this.

    Atomicity – If you have a transaction that makes some change, it has to all succeed or all fail. No partial transactions. That doesn’t mean that each statement succeeds, but everything wrapped in a transaction, whether 1 or 100 statements, all get completed or all rolled back.

    You can appear to muck with this by nesting transactions, but not really. SQL Server follows this rule of enforcing transactions as a complete unit or work. Either all committed, or all rolled back.
    SQLdependencytracker[1] 
    Consistency – This means that the database essentially enforces consistent change to the database from transactions, and that the database is logically consistent at all times. So references are enforced, cascades take place as part of transactions, etc. It’s a strange concept, but it really means that the database enforces all rules defined.
    Note that one funny thing here is the internal sysreferences aren’t always consistent. That one bugs me, though my employer, Red Gate Software, has a tool that finds these: SQL Dependency Tracker

    Isolation – I always get this one wrong, thinking it’s integrity, but really that’s the consistency piece. Isolation means  you cannot access data that is changed but still in an uncommitted transaction state. This essentially ensures that you get a consistent, accurate view of data. SQL Server allows you to bypass this with dirty reads, and lots of people do this to improve performance, but I think it’s a bad idea in general.

    Durability – If you have any type of failure, usually hardware, this ensures that all committed transactions can be restored. Or that uncommitted transactions are rolled back.

    SQL Server enforces this with its roll back/roll forward process when a database is started. This uses the write-ahead log to ensure that the database is in a durable state when users access the data.

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

  • Top 5

    I’m back in the top 5 for the first time in a long time. I’ve been spending too much time in the forums lately, avoiding other work, but trying to answer some questions for people and help them out.

    top5

    I used to always be in the top 5, but this year it seems we’ll get a bunch of people that are working on the Question of the Day, and they’ll do a lot in a short period of time. Since those can be 1, 2, or 3 points, they can quickly amass hundreds of points in a 30 day period.

    Still, this is a good reminder to spend some time on other things, like my upcoming presentations.