How to separate programming logic and data in MS SQL Server 2005?

I am developing a data driven website and quite a lot of programming logic resides in database stored procedures and database functions. I found myself changing the stored 开发者_Go百科proc/functions quite a lot in order to fix bugs or add new functionality. The data (tables) have remained mostly untouched.

The issue I am having is keeping track of versions of stored proc/functions. Currently I am incrementing version of whole database when I do a set of changes. As data is huge (10 Gb) I get issues having to run development version and release versions of databases in parallel.

I wish to put all the stored procs and functions in one database and keep data in one database, so that I can better manage the changes.

I am sure others would have encountered similar suggest and request suggestions on how to best handle this situation.

I would also recommend using source control keyword expansion in your stored procedures ($Version:$)

That way you can eyeball, grep, search syscomments, etc to see what version you have on your deployed database.

You can version just the schema dumps. In combination with source control keword expansion (as suggested by Rawheiser), you just take a look at what version you have in the database, generate a diff and apply it.

Also, there are several excellent tools to compare databases and their schemas, generate DDL scripts etc.: SQL Workbench, Power Architect, DDLUtils and Redgate SQL Compare, to name a few. SQL Compare is likely to work best with SQL Server, although all the others are FOSS and provide a higher ROI (in terms of time spent learning and what you can do with them) as they are platoform and RDBMS independent.

Finally, I have to say...I understand that the immediate results you get with logic in the DB are tempting, but if you've gone beyond more than a couple of procedures in the database, you're setting your self up for quite a lot of pain, sifting through what easily turns into spaghetti code and locking your application to a single database vendor. You might have your reasons, but I've been there and didn't like it very much. Logic can live very nicely in a different layer.

For source control you have several options:

1. Use a Visual Studio Database project.

2. Use SQL Server 2005's built-in support for source control

3. Use a third part tool such as SQL Compare

IMO Option 1. is preferable.