Run Schema Compare from Azure DevOps pipeline to update SQL database project

Question

We have a SQL database project that we use to deploy changes into our TEST and PROD environments. During the day developers make changes on the DEV database directly (do not use database project). Periodically, a schema compare is done within the database project to collect all the changes made by developers in the DEV database so they can be applied to TEST environment via the Azure release pipeline.

This all works great from Azure Pipelines, but we would like to create a pipeline that automatically runs the schema compare between DEV database and applies the changes to the database project (we have to do this manually). Years ago I looked into this and the command line did not support having the database project be the target of the schema compare. Does anyone know if this type of workflow is possible these days?

Answer 1

As Daniel mentioned, it`s not a good idea to use dev environments without database project. However, there are cases when we have to work in a common database because of sandbox prices, test data, or any other constraints. In this case too complex to check when your DEV database is ready to sync changes because it may contain raw changes. Consider using the process: your developers add changes to the database project under source control. They can change DEV database directly but each developer commits only its work scope to the source control. In this case, you can see changes for each user story and bug in the source control and link them to corresponding work items if needed. Additionally, you can add an integration database to check the consistency of new changes:

DEV (manually + commit to SC) -> INT (pipelines CI/CD) -> TEST -> PROD

As additional ideas:

1. You may try to create a Coded UI test or Selenim test to press buttons on VS and update your database project. Then you can add it to a pipeline and update your SC every night.
2. SqlPackage.exe generates an update script Database->DacPac file. However, there are no ways to apply it to a database project.