Multi-environment project setup

In this tutorial, we will explain how to set up your project within DB Version Control when there are multiple environments available (e.g. development, test, QA, production), and your databases already exist.

Add a new environment option

If you tried creating a new project within DB Version Control using Getting started guide, you probably saw the following screen:

In that tutorial, for simplicity, we used Initialize and skipped Add a new environment option.

When should a new project setup use Add a new environment option?

Ideally, a database should be versioned from the moment a new database has been created during project setup - however, that is not always the case.

The Add a new environment should be used when:

  • multiple environments (e.g. development, test, QA, production) are used
  • the databases in those environments already exist
  • there are differences between those databases
  • changes between environments are moved using version control commands (e.g. merge)

If all those conditions are met, a project should be set up using Add a new environment option.

During project initialization, DB Version Control generates a baseline script - a script that (if executed) would create a database from scratch to be identical to the current database. The changes to the database are tracked after a baseline script has been created. However, during the deployment, the baseline script is only executed when the target database does not exist (to not overwrite any database objects that already exist).

To generate change scripts between existing databases, the databases should be added in reverse order (e.g. PROD -> TEST -> DEV). This allows change scripts to be moved to different version control system branches (by merging) and deployed correctly to different available environments.

An example

Let's take an example when there are three environments - development (DEV), testing (TEST) and production (PROD) with differences between them:

 DEV database TEST database PROD database
dbo.FeatureA dbo.FeatureA
dbo.FeatureB

What happens during project setup, if Add a new environment is skipped and Initialize is clicked?

The development database is being scripted into files and a baseline script is created that includes both dbo.FeatureA and dbo.FeatureB tables - once the scripted files are moved to TEST and PROD branches for deployment, it means that TEST database will not have dbo.FeatureB created and PROD database will not have both dbo.FeatureA and dbo.FeatureB created (because baseline scripts are not executed as databases already exist). 

To avoid such scenario, Add a new environment option should be chosen:

The database objects and static data should be reviewed for inclusions/exclusions - after clicking Initialize, the baseline script should be generated and a message that Database scripted should be shown.

As mentioned in the message, it is a good time to commit these changes to the version control system as adding another environment or initializing a current database may overwrite existing files.

Next, we should repeat the same action for the TEST database. Note that if there is more than one additional environment in use, the databases already exist and there are differences between them, all those environments should be added - however, in the reverse change flow order. For example, if the change flow is DEV -> TEST -> QA -> PRODUCTION, the Add a new environment should be done in the following order:

  • PRODUCTION
  • QA
  • TEST

The current database/environment (in this case, DEV) should not be added as it will be scripted during initialization.

Once the TEST database is scripted, we can view the differences in our version control system:

There are 4 different files:

  • Create database.sql (modified) - usually, only the comments section differs because it contains information on which database was used to generate the script. Such differences can be ignored.
  • Baseline.sql (new) - a new baseline file for TEST database
  • Migration script (new) - the file which creates a new table dbo.FeatureA
  • Tables\dbo.FeatureA (new- dbo.FeatureA table, scripted into a file

The migration script to create dbo.FeatureA table makes sense but why is there a new Baseline script? The second baseline file is needed to ensure that the migration script (that creates dbo.FeatureA table) does not get executed on the TEST database (DB Version Control only deploys changes after the last baseline script).

In order to transfer changes to TEST/PROD environments correctly, the scripted changes should be committed into two separate commits:

  • the first commit for dbo.FeatureA table changes and scripted dbo.FeatureA file
  • the second commit for TEST database baseline file

Add all other environments and then click Initialize - the current database should be scripted into files. Commit files in the same as mentioned above.

If we go back to our version control system, we should get a similar view in the History tab:

In order to deploy TEST database (without any changes from DEV), both dbo.FeatureA and TEST baseline commits should be merged into TEST branch:

In order to deploy PROD database (without any changes from TEST), only the initial commit should be merged into PROD branch (there are no migration scripts after baseline, therefore, no migration scripts will be executed):

To transfer dbo.FeatureA changes from TEST to PROD, we only need to merge up to a specific commit from TEST branch:

Once these changes have been deployed, we can merge the rest of changes from TEST branch (a second baseline script will be merged but deployment would not execute any scripts):

The changes from DEV environment could be transferred to TEST and PROD in a similar way. 

Here is how the migration scripts order look like:

  • V0__2020.07.24_083023__!Create database.sql
  • V0__2020.07.24_083024__Baseline.sql (PROD baseline)
  • V0__2020.07.24_083520__Test.sql (changes in TEST)
  • V0__2020.07.24_083529__Baseline.sql (TEST baseline)
  • V0__2020.07.24_084532__.sql (changes in DEV)
  • V0__2020.07.24_084540__Baseline.sql (DEV baseline)

Summary

In this tutorial, we learned that Add a new environment should be used when:

  • there are multiple environments (e.g. development, test, QA, production) in use
  • the databases in those environments exist
  • there are differences between those databases
  • the changes between environments are moved using version control commands (e.g. merge)

Multiple baseline scripts are generated in order to not execute changes on the databases that already have those changes applied. The change scripts and baseline scripts should be committed separately to allow changes to be transferred and deployed correctly to other environments.

Leave a comment