We’ve several times made reference to the need for organizations to design for rollback to be successful as a SaaS company.  Put simply, given the speed with which we want to make releases, it is critical that we limit our risk in delivering any given release by being able to easily roll back these releases.

Here are some hints on how to develop systems such that they can be easily rolled back in the event of a problem in production.

• Database changes must only be additive – Columns or tables should only be added, not deleted, until a version of code is released that deprecates the dependency on those columns.  Once these standards are implemented every release should have a portion dedicated to cleaning up data from previous releases that is no longer needed.
• DDL & DML scripted and tested – DBMS changes for a release must be scripted ahead of time instead of applied by hand.  This should include the script used to rollback any changes.  The two reasons for this are that:

1. The team needs to test the rollback process in QA or staging in order to validate that they have not missed something that would prevent rolling back and
2. The script needs to be tested under some amount of load to ensure it can be executed while the application is utilizing the database.

• Restricted SQL queries in the application – The development team needs to disambiguate all SQL by removing all SELECT * queries and adding column names to all UPDATE statements.
• Semantic changes of data – The development team must not change the definition of data within a release.  An example would be a column in a ticket table that is currently being used as a status semaphore indicating three values such as assigned, fixed, or closed.  The new version of the application cannot add a fourth status until code is first released to handle the new status and then code can be released to utilize the new status.
• Wire On / Wire Off – The application should have a framework added that allows code paths and features to be accessed by some user and not by others, based on an external configuration.  This setting can be in a configuration file or a database table and should allow for both role based access as well as random percentage based.  This framework allows for beta testing of features with a limited set of users and allows for quick removal of a code path in the event of a major bug in the feature, without rolling the entire code base back.

Okay we really don’t hate stored procedures and have actually used them  extensively on past projects but we so often tell people to remove them  that we’ve been accused of hating them. Here’s the deal, as we mentioned  in a past post, two scalability best practices are 1) put as little  business logic in the database layer as possible and 2) use as few  features of the RDBMS as possible. Both of these are violated by the use  of stored procedures, if they contain any business logic. If the stored  procedures are simply instantiations of SQL data statements then you’re  probably okay.

Expanding on the two reasons for avoiding stored procedures mentioned  above, we’ll first discuss why business logic doesn’t belong in the  database layer. The biggest reason for this is that the database server  is likely the most expensive server in your system and the most  difficult to scale. Adding more computational processing on it makes it  the most expensive layer to perform these and requires you to upgrade  the hardware or split the database sooner than necessary. It is much  cheaper from a cost per computation perspective to have this business  logic processed on a much cheaper application server.

Avoiding the use of RDBMS features is important when it comes to  remaining vendor agnostic and getting the best possible price during  negotiations. Using features specific to certain database vendors locks  you in to that vendor. While you may never want to switch database  vendors it does reduce your negotiating strength. When you are locked in  to a vendor your best alternative to a negotiated agreement, BATNA, is  not good and their sales staff knows this.

While we don’t hate stored procedures we do seem them as an impediment  to scaling. If you already have them in place you may be hesitant to  spend the money moving them into the application logic. One method of  doing so at minimal incremental cost is to start today with the policy  that if any engineer touches a stored procedure they must move it. This  way the cost is small, because you’re already touching the code, and  spread out because it’s not done all at once.