Maintaining multiple build versions of asp .net / SQL Server applications

Question

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

Answer 1

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.