Sql-server – Maintaining multiple build versions of asp .net / SQL Server applications

sql serversql-server-2008ssms

We have new versions of .net applications deployed every week for testing given in form of new build files. Can any one suggest how I can maintain many versions of the builds without having to keep many copies of builds and databases?

Here's what I have in mind :

For each build maintain a new folder in inetpub > wwwroot
Have one database
Keep SQL Server stored procedures in same one database in different^schemas(*) or keep it in local folder drives (*not sure how to do this)
In web.config file point to same database for each of the builds, but somehow be able to suggest which build version of stored procedures to use for each build from the web.config (again not sure how to do this)

Is this possible and how? A way of version control and deploy and maintain builds on a single db Or can anyone recommend other better methods for maintaining multiple build versions, any links or thoughts will be helpful

Best Answer

This would be an unusual deployment pattern: normally you'd want to have 1-to-1 correspondence between an application and a database. This mainly is because you'd want your development environment to mirror your future production environment as much as possible.

If you cannot create databases on the server you could still look at pre-creating a set of deployment slots (e.g. QA1-QA5) and re-using them.

One thing that might help would be something like DbUp - it allows you to easily deploy just the delta scripts that are needed. Alternatively you could look at DB change management software like DBGhost or RedGate SQL CI.

Related Solutions

Sql-server – SQL Server configuration / specification recommendations and advice!

What I'd do

4 disks for RAID 10 for all log files
Disk RAID 5 for all data files + OS
Don't bother with RAM disks

Why?:

With 24GB RAM all databases will be in memory so RAID 5 doesn't matter because disk reads won't really happen
Write performance is determined by log file throughput
Data files are larger then log files so RAID 5 is more efficient

What isn't ideal:

Not having one volume per database log file
Separation of local backup drives
Not having separate RAID 1 for OS + binaries

Other options:

Define "heavy write": you could go to RAID 1 for LDFs and another for OS for less than a few million rows per day

Sql-server – How to run multiple concurrent versions of the application against the same database

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 - stored procedure names are hardcoded only for testing.

Best Answer

Related Solutions

Sql-server – SQL Server configuration / specification recommendations and advice!

Sql-server – How to run multiple concurrent versions of the application against the same database

Related Question