It occurred to me, all the searches coming by looking for information on composite projects in VSTS:DB, maybe instead of wondering how it’s done you’re wondering why it’s done.
In the GDR release there are three kinds of projects you can create; database, server, partial. Database and Server projects are created directly through the VS GUI as new projects and I covered that a bit in the last post. Partial projects are something different.
Partial projects are pieces of structure or code, for example a tally table, that you want to share across multiple projects. Partial projects are created by accessing the object or objects you want to define as a partial project in the Solution Explorer. Right click on the object(s) and select Export As Partial Project. This creates a .files file (repetitive, I know). You can then go to the other projects and, in the Solution Explorer, right click on the project itself and select Import Partial Project. Navigate to where you created the .files partial project (avoided the repetition that time) and select it. It will make the objects defined within a part of your project. It will add a folder within the project definition in Solution Explorer showing the source of the partial project and a date & time so you’ll know when it was imported.
So, if you have common code that you share amongst your databases, partial projects are the mechanism for defining them and communicating them between projects.
Why then would you make the other types of projects, combining Server and database projects into a composite project. Speaking only for myself, the main reason I would do that is to be able to deploy a database to more than one environment and make changes depending on the environment I’m deploying to. So, for example, you create a database that has all the necessary objects, tables, procs, roles. Then you create multiple server projects for the various environments, Development, Sandbox, QA, Continuous Integration, Staging, Production, whatever. Then you create a composite project combining your database project with each of these server projects. This way, the server project has the necessary security settings for Development, which are different than QA, just as an example. The composite project can have the new roles and security settings unique to the environment. For example, in development, in addition to the needed application roles, you want to have a developer role that has data_reader, data_writer, ddl_admin, security_admin, but you’re revoking create table, create index and some others. If you define that role in the composite project, then you can deploy to development without fear of messing up security. You can create a different composite project for QA and create a QA role that can execute procedures and has data_reader, more than the roles necessary for production, but less than what’s needed in development.
Another example that comes to mind is creating a database project that has structures only. Then you create a composite project that has only stored procedures. The composite can deploy a full database, but you can give the developers access only to the composite project and not to the project that maintains the structure. This way, they can write code, but can’t affect structure and you can control that within source control, let alone at the database level.
The possibilities for the various types of database project management are very wide because of the composite projects in the GDR.
OK… so, can you create a composite project that overwrites certain portions (files) from a referenced project? For example, I have a base DB schema that will be deployed to multiple servers. Sadly, one of them requires a tweak to a stored procedure that the rest don’t. How would you handle this?
anybody?
Oh man, I lost the alert that you posted. I’m sorry.
First blush, create a separate project that references the full project but the only other thing in the project is this procedure. I’ll have to experiment to see if that will work the way I think it will.
Composite projects exist to handle dependancy conflicts. For example:
Database1 has the following objects:
CREATE TABLE t1(c1 int);
CREATE VIEW v2 AS SELECT c2 FROM Database2..t2
Database2 has the following objects:
CREATE TABLE t2(c2 int);
CREATE VIEW v1 AS SELECT c1 FROM Database1..t1
As you can see an object in Database1 references an object in Database2 while the opposite is also true, an object in Database2 references an object in Database1. We have a chicken-and-egg situation – you can’t deploy Database1 before deploying Database2 and you can’t deploy Database2 before deploying Database1.
Composite projects alleviate this problem by allowing you to split objects from a single database into multiple projects. So, you could place view Database1..v2 into a new project (called, say, Database1_b). You would then have 3 projects (and hence 3 deployments to do) but at least all of your cross-DB references can now be resolved.
Note that Partial Projects and Composite Projects are not the same thing.
Regards
Jamie