Performing Database rollbacks is complex and need to be thought of during the initial deployment process itself. The objective for doing rollbacks is to bring the system to a previous stable version which has been thoroughly tested and approved by Business. Planning for a failed deployment and rollback cannot be an after thought!

For most of my projects, we can rollback our database deployments by rerunning our MSDeploy package and redeploy the previous version – however we do our due diligence during the development phase as to not introduce any breaking database changes.

If a deployment has not gone as expected and users are reporting issues, it is better to rollback the deployment to the last stable state which you are confident of. This is a safe thing to do, but needs to be well planned. If you have made breaking changes, the rollback strategy gets complicated. If you are doing frequent database deployments, you should often test your rollback strategy by doing rollbacks in your test environment to ensure you are rolling back your changes to the earlier version.

Let us quickly understand the definition of Breaking and Non Breaking Changes in the context of Database deployments.

What is a Breaking Database Change?

These can be categorized as changes which can impact current functionalities and break the existing clients —

• Renaming a Column/Table/Stored Procedure/ View/ Function
• Dropping a Column/Table
• Remove a Stored Procedure/ View/ Function
• Moving a Column from one table to other

What is a Non Breaking Database Change?

These can be categorized as new changes which don’t break existing clients and is safe to implement —

• Adding a Column/Table/Stored Procedure/ View/ Function

Additive changes are non-destructive changes – if you add new fields which the existing clients are not using, then this should not impact them and backward compatibility is not broken.

How to make your database change backwards compatible?

I would highly recommend you to not make breaking changes and follow the ‘Expand and Contract Pattern‘ for your database changes to make sure that your database changes are backward compatible.

For example, if you want to make a breaking change like renaming a column name then follow the below steps —

• Expand — Instead of renaming the existing column name, create a new column with the updated name.
• Migrate — Move the data from the old column to the new column.
• Contract — Once you verify that the code is functioning all right with the new column, then delete the old column name and make it non-existent.

On the client side, you can use Feature Flags so that when the feature flag is turned ON the new column is used and when the feature flag is turned OFF the old existing column is used.
To implement feature flags, we use an open source framework called “Really Simple Feature Toggle” started by David Whitney in GitHub.

Also as part of your API Versioning strategy, breaking changes should always be released as a new version – so that the existing consumers are not impacted. The changes needs to be communicated to the consumers and they can upgrade to the new version when appropriate. Feature Flags come in very handy in such cases when you need to support backward compatibility.

We use Thoughtworks GO for our Continuous Delivery Pipelines, and deploying an earlier version in an environment is just a matter of a single button click.
If you want to read more about the implementation for CD Pipelines using ThoughtWorks GO do read the blogpost by my colleague Robb Schiefer here.

Conclusion –

With the combination of using an automated CI/CD Pipeline, Feature Flags and the Expand & Contract Pattern during your database development to eliminate breaking changes — you can make your Database Rollbacks a non-event and have the luxury of one click rollbacks.

If you have any further questions regarding your database rollback strategy, please add a comment below and I would be happy to discuss.

Categories: DevOps, SQL Server