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 SQL Change Automation projects in source control.

In the vast majority of cases, having one project per database is recommended; indeed, SQL Change Automation'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 SQL Change Automation project for all of our services is a non-starter for us. Is there a way to split the SQL Change Automation 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 SQL Change Automation 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 SQL Change Automation project file (<ProjectName>.sqlproj) is required.

In order for each project to have it's own migration log, you must add the MigrationLogSchemaName property. This sets which schema the __MigrationLog table (and associated objects) is deployed to. If the specified name doesn't already 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 SQL Change Automation project for each schema. Before you connect your project and start importing objects from your existing database, set the above property 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.

The next step is to introduce a SQL Compare filter file which will only include objects for the schema you want. So, project A will have a filter file that only includes objects from schema A, and so on. You can learn how to configure filters in Filtering Database Objects.

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 SQL Change Automation tool-window to Working with 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.