This article is part 4 of my series on version control.
- Part 1 - Dealing with Code
- Part 2 - Tracking Changes
- Part 3 - Migrating Objects to Production
- Part 4 - Rolling Back
Introduction
Managing changes to your system is extremely important, but there is a more important item. Being able to recover if those changes somehow cause you a problem.
In this article, I will examine a few techniques that I have used to back out changes from an environment that have caused an issue. The first section of this article repeats a bit of background from the other articles. If you have read them, skip to the My Process section.
My Environment
Before I delve further, let me explain the type of environment that I have used in each of the last few jobs. Basically I have (at least) three systems setup for each application. These are not necessarily, but usually are three separate servers. These systems are defined as follows:
- Production - The "live" system that is actually being used by users. This is the data of record, the system that runs the database, accepts orders, etc. and runs the business.
- QA - A copy of the production system that is used for testing. This is usually the system that is most often updated (overwritten). Developers should not have access to this system, instead this should be exclusively given to the testing or Quality Assurance group. Integration and system testing occur here.
- Development - The "sandbox" or "playground" for the development group. This is where items are developed, tested, and verified by developers before being submitted to formal QA. This would be where unit testing and developer integration testing occurs.
- Beta (optional) - A copy of the production system where clients can perform testing. This is exactly like the QA environment. This is a playground for clients.
- Demo - The last couple companies have needed a "demo" system for the salespeople to use. This functions like the Beta system, except that it is rarely overwritten (from a data perspective).
Changes in the SQL environment are new and altered objects, data seeds, imports of data, etc. All of these changes are first made in the development environment. They are then applied to the QA environment where the intended effects can be verified by our QA environment before being migrated to the other environments.
It is my job to manage the changes as they move from one system to another, along with the QA person, though in actual practice, I "build" a package that migrates change from the development environment to the QA environment. The QA person is then responsible for running my "package" against the other systems to apply the changes. This article discusses packages.
My Process
There are actually a few processes that I use, none of which I am thrilled with, but all have worked. I will look at both scripting rollbacks and using the tried-and-true backup/restore.
Scripting
I used to work in a real-time financial environment where our clients were accessing our systems all day long to gather information and make decisions based on this information. Real money was involved in these systems and our company was actually responsible in places for the problems that occurred in trading. At the time our QA department wasn't the greatest, and we often encountered bugs in the live systems.
Of course, not all of these bugs could be fixed during the next release. Sometimes, we had to implement hot-fixes in the middle of the day. In preparing for these changes one thing that I decided to require from all DBAs was a "rollback" script in addition to a "roll forward" script.
What's a "rollback script"? For that matter, what's a "roll forward" script? Suppose that you need to change a stored procedure. Here's the original script you used to create the procedure:
if object_id('GetUsers') is not null
drop procedure GetUsers
go
create procedure GetUsers
as
select Name, Email
from Users
return
Now your clients are upset because the new version of the web site also expects to see the last login time, but the developers negelected to request a change to this procedure. You check out your script from version control (remember Part 1?) and change it to:
if object_id('GetUsers') is not null
drop procedure GetUsers
go
create procedure GetUsers
as
select Name, Email, LastLoginTime
from Users
return
This last script would be the "roll forward" script that applies your changes, or "rolls" them out to the live system. The first script, however, would be the "roll back" script that undoes the change in the event that it causes problems.
Whenever we implemented changes and built a script to make these changes, we also used our version control system to get the last versions released to production and built script like the "roll forward" script (explained in Part 3, but one that implemented rollbacks.
Now when we tested the roll forward script in QA, we would also test the rollback script so that we could be sure that worked as well!!!!
Backup/Restore
The other method that our QA person uses currently is to perform a backup of the database before applying changes. This is done by means of a job in the SQLAgent that I have setup to create a specific backup file. This job is unscheduled and is manually run just prior to performing an upgrade.
The "roll back" for this method is a matching job that performs a restore from the backup file. This is also unscheduled and run manually if a problem is encountered.
Conclusions
Neither of these methods is perfect. If problems occur in a roll forward, two things can happen. If it is while I (or another DBA) is available immediately, then I am called and we exmaine the error. We can then make a snap decision to rollback the entire event, or merely rollback the portion with an error. We make a decision based on how quickly we can look at something and what dependencies exist.
If this is at night, then the QA person immediately rollsback the entire script and contacts someone (and sends the error logging). Since we try not to delay roll outs, someone makes a decision and perhaps hot-fixes the item then and there. Often we find there are minor ordering dependencies that can be fixed in a script, and were fixed on QA, but never moved to production.
As I said, neither of these methods is perfect, and once your database grows to a certain size, they become cumbersome. I know there are third party tools that can help with this process, but in my environments, I have found them to be cumbersome and still require some manual intervention. Since I am a firm believer in the KISS principle, this is what I have come up with to date.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article. If you know of a better method or tool, please let me know.
Steve Jones
©dkRanch.net November 2001
