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.