Published 31 July 2019
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
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:
SQL Change Automation will automatically generate a corresponding shadow database in order to perform these comparisons when using a database as the development source.
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:
- Developers generate their own migration scripts in SQL Change Automation from their local database changes before committing changes to version control (option A below)
- 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.
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.
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.
To use a database as your development source you must specify:
- The connection string for the database
Set the development source to a database in the .sqlproj
<PropertyGroup> <DevelopmentSource>Database</DevelopmentSource> </PropertyGroup>
Set the database connection string in the .sqlproj.user
<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
When setting the shadow database connection ensure it isn't an existing database as this will be dropped and recreated multiple times during the usage of SQL Change Automation.
Set the development source to a SQL Source Control project in the .sqlproj
<PropertyGroup> <DevelopmentSource>ScriptsFolder</DevelopmentSource> <ScriptsFolderPath>../path/to/folder</ScriptsFolderPath> </PropertyGroup>
Set the shadow database connection in the .sqlproj.user
<PropertyGroup> <ShadowConnectionString>your_connection_string</ShadowConnectionString> </PropertyGroup>