How to prevent SSDT/DACPAC publish with DropObjectsNotInSource=True from dropping Azure Functions az_func runtime tables

by

Summary

The problem arises when using SSDT/DACPAC publish with DropObjectsNotInSource=True, which drops objects not present in the source. This causes issues with Azure Functions runtime tables in the az_func schema, leading to loss of runtime state. The goal is to prevent dropping objects in the az_func schema while maintaining DropObjectsNotInSource=True for other schemas.

Root Cause

The root cause of this issue is the DropObjectsNotInSource=True parameter, which instructs DacFx to drop objects not present in the source. This includes objects in the az_func schema, which are not part of the SSDT project. The reasons for this behavior include:

  • DacFx includes drop statements for objects not in the source
  • Pre/post-deployment scripts do not prevent drops, as they run before or after the deployment
  • DoNotDropObjectTypes=Schema is not a valid solution, as it does not prevent dropping tables

Why This Happens in Real Systems

This issue occurs in real systems due to the following reasons:

  • Azure Functions create and manage objects in the az_func schema
  • SSDT/DACPAC publish with DropObjectsNotInSource=True is used to clean up schema drift and renames
  • DacFx drops objects not present in the source, including those in the az_func schema

Real-World Impact

The real-world impact of this issue includes:

  • Loss of runtime state for Azure Functions
  • Breakage of Azure Functions runtime integration
  • Inability to use DropObjectsNotInSource=True for schema cleanup

Example or Code

using Microsoft.SqlServer.Dac;

class CustomDeploymentContributor : DeploymentContributor
{
    public override void PopulateDeploymentPlan(DeploymentPlanContributorContext context)
    {
        // Filter out deployment plan steps touching schema az_func
        context.DeploymentPlan Steps = context.DeploymentPlan.Where(step => step.ObjectName.SchemaName != "az_func");
    }
}

How Senior Engineers Fix It

Senior engineers fix this issue by:

  • Creating a custom deployment contributor to filter out deployment plan steps touching the az_func schema
  • Using a DacFx deployment contributor to modify the deployment plan
  • Implementing a plan modifier to exclude objects in the az_func schema from being dropped

Why Juniors Miss It

Juniors may miss this issue due to:

  • Lack of understanding of DacFx and SSDT/DACPAC publish behavior
  • Insufficient knowledge of Azure Functions runtime integration and object management
  • Failure to consider the implications of DropObjectsNotInSource=True on non-source objects

Leave a Comment