Build and deploy a SQL project for multiple SQL versions

Sometimes you need to deploy the same database project to both a SQL Server instance and an Azure SQL Database. Your test infrastructure could be SQL Server in containers while your application runs in Azure SQL Database, or your application is could simply be used on different infrastructure per tenant. With the consistency of T-SQL syntax across the SQL engines and smaller differences between versions, this isn’t an uncommon scenario but you do want to be thoughtful and careful about how you approach it. In this article I’ll look briefly at how you can deploy a SQL project for multiple versions of SQL, like SQL Server 2019 and Azure SQL Database, from Azure DevOps pipelines.

Deploy the Same DACPAC to Multiple Platforms with /p:AllowIncompatiblePlatform=true

When building your database project, the generated DACPAC is targeted for a specific SQL platform (eg SQL Server 2019). If you try to deploy that DACPAC to a different type of SQL server (eg Azure SQL Database), you'll likely hit a platform mismatch error when the deployment starts. To override this platform match check, you can use the /p:AllowIncompatiblePlatform=true option with SqlPackage or your deployment tasks. This tells the deployment tools to skip platform validation and proceed with the deployment and assumes everything in the DACPAC is compatible with wherever it is being deployed to.

In Azure DevOps, if you're using the SqlAzureDacpacDeployment task, you add this extra property to the AdditionalArguments field:

- task: SqlAzureDacpacDeployment@1
  inputs:
    ...
    AdditionalArguments: '/p:AllowIncompatiblePlatform=true'

You can also manually call SqlPackage in a script step if needed — which gives you full control, especially if you're deploying to a SQL Server VM (the Azure task doesn't always work there).

Build the Project Twice to Validate Syntax for Both Platforms

Rather than relying on deploy-time compatibility settings, another option is to build the project separately for each target platform. This adds additional time and complexity to the pipeline, but is technically safer as a completed build won’t error out mid-deploy because the SQL project build validations check its compatibility with the target platform. This way, you catch platform-specific syntax issues before deployment.

You can do this by specifying a different DSP (Database Schema Provider, aka target platform) at build time:

- task: MSBuild@1
  inputs:
    solution: '**/*.sqlproj'
    msbuildArgs: '/p:DSP=Sql150DatabaseSchemaProvider' # For SQL Server 2019

You don’t need to specify a DSP property for the build step that matches the target platform in the SQL project file, this is only for temporarily overriding it at build time. All of the DSP values can be found in the SQL projects documentation - https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/target-platform?view=sql-server-ver16&pivots=sq1-command-line

A quick plug for other pipeline fundamentals

1. The pipeline has to have network access to the database. One benefit of the SqlAzureDacpacDeployment task is that it can temporarily add/remove a firewall rule for Azure SQL Database, but you may end up deploying through a self-hosted agent within your private network.

2. Archive (publish) your DACPAC files, since they’re build artifacts and you may want to retrieve them at a later date.