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

Question

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

Wednesday Currently, SQL Server 2005, most likely moving to SQL Server 2008, ASP.Net

Problem . Each version of the software may or may not use different versions of stored procedures that interact with data in the database. Currently, when a version is changed, anyone who changes it creates a new copy and adds the incremental version number to the end. At the moment, we have many versions of some stored procs and only one version of others, and no one is sure which version of the application indicates which version of stored procs is a mess.

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

I initially thought about diagrams, but one part of the problem is that procs is closely related to other proc and user defined functions, so when copying to another diagram we would have to display and replace all these links that are not Ideal.

It seems that this is a problem that should already be solved, but I do not know what I am looking to find a viable solution.

Does anyone have any ideas?

Answer 1

Given the clutter you are describing, if you have to stick to stored procedures, I would be inclined to have only one copy of each stored proc, but all stored procs take versionNumber as the last parameter. Then each stored proc has one name, and any code related to a particular version is present in the context of the stored process to which it relates. Stored procs that do not have a specific version code simply ignore this versionNumber file (but still have it in their signature).

One of the big problems that I see in your current situation is that if there are 4 almost identical versions of the stored procedure, and someone corrects an error in one of them, then it is too easy to forget to make the same correction in all other versions . The solution that I described will at least reduce the number of duplicate code and reduce the likelihood of error correction only when fixing one version of the stored process.

In this situation, however, I would also feel a strong desire to get the logic of a specific version of the application from the database and into the code base and leave the logic in the database that is specific to the data, not agnostic.

Answer 2

Assuming that between versions there is no reverse and incompatible schema and data changes, I would move all the application logic to the application code. After that, you can use the wealth of existing version control technologies to deploy multiple versions of the application that all point to the same database.

Trying to perform version storage procedures in many cases is fruitless, because you simply do not need access to the code in most RDBMSystems. In general, stored procedures are considered a potentially good idea than simply fail.