Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Managing Data Change

Often DBAs receive requests to manually manipulate production data. Ideally, all data changes should be performed through the appropriate application or interface. Unfortunately, sometimes applications lack a feature or has a bug that does not allow the user to modify data. To fix a bug or add a feature may require another software release. Sometimes the business need is too great to wait for the next release.

Manually manipulating data can pose various problems for both the DBA who designed the database and knows everything about the schema, as well as the DBA who recently inherited a database and knows nothing about the schema. Some of these problems are:

  1. Data cannot simply be changed whenever we want, to any value we want. Most tables are related to other tables. Some data is designed to represent a record’s state at a certain point in time. Changing a value may break that relationship or state and, therefore, break the application and corrupt data.
  2. In most cases with a well designed and documented database that has all the appropriate keys and constraints, a DBA can quickly determine if modifying data will break the application. In the real world, this is often not the case. The schema does not necessary reveal the application logic, or how the data is used or modified. Sometimes only the original developer or experienced analyst knows the application well enough.
  3. Changing data manually is very labor intensive. Also, DBA and developer labor is not cheap. Because it is labor intensive, continuing to manually manipulating will always become more expensive at some point in the future than coding the application appropriately.
  4. Changing data manually is error prone - Despite their appearance, DBAs are human. Even a highly skilled DBA with years of experience can write an UPDATE statement incorrectly. Also, the DBA can be given the wrong record to update.

If requests for data modification are frequent, policy and procedures to manage data change are needed. These policies and procedures should have the following objectives:

  1. Retain old data - Retain data prior to the change so the changes can be tracked and reversed, if needed.
  2. Document Purpose - Documenting the purpose of the data change in order build a case for a software update. By doing this you may find that you are updating the same column on the same table several times a month. Now you will have the documentation to prove it and why.
  3. Notify Interested Parties - There may be other interested parties in your company that need to be aware of the change. Operations managers may not want you changing records during the day or after an order is shipped. Software Development and Product managers may want to know about a missing feature or bug in the software.
  4. Only certain data can be changed - Because the database does not necessarily reveal the application logic and table relationships, only approved tables and columns should be changed.
  5. Test all changes - All changes should first be done in a development environment.
  6. Verify changes - All changes are verified in a QA or testing environment.
  7. Use the best person to develop an update script - The DBA may not be the best person to develop an update script. Sure, he or she may be the best skilled, but as I mentioned above, the DBA may not completely understand the application logic or business implications. In such cases the developer, maybe with the help of a business analyst, can develop an appropriate update script.

How does your company manage data change requests? Besides my list above, what other problems do you see with manually manipulating data? Can you think of other objectives a data change request policy should have?

Comments

Posted by Steve Jones on 23 May 2011

Great points, and a great post. I've not always done everything in your list of seven steps, but I have definitely done #1 and #2 most of the time. It's so easy to forget what the old data was in an ad hoc change if you don't save a copy.

Posted by Bruce Samuelson on 26 May 2011

Another modest safeguard is to wrap the UPDATE statement in a transaction and only commit it after doing a brief sanity check. A long sanity check may lock the table for too long.

BEGIN TRAN foo

UPDATE... SET... WHERE...

--Do brief sanity check.

--COMMIT TRAN foo

--ROLLBACK TRAN foo

Posted by Peter Maloof on 26 May 2011

Jon:

These are all good points. I'm wondering if Problem #2 should read, "The schema does NOT necessary reveal the application logic".

I would add that documenation may be necessary for auditing purposes.

Thanks,

Peter

Posted by Jon Russell on 26 May 2011

Bruce - I completely agree. In fact I use transactions myself.

pmaloof - Yes, that was a typo. It is fixed. Thank you.

Posted by Anonymous on 31 May 2011

Pingback from  The Ad Hoc Change « Voice of the DBA

Posted by Nakul Vachhrajani on 31 May 2011

Great post, Thank-you!

I worked in with a sustenance team wherein we plugged in directly into the Level 3 support teams. Often we had to undertake data cleanups resulting out of changing business logic (our application is more than 15 years old and therefore what held true a decade ago, is no longer true). The other reasons that required production data updates were that the development team did not take care of particular data conditions.

In all of these cases, we always made sure to run through steps #1-7 - that helped us ensure that although in Release r1 we are undertaking a data cleanup, the issue is resolved in code and/or in our upgrade process in release r2.

Data cleanups are always risky, and therefore our goal was to clean the data, and fix the code so that we don't have to perform the same cleanup again and again.

Thanks & Regards,

Nakul Vachhrajani

beyondrelational.com/.../default.aspx

Posted by Andy on 31 May 2011

Great Post - Amen!

Posted by eric_russell 13013 on 31 May 2011

For the sanity check, I typically know ahead of time how many rows should be affected in production, so I implement an assertion that raises an error in the event of an unexpected row count. This will make the batch fall to the error catch block and roll back the transaction.

For example:

if @@rowcount <> 3

  raiserror ('Assertion 38: An unexpected number of rows were updated. The transaction will now be aborted.', 16, 1);

Posted by Eichpeel on 31 May 2011

Very nice post. And so very true about the DBA expected to fix the data issues "magically" in production because it is "urgent". It does not seem like we can ever say "no" to that type of request but it usually underlines much deeper issues about the organization.

Posted by Tahir on 31 May 2011

Thanks for sharing these valuable information.

I have seen time when the data was incorrectly updated and we were having no backup.

Posted by Jon Russell on 31 May 2011

Eric - Yes! Row counts are a great sanitity check. I have our developers create two scripts, one to save, one to change. Both scripts are created to clearly show the row count. Before deploying to production, I will compare row counts of the scripts on a development database, recently restored from production, as one last sanity check.

Posted by Jon Russell on 31 May 2011

Eichpeel  - You are correct. The need to constantly update data manually underlines a deeper issue. Steve Jones recently posted an article called "The Ad Hoc Change" that speaks directly to your point. I did not dive too deep into this issue in my post, but Steve correctly points out that the only need to make an ad hoc change is there is a bug, a user error, or lack of feature.

The Ad Hoc Change - Steve Jones

www.sqlservercentral.com/.../73936

Posted by Jon Russell on 31 May 2011

gul_vu - Ouch. Not having a backup is probably the biggest problem a DBA can have. Updating the wrong data is also a top concern, especially if you accidently expose sensitive data to the wrong user. Not only can this cause data corruption and wasted hours of company time to correct, but can also have legal implications.

Leave a Comment

Please register or log in to leave a comment.