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

SQL Server Management Studio solution

• allow use GUI designer for creating and editing some objects.

Visual Studio Projects

• allows use TFS as version control and more integrate work of all developers working with a database;
• better implements IntelliSence