Voice of the DBA

Category: Editorial

  • A Nice Holiday

    It’s President’s Day in the US today, which is a holiday for me. I don’t think of it as much of a celebration, but it is a day off.  So, I’m taking the day off and republishing some content.

    This is a US holiday, but it’s Louis Riel Day in Canada, and celebrated in quite a few other countries as well for different reasons. I hope quite a few of you get the day off and don’t read this until tomorrow, Feb 21.

    In any case, enjoy the time off when you get it. Easy to get caught up in work and overwork yourself. I am working today, but in coaching, not technology.

  • Building Recommended Software Practices

    Many of us work inside an organization that has a process for building and deploying software. We may find our org doing this well, or we may feel our process is poor with lots of room for improvement.

    A lot of the discussion around how to be better at building software in the last ten years has been around the philosophy of DevOps. This concept doesn’t really prescribe how to build software, but give you goals to aim for. That means you still need to take the ideas of flow, feedback, and learning and decide how you implement them with your staff. What practices do you follow to ensure you can deploy quickly anytime your code is done? These can include ensuring you’ve tested code, getting feedback from customers, and more.

    I ran across a post on recommended software engineering practices for an organization. The list includes seven things you should do:

    1. Keep documentation in the code repo
    2. Have a mechanism for test data creation
    3. Use rock solid database migrations
    4. Create templates for new projects
    5. Automate code formatting
    6. Automate a process for new dev environments
    7. Automate preview environments

    This is a set of things I often preach to customers as well, especially 2, 3, 6, and 7. I often focus on the database and having curated test data, migrations you can count on and easy setup is important. And, of course, with SQL Prompt, you don’t need formatting ;). Just kidding, that’s important, too.

    These are solid practices, and none of them are that hard to set up, but they do require some discipline and willingness to work as a team and maintain your process across time. Each of these items needs some care and feeding across time to remain relevant and helpful to your staff.

    Do you have good software engineering practices? Are you proud of them and would you bring them to a new position? Or perhaps you wish your team would adopt better habits and a different mentality towards building software.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Cost of the Cloud

    Basecamp (formerly 37 Signals) is quitting the cloud. One of the founders gives some reasons, and he had some detail in a tweet on what they’ve spent in the cloud the last few years. Over USD$3mm on various services, though their costs in search seem very high. I don’t, and haven’t, run as busy a business as they do, so I don’t know if they’ve truly done a good job architecting things and setting up services. They say they have, though I’d expect everyone to say and think that.

    However, I’ll assume they are correct and they can’t optimize things any more than they are currently. Their decision makes some sense, and I agree with it. I’ve been surprised at the growth of the cloud, both in size and how quickly people are moving to the cloud. I’ve also been saying for years that if you have a steady or known workload, the cloud is likely very expensive.

    Maybe that’s worth it for your organization. Not dealing with physical resources, maybe having slightly less staff, maybe less CapEx vs. OpEx. Those are decisions for management and finance people. For most of us, the cloud both simplifies some tasks and makes others more complex. Provisioning, testing out Proof-of-concepts, and scaling are easy. Identity protocols, gaining (and keeping) knowledge of how various options work (networking, storage, etc.) , and keeping track of resources become more complex. Not to mention the world constantly shifting under your feet as cloud providers change how their platforms work.

    There are costs in both hard dollars (or your currency of choice) and in the time your staff spends dealing with a new way of doing business. The calculation of whether this is a cost that makes sense is very dependent on your situation. I have customers that love the cloud and others that hate it. The value they get varies dramatically and some would never go back to data centers while others are ready to work long hours to leave the cloud. Overall the sentiment is the cloud is great, but like many decisions made by management there are particulars that baffle the technical staff.

    The one thing I have learned about the cloud is that it takes a different sort of mentality from staff than on-premises resources. We have to learn to spin things up and down, scaling as needed. We need to better understand budgets and not look at costs as though they were personal expenses. We also need to be flexible with resources, understanding that machines that are idle are not sunk costs; they are ongoing costs.

    The cloud is amazing, and I think it is very useful in lots of situations, but a blanket move to the cloud can be expensive. Make sure that everyone involved in moving to the cloud understands that.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Growth of T-SQL

    I saw this tweet recently, where Richie Rump asked what has changed in T-SQL since the SQL Server 2012 version. A few people from Microsoft responded that there were changes in all versions, and while I think some versions have few changes, I decided to look.

    SQL Server 2012 introduced the window functions with the OVER() clause to SQL Server. This was a huge change in that many aggregate queries were much easier to write without needing complex GROUP BY lists and subqueries or unions to join together different data. While I’m not an expert by any means, I find lots of queries for reporting easier to write with the window functions, and I’ve grown to enjoy using these in code.

    Looking across other versions, I’ve seen these changes:

    SQL Server 2014

    • UTF-8 for Bulk insert
    • SELECT..INTO works in parallel
    • In-Memory OLTP language enhancements

    SQL Server 2016

    • temporal tables
    • JSON support
    • more In-Memory T-SQL changes
    • Security – DDM, RLS, AE T-SQL changes
    • R services

    SQL Server 2017

    • graph query
    • CONCAT_WS, TRANSLATE, TRIM, WITHIN GROUP
    • BULK INSERT options
    • Memory-optimized enhancements (CASE, TOP, JSON, computed columns
    • Python language services

    SQL Server 2019

    • Graph enhancements
    • UTF-8
    • Java and other language enhancements

    Some of these were to support other features, so perhaps these aren’t really T-SQL changes per se. If I look at PostgreSQL release notes, I see enhancements and changes, but relatively few new language changes. Certainly, there are some additions, but lots of improvements, which I think reflect the nature of a mature product. Not a lot of new things, but regular improvements and refinements to existing items.

    I’ve been working with SQL Server since 1991, and it feels like T-SQL has grown a lot in that time. Back then it felt like there were relatively few keywords and functions, requiring complex coding for tough problems. Now, with the way the language changed a lot in 2005, 2012, and 2016, it feels like we have a lot of tools at our disposal. We could always use more, and we got some neat ones in SQL Server 2022. I hope to see more useful changes in future versions to come.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.