How can I run multiple versions of my application against the same database?

Question

Backround: I have multiple versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of the software.

Environment: Currently SQL Server 2005, imminently migrating to SQL Server 2008, ASP.Net

Problem: Each version of the software may or may not use different versions of the stored procedures that interact with the data in the database. Currently when a version is changed, whoever changes it creates a new copy and appends an incremental version number to the end. At this point we have numerous versions of some stored procs and only one version of others and nobody is sure which version of the application is pointing to which version of the stored procs - it's a mess.

I'm looking for a solution that will neatly package the stored procs up for any given version of the application to be used as the basis for a new version. This means that the new version of the application can be pointed to the new set of procs which can be rewritten or modified to the end user's content without affecting other versions of the applications that are currently available in production.

I originally thought about schemas, but one part of the issue is that the procs are heavily coupled with other procs and user defined functions, so when copying to another schema, we'd new to map and replace all these links which isn't ideal.

It seems like this is a problem that should already be solved, but I don't know what I'm looking for in order to find a viable solution.

Does anyone have any ideas?

Answer 1

Given the mess you describe, if you must stick with stored procs, I would be inclined to have only one copy of each stored proc, but have all stored procs take versionNumber as the final parameter. Then each stored proc has one name, and any version specific code is there in the context of the stored proc it relates to. Stored procs that have no version specific code just ignore that versionNumber paremter (but still have it in their signature).

One of the big problems I see in your current situation is that if there are 4 nearly identical versions of a stored proc, and someone fixes a bug in one of them, it is too easy to forget to make the identical fix in all the other versions. The solution I described would at least reduce the amount of duplicate code, and reduce the chance of bug fixes only fixing one version of the stored proc.

In this situation though, I would also feel a strong urge to get application version specific logic out of the database and into the codebase, and leave in the database that logic that is data specific but application agnostic.