Voice of the DBA

Category: Editorial

  • A Programmer’s Attitude

    I caught this piece on the need for programmers (developers) to not trust anyone, including themselves. It is written by a software developer for other software developers, but I think it can also apply to database work as well. It is a bit long, but it starts with the nature of abstractions in the world and how they let us work with simpler models of a situation or environment. However, most abstractions are leaky, and our assumptions about them can cause our systems to fail.

    The leap from trust to abstractions seems a bit funny, but it makes some sense. We ought to simplify our situations so that we can generalize how to solve them, but we also need to verify things. There are a few examples, one of which is we ought to use tests to ensure the code does what we think it does, including using a wide variety of data. We ought to ensure that refactoring something doesn’t break the system, or more often for databases, we return the same results. Changing a query to run more efficiently with joins or a window function instead of a loop or subquery should return the same results. Not just for one row but all rows, and across different inputs.

    One I especially like is the check on deployments. If we changed our code and had a deployment, did our code actually get deployed? In today’s world where different people might be responsible for merging and deploying code than those writing the software, we might want to verify that our changes actually got deployed. Perhaps reviewing deployment reports or logs can help ensure that we know the state of our live systems in addition to those in development.

    There are some suggestions for how to become a better software developer, and as you might guess, this requires learning. I think in today’s world, some group exercises (katas) or reviews can be helpful as well. Maybe even practicing new techniques in a sandbox and running them through a PR process to let others see them. It can be scary to ask others to review your code when you are learning, but they might teach you something, or you might teach them something. We build in teams; we should think about learning in teams, at least periodically.

    Becoming a successful software professional, data or application, takes regular work to improve our skills. Just as a woodworker might practice with their tools or a chef with recipes, we ought to practice with our tools. I’d hope that many organizations would also see this as something that needs to be encouraged with some amount of time allocated towards ensuring your staff continues to improve and produces high-quality results.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Cloud vs On-premises

    The cloud has been a controversial concept for much of its existence. While the idea has been around for many decades, AWS started selling IT services in 2006, with Azure following suit in 2008. Since then, the use of cloud services has grown tremendously. While some applications and organizations have embraced the idea from the beginning. I found many of you at SQL Server Central were very hesitant at first. I guess some of you are still skeptical about the value of a production database in a public cloud.

    From the beginning, I’ve felt that cloud computing has a place in the world, but in a way that is more appropriate for some situations than others. In terms of database (and maybe compute services), if you have a very well-known and predictable workload, the cloud can be very expensive. It might still be a good choice, but I think it often isn’t. If you have a variable or growing workload, then the cloud might serve you better than trying to keep up with new hardware in your own data center.

    Bluesky has had a tremendous amount of growth since its founding. Twitter invested in this as a distributed project and when Elon Musk purchased the site, many users moved away. A lot of them went to Bluesky, which had to deal with a quickly changing workload. They started in AWS, but eventually decided to move to an on-premises setup.

    Why? One would think their continued growth would mean AWS (or another cloud) would be a natural fit. However, they hired someone that provided an analysis showing they could invest in their own hardware, overprovision what they needed for growth, and keep up with the demands as they had developed a fairly accurate method of forecasting future needs. The savings in purchasing their own hardware allowed them to buy more than they needed and handle short-term spikes.

    To be clear, this doesn’t mean the cloud is worse for most or even many organizations. Bluesky knows they need to continue to invest in hardware, and they are prepared to keep adding resources. They also architected a distributed system that still allows them to scale into AWS if needed in the short term. I don’t know many organizations that would prioritize those things alongside the rest of their business. Most of us do a poor job of forecasting load. Even if we do, often the difficulties of purchasing new resources mean that we can struggle to meet increased demands.

    The companies that have moved to the cloud with success, and those that have left the cloud with success, are those that measure, monitor, and make appropriate decisions based on operational data, not opinions and feelings. They aren’t afraid to make a decision one way or the other, choosing what’s best for the organization, not what someone wants to do or thinks will be better.

    The cloud might be better for you, or it might be worse, but you ought to have a way to measure and analyze the options. You also need a talented staff that isn’t afraid to try new things and adapt their architecture to take advantage of modern hardware and software. Too many of us aren’t as flexible as Bluesky and might not have the success they have, in or out of the cloud.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Least Privilege

    I assume that most of you know about the principle of least privilege. If not, please read this short blog from Brian Kelley and make sure you understand how you should approach security. In the modern world, we also ought to adapt our systems for the zero trust model, which includes the least privilege principle.

    However, I wonder how many of your organizations really follow these security guidelines internally. Are you strict about adding limited access and removing it when people change jobs/roles? If you use Windows Auth (or Entra), are your admins doing that or just adding in new roles? Do you scope down database access roles in granular ways or just stick with 1-2 roles for the most common things people do?

    Maybe more importantly, do you use roles or are these systems that still have explicit grants for users?

    Microsoft had a major hack recently from a test account that had administrative privileges. While there certainly might be a need for a test account to have privileged access, I’d hope that any test account created had a limited lifetime. I’ve created privileged database access accounts for vendors, but usually set a reminder to myself to disable the account after xx days. When I got smarter, I wrote a one-time job to do that and scheduled it. These days, I’d also file a ticket for my team noting that this needs disabling as well.

    Humans get lazy and often don’t think about the future. If you’ve never had an issue with a test account, why think something might happen? Why spend the time writing a note or a job when surely you’ll remember or deal with it later? Maybe more common, why disable a login when the user might need access longer? We don’t want to deal with another phone call and enabling the account. That’s an interruption to our work week.

    What has been humorous to me is that I’ve seen quite a few people who are very security conscious get annoyed when some automated system or process disables their account and forces them to make a call.

    It is annoying. However, these little things, the details, the adherence to good practices are what help ensure we have better security. When we take shortcuts (like not enabling MFA), when we skip steps, when we do small favors for others, we’re increasing risk. Most of the time that’s fine.

    Once in awhile it really comes back to cause problems. I’m not sure the savings are worth it.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • The Stretch Database Retirement

    Stretch Database is finally going away. It is being retired. It was deprecated on Nov 16, 2022, from SQL Server 2022. Effective Jul 9, 2024, the supporting Azure service is retired. I saw this in an announcement on Jul 3, though I hope anyone using this service has been seeing lots of reminders over the last couple of years. I know I’m getting MySQL retirement notices for one of my services and need to migrate some workloads this month.

    If you tried this service, you might have realized that the pricing didn’t make sense for most of us. If you hadn’t tried it, it worked by moving some of the data in your tables into Azure, where it could be queried if needed. It was an interesting idea, though most of us would have wanted this to work between two SQL Server instances, not between SQL Server on-premises and Azure.

    In any case, if you’re on an older version of SQL Server, the recommendation is that you bring your data back on-premises. If you have SQL Server 2022, they recommend CETaS (Create External Table as Select), which lets you query data in Azure storage. This lets you put some data in text formats and query it as needed, reducing the use of expensive relational storage disks. Parquet is the recommended format here. There’s also a weird mention of Fabric in the announcement, which doesn’t seem to fit with the objective of the rest of article.

    I haven’t worked with this enough to know how well this performs or what patterns might fit here. I do know that reducing the queries from clients, especially SELECT * and unbounded queries across all your data helps. If your clients always want to query all data in a table, then nothing works well. Building systems that query hot data by default, and filter away from warm/cold data is always helpful. Having some good indexing is also important.

    I don’t know of anyone that uses Stretch Database, so I’m not sure how many people are affected here, but I hope they knew about this before Jul 9.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.