Multiple Sql Server 2008 Database Projects, do they go in a Sql Server 2008 Server Project?

Question

I'm a bit new to database projects, but I find that with a bigger team it is a good way to synchronize development.

• I had a single Database Project that contained all the tables used by a web app.

• The powers that be want to display data from another database in the web app.

• I create another Database Project that represents the tables I need in the other database.

• I create views in the original database that represent tables in the second database (for linq to sql joins mainly)

Questions

1. Do I create a Sql Server 2008 Server Project and merge both of these databases into it? From what I can tell, a Server Project still only represents a single database.

2. If a Server Project isn't meant to represent multiple databases, what is it for?

3. I have added steps in our TFS Workflow to deploy changes from the first Database Project into our Dev environment on check-in (CI). Assuming a Server Project is meant to contain multiple database projects, can I deploy it and have it deploy changes in both databases?

Thanks!

Answer 1

A server project is for server scope objects needed by your solution: endpoints, server principals (logins), server level permissions, linked servers and other similar constructs that are not scoped to any particular database.

Seems to me that what you need is a solution with two projects:

• your original database project
• you reference database project

You need a third project in this solution, a server project, only if you must add server scoped objects.

For more details, see Using References in Database Projects.