When beginning the process of turning your database environment into assets that can be shared among team members, and subsequently configured for automated deployment, you'll need to decide on how best to structure your database projects in source control.

In the vast majority of cases, having one project per database is recommended; indeed, ReadyRoll's script-authoring and deployment workflows are primarily optimized for this configuration.

However, the need to partition a database into multiple projects/solutions may arise in this increasingly-common scenario:

Our micro-services architecture requires us to have a separate Visual Studio solution for each service, to allow each service to be deployed independently. The problem is that we use the same database for a multitude of services -- each service has its own schema -- so having one ReadyRoll database project for all of our services is a non-starter for us. Is there a way to split the database projects by schema, to allow each schema and its objects to be deployed independently?

In such cases it may be desirable to adopt a one-project-per-schema approach, rather than the typical one-project-per-database approach.

Partitioning a database by schema

The simplest way to partition a database into individual projects is by-schema, which is possible provided:

• Each schema is self-contained; there cannot be any cross-schema dependencies (e.g. SchemaB.MyTable cannot have a foreign key that references SchemaA.AnotherTable)
• If there are common dependencies between all schema, such as a role that is granted select permission on various tables, these objects are included in all relevant projects

In order to deploy each schema and its contained objects independently of other schema, your project will need to be configured to deploy its own, individual migration log table. Additionally, to ensure that ReadyRoll generates scripts for the objects contained within that schema only, your project will need to be configured to filter out all other objects outside the pertinent schema (aside from any common objects).

To setup each project to handle the deployment of objects related to the relevant schema only, some editing of the database project file (<ProjectName>.sqlproj) is required in order to add the following properties:

• MigrationLogSchemaName: sets which schema the __MigrationLog table (and associated objects) is deployed to. If the specified name doesn't already exist, ReadyRoll will create the schema automatically during the initial deployment.
• ExcludeObjectsFromImport: sets filtering criteria for the project using regular expressions. More information on this setting can be found in Key Concepts > Excluding Database Objects.

For example, say there are 3 schema involved: A, B and C. Start by creating a ReadyRoll project for each schema. Before you connect your project and start importing objects from your existing database, set the above properties within each of the project files by right-clicking the project in the Solution Explorer, selecting Unload Project, then Edit Project to open the .sqlproj file within Visual Studio. Then add the property group to each project as follows:

<PropertyGroup> 
  <MigrationLogSchemaName>A</MigrationLogSchemaName>
  <ExcludeObjectsFromImport>
    Schema=[B]; 
    Table=[B].[(.?)]; 
    Schema=[C]; 
    Table=[C].[(.?)];
  </ExcludeObjectsFromImport>
</PropertyGroup>

<PropertyGroup> 
  <MigrationLogSchemaName>B</MigrationLogSchemaName>
  <ExcludeObjectsFromImport>
    Schema=[A]; 
    Table=[A].[(.?)]; 
    Schema=[C]; 
    Table=[C].[(.?)];
  </ExcludeObjectsFromImport>
</PropertyGroup>

<PropertyGroup>
  <MigrationLogSchemaName>C</MigrationLogSchemaName>
  <ExcludeObjectsFromImport>
    Schema=[A]; 
    Table=[A].[(.?)]; 
    Schema=[B]; 
    Table=[B].[(.?)];
  </ExcludeObjectsFromImport>
</PropertyGroup>

After you have configured the rules for each project, in the Solution Explorer, right-click each of the project node and select Reload Project. Build the solution to ensure that the new property settings take effect, then use the ReadyRoll tool-window to Deploying to an existing database the subset of objects into each project.

When you later import new changes into the project and subsequently deploy the generated migrations to other environments, any objects outside of the project schema will be ignored, allowing you to develop and deploy each database schema independently.