Splitting database projects by schema

dnlnln

Our micro-services architecture requires us to have a separate Visual Studio solution for each service, to allow each service to be deployed independently. I realize the typical way that a ReadyRoll project is deployed is with a one-project-per-database setup. The problem being 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?

dnlnln

Please note that information on partitioning database projects by schema is now included in the ReadyRoll documentation: Key Concepts > Partial projects.

ReadyRoll does allow you to have multiple projects for the one database, providing the following conditions are met:

• Each schema must be self-contained: there cannot be any cross-schema dependencies between the objects
• If there are common objects used by multiple schemas (such as roles), then these must be included in all projects along with idempotent logic to ensure that each project can deploy the objects successfully (e.g. IF NOT EXISTS ROLE THEN CREATE ROLE...)
• Each project must be set up to only include the relevant objects. This is done by filtering out sets of objects with regex patterns.

Groups of objects (or individual objects) can be filtered at the project level with the ExcludeObjectsFromImport property. This will stop the ReadyRoll tool-window from importing any objects that match the pre-defined regex patterns. As the behaviour of this property is to filter-by-exclusion (rather than the filter-by-inclusion that would perhaps be more suitable for your use case), you will need to add exclude expressions for each of the schemas that you do not want to include in that project.

For example, say there are 3 schemas involved: A, B and C. Start by creating a ReadyRoll project for each of the schemas. Then, before you connect your project and start importing objects from your existing database, you'll need to add the exclusion rules to each of the project files. This can be done by unloading the projects and editing the .sqlproj file within Visual Studio. Under the root node of each project file, add an "ExcludeObjectsFromImport" property as follows.

ProjectA.sqlproj:

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

ProjectB.sqlproj:

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

ProjectC.sqlproj:

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

If you have other types of objects apart from tables, then those will also need to be included in the above property values. For the full list of object types see:
https://documentation.red-gate.com/display/RR1/Excluding+Database+Objects

After you've configured the rules for each project, you can then use the ReadyRoll tool-window to connect to your existing database and import the subset of objects into each project. When you make changes to the project, and subsequently deploy the migrations to other environments, ReadyRoll will deploy incrementally. This ensures that only the objects you change within each project will be affected by the migrations.