Voice of the DBA

Category: Editorial

  • How Do You Decide to Rollback?

    One of the constant questions I get from Redgate customers is about rolling back database changes. We build software to help you deploy changes, but what about rollbacks? I think I’ve heard this in almost every conversation I’ve had on this topic in the last 5 years. It’s one reason that Flyway has undo, though with some caveats. Please don’t expect this works magically like a restore operation.

    These is a complex topic, and one that can be way harder than a rollback for application changes. In fact, everyone wants it to be simple, but I don’t think it ever will be. There is a big reason for that: data. Undoing data changes can be impractical, especially in if there are lots of changes.

    The question I’d like you to ask yourself, and give me thoughts about, is when do you decide to rollback. How can you make that decision in a production system? Are there criteria or guidelines you use? Do you ask someone else? Maybe another aspect of this situation is how you decide to roll forward instead of rolling back.

    I was listening to some MVPs discuss this awhile back and one of the main criteria that one person brought up was data changes. This individual said if no data had changed, they just undid everything. However, once data changed, they were likely in a roll forward scenario where they needed to fix code quickly.

    That’s often the big factor for me. Once data is changed (or added), then we often can struggle to rollback. If we have an application that needs a new column, and we add it, if there software has a problem, we may not be able to roll back because the old software doesn’t work with the new column. We don’t want to delete the column, at least not until we save the data. These are the problems.

    The easy rollbacks are when the deployment fails and transactions undo the work, or we just quickly undo everything we did. That’s an easy decision, but I rarely find this sort of issue. Instead, usually we find certain data breaks our application or the logic is improperly implemented.

    I look forward to your ideas and thoughts here, but I do have some advice. One thing I always tell customers is that you want to deploy often, so that you are ready to fix something you broke. That’s important. The other thing that simplifies life is to never add and delete objects in the same deployment. This goes for everything. If I add new first and last name columns, but keep the old fullname column, if I’ve broken something, I still have the old columns and data. I can rollback easier. If everything works, I can always delete the fullname column later. Following these concepts has made my life much easier when I made database changes.

    Steve Jones

  • The Second Half of 2021

    July is starting, and with it we move to the second half of 2021, or H2 2021 for those of you that like the planning shorthand on roadmaps and project manager boards.

    The first half of 2021 has felt like a big of progress in the world, as we try to recover from the pandemic that froze time for most of 2020. I felt like most of last year was one grinding day after another, with little change, lots of limitations, and a constant “sameness” in my life from day to day. While I like routine, I also like some changes and little alterations that keep life interesting.

    I know the first quarter of this year felt like that as well, but slowly in the US things started to change. I got my vaccine does, something I didn’t expect until this month or later. The US and a few other countries have deployed so many vaccine doses that life has started to change and open a bit. I know many other countries aren’t as lucky, and I do hope that more assistance and sharing comes from the US and other privileged countries to ensure that we can end this pandemic.

    As things change, I’m more hopeful than I was six months ago that we will move towards a more normal way of life, albeit likely will still some changes. I know I see less masks in the US, but not zero. We still have precautions in many places, and most people seem willing to follow whatever rules a business wants. More offices are opening up, although partial remote work seems to be the accepted practice. I’ve seen a few music and other entertainment events start to take place, so maybe we’ll see more technical events. There were good decent number of people at DevConnections in Florida, so that was a hopeful sign as well.

    We are all emerging from the pandemic at different rates, with vastly different risk tolerances. Not only are countries taking different approaches, but even within a country like the US, the experience can vary. On a few recent trips, it seemed to me that we all need to be flexibly and respectful of others, willing to adapt to the situations as they occur.

    The second half of 2021 will be interesting, but likely very different from the last year. No matter where you are, and how you feel, I do hope that life improves and you can enjoy yourself, both at work and away from work.

    Steve Jones

  • Learning Intentionally with a List

    We’re coming up on the middle of the year, and I wonder if you are improving your career this year? Did you make plans and are you sticking to them? I’ve been tracking progress on my goals, and so far mediocre progress.

    It’s tough to learn, but even tougher to learn when you don’t have a specific focus. Choosing to “get better at T-SQL” isn’t a good goal. It’s vague and hard to measure. To me, you need to pick some specific areas to focus on and then develop some goals on what you want to accomplish. For example, you might want to learn to work with APPLY and convert some queries from joins to APPLY or pick a series of challenging problems that you can solve with that operator.

    Mental note: make some lists of problems to solve.

    These days there are so many areas that you could focus on when working with data that it can be overwhelming to try and pick something. If you were to think about what you want to learn today, what’s the list of things you think you need to learn?

    If you have that list, then can you choose a few specific things that will help you measure your progress? I’ve picked a few, but not enough to measure tightly. That’s something I need to change for next year, with some ways to measure myself better.

    If you are working on growing your skills, share some ideas on how to improve your career and skills. What types of things help you get better? Maybe more important, what things do you think are important to learn and why?

    Steve Jones

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

  • Patching Challenges

    In my career, I’ve had to manage many production database instances. In fact, there was a time when two of us were patching hundreds of instances (600?) when MS released patches. That wasn’t too often back then, unlike the every other month(ish) schedule that we have with 2017/2019 and the Cumulative Updates.

    My process was often to test patches on QA/Test servers and then start to roll out the patches to production. We didn’t quite follow what Brent Ozar recommends, in that we often patched development servers later. We didn’t control those, and unless production was patched, we couldn’t get developers to patch their systems.

    We didn’t patch DR servers first, but that’s a good idea. It’s one I think makes a lot of sense. We did patch secondaries first, ensuring that if there were issues here, we wouldn’t impact production, and these days with Availability Groups, hitting the secondary replicas, especially read-only ones, is a good idea.

    The big thing for me wasn’t so much the type of servers, but in having a series of rings to roll out the patches in groups. We used automated processes (no one wants to click next-next-next) and while we might patch a lot of servers, we never wanted to patch them all. We typically had 3 rings. Ring 1 was test servers, and ring 2 was most of production. Ring 3 was any production servers that got an exemption from initial patching. There were times when some process was important and couldn’t be interrupted or a client needed a few more weeks to test. We’d let them delay a month, but not longer.

    I think it’s important to have a strategy, and as Brent notes, also a protocol for how you handle things. I’ve often depended on our normal backup processes, especially in large environments, and the patches tended to stop the SQL services, so I don’t know how important it is to stop client apps, but think about it.

    One note about backing out changes is that containers make this a lot easier. If you move to production containers (linux, HA challenges, features missing, etc.) you can swap out an updated container with a new (or old) patch level as needed. There are caveats here, and certainly I’d start implementing this in a dev area first to understand the implications, but I expect over time containers will make patch deployment and rollback much easier.

    Steve Jones

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