Splitting database projects by schema

Comments

• dnlnln Redgate › Posts: 234 Gold 2

  August 24, 2016 4:41AM edited October 17, 2017 7:51AM

  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.

  Daniel Nolan
  Product Manager
  Redgate Software

  0 · Share on Twitter