Voice of the DBA

Category: Blog

  • Powershell in a Month – Day 3 – Commands

    This is part of my Powershell Challenge, to learn more about Powerhsell (PoSh) using the Learn Windows Powershell 3 in a Month of Lunches book by Don Jones.

    Day 3, chapter 4.

    This one is about commands, and getting familiar with what a cmdlet is, the naming convention, and how to call things. Again, simple stuff, but knowing that most everything is a Get-xx, or Set-xxx, or New-xxx, etc. is handy. I think the hyphen separation actually makes things easy, though the completion in the ISE and with tab is very handy.

    The big thing here is the parameter naming and cmd.exe execution. I wasn’t always clear on how parameters work, but this chapter helped. In this case, unless there’s punctuation or spaces, you don’t need quotes. That’s handy. I also like that the shell is explained in reference to cmd.exe, which is the command prompt that so many of us use.

    This was a short chapter, a quick skim, and I made sure that as the authors talked about commands, aliases, etc, I ran all the code. When I got to the end of the chapter, I was able to actually solve all the exercises without using any references. I used help to look up commands (like Get-NetFirewallRule), but otherwise, I was able to solve all the exercises in about 5 minutes.

    I felt successful and more empowered today. It’s getting a little exciting, and I can see where this will be very helpful over time.

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

  • Learn about Database Deployment in DC

    SQL in the City is coming to the DC area in a few weeks with a seminar on the Stairway to Database Deployment. Both Grant Fritchey and I are traveling to the Washington DC area to deliver a half day, free seminar event on Friday, December 6, 2013. We are likely going to be in the Chevy Chase, MD side of DC that afternoon, but I don’t have a location right now.

    If you are in the area, and want to see our vision of how to streamline your database development and deployment and make it run smoother, register today. It’s free, it’s a half day, and afterwards Grant and I will be chatting with you at a happy hour to talk shop, deployment, anything you want.

    The seminar is three sessions. I have been presenting the first 2, and Grant the third. The sessions build on each other and are the following:

    • Team-based Development with Version Control
    • Automated Database Build and Test
    • Best Practices for Database Deployment

    I’ll also be at SQL Saturday #233 – Washington, DC on Saturday, December 7, 2013. I think I have one session, and I may leave early as I have family in the area, but I’ll be there all morning and happy to chat with you, shake hands, or even just say hi.

    If you can attend, please register for SQL in the City or SQL Saturday #233. Both events are free, but we’d like to know who’s coming so we can plan.

  • More Vacation

    As I try to recharge and relax, I’m taking today and tomorrow off. Just a quick note that I’m trying to live up to my advice and get away from work when I can.