More on Column Choice and Order for Multi-column Non-Clustered Indexes (2021-07-07)

Daily Coping Tip

Pause, breathe, and feel your feet firmly on the ground.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

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 - SSC Editor

Join the debate, and respond to today's editorial on the forums