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

Question

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 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.

Answer 1

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.

Answer 2

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.

Answer 3

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.