Voice of the DBA

Author: way0utwest

  • Separate Reads

    Recently I was watching a presentation on how to scale performance in your SQL Server environment and one of the suggestions was setting up Availability Groups (AGs) and having read-intent connections that would query the secondary and not the primary. It’s not a bad idea, and the SQL Native Client (and other drivers) support this and make it easy to implement.

    The pattern of using multiple connections in an application, one for reads and one for writes, has been suggested often. However, in practice, I’ve rarely seen this work. Apparently having a connection variable, named dbConn, for writes and a second one, named dbConnReadOnly, for reads is too complex for most developers or teams.

    Or maybe the idea of having to pick the right access point is a human problem? I’ve seen no shortage of problems in restaurants when we have specific “in” and “out” doors. Lots of people go through the wrong one and we end up with plates and food on the floor. Even broken noses or fingers at times. Perhaps I shouldn’t pick on software developers too much.

    How many of you use two connections from apps? Meaning, do you think about reads and writes in separate connections. Even if you read and write from the same database, this can be a nice practice that future proofs code. It’s a small change, but it gives you room to grow if you get a read replica for analytics or reporting.

    Of course, you could take it too far with different connections for different “services”, aiming for a microservice-style architecture. We could have dbUser for user stuff and dbOrders for the business side, and other connections for other services. I wouldn’t do that, as I think many of us will get confused, and we’ll often be doing two different type of service things in the same code. If I need something from a customer to write an order, do I have two connections in my method? I could, but I bet lots of developers would try to re-use a single one.

    Plus, if developers get into trouble with two connections, then what will they do with 5 or more? There are lots of ORMs that might even support this, or if they do, not make this easy to code.

    I’ve always liked the idea of separating reads and writes, but maybe the better solution is using one connection whenever we have simple CRUD work and another one for any sort of complex querying or reporting. That would make more sense as I suspect many of us will eventually offload reporting or analytics in some way to another system. A Delta Lakehouse of some sort seems likely if the current trend takes hold in more organizations.

    Let me know today if you used (or have tried) different connections for reads and writes.

    Steve Jones

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

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

  • Simple Talks Episode 9–Data masking and subsetting

    The episode on data masking and subetting is out. You can see it here:

    Watch and check this out. This is especially close to my heart as I’ve been pushing for subsetting from Redgate for a long time. I think subsetting is incredibly important for development agility.

    Some interesting thoughts on the problem space, which isn’t a simple thing to solve. It’s not that complex (hard to understand), but it is complicated (lots of moving parts). As I’ve talked with others and worked on the problem, it’s not something that you can just knock out quickly.

    What’s funny to me is that Andy Warren and I asked Redgate for this in the early 2000s and they didn’t build it.

    We have a subsetter and masker at Redgate in our Protect/TDM area. Check those out if you need a solution for your org.

  • Monday Monitor Tips: Tracking The Cost of Instances

    One of the things that I’ve been asked in every operations situation is what licenses do we need for our servers. This is a rare request, often once a year, but it results in a bunch of work to figure out what’s running.

    I hate these requests because they always cause delays in other work.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts

    What’s Running?

    Redgate Monitor makes this easy to track (if you’re monitoring all your servers). In the Estate tab, there is a Licensing item. If you check this out, you see something like this:

    2024-10_0192

    This is a quick view of our systems, letting me know what cores we have in service based on the edition. This is a quick report, and if you’re monitoring all prod systems, this gives you a pretty close look at what you need.

    The exceptions here are that we are reporting all cores for all nodes in HA/DR setups, and that might be more licenses than you need. However, you can subtract all the standby nodes if you have Software Assurance on those systems since you do not need to license those.

    Of course, you shouldn’t need to license dev/test systems if you run developer edition, and you should.

    You can filter by groups or other items that can help you focus on part of your estate. This is the standard Redgate Monitor at the top set of filters.

    2024-10_0193

    There also is an “Export” button on this page, so you can save off your data as an Excel XLSX.

    Summary

    This section of the Estate tab gives you a quick view of your obligations to Microsoft for licensing. It’s a nice way to track this and report on it when you need to audit this information without spending a lot of time compiling the information.

    Your Finance group will thank you.

    Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

  • Time to Change Your Team

    I assume most of you work with others in a team. Even if you are the data specialist and others work on different technologies, you still have a team. How long has your current team been together in this form? Have you had a stable team that might have grown, but the rest of the individuals and roles/responsibilities stay the same? Or has your team changed makeup, roles, responsibilities, or something else?

    I don’t see a lot of organizations that change their team structures often. There may be people who come and go from a team, but the core structure remains the same. Even when your company might reorganize a bit, often it’s teams that shuffle between managers, but mostly remain the same. There certainly are exceptions, and some large orgs (Microsoft, Amazon, etc.) regularly shuffle lots of people around, but I’m not sure the teams change their makeup or their mandate much.

    I was thinking about this as I read an article on knowing when to restructure your team. I won’t recommend you read it as I think seems to imply restructuring technology teams will make them perform better and start meeting all the commitments that have been made. While I do think that a well-led team can perform better, restructuring your teams isn’t likely to make them more efficient and productive. That being said, I do think the article raises some good questions about how you might evaluate your team.

    There are certainly times when an IT team, whether in development or operations, might start to miss deadlines or may seem to work inefficiently from the outside. We are human, and humans can get complacent, or they might focus on tasks or work that they want to complete, ignoring work they don’t enjoy. The latter might be things the business needs, and restructuring the team isn’t going to fix that. Either the current staff has to be managed more closely to get them to focus on necessary work, or maybe different people should be assigned to those projects or tasks.

    Just remember you work with humans, and they often struggle with change. Change might be necessary, but a little empathy helps us cope with the challenges and learn to work together in our new structure. If we don’t have that, likely nothing gets better and we fall into old habits.

    Steve Jones

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

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