SQL Server Management Studio solution vs Visual Studio Projects

Question

Almost all of our projects involve a web application or winforms application and a data access layer (class library) and stored procedures/database scripts.

We are looking for a good way to organize the solutions, and found a few ways:

1)We could have a sql server management studio solution for all the db related things, then have a visual studio solution for the application and data access layer projects.

2)We could do it all in visual studio with 3 projects, a sql server project, a web app/winforms app project, and a data access layer project.

3)We could do it all in visual studio with 2 projects, a web app/winforms app project and a data access layer project, and just put the sql scripts in a directory in the data access layer project.

I'm sure there's other ways, but I'm just curious to see how others go about doing it.

Answer 1

I have done this successfully using Visual Studio Team System Database Edition - multiple types dot net projects with a database project in a single solution.

Keep in mind that deploying a database project takes much longer because you usually need test data and most inserts thereto are RBAR insert statements. We used to turn off deploying the database project until absolutely necessary (usually once or twice a week).

Of course, all of this was in TFS for source code control

Answer 2

I use Management Studio and some batch files to export/import/store the sql in the solution (under source control too).

So each time there's a change in the db it's exported to the solution and a custom tool is run to update the ORM proxy classes.