Voice of the DBA

Author: way0utwest

  • The Powershell Challenge

    indexI’ve been wanting to do more with Powershell (PoSh). I’ve seen Allen White present on it for years, and I’ve dabbled in various ways, using it to handle small tasks. However I haven’t really committed to learning a bunch of PoSh.

    Last month I was with Grant Fritchey on the 2013 SQL in the City tour and in one of his sessions, he uses some Powershell to audit his SQL Server instance. I thought that was pretty cool, and it made me want to dig in further. At the end of his talk, he mentioned a book, and I decided to grab it.

    It’s Learn Windows Powershell 3 in a Month of Lunches from Don Jones. The name alone made me want to grab it, and it has a catchy title.

    My plan is to take time every day or two and read a chapter and work through exercises in the book. I really want to get more comfort in PoSh because I do believe in it and I think it’s a fantastic way to get things done. I don’t work at scale, but I suspect with a little practice I’ll be using PoSh more often for simple things and saving those scripts.

    I’ll blog along the way and see how things are going. Once I get through, I expect that I’ll be digging into more with other articles and books that deal specifically with SQL Server.

    The days in the Challenge:

  • Transactionally Consistent

    What does this mean? I had someone ask me in a session recently, and I think I have a good explanation, but I thought this was worth a blog.

    Transactionally Consistent means that there is a snapshot of the database at a point in time that ensures committed transactions are actually reflected in the database and uncommitted transactions are not. I haven’t found a better concise definition, but Paul Randal has a great longer explanation.

    This is reflected in the ACID RDBMS model as the C for consistent.

    In a SQL Server backup process, this is accounted for by including some amount of the transaction log in the full database backup. The amount included depends on your workload and the amount of data you back up, and if you read Paul’s explanation, you’ll understand more.

    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.

  • Project Hekaton

    If you haven’t heard about Hekaton by now, you should think about reading up on it. There’s some information from TechNet, a keynote video, and more. Despite the ranting from Oracle’s Bob Evans in 2012 that this was “vaporware”, it’s coming in SQL Server 2014. The latest demos don’t show 100x increase in query speed, but they 30x or so numbers I’ve seen are very impressive.

    Hekaton is an in-memory database technology, actually called In-Memory OLTP in Books Online. This isn’t a re-invention of the pintable concept from SQL Server 2000. Instead it’s a very well though out architecture and rather interesting in the way it’s implemented. The keynote video gives you a deep overview, though to use the tables, most of us don’t need to know the internals of how they work.

    However we do need to look at the restrictions, err requirements. Since these tables will exist in memory, obviously you need more memory, but more than you thought. The recommendation is twice the expected table size, to account for versioning. In addition, this memory is in addition to the buffer pool and other memory your instance already needs. That means much more complex memory calculations and monitoring for DBAs.

    The complexity of SQL Server continues to grow over time as more and more features are added to the product. Many of us don’t need much more than the core relational database functionality for many applications, but the in-memory OLTP tables might be one feature that can help dramatically increase the speed of your application.

    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.3MB) feed

    MP4 iPod Video ( 18.1MB) feed

    MP3 Audio ( 3.7MB) feed

    Feeds are available at iTunes and Mevio

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

  • Learn about Encryption at SQL Saturday #265

    You can learn about encryption at SQL Satuday #265 is Portland, Oregon in a couple weeks. I’ll be heading out to the event on November 16, 2013, my first visit to Portland in 15 years.  In addition to my session on the various encryption options in SQL Server, I’ll be talking about your career with my branding talk, plus there are many other amazing sessions.

    A few quick links:

    Register for SQL Saturday #233

    Register for the FRIDAY, November 15, 2013, FREE Red Gate Seminar

    With the MVP Summit in Redmond the following week, you have the chance to learn about:

    Check the schedule for the details and to pick the best sessions for you. It’s a tough choice, but no matter what you choose, I’m sure you’ll learn a lot.

    It’s a busy day for me, as I’ll also have a lunchtime session where I’m looking to talk about Red Gate Software’s vision and experience with database deployment. We’ve been working hard to find ways to deliver our software faster, and we’ve put that knowledge into a few products. Our goal is to try and make it easier for you to deploy changes to your databases, so come let me know what challenges you face and what holes are in our solutions.

    It should be a great event, and if you’re in the area on Friday, November 15, 2013, and you want to learn about better database development and deployment strategies, Red Gate has a free SQL in the City Seminar that you can register for. Grant Fritchey and I will be talking about version control, continuous integration, and deployment in this FREE, half day seminar. It’s aimed at beginner to intermediate level people, so register today if you can attend.