How do you actually use Visual Studio Team System database projects to version Sql Server

How are you supposed to correctly use a Visual Studio Team System database project to implement version control on a sql server database?

This might seem overly generic but everything I've found so far online hasn't helped me in being able to achieve anything useful. I have managed to find functionality that appears to be similar to features that are in Redgate's tool Sql Compare but it definitely didn't seem as intuit开发者_如何学Goive as their product.

From my understanding of how these db projects are supposed to work is that you're able to have a version of the database that either lives in Team Foundation Server (or inside the sql server itself) that you can check out to your local machine work on it and then check in the new changes which would allow for simultaneous development to work normally as it does for coding. Was I misinformed? Or is it just a complicated process to get setup?

Related is then how do you use it to deploy changes to the staging/production servers?

We don't use that, we simply script every thing and put it in source control like any other file and ALL deployments to prod are only through scripts pulled down from source control. I think the real key is that nothing gets put on prod except thorugh a source controlled script. Once the developer can't get his change to prod any other way (Devs should not have prod rights), there is no incentive to not put the change in source control.

Funny you should ask. I am the one responsible for getting our production databases under version control, and we're using Visual Studio Database Edition to do it. It is a fantastic tool. The very nice thing about this tool is that not only will it keep your schema under version control but it will validate your database schema as well and permit you to run code analysis against it. It also allows refactoring operations, and many other things.

Typically we work against a local development database, synch the changes back to VSDE, build the database to make sure there are no warnings or errors, and then create a deployment script for deployment to our production databases.

This is a simplified explanation of what and how we doing this, but I think it gives you a general idea of how it can be used. I'd be glad to answer any more specific questions you have.