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

Question

Background: I have multiple concurrent 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. Is there a way to write the procs so that moving them to another schema would bring the relationship between them along for the ride so that for instance if Schema1.Proc1 referenced Schema1.Proc2 and we copied them to Schema2 that Schema2.Proc1 would now reference Schema2.Proc2 without having to do any additional work? How would this handle user defined functions where you have to reference the function by SchemaX.Func1()? It would be handy if I could refer to the function without defining the schema the function belonged to in the same way I can with a stored procedure, but I don't think that's possible.

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

Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'

You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)   
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END

Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - sproc names are hardcoded only for testing.

Answer 2

Somehow you need to maintain a list of the stored procedures that relate to each software version. There will be a pile of ways to do this, but the version/procedures will need to be documented clearly somewhere or maintaining the code base will be difficult (expensive) and end up buggy.

One method that springs to mind is to maintain a list of the procedures and their versions - as a database table or text list or web.config or compiled into your application. This list would have the procedure name and the version that it corresponds to (maybe with a base version) Then in the code you will need to write a method or class that will get the relevant procedures name as a string that you can return to the Data Access Layer (DAL) to use - I am assuming that the DAL know which query to run. To keep it simple, have a base list and then only put in "version" procedure entries where they are needed and get the most recent one.

You could take this a step further, and dynamically create the procedures that you need by passing the query text or variables (procedure names, table names) to be substituted into your query in the stored procedures at run time. ie: if procedure foo needs to call procedure bar.vn the pass bar.vn as a variable to procedure foo. This allows you to dynamically take into account different versions, but wont allow the database engine to effectively create statistics for that procedure. Here is a example of how to code something like this up http://www.codeproject.com/Articles/11739/Dynamic-Sales-by-Week-Procedure-in-SQL-Server

The best solution is to not have multiple versions at once, try to have the different functionality as different execution paths in the application (code or stored procedure). It will mean a few IF..ELSE bits of code, and what looks like a bit of work, but is much easier to maintain because the alternatives are in front of the developer - they do not need to know about which version needs which procedure.