Voice of the DBA

Category: Blog

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

  • Back to Work

    It’s strange to be back to work on a Monday, with kids still in the house (they start school tomorrow), after being gone for the last 10 days  from work. It was really gone, as I didn’t process email or mess with work in any meaningful way. I did clean some spam over vacation when others were gone, and I added a few Database Weekly links last week, but I didn’t check SQLServerCentral, edit articles, or do anything really SQL related.

    About the only computer tasks I’ve done since the day before Christmas were a few hours on the Advent of Code stuff I’ve been working on in my spare time, and even that was minimal. I got stuck on a few puzzles, and didn’t have much time to work through them. I’d give it 20-30 minutes, but then break for family time.

    It’s a new year, and I’m starting slow. At least slow this week as I take stock of the publishing queues and start to plan how I’ll proceed forward. As I left last year, there were a lot of new technologies coming from Microsoft in 2016 that I felt I needed to get up to speed on. Some are out, some coming, but they’re a point of emphasis. Like Buck Woody with his Data Scientist work, and Grant Fritchey with R, I’m proceeding to learn more about analytics, including data lakes and statistics, as I move through the year. Particularly I’m focused on Python as the language to use, though from what I see with R, lots of the same libraries and formulas apply.

    I’m also looking to continue forward with the #SQLNewBlogger posts and see if I can inspire some of you to move forward with your own skills and thoughts on how SQL works for you. I’m going to look to get at least 2 posts a month out here, and perhaps more.

    Travel starts quickly, with a week long trip to the UK in two weeks and then SQL Saturday Austin, but other than that, it will be a light quarter for me. Just one event in Feb, and none in March. I am looking forward to that slow beginning.

  • 2015 For Me

    This was an interesting year for me. Certainly a time of change with one kid starting high school, one entering his last year, and one graduating from college. These events signal a bit of a change for me, and have me starting to look forward to a life without kids, where I may work more, or differently, and explore other interests. As I freed up some time, and became a bit excited by changes in learning for my kids, I also kick started some of my learning.

    After lots of puttering, I finally decided to actually get back to writing some code this year. I’ve been working to build small utilities in C#, Python, and M this year. My goal has been to not only improve and expand my skills, but also get back to some software development and observe how this aligns with my work on ALM/DLM for Redgate. It’s been exciting to follow along with the San Diego Tech Immersion Group, Pluralsight, and even the Advent of Code.

    In terms of travel and vacation, this was a pretty good year. Despite some long trips (two 10+ day journeys), I managed travel pretty well overall. Still a bit much as I was worn out by the time November hit, but I learned a few things. I am managing things better for 2016 already, limiting the number of trips I’ll take. I also managed to take almost all my vacation, and spend a few different trips with family. I’ve got at least one big family trip planned next year, so I don’t feel bad about carrying over the 3 days I have left.

    Career wise, I’ve enjoyed some of the changes that have come along in my work with Redgate. I do more development than administration, which I enjoy. I started more as a developer, so getting back to those areas, at least for a year or so is nice. I’m looking forward to some of the changes at Redgate, and am especially interested to see where we go with Ready Roll. I really like the product and that style of work.

    I’m certainly getting older, and felt my age this year more than ever. I struggled to run, even sporadically, and while my surgically repaired knee is fine, I think I hurt the other one playing baseball. We’ll see how it does over the winter with snowboarding, but I am more conscious of how I work and take care of myself than ever before. I think 2016 will be a rededication to building more muscle and improving health. In line with that, I think I may actually get a real standing desk I can adjust to allow me to go up and down during the day. I don’t mind standing too much, but I do get a little worn out and I’d like to have my desktop and laptop set up semi-permanently in one environment. Right now I drag my laptop to a couch or table, which isn’t ideal.

    All in all, a good year. Hope it was the same for anyone reading this.