Voice of the DBA

Author: way0utwest

  • Rulebreaking Developers

    The VW emissions scandal fascinates me. Maybe because it’s a case of software being used to fundamentally alter a product to deceive us. Maybe because I’m a car guy and have a soft spot for VW and the old Scirocco I used to drive in college. Maybe because we have blame being passed between management and software developers that could potentially affect our industry.

    In any case, the latest news I saw was of a press conference where the upper management of VW seems to indicate that the software developers on the project decided to alter the software. Certainly I’m sure some managers were involved, but I still fundamentally find it hard to believe that a small team engineered these changes independently. Even if they did, I think that ultimately upper management throughout the company has to take responsibility. After all, they took credit for the sales.

    There are a couple interesting points made by the company. First, they are upgrading IT systems to track work better. I’m hoping they’re moving to more of a DevOps practice, with transparency and openness, as well as lean processes that can be changed quickly. I suspect they might be moving to more overhead and auditing, but time will tell.

    The other interesting thing is that the company noted that even when technical solutions were available, they weren’t used. To me, this directly points to the problems so many of us face as technologists. When we have better solutions, or we can correct deficiencies, management doesn’t want to spend the time or effort to do so.

    That is one of the fundamental things that makes software so powerful. We can make changes, we can improve things, we can implement better security in a way that’s a fraction of the cost of making similar changes in the analog world. However it seems that even smaller efforts aren’t worth making if there is a little more profit to be squeezed out. At least, that’s what managers seem to think.

    That’s a part of business culture that I’d like to see evolve. Let’s look to continually improve things, making them better, more secure, and less bug ridden, even if it slightly slows down new enhancements or costs a little money.

    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.

  • Is Your Deployment Process Perfect?

    I’m sure your deployments aren’t perfect. I’ve had many successful ones, but I have always found some flaws in my systems over time. I’ve also found that the less flexibility I allow, the more likely things are to succeed.

    However less flexibility isn’t what most companies or clients want. In fact, they want the opposite. I know most developers want this as well, without constraints on what they can do.

    I think Redgate has been working to make things easier and more reliable for most companies. Certainly we have work to do and there are places to improve our products, but our DLM work on various products is really designed to reduce, or eliminate, silly mistakes that cause issues. We’ve tried to help companies set up a process that limits regression issues.

    I saw this short video from Redgate, where the lead Data Anlyst at Yorkshire Water talks about some of the improvements in their development process come about by implementing a DLM process. Over a couple of years, they’ve gotten many of their projects into a Continuous Delivery stack and that’s just the way they do things.

    Yorkshire Water

    The time savings are the big issues. There is less copying of production data around, more confidence in development, and as Shaun notes, time is money.

    If you think you want to build a more repeatable, reliable database development process, I think we give you a few options. The traditional, work in SSMS approach with SQL Source Control, or our Ready Roll work in Visual Studio. I’m particularly excited about Ready Roll as that’s how I’ve done a lot of development in the past.

    And, of course, I think our DLM Automation suite, allowing you to deploy changes to a CI environment and build deployment packages works well.

  • Multiple CTEs – #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

    One of the techniques that I find very handy in solving SQL problems is the CTE. This is much easier to read, for me, than embedding code into a derived table. For example, which of these is easier to decode, or perhaps more importantly, debug?

    WITH calendarquarters (qtr, yr, dt)
    AS
    ( SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
           , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
           , c.CalDate
       FROM dbo.Calendar AS c
    )
    SELECT *
    FROM calendarquarters cq
    INNER JOIN dbo.CustomerSales AS s
      ON cq.dt = s.LastSale
    Or this:

    SELECT *
    FROM (  SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
           , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
           , dt = c.CalDate
       FROM dbo.Calendar AS c
    ) cq
    INNER JOIN dbo.CustomerSales AS s
      ON cq.dt = s.LastSale

    I’d argue the first becomes easier, especially when I have multiple tables in the join. In this way I can more easily see in the first example I’m joining two tables/views/CTEs together. If I want to know more about the details of one of those items, I can easily look up and see the CTE at the beginning.

    However when I want multiple CTEs, how does this work?

    That’s pretty easy. It’s actually just listing each CTE, separated by commas. In my case, I wanted to do this:

    with upcte

    as

    (select uplen = len(replace(floorstring,’)’,’’)

    from Day1

    )

    , downcte

    as

    (select downlen = len(replace(floorstring,’(’,’’)

    from Day1

    )

    select uplen – downlen

    from upcte, downcte

    That’s it. I use the WITH once, and then each CTE stands along. I could add the column names if I wanted, but here I can easily see I’m querying two numbers from two “tables”, CTEs in this case, and performing subtraction. If I want the details, I get that from the previous definitions.

    SQLNewBlogger

    A quick post as I used this technique in solving Day 1 of the Advent of Code. This took about 5 minutes to write, and I got to add my own twist to the concept.

    References

    A quick one from my first Google result. While I knew how to do this, I double checked myself with a search.

    Multiple CTE in One Select Statement Query – http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

  • Hacking to Hide

    It’s probably no surprise to you that the black boxes for ships are vulnerable to hacking. These are the Voyage Data Recorders (VDR) that should capture telemetry, audio recordings, and more. These devices are really computers now, connected to the onboard networks used for satellite communications and physically accessible in many vessels.

    It was surprising to hear that some of these VDRs are running Windows XP. While I get that there is some ease of development in using Window systems, that OS wasn’t what I’d call robust and stable for stressful and rugged environments. Some systems use real time OSes, which seems like a better compromise, but Linux might be the best choice for a system both tolerant to a variety of conditions as well as one that might be easy to build applications for.

    However no matter what the choice, I’d hope that the developers building software for these systems would treat them with the importance they deserve. While lives aren’t at stake from these applications, liability is. These systems are used in legal proceedings, so the data they collect, in an autonomous fashion, should be protected to ensure its integrity.

    Apparently that doesn’t happen, as there are incidents of these devices being hacked an data erased, corrupted, interrupted, or even accessed by those on the ships. That’s not surprising as it seems people always find ways to take advantage of the computer systems they physically control. Ultimately I’d hope that we might constantly transmit some of this data off the ship to ensure there are backup copies, but that brings to mind the problems of securing data in transit, preventing access or disclosure and more.

    However these systems might provide a good testbed for researchers looking to better build and architect auditing systems. This is a challenging environment, with high stakes, and if we can develop ways to ensure auditing data is intact when we have lost physical control of the device for long periods of time, perhaps we can find ways to build this same auditing into other platforms.

    Steve Jones

    The Voice of the DBA Podcast

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