If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

SSDT: /p:AllowIncompatiblePlatform, what platforms are compatible?

Sometimes you don’t have the perfect development environment for SQL Server, sometimes you deploy to things like SQL Azure and want to test locally, for various reasons it is possible that you want to deploy to one version of SQL Server but set the project properties to a different version of SQL Server. If you find yourself in this situation you might need to use the parameter AllowIncompatiblePlatform which essentially says “I know I said the project is for SQL 2017 but I am deploying to SQL 2014”, anytime you say this you also sign the contract that says “If I am deploying to a lower version then I have made sure I haven’t used any keywords or object types that didn’t exist in that version and also if everything fails then so be it, I live with my choices every day”.

This led me to think that if there are incompatible versions then perhaps there are also compatible versions, being a man with a simple mind that seems obvious to me.

First lets take a quick look at which platforms are in the DacFx, there is an internal enum called SqlPlatform and I can’t find any documentation about it so if we use a bit of reflector magic we can find these platforms:

namespace Microsoft.Data.Tools.Schema.SchemaModel
{
    using System;

    [Flags]
    internal enum SqlPlatforms
    {
...
        Sql100,
        Sql100AndUp,
        Sql100AndUpAndSqlAzureV12,
        Sql110,
        Sql110AndUp,
        Sql110AndUpAndSqlAzureV12,
        Sql120,
        Sql120AndUp,
        Sql120AndUpAndSqlAzureV12,
        Sql130,
        Sql130AndSqlAzureV12,
        Sql130AndUp,
        Sql130AndUpAndSqlAzureV12,
        Sql140,
        Sql140AndSqlAzureV12,
        Sql140AndUp,
        Sql140AndUpAndSqlAzureV12,
        Sql90,
        SqlAzure,
        SqlAzureV12
    }
}

This is interesting as we can probably guess that all source platforms are compatible with the target platform and new ones so you can deploy a dacpac that targets SQL 2012 to a database running on SQL 2017 - I don’t think any deprecated commands have ever actually been removed so maybe it is right.

So what platforms are compatible with which platforms?

Take a deep breath and remember this little ditty, if you would like to sing a song in your head while you read, go ahead there is no extra fee for frivolity:

  • A dacpac that targets Sql Azure can be deployed to a SQL 2008+ (Note this is not a typo) and either version of Azure Sql DB
  • A dacpac that targets Sql Azure V12 can be deployed to a SQL 2016+ instance so a localdb would be great (hint hint). It can also only deploy to a Azure v12 database.
  • A dacpac that targets a specific version such as 2012, 2014, 2016, 2017, 2019 can be deployed to itself or a higher version
  • If you don’t meet one of these rules then you can still deploy using AllowIncompatiblePlatform which is set to false by default.

Enjoy - I have used this a few times, particularly with Azure and localdb.