Voice of the DBA

Category: Editorial

  • Document, then Install

    I saw someone post a note that they had installed a new SQL Server and wanted to document the install. Did anyone have a good script or process for doing this?

    I’ve done this myself, but lately it seems to be the wrong way of actually building systems. One of the things I have started to feel is important is that I should strive for consistency and known states for systems rather than works of art.  However if I’m installing systems and then documenting them, I think that I’m doing things backwards.

    There has been a trend towards declarative actions in technology, whereby we tell the system what we want and it configures itself to arrive in that state. An example of how some this can be done is with Puppet. This is a case of the administrator essentially documenting what they want to be done first, and then letting the system put itself in that state. It’s almost like programming the installation and configuration of software, but with tools to make the process much smoother.

    In this model, administrators don’t need to document the installs. They’ve already declared what needs to be done. If vendors change defaults in the future it doesn’t matter, as the installations will configure themselves to the same state, ignoring defaults and human expectations. This also results in much more consistently configured systems, something that’s critical for building a smooth software delivery pipeline.

    The closest thing I’ve seen to this for SQL Server is the Finebuild project that Ed Vassie set up. That’s much better than unattended installs and I’ve seen Finebuild demo’d, and it looks good. I need to set it up so that I can ensure that every instance I set up is in a known configuration. I can have multiple configurations ready, but I’d rather choose one of my choices and then let the system apply the settings than remember to click a particular check box or change some item manually. I’d also appreciate this for DR and auditing, with some list of settings for each instance or host.

    That just seems more like a 21st century way of working to me.

    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. 

  • Fun Data Analysis

    The first part of this article is very true: “… correlation doesn’t always equal causality.” It’s a fun look at statistics and there’s a short video from a news station that contains this other great quote: “… numbers can be tricky.”

    As data professionals, we are often tasked with helping business people understand the information hidden in data. We look to identify patterns and relationships between the data that our applications collect or our companies purchase. However it’s not as simple as it might appear.

    This week, I’m wondering if you have some fun stories, or perhaps some scary stories, on conclusions that you or others have reached with data.

    When has a correlation been mistaken for causation by you?

    I know that most people in business don’t have extensive statistics backgrounds, and I’ve often seen people fooled by finding simple or obvious patterns in data that didn’t necessarily match reality. I’ve seen plenty of money lost because of bad decisions made by incorrect data interpretations.

    I once worked in a restaurant where a new chef decided to use sales data from the previous year to predict how many meals would be served. Using a growth factor from sales over the previous year, he increased his purchasing as the summer season approached. A month into summer, we realized that we were wasting almost twice as much food as we had in the past. I had a similar story where purchasing managers used a straight average of the previous three months that left is woefully short on imported wood.

    Let us know this week if you’ve seen mistakes made from naive interpretations of statistics.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Continuous Delivery for Windows?

    I read a bit about the next version of Windows, which is coming in 2015 as Windows 10. I’m not sure how much I care about some of the changes coming in the OS, though having CTRL+V working in a command prompt is very welcome. However I did read this piece about the people testing the Technical Previews and was a little intrigued by one quote: “… Threshold testers … will have those features and fixes pushed automatically to them…”

    Does that mean that Microsoft has re-engineered Windows to be integrated with a Continuous Delivery process? If so, then I think this is a good move. We’ve already seen SQL Server move to a pace that releases new versions every two years and bimonthly patches to fix issues. Imagine that we could get patches even more often, as bugs are fixed.

    Also imagine that we could get those bugs quickly rolled back and patches pulled if there are issues.

    I think that’s one of the interesting things for me. There have been patches in the past which caused, issues and were sometimes hard to remove. If new changes can be pushed out quickly, I’d hope they could be removed quickly. And with all the feedback that Microsoft gets from existing installations, I could even start to see custom patches built that are deployed to only certain configurations that are compatible with the patch.

    Of course, that’s an ideal view. I suspect that we’ll still see overworked developers releasing patches that not only fix issues, but cause other problems, and at times, can’t be removed. At least we’ll probably get the patch to fix the patch, a little faster than in the past.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A T-SQL Code Testing Guide

    Do we need a guide for code like this one? The piece linked is from a programmer at Google who gives some code review items that should be tested for. I’m wondering we we might want to have something similar for T-SQL code?

    I’ve given a few talks on how I to get started with tqslt, which is a great framework for unit testing T-SQL code. As I’ve learned more about it, and experimented, I’ve been amazed by how flexible it is and how it makes it easy to setup and run tests on T-SQL code. However I can only experiment with the way I code and the tests I think of, and I’m sure there are many other ways in which we can better verify that our queries work as expected.

    Testing has changed since I developed software in VB and C++. It seems that much thought has been given to building better unit testing that can not only catch bugs, but also ensure the code performs as we expect it to. There’s also the idea that we can run these tests in an automated fashion. That helps prevent some bug when we first write code, but it more often ensures code continues to work as we re-factor our work and enhance our applications. More and more I think that the constant and continuous regression testing is at least as important as initial testing, if not more so.

    I really hope that we improve our development processes and testing methodologies for SQL code. It seems that it’s an area where we can also increase the speed at we build better database applications, and improve the quality at the same time. If any of you are formalizing the testing of your T-SQL code, we would be interested in publishing your thoughts and results for others to learn from. Please feel free to submit an article.

    Steve Jones

     

    The Voice of the DBA Podcast

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