Mike-O-Matic

Interesting article at Spoiled Techie today.  Scott disagrees with the relevance of stored procedures today, despite Microsoft’s pleas.  Amongst his points:

• SP’s mangle the three tier architecture designed for apps.
  • Instead of having a structure which separates logic from storage, you have storage and logic on the same tier.  This will cause potential problems down the road.
• Business logic in a SP does not scale.  Business logic should be in code and not in the storage center.
  • When you have multiple database servers, it is more difficult to keep the triggers and SP’s synced.
• There is no Version Control for SP’s.
  • In a place that can be disastrous if you screw up, it should have something.  This is one big reason why people like to avoid SP’s like the plague.  They run away and never move back into town.

As someone who is heavily invested in perpetuating the myth that stored procedures are a Good Thing, I feel I need to say a few words here.

Scott’s first point is well taken, but I think that stored procedures could still be considered part of the “Model” section of the MVC architecture.  They exist to enforce the integrity of the data model, after all, by limiting the ability of things external to the database to modify data willy-nilly.  The Model in the application code sense could easily become a thin wrapper to the procedures.  Sure, some application language paradigms (such as Active Record) might be uncomfortable with incomplete access to the database, but that could just mean that their assumptions are faulty.

The second point seems to bring in a few different ideas.  First, it assumes that the database (or business rules engine) is merely a storage center.  Second, it assumes a particular way of achieving scalability which may not in fact be an issue.  Databases do have facilities for being more than a storage centers, if you choose to use them.  Like stored procedures.  And access controls, and triggers.  The fact that most application language-centric frameworks choose to work around these is unfortunate.  As for the scalability concern, most database clustering solutions have syncing built in.

Finally, the last point is very astute and is indeed a problem.  Many SQL shops struggle with how to keep code changes in sync with database changes.  The primary way I have seen is to maintain a script file for each release of the code and run it as part of the rollout process.  This is kludgy but not insurmountable.

(CodaServer addresses this by maintaining an internal revision history for each application.  In the system schema’s transaction table you can see every DDL statement that has been run and the revision number.  Promoting the schema to different environments is a single PROMOTE command, and generating a one-off version of the schema requires a simple FORMAT command.)

This third point does little to take away from stored procedures’ utility, though.

They arise from the school of thought that critical data validation and heavy lifting should occur as close to the datastore as possible.  The reality of enterprise development today is that the database becomes the integration point for many different codebases, written in many different frameworks, versions of frameworks, and even languages.  As an integration point, the database is ultimately responsible for ensuring that everyone plays nice and treats data in a consistant way, and stored procedures can be an excellent tool to help in this mission.

Application frameworks have been getting a lot of attention recently as they seem to bundle together lots of things and present a constant, appealing view of how simple development can be.  And they are, as long as you do everything their way, across all of your applications.  Maybe I’ve just been unlucky, but I have yet to see this happen in the wild.

If you treat your data model as an independent entity with well-defined access points such as stored procedures, you can leverage whatever front end frameworks you need to and still maintain your integrity on the backend.

Til next time.

Subscribe to this Blog!