You will need to decide on the structure of your SQL Change Automation projects in version control. It is most common to have one project per database. However, there are situations in which it is more appropriate to partition the database into multiple projects. In such cases it can 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 (for example, 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 scripts are generated 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 project file is required.

In order for each project to have its own migration log, you must add the MigrationLogSchemaName property. This sets which schema the migration log table (and associated objects) is deployed to. If the specified name does not exist, SQL Change Automation will create the schema automatically during the initial deployment. Set this to something that reflects the project.

For example, say there are 3 schema involved: A, B and C.  Start by creating a project for each schema. Before you connect your project and start importing objects from your existing database, set the MigrationLogSchemaName property within each of the project files by editing the  .sqlproj file.

The next step is to introduce a SQL Compare filter file which will only include objects for the schema you want. Project A will have a filter file that only includes objects from schema A, project B will have a filter field that only includes objects from schema B and the same concept applies to the remaining schema C. You can learn how to configure filters in filtering database objects.

Once you have configured the rules for each project save the project file. Then use SQL Change Automation to work with an existing database and subset the database objects into each project. This will allow you to develop and deploy each database schema independently.

Learn more

• Using partial projects for configuring tSQLt tests
• Working with an existing database in SSMS
• Working with an existing database in Visual Studio