A development source represents the current state of the database in your development environment.

There are two types of development source in SQL Change Automation:

• A development database
• A SQL Source Control project

Development database

A development database is your development environment where you can safely edit your schema and static data.

SQL Change Automation will compare the contents of your project against the development database in order to:

• Generate new migration scripts
• Apply migration scripts from other members of your team

SQL Change Automation will automatically generate a corresponding shadow database in order to perform these comparisons when using a database as the development source.

If using development databases as the project source, we recommend that each developer has their own development database.

SQL Source Control project

It is possible to link SQL Change Automation to a SQL Source Control project. This is a hybrid approach to managing database changes: managing the schema model state in SQL Source Control, and generating migrations for deployment using SQL Change Automation.

A SQL Source Control project defines the desired state for a database.

You may wish to use a SQL Source Control project as your development source to:

• Have greater control over how the SQL Source Control project will be deployed
• Decouple making changes in development from defining how to deploy them
• Avoid creating a migration script in order to share changes with your team

Migration scripts can be created as changes are made, or at a later point in time, depending on the roles and skill sets within the team:

1. Developers generate their own migration scripts in SQL Change Automation from their local database changes before committing changes to version control (option A below) 
2. Developers commit their changes to version control without migration scripts, and at a later point in time a DBA or SQL developer creates the migration scripts (option B below)

Either way the deployment artifact for your project is now the set of migration scripts, rather than the "state" saved in the scripts folder.

Option A

Option B

Migration scripts (rather than the state) becomes the build artifact used at deployment time.

SQL Change Automation will compare the contents of your SQL Change Automation project against the SQL Source Control project in order to generate new migration scripts.

SQL Change Automation will need to be configured with a shadow database in order to perform these comparisons.

Configuration

When you initially set up your project you will be asked to specify a development source.

You can change the development source that your project uses at any time. The configuration for your development source is stored inside the settings file for your project.

Development database

To use a database as your development source you must specify:

• The connection string for the database

<PropertyGroup>
<DevelopmentSource>Database</DevelopmentSource>
</PropertyGroup>

<PropertyGroup>
<TargetConnectionString>your_connection_string</TargetConnectionString>
</PropertyGroup>

SQL Source Control project

To use a SQL Source Control project you must specify:

• The path to your SQL Source Control project. This should be a relative path based on the location of the SQL Change Automation project
• A shadow database connection string

<PropertyGroup>
<DevelopmentSource>ScriptsFolder</DevelopmentSource>
<ScriptsFolderPath>../path/to/folder</ScriptsFolderPath>
</PropertyGroup>

<PropertyGroup>
<ShadowConnectionString>your_connection_string</ShadowConnectionString>
</PropertyGroup>

Learn more

• Setting up a project with a SQL Source Control project in SSMS