How to Develop TSQL in Visual Studio 2010 Database Projects

Question

Silly sounding question, I know... Let me lay some groundwork first.

I have successfully created a database project comprised of the hundreds of tables, stored procedures, indexes, et.al. that make up our production database.

I have successfully added the solution to source control (TFS).

I have made a change (as a test) to some of the objects and generated a deployment script, and the whole system is very impressive, I must say. But it seems the strength of VS 2010, from a DB perspective is deployment, and not necessarily development.

I am totally baffled on the day-to-day workflow involved in database/TSQL development using Visual Studio. Let's suppose I need to add a few columns to a table, and modify related stored procedures to return/update this data for these columns.

While it's easy enough to modify all the scripts in my database model, I'd like to be able to isolate them against a dev database where I can do some testing... But it's as simple as not being to update a proc if it exists without manually changing the script to an ALTER (or adding DROP code prior to the CREATE). Having to do this once or twice is a non-issue, but in a real dev environment, we do this all day long.

Perhaps the answer is to perform frequent deployments to the dev server, as I debug and make changes to procs, for instance? Quite a bit of overhead; I could execute the necessary scripts manually in a few seconds, building and deploying takes a few minutes. Plus, if three of us are deploying different changes to a dev DB, wouldn't we overwrite each other's modifications?

Sorry to be so longwinded, but I can't help but think I am missing something simple here.

Are there any books/tutorials/webinars that showcase this type of approach to actual development?

Answer 1

I think you've hit the nail on the head. In order to test your modified stored procedures, you have to go through the deployment step to update your database. That's the drawback of the offline development model.

Here at Red Gate we've had numerous requests to make SQL Source Control support the Database Project, which would allow developers to benefit from the 'online' development model whilst still benefiting from the Database Project features. We haven't made the decision either way, but we'll certainly look into this if there's sufficient community demand.

In the meantime, you'll have to keep deploying to dev on a regular basis!

An alternative is to develop on a real database, and use the Schema Compare feature to synchronize back to your Database Project. Schema Compare is available in the Premium and Ultimate editions of Visual Studio.

David Atkinson Product Manager Red Gate Software