Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Schemas do seem like a good answer for this. Something else you could do along with that is changing the credential for each version of the app (one user per version) and setting a default schema for each. –  JNK Jan 19 '12 at 18:32
    
How do you currently trace your dependecies? In other words, how (at a macro level) do you see that Version2.SPROC1 depends on Version1.FUNC1 which depends on Version1.TABLE1? I mean aside from reading all of them manually... Also, I totally agree with @JNK and think the account idea is a great trick. –  JoeGeeky Jan 21 '12 at 11:46
    
I'm thinking that as we roll each version of the app, we create a full set of procs/functions with the new schema (version) name. Each of the dependencies are updated to point to the version from the new set. It's more work than I'd like, and it means that there are frequently unchanged copies but it's the tidiest and least confusing approach. –  BenAlabaster Jan 22 '12 at 5:44
    
How can this work? Different versions presumably have different functionality. Are you saying they work with the same data, same tables and columns? How can a single database switch around versions without changing the data? –  ErikE Feb 12 '12 at 19:40
add comment

2 Answers 2

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.

share|improve this answer
    
This is kind of what I was thinking. Better yet, if your program sets the Application Name parameter in the connection string, you can switch based on that so you don't end up coupling it to your security design. –  db2 Feb 15 '12 at 13:21
add comment

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.

share|improve this answer
    
I'd already thought about doing it this way, and it may be the way to go. I was hoping not to have to go to that much pain to do it, but I think this may be the option we end up taking. –  BenAlabaster Feb 13 '12 at 13:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.