Best practices for database development in Visual Studio 2010?

I'm starting a new, large server based project using .Net 4 and Visual Studio 2010. I've done this many times before (with various versions), but I've never found a good way to maintain my database. There's been a lot of new technologies in the past few years to make this easier. However, there is one thing I haven't found: A technology/framework that can automatically update a database based on a model, without regenerating the entire thing and losing all the data. Can anyone point me in the direction of anything like that?

If not, can anyone point me in the direction of best practices for such development? We've got one development DB, one test DB and a production DB. I would like to be able to just run some auto generated script or code to update a database according to the latest model. I know that certain changes are harder to track than others, but at the moment about 95% of changes to the schema are added tables or columns. Those changes should be possible to auto generate script for - right?

Any help in helping me get this database off to a good start will be much appreciated!

This looks like the sort of thing I'm looking for. However, it seems that both frameworks have been idle for quite some time. Are these up to date? Will they work 100% with the latest version of SQL Server? I will absolutely try one or both of these out!

Chris Ridge 2010-09-06 20:33:28

@Chris Ridge - Wish I had an answer for you. From other posts about this, RikMigrations seems to be a more complete solution. I am unaware of other projects of this nature.

Oded 2010-09-06 20:35:29

This is an old post, but there are quite a few tools out there, it seems. Most of them seem to be in the same category (activity wise) as the two you mention, though: http://flux88.com/blog/net-database-migration-tool-roundup/

Chris Ridge 2010-09-07 04:46:13

I ended up using RikMigrations, but I have the source code in my solution as I may need to alter it, and I've done some refactoring to make it easier to test against. Thanks for the tip :-)

Chris Ridge 2010-09-07 10:47:11

If you're referring to the standard Database Project in VS, then yes, I've tried it but not found it fitting. I may just not know it well enough, though - so I'll have a look at your link and see if I may have missed something vital about how to get the most out of that approach.

Chris Ridge 2010-09-06 20:31:12

Does anyone have actual experience with VSDB?

Andomar 2010-09-06 22:03:20

I tried it. But I still haven't found a way to use it in my projects. I use nHibernate and we manage database differently.

Pierre 303 2010-09-06 22:05:17

ansaurus

tags:

views:

answers:

Best practices for database development in Visual Studio 2010?

related questions