VSTS:DB More on Composite Projects

February 6, 2009 at 10:47 am (SQLServerPedia Syndication, Visual Studio)


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.

fig11

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.

3 Comments

  1. josh said,

    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?

  2. josh said,

    anybody?

  3. scarydba said,

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: