How to version SQL Server schema using VS 2005?

Question

I am new to C# programming and am coming to it most recently from working with Ruby on Rails. In RoR, I am used to being able to write schema migrations for the database. I would like to be able to do something similar for my C#/SQLServer projects.

Does such a tool exist for the VS 2005 toolset?

Would it be wise to use RoR migrations with SQL Server directly outside of VS 2005? In other words, I would handle all schema versioning using ActiveRecord:Migration from Rails but nothing else.

If I do handle migrations outside of C# and VS 2005 with another tool, is RoR ActiveRecord:Migration the best thing to use or is there something which is a better fit?

Answer 1

There's no built in way of doing this without the Database Edition of VS Team Edition. We use a NANT script similar to this and date the migration files with a date similar to how Ruby does it.

http://www.bottleit.com.au/blog/post/Continuous-database-integration.aspx

Answer 2

I have been happy with DBDeploy.NET for handling our database versioning. My current project uses C# + SQL 2008. DBDeploy is not integrated into Visual Studio but I suppose you could make it happen with some custom external tool entries in the IDE.

There are other tools out there that certainly work. I am not familiar with Ruby ActiveRecord:Migration but if you are already experienced in using this particular tool why not stick with it? As far as database versioning / migrations inside of Visual Studio I believe you will need to upgrade to the Database Professional Edition (extra cost for this version last I checked).

In summary, I would go with what you know. Most of the free tools for DB versioning are still somewhat half-baked at present IMHO. If you would like more information on DBDeploy.NET you can read up on it from the original project it was ported from here - http://dbdeploy.com/ Also, keep in mind that the DBDeploy tool is cross-platform (supports many DB systems, not just SQL Server & Oracle) and open-source.

Answer 3

For distributing application my favorite approach is actually one built in-house: Version Control and your Database.

I use the database extended properties to store the current on-disk deployed schema version, and then run an internal upgrade array that maintains a map between on-disk version => upgrade script to next version. At start-up, the app runs the steps in the upgrade array until the on-disk version matches the current app version. So an upgrade goes through all intermediate versions. Deploying a new site ( a new location) goes through every schema version, sometimes creating and dropping object no longer used. This may seem weird, but in the end the application can be deployed over any previously released version. If a client has a schema from 3 years ago everyone forgot what it contains, the app knows how to bring it up to date, always, which is great.

I favor this approach over diff compare tools (eg. VS DB project integration) because is testable and offer much better control over the exact steps taken on any upgrade. Diff tools do all sort of questionable actions, like copying tables and renaming, which doe snot work for deployments measuring +1TB (which my app has to deal with).

If the data size you expect is reasonable small (<100 Gb) I'd consider diff based tools. VS DB project deployment based on vsdbcmd works fine in such conditions. Also, if your deployment target is only one location (ie a web app where there is only one target, the web site db), then ability to upgrade any previous version looses it's appeal.

Answer 4

Hey mate,
Try DBSourceTools. http://dbsourcetools.codeplex.com
Its open source, and specifically designed to help developers version control databases.