Voice of the DBA

Author: way0utwest

  • Why Containers?

    I’ve been looking at and dabbling with containers for some time. I got more interested a few years ago as I saw the changes that Microsoft made to ensure container support for SQL Server, especially with the introduction of the WSL and Linux container support on Windows. This is one of those technologies that I think fundamentally changes the world, especially when we get good orchestration options, like Kubernetes.

    I was thinking about how the world changed as I read this piece with a little history of containers. It’s an interesting read, and eventually gets to the point of the title, the containers have changed the world. They have, and I think they will substantially change how we think about software in the next decade as more and more applications and systems move towards using containerization.

    Containers do change the idea of what we think of as a computer. It’s not quite a piece of hardware and some software. Now it’s just software and it can be running on all sorts of hardware, perhaps even at the same time with complex orchestration layered over a variety of hardware.

    What does that mean for us data professionals? I think this will alter the SQL Server world. This image of a Big Data Cluster is the beginning, with the same architecture likely appearing for “normal” OLTP workloads over time, though likely in some managed environment. I think hybrid, Azure Arc type of architecture, where we have SQL Server containers connected together and allowing us a highly scalable data platform.

    I suspect we’ll have lots of other software components, communicating across networks, each in its own little container. We’ll learn to build and update containers, and ensure communications between different components. I’m already seeing some uptake at Redgate in creative ways, and I suspect we’ll deliver more containerized solutions, as will many others.

    Containers are the future, more so than VMs, and I’d recommend you start learning something about them.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • T-SQL Tuesday #124–Query Store in Action

    tsqltuesdayToday is T-SQL Tuesday #124 and I’m dropping a quick post as I’ve had lots of other stuff to do since returning from my sabbatical. Tracy Boggiano is the host this time and she asks about Query  Store.

    A Quick Review

    I don’t use Query Store (QDS) much. I don’t have to deal with the production operations much, and really, I’m not supposed to worry about the SSC servers. We sometimes have bad plans and bad code, but it doesn’t affect the performance of the servers overall (we’re over provisioned) and we use SQLServerCentral as part of our SQL Monitor demo site, so we want some bad stuff showing up.

    5129kPl5kCLGrant wrote a book, SQL Server Query Store in Action, that is on the Kindle Unlimited list. Since I subscribe, I grabbed this last year and dug in on a few airplane flights to see if I’d missed anything about how the QDS works.

    It’s a good intro look at what the QDS is, how it works, and how you can get information out of it. Grant does a good job of digging into the DMVs and helping you to understand what happens outside of the SSMS GUI.

    Grant and our host, Tracy, have another Query Store book that goes into more detail ( and it more recent) if you want to learn more.

    Overall, this is a good basic look at QDS. If you want to get started, and subscribe to Kindle Unlimited, give this a look.

  • Data Modeling in a New World

    The world is changing in some fundamental ways as we recognize and deal with some of the social changes that are occurring regularly. We have had new countries appear and old ones disappear in my lifetime. Borders are redrawn and political systems change. Many of those are just data changes that we can import from some recognized source into our tables.

    There are other changes that might change the way we model our database schemas. One example is the concept of gender, which has changed from the simple male and female of my youth. I haven’t thought much of this, and many of you might not have as well. No matter what your opinion of gender fluidity is, you should still understand how this might impact software and your database systems.

    I was thinking about this while reading an article on designing forms for gender diversity. While the form is important, equally important is how we handle this in the database. A single character might not be enough, but even if you encode various values, what do you use? I haven’t seen a standard and likely this area of data capture is still evolving.

    I suspect that ISO/IEC 5218 needs to be updated, and until it does, I think many of us that work on schema models ought to think about being flexible and storing data in a list, perhaps in a lookup table of some sort. The UK has recommendations, but they don’t appear to have much granularity for the different ways people identify. New Zealand is slightly better, but this does appear to be a thorny question from the modeling perspective.

    Much of the reason we capture gender for is some marketing purpose. This allows data analysts to better understand behavior as a person’s gender sometimes provides some understanding of other behavior. While adding in new pronouns, titles, and genders can increase complexity, it’s also important from the standpoint of having data that is useful.

    With that in mind, it makes sense to not use hard coded constraints in tables, and ensure we have a flexible data model that handles the data we are likely to receive, including some sort of not applicable or prefer not to say answer. As data modelers, our goals should be to accurately capture and record data, and that means we need to adapt to a changing world.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The Developer Arguments for Stored Procedures

    In all of the decades that I’ve been working with SQL Server, many people have been preaching the benefits of using stored procedures, In all of that time, the vast majority of developers that I’ve worked with have not wanted to actually write, or even call them. In one job, I offered to write all procedures within a day of being asked for them for one development team, and they still didn’t really want to use them. I made them do this by revoking permissions on many tables, but they still grumbled about the overhead of using procedures.

    I was having a discussion with a friend recently about using procedure, and he was having a similar battle. There was a group of developers that wanted to embed SQL  or use LINQ->SQL in their applications instead of calling stored procedures. It was frustrating, but he managed to secure a meeting to discuss the concerns with the developers.

    As we talked, we tried to frame the problem from the perspective of a developer. Why would or wouldn’t you want to use stored procedures? We had a few ideas, but really, I’m curious what you think today. Do you have any debate points that make sense from a developer point of view?

    I certainly do understand the ease of just writing SQL in whatever IDE you use for C#, ASP.NET, Java, etc. I get not wanting to open up SSMS, which can be slow. I also get that it’s nice to see all the code in one place, and not have some split between the application and the database.

    On the other hand, the idea of encapsulating code in the database feels a lot like the reason developers refactor code into new classes and methods to provide a clean interface between different types of functionality. Many times developers work with applications and code split among different components. On top of this, when you use procedures you push a lot of the performance work down to the database and on the DBAs.

    If you think stored procedures do or don’t make sense from a developer point of view, let me know today. Leave a comment in the discussion and explain your position.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.