Getting Started with Database Projects: Deployment Scripts and Breaking Changes

Mark Wheeler


Posts in this series:

  • Setting Up
  • Deployment Scripts and Breaking Changes
  • Temporal Tables

In my first blog I walked through setting up a database project and explained the benefits. Now that you have implemented your database project and developers are making their changes, your deployment process is failing every so often. What is up with that?

The errors that are most common in deploying a database project fall into the following groups (based on what I have seen over the last few years)

Breaking Changes

  • The default settings for database projects do not allow changes to take place that would result in data loss. Examples of breaking changes are dropping a column in a table that already has data or changing the data type on a column that would end up truncating data.

Data Issues

  • For me, deleting data should be a process that is done outside of your normal deployment process. Delete scripts should executed by the DBA team.

  • Post deployment scripts managing lookup data should use Merge statements so they can be safely rerun with consistent results.

  • In the event you are going to delete data as part of your deployment process, there are a few things to consider. First, managing all the constraints and disabling them prior to deleting any data. Once you have deleted the data and turn your constraints back on, there are couple of ways to accomplish this.

  • ALTER TABLE tablename WITH NOCHECK CHECK CONSTRAINT constraintname or ALTER TABLE tablename CHECK CONSTRAINT constraintname

    • Adding the constraint back with this setting allows the constraint to be added even if data exists that fails the data integrity check. Data is not validated with this setting.
  • ALTER TABLE tablename WITH CHECK CHECK CONSTRAINT constraintname

    • Enforces data integrity on existing data. If data exists that fails the data integrity check, adding the constraint will fail.

Temporal Tables

  • SQLPackage.EXE does not always create your script in the right order.

    • Changing the primary key on a system versioned table. The problem shows up in the delta SQL script. The table modification to the base table is prior to turning system versioning off. I have opened a case with Microsoft on this and it is currently flagged as a bug (Case # 119122421000099).

    • System versioning is not enabled for table X. This error is caused by the procedure sp_refresh executing prior to system versioning being re-enabled for a table. The work around for this is to turn off the option script refresh module under the advanced settings when manually publishing the database project.

Gated Check Ins

Most of the deployment errors I have outlined due not show up until the changes have been merged and deploy the database project. To minimize database deployment failures, setup gated check in rules as part of the pull request.

The one process to enable as part of the gated check in is to deploy the database project to an integration server/database prior to completing the merge for the pull request. You have two options for deploying the database project:

  • Deploy to an empty database and allow the database project to create the database.

    • This is the easiest option. The deployment process creates an empty database and deploys the project to the empty database. The drawback to this process is you will not see any data errors. This process does validate that all your scripts are syntactically correct. If you chose this option, make sure the settings for the database project deployment step is set to continue on error. This setting is found under the control options for Azure DevOps Pipelines. If this is not set, the step to drop the database is not run .
  • Deploy the project to a database with data. The drawback with this method is the data may be different between the environments.

Regardless of the option you choose for your gated check in, the benefits for testing the deployment prior to merging the changes are:

  • You have a better chance of finding a problem before it is merged with everyone else’s changes.

  • If there is a problem, the person who created the pull request has to address the issue in order for their pull request to succeed and putting the ownership of the fix back on the developer who made the changes.

The Pipeline Deployment Process

Setting up the deployment pipeline, there are a few things to consider. One being the difference in deployment settings between different environments. Another is debugging in the event of a problem.

Production - All of the companies I have worked with, not one would allow the deployment process to automatically create and deploy changes without DBA review and approval. If this is true for your environment, generate the delta script without deploying.

Lower Environment - For the lower environment, configure the deployment process to generate the delta script and then deploy the delta script in two steps. The benefit of using two steps versus the single publish process is the delta script is saved as an artifact of the deployment.

Now for those pesky breaking changes. The best way to address breaking changes is to use a single script. Each new breaking change is appended to the end of the script. Each change should have an if exists check to verify that the change still needs to be made. This script is executed prior to the database project deployment. This will apply all the breaking changes prior to the database compare process running and creating the delta script.

NOTE: As of the writing of this article, there is a difference in behavior when deploying changes via the publish process and creating the delta script and then executing the script in two steps. The publishing process uses the task Azure SQL Database Deployment . The publish process includes the object names and more information in the event of an error than executing the SQL script. This should not be the case as each is using the same task in the pipeline, just different settings. I have opened a case with Microsoft on this and waiting on a resolution. Case # 120042721002709

Troubleshooting Failed Deployments

Regardless of how diligent you are with your development process, eventually you will have a deployment that fails. How do you resolve the deployment error and determine why it failed?

Here are a few steps to follow:

  1. For Azure DevOps, go to the pipelines and look for the failed deployment. Failed deployments will have the Red icon next to the name.

  2. Download the logs for the failed deployment.

    • Execute Pre Deployment Script.log - Log file for the pre-deployment process.
    • Azure SQL System DB Publish.log - Log file for the deployment process.
    • Generate deployment script for #####.log - Log file for generating the delta SQL file.
    • DBNAME_Script.sql - If your pipeline process is configured to save the delta script as an artifact, this is the delta file that was run against the database. This file will be prefixed with the database name for the deployed database.
  3. Look through each log file for errors. Also take note of any warnings. These will not cause the deployment to fail. Warnings should be evaluated though.

    • 2019-12-23T17:44:05.1976430Z ##[error]Invalid column name 'IsActive'.