Voice of the DBA

Author: way0utwest

  • A Broken Data Model

    This apparently wasn't foolproof on one of my trips.
    This apparently wasn’t foolproof on one of my trips.

    On one of my recent trips, there was a delay in leaving the gate. There were two passengers, both of whom had the same seat assignment on their boarding pass. You’d think there would be some sort of primary key that prevented this situation from occurring, but with the constant reassignment and movement of passengers at the gate, I can understand this happening. We print passes at a point in time, but since data can be revised, and new passes printed, there potentially would be a conflict.

    However what came to light as we waited was both disconcerting and puzzling to me. The real problem was that one of the passengers was supposed to be on a later flight to the same city.

    Apart from the security implications, it seems to me that the ability of someone to get through the scanner near the jetway with a boarding pass for the wrong flight implies a broken data model. I can only guess that the scanner is reading a passenger’s name, destination, and seat number. Or that the flight data model is broken and there are two flights on the same day that share a flight number. The journey was short, so I suppose this airplane could have actually been making two round trips in a day.

    I can’t image the complexity and problems that programming an airline’s flight system must bring to the data models and applications. With the constant shuffling of people, flights, and seats, I’m regularly surprised that it works smoothly most of the time. As my airline has modernized their systems, I’m even amazed at how much data they disclose on monitors to passengers waiting for upgrades or standby flights, and how quickly my mobile app updates with new data when there’s a change.

    I’d never experienced someone getting on the wrong flight before, and was surprised to find their software allowed it. However given that so much of the airline industry relies on systems that were developed decades ago, perhaps I shouldn’t be surprised.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

    Due to some technical issues, there is no podcast today, but we will return with a new episode tomorrow.

  • Starting with Git for Database Development

    One of the tools that Red Gate Software makes is SQL Source Control. It’s a plug in to SSMS that allows you to hook your database up to version control. I’ve used it a few times in the past in conjunction with Subversion to demo the product. However I hadn’t really used it for my own scripts since it’s primarily a single server system and not distributed. My own demos were on VMs with limited connectivity to the world.

    A few weeks back, I was flipping through the https://www.simple-talk.com/Simple Talk newsletter, and saw an article on Continuous Integration for SQL Server using Red Gate Tools, Atlassian Bamboo, and Github. I’ve had many people over the last few years ask me about Git support for SQL Source Control and I was curious to see how well we supported things. I read the article and started downloading things.

    The first thing I downloaded was GitHub for Windows. I installed it and wasn’t thrilled to see it not working. However what was unclear in the article and the installation was that this is just a GUI for your Git installation. Since I hadn’t installed Git, I needed to do that. Once I had done that, and set up a Github account, things worked fine.

    I have numerous databases that I use for talks, but I usually create and destroy them with scripts during the talks. Not quite the way that SQL Source Control is designed to work. It works with objects, not scripts. However I wanted to test things, so I decided to build a database with the objects in it already. I wasn’t sure this would work for demos, but it would be a good test.

    git_a

    As you can see above, there are a few objects in my database.

    My next step was to pick a place on my local drive where I could store my Git data. Since I do a lot from Management Studio, and use quite a few VMs, I wanted a standard location I could use easily and chose a folder under my SSMS installation.

    git_b

    Once this was setup, I created a new repository on my drive.

    git_c

    This was the place I’d keep all my code. With a free GitHub account, you can’t keep your code private, but that’s OK for this project since I give all my code away after talks.

    Once I’d created this, Git put the folder on my drive.

    git_d

    Inside the folder was nothing, just a few Git files.

    git_f

    And in my Github interface, there was nothing to commit.

    git_e

    The next step was to link this with Git. After selecting my database in the Object Explorer, I switched over to the SQL Source Control tab to link my database to source control. I entered the path of my Git folder (from above) and selected Git as my VCS.

    git_

    I chose a dedicated database, meaning this is just my database. The Shared database model can work, but I don’t like it for teams, or even solo.

    Once this was done, I could see my database linked in SSMS.

    git_h

    I clicked the “Commit Changes” tab, and could see my three objects listed there as new items to commit.

    git_i

    If you have an existing database, this is the situation you’ll find yourself in most of the time. You link it, and commit all the changes. Once you enter a comment (please enter real comments you’ll understand), and press Commit, the objects are added to your folder. If I look at the folder, I see more stuff:

    git_j

    Only a few folders have objects, and those contain the .sql files for those objects.

    git_k

    If I look at Github, I see my comment and the changes are committed, but unsynced. If I press the “publish” button at the top, these are sent to Github.

    git_l

    That’s it. Any changes I make to objects, which I’ll show in another post, will commit to Git, and I can sync them to GitHub.

    Come learn more about these topics on the SQL in the City 2013 tour.

    sitc

  • Our Race with Machines

    Terminator2-580-75
    Hopefully we aren’t racing these types of machines for our jobs.

    The Terminator andMatrix movies showcase a world where our computing machines have taken over the world to the detriment of humanity. These are worlds were the machines are superior to humans in many ways. It’s a view that scares many people and has them concerned about the future as we find machines doing more and more work for us humans.

    Whether you think we’ll reach a point where machines are sentient or not, we do know that for single tasks, or a limited domain of tasks, machines can excel at some tasks. We’ve seen tremendous improvements in any number of fields where computer technology combined with machinery has greatly improved the way in which a business can function. These improvements have sometimes come at the cost of jobs, which can be a problem in some cases as humans must adapt to the changing ways that organizations function.

    However the news isn’t all bad for human workers. I ran across a very interesting TED talk about machines and how we might compete or work with them. It’s from Erik Brynjolfsson and discusses some of the advancements, including the Deep Blue and Watson projects from IBM. While these projects have sometimes proved they can beat individual humans, groups of both humans and machines have proven to be even more capable of solving problems.

    I don’t know how the world will change, especially our jobs, but as I’ve watched technology be introduced, grow, and dominate business in the world, I’ve also noticed that there are more and more opportunities for those of us willing to work hard, learn, and adapt.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Small Data

    Big data is a hot topic these days. Like the “cloud” terminology we’ve been hearing about for the last few years, there isn’t a good definition of what “big data” really is. The best one I’ve seen so far is data that “doesn’t fit in Excel,” which I like. So many people perform their analysis on a spreadsheet of sorts, that if the data doesn’t fit inside their edition of Excel, they’d probably consider it big.

    The problem with big data, however, is that it while it contains more information, it can also contain more irrelevant information. That’s noted in this piece on small data (from Brent Ozar, PLF), where the author states the signal to noise ration may be decreased when you examine very large data sets. You may find that there are correlations that appear to causations. With enough data, with enough things to examine, you can often start seeing patterns that aren’t really there. These ghost patterns can lead you to draw incorrect, or at least less correct, conclusions if you do not investigate further and test your ideas on portions of your data set.

    Some of you might have noticed fractal patterns like this:

    Mandel_zoom_00_mandelbrot_set

    This is a well known Mandelbrot Set pattern. However if we were to zoom in on this picture, we’d find that the patterns repeat over and over again. What holds true for the largest image we have holds true inside smaller sections. The pattern repeats.

    The same thing can happen with patterns in business. We may see a pattern in a large set of data, but we should verify that it also holds true for subsections of the same data set before we make a decision based on that pattern.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.