August 21, 2016 at 11:40 am
Comments posted to this topic are about the item Is Rollback Feasible for Database Changes?
August 22, 2016 at 12:27 am
One of the benefits of releasing a little and often is that a deployment is rarely big enough to cause major headaches.
The advantage of robust automated testing is that a rollback is extremely rare.
In terms of losing data from a new field I simply wouldn't worry about it. The organisation did well enough without it before, the amount gathered up to the decision to rollback won't alter the gravitational constant of the universe, particularly as the rollback implicitly stops recording that info.
In terms of how to rollback it is all in the planning and analysis. For a large table I will consider what data needs to be available on day one. Does all data need to be available or can I migrate from one state to another over a period of time?
Is my best strategy simply to create a new table and sp_rename to swap old/new versions?
I would focus more heavily on the things that cause rollback decisions to be taken and design them out of the equation as much as possible
August 22, 2016 at 12:30 am
I see the abandonment of rollback scripts as more of a cultural hurdle than a technical one. All too often I see too little effort put into these scripts when compared with the level of reliance upon them. It is akin to never testing that the backups can be restored. How often are rollback scripts tested?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 22, 2016 at 6:02 am
Gary Varga (8/22/2016)
I see the abandonment of rollback scripts as more of a cultural hurdle than a technical one. All too often I see too little effort put into these scripts when compared with the level of reliance upon them. It is akin to never testing that the backups can be restored. How often are rollback scripts tested?
In one place I worked the DB rollback scripts were thoroughly tested and peer reviewed due to rollbacks of some form or another being quite common. They were also useful artefacts in their own right as the actual development of the scripts required the ability to roll forward/back.
We didn't have the environment that would allow a total tear down and rebuild so it was deploy from a baseline to various milestones along the way and then revert back to a specified milestone or back to the baseline.
One thing that did come to light was that it is pretty pointless having a robust DB rollback strategy without there being a comparable application and infrastructure rollback strategy. This reinforces Gary's point.
To my mind software deployments need to be designed as much as possible to be clean units. As soon as technical debt starts to be put forward as a "pragmatic" or "tactical" solution then the system is being deployed at risk. Tech debt starts to make rollbacks and eventually deployments risky.
August 22, 2016 at 7:31 am
The time to revert changes is also important. If you practice doing it once in a while, you should be quicker at it if you have to do it.
412-977-3526 call/text
August 22, 2016 at 7:35 am
My organization just deployed a major release that contained changes to multiple databases. Since we considered this a risky release due to its scope we drilled not just the deploy but its rollback repeatedly.
We use SQL Source Control to commit our changes into Git, then SQL Compare to create a migration script by comparing the db to our Release Candidate branch (one added complexity is that a couple of the dbs are involved in replication, so a secondary step involved running the delta script through a Powershell script to remove commands that would have broken replication). The rollback involved comparing the deployed db to our Master branch.
Happy to say that the release went well, so the rollback wasn't needed, but I slept a lot better knowing that we had drilled it to the point that we would have been well prepared if a fire broke out.
August 22, 2016 at 7:44 am
I do agree with the previous David that the complexity and risks associated with deployment rollbacks can be mitigated by performing incremental releases, rather than bigger "bet the farm" deployments.
It's good to have a predefined rollback plan and then materialize than plan in the form of rollback scripts just so it's clear. Even if the rollback scripts are not trusted enough to actually use in the event of a botched deployment, they can at least be used as a starting point for reference.
For a more reliable and straightforward rollback, and if preserving user activity during and after the deployment is not an issue, you can also perform a database snapshot prior to the deployment, and then simply rollback from that if needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 24, 2016 at 2:50 pm
One thing that seems to be sometimes overlooked is system databases -- specifically agent jobs in msdb but also IS packages for non-SSISDB packages. Then there are SSISDB, MDS and master and possibly others. What about complex changes that involve instance-level security, for example? Those schemas for those databases may rarely change but the contents can be quite volatile. Actually, the schema for MDS (as well as the data) can also change a lot as new domains and entities and views etc are added.
No good rolling back changes in a user database if packages/projects/jobs/permissions/master data etc. that depend on them don't keep pace.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply