This posts talks about the high level steps I went through in order to get the SQL Server related components ready for automation in a project I worked on recently.
This project uses SQL Server Data Tools (SSDT) project in order to maintain the database schema in source control. Its output - the Data-tier Application Component Packages (DACPAC) gets deployed into the appropriate target environment via a WebDeploy package. And considering that the solution was designed as an Entity Framework (EF) database first approach, code first migrations were not a viable upgrade strategy.
Here are the steps I followed in order to bring the production environment up-to-date:
Create a baseline DACPAC and move it into source control - this represents the schema currently in production.
Next, ensure that every time the SSDT project is built a post event would generate a differential delta script between the baseline and latest DACPAC. I tried to simplify the following command by wrapping it up within a powershell script: