Visual Studio 'SQL Server 2008 Server Project' vs. 'Sql Server 2008 Database project'?

Question

I can't see to find a quick explanation of the differences so I can figure out which to use.

One is for a server one is for a Database? Im not sure what that means..

Basically we are doing a new web app and I want to see what these project types can offer me in terms of tracking the DB code/schema etc..

Answer 1

SQL Server 2008 Project: this is used to create a SQL-CLR module, e.g. create a stored proc, a function, an aggregate etc. in C# (or VB.NET), that will be run inside SQL Server.

When you create such a project, and you click "Add New Item" in Solution Explorer, you're given the choice of creating a stored procedure, a trigger, an aggregate, a user-defined function, a user-defined type or a helper class. These will all be compiled into a .NET assembly, which will be deployed to SQL Server and be executed inside SQL Server in the SQL-CLR runtime environment.

SQL Server Database Project: that's only a collection of SQL scripts to be run against a database, to create and manipulate database objects

In a SQL Server Database Project, you basically only get to add SQL scripts - .sql files. Nothing else, really. So it is indeed quite different from the SQL Server 2008 Project type!

Answer 2

The answer here doesn't seem to actually answer the posted question. "SQL Server 2008 Server Project" "SQL Server 2008 Database Project" are both things that exist in Visual Studio 2008 Database Edition, but sadly while there isn't one named "SQL Server 2008 Project" as the answer suggests, there is one "SQL Server Project" that matches the description. Moreover, there isn't a project type anymore called "SQL Server Database Project" (at least not in Visual Studio 2008 Team Suite), although the description given above seems to describe the much older database projects that were at one point available in VS.

After thinking about it further, I'm going to guess that marc_s doesn't have the Database Edition (otherwise known as Data Dude or DBPro) installed. Correct me if I'm wrong.

Sql Server 2008 Database Projects and Sql Server 2008 Server Projects seem to do basically the same thing. I would have hoped that the Server projects would be used for the installation and configuration, and change management of a Sql Server instance, while Database Projects would be used for individual databases inside them...but this doesn't seem to be the case. Has anyone actually been able to determine the differences between them?

After playing around for a bit, the only difference I've been able to find is that Sql Server 2008 Server Projects will always deploy to the master database on the server you deploy to, regardless of what kind of database you import (master or otherwise) when you create the project. So in that case, Database Projects are for your business databases while Server Projects are specifically for the master database on the server that houses your business databases.

Edit: After scouring the VS2008 documentation a little harder, I came across this specification:

"Deploying Server Projects A database project can contain definitions for database objects, for server objects, or for both. In most environments, developers can change database objects, but only the database administrator can change server objects. You can enforce this restriction by putting server objects in a separate project (known as a server project). You can then restrict version control so that only your administrators can change the server project. In a staging or production environment, the server project and its objects will most often be deployed separately from the project that contains the database objects.

You deploy a server project by using the same procedures that you use to deploy a schema project."

http://msdn.microsoft.com/en-us/library/dd193413.aspx