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

views:

266

answers:

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 intuitive 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?

+3 A:

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.

HLGEM 2010-02-01 18:48:37

I've seen DBs maintained that way and it's entirely not the way I want to do it, how do you handle a change that requires a table being dropped and readded through that? Every script changes needs a SSIS package with it too?

Chris Marisic 2010-02-04 17:04:53

We don't create SSIS packages, just t-sql scripts. All our table creation scripts drop the table first if it exists. If we don't want to drop the table we write alter table scripts to run with the particular project that we are putting to prod and then go in and reviae the table creation scripts to include the changes in case we ever need to recreate.

HLGEM 2010-02-04 18:18:32

What about the changes that require a table drop that has data in it?

Chris Marisic 2010-02-19 13:51:51

We don't drop tables with data in them. We use alter table to change them. IF you have to make a change requiring a drop table when the table has data in it, then you include the code in the script to take all the actions you need to take to preserve the data.

HLGEM 2010-02-19 14:23:20

And having worked at a job that maintained it's database in this exact fashion it always felt like a nightmare to me.

Chris Marisic 2010-02-21 04:22:11

+2 A:

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.

Randy Minder 2010-02-01 19:11:58

Your entire middle paragraph is what I know VSDE is *supposed* to do but I'm missing what you do to make it work that way.

Chris Marisic 2010-02-01 19:58:06

You start by creating a new database project in VS. You then import your database schema into the project. Is this what you were looking for?

Randy Minder 2010-02-01 20:12:56

What happens after the initial import? I couldn't figure out what to do after creating the project

Chris Marisic 2010-02-04 17:04:08

@Chris, Are you looking for something similar to Rails way of versioning the database? If so, Database Edition is NOT similar to it.

Xinxua 2010-02-19 17:34:07

I'm not sure how rails does it? If you could elaborate on that maybe that's what i'm looking for.

Chris Marisic 2010-02-21 04:18:23

ansaurus

tags:

views:

answers:

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

related questions