andrew burke
dot
me

about

contact

blog

rss

More On Avoiding SQL Disasters

Posted on: 2006-11-14

An addendum to my post from yesterday:

Of course, you should generally avoid updating data directly into a live database with SQL commands. Obviously there are many kinds of application where you can't just type things in to the database - business-critical financial systems, etc. - but if you do have access to do this, you should generally do the following in descending order:

1. Have functionalty built in to your application. If you can set up an administration interface with tools for things like deleting orders or documents, or changing statuses, you can keep the changes clean and in objects and code, which is a lot safer than typing in ad hoc SQL.

2. Have a cheap administration interface. Even if an application is in JSP or PHP, I've often set up a stand-alone Ruby on Rails Scaffolding app just for managing the database cleanly. Many hosting providers (Korax in the Toronto area, for example) don't fully support Ruby on Rails hosting, but include it as something that can be run - so if something needs to be changed, I fire up the Rails server and then close it down when I'm done. This doesn't work very well for bulk updates or deletions, though.

3. Use pre-built scripts. If you can, make a copy of the database and build a script of SQL commands against that copy - if you make a mistake in writing it or testing it, you can just make another copy. Once you're sure it works, you can just run the script on the live database. Even better, set up stored procedures in your database for common tasks and simply call those.

4. Work after hours with a new backup handy. If a script isn't feasable - maybe you have to cross-reference live data or something is time sensitive in a quirky un-automatable way - see if you can do things after hours. Before doing anything, make a new backup of the database. If something happens, you can easily restore without losing anything. Many applications, with scheduled agents and worldwide user access, never have 'after hours' so this can be tricky. At least check to see how much usage the system is getting before making your changes.

Only if none of these other options are possible should you try writing SQL live into a production database. This should really mean 'never' - but you know how the Real World conspires against 'should' and 'never'. If you do find yourself in the tricky situation of having to enter things manually - then the tricks from yesterday should help.

Previous: Three ways to avoid disaster with manual SQL statements
Next: TIBCO General Interface: Web 2.0 is the new Visual Basic