Voice of the DBA

Tag: deployment

  • Patterns and Potential Problems

    I saw a post recently from a developer that needed to refactor and rename a table in a live system. The post describes a pattern for doing so and gives the steps taken, though not the actual code. I like the pattern overall, and I think it can work well in many situations. It’s for a PostgreSQL table, so I don’t know what restrictions might be different from SQL Server, but this type of pattern can work for SQL Server as well.

    It also could be problematic. Using the famous “it depends”, there could be issues with this pattern, depending on your workload and how your application is structured. The triggers in use could also be an issue in some environments, as they create an additional load.

    The biggest concern I have with this pattern is the copying of the data. Likely this is something that always works on a developer’s machine with a few dozen or even hundreds of rows of data. If this is millions, or tens of millions, the copy could end up taking substantial time. There is also the issue of changing data, with data being added or changed in the table, separately from being copied out. Depending on your locking and concurrency schemes, you could miss data.

    Or you could just lock the table and cause issues for clients. Both things that might not show up in developer testing. The lesson here is that changes to big tables need to be tested in a big way.

    This isn’t to say the pattern is bad, but that you should be aware of the potential pitfalls and then develop mitigation strategies. One way to get around the data issue might be choosing a way of copying over new or changed data after the initial data movement, or maybe even a cleanup load later after the new table is online. There are many possible ways to mitigate issues, if you take some time to think about the potential issues and then come up with a solution.

    When we are looking to make changes to our system, patterns are important to help us and others adopt the processes that work well. As we find and develop patterns, we need to ensure that we understand the strengths and weaknesses, and choose what’s best for us, with mitigation strategies to get around the potential problems. There often isn’t a perfect solution, and ensuring you and your team understand the limits of your chosen approach, helps ensure that we deploy code that not only works, but is deployed without causing issues.

    Steve Jones

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

  • 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

  • Practice Those Scripts

    I’ve written lots of scripts that were deployed to production. I’ve often had another set of eyes look them over, and still, we made mistakes. In fact, a recent Salesforce outage was blamed on a poorly written database script that gave users more rights than they should have gotten. There wasn’t an actual outage caused by the script, but since customers might have been able to see data and change from other customers, Salesforce took its own service down to prevent anyone from doing so.

    I’m a big fan of DevOps, and certainly including the database in a DevOps process to build a better software development flow. Part of that is ensuring that you can deploy by practicing the act multiple times. In a database world, this would mean that we run a script not just on a development server, but on a QA server, on a staging server, on any other environment we can find to practice and test the deployment. At that point, we should be confident of execution on the production system without issues.

    Good in theory, but sometimes you can’t easily test scripts in intermediate environments. I think changing security is a place where it can be hard to actually test things, especially if specific accounts are referenced that might not need or have access in that environment. Certainly some data changes might be easily be tested in intermediate environments, especially when these refer to configuration differences, like email or messaging systems.

    In this case, I suspect the “access changes” were data changes that updated values in certain tables in the Salesforce application. In that case, why wasn’t this tested? A restore of production to a staging environment would allow developers to test their script. It’s not multiple executions on intermediate servers, but it is better than nothing.

    I’m sure many of you have had the need to execute scripts to change data, alter permissions, or something else in production. Could the same thing that happened to Salesforce happen to you? What precautions do you take, or what would you recommend to prevent this type of issue. Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn.

  • Rolling Back Migrations

    I happen to be a fan of database migrations as a way of making and deploying database changes. This is an approach that tracks each of the scripts run by developers in their working environments and the replays these scripts in production to deploy the changes. It works really well, and is the most bulletproof method I know of for ensuring the changes will work in production. That’s not to say there aren’t issues, but it’s the approach I favor. It’s a part of what SQL Change Automation from Redgate Software does, and it’s also what Microsoft and other companies see as the future of database deployments.

    This is in contrast to making changes in development and then using some technology such as SQL Compare or Schema Compare in SSDT to create a script and use that to deploy changes. That works very well for many people, but I do find that most customers outgrow the technology with certain changes that don’t lend themselves to this state or model based method of script generation.

    In either case, rollbacks are a concern for many DBAs and developers. After all, the database is a stateful service, as our data must be maintained over time and there are certain changes that are difficult to rollback. While many developers that might have renamed an entity or added a column might be tempted to just reverse the change, this isn’t always easy to automate, especially in most of the tools we use. Often we depend on the skill of a particular individual to manually execute the reversing DDL, and possibly determine what to do with any data that was changed.

    I ran across another developer that things migrations are a better way, and that in a year of development, they never had to roll back any changes. While I agree that migrations is a more reliable process, I do think that assuming you’ll never roll back a deployment is highly optimistic. I’ve had tales of application developers being out of sync, of other applications not working with a new version of the database, and more. Some of these might be fixed with a quick roll forward, but ultimately I think that there needs to be an easy way to make rollbacks normal.

    The one downside of migrations is that the reversing transactions can be complex, since each migration script might make complex changes moving forward. There isn’t much help from most migrations tools, whether that’s SQL Change Automation, FlywayDB, Entity Framework migrations, and more. These tools can certainly generate reversing code for simple changes, but any sort of complex alteration requires custom code.

    My view is that a few simple rules govern how I view rollbacks. For views, procs, functions, I’d grab the previous version from our VCS and re-deploy that. I might try to run through a DevOps pipeline, but if I were in a hurry, I’d grab the code and run it. I’d also then recommit the old version as the latest one. For tables, we should write reversing migration scripts for any entities that are risky. Risky meaning this might affect my employment status. I’d be sure I had a second deployment pipeline that I could use to run these scripts in QA, staging/pre-prod, etc. after we’d verified the code we were deploying. I’d then have checks to ensure we really were reversing the changes.

    The last rule I have is that I use time to make deployments easier. I would never add new columns and drop old ones in the same deployment. If I move or change data, I’d always ensure the old versions of data remained. That way I could reverse changes without problems. I can always do cleanup in a later deployment that just removes objects or data, but I want to be sure that old data is really no longer needed. That means I need to be organized and have a good calendar system to scheduling future cleanup work as well.

    Migrations are really a better way to do database deployment, whether you’re working in a relational system, or you might be altering documents in a NoSQL system. Replay the changes you’ve made in development, that you are sure worked on “your” machine. You’ll be more confident they’ll work on another machine.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.4MB) podcast or subscribe to the feed at iTunes and Libsyn.