SQL Projects: add ability to skip deployment if current package has already been deployed

[ErikEJ]

Related to an existing integration?

Yes

Existing integration

CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects

Overview

Deployment can sometimes take some time during launch, and if the SQL database is on a persisted volume, may not be needed.

This feature is about adding an option to add a check if the current .dacpac has already been deployed, and in this case, skip deployment and exit.

Usage example

New "WithXxx" method, or an additional parameter on AddSqlProject/AddSqlPackage

"WithSkipOnDeployed" ?? (needs design 😄 )

Breaking change?

No

Alternatives

Use WithExplicitStart, but this requires manual intervention and knowledge about current state

Additional context

@jmezach @JerryNixon Thoughts and ideas about actual implementation (and naming!)

Thinking about getting a checksum from the file path + file size + last modified time stamp, then store this in a table or as extended properties in the user database with filename and checksum columns in the target database

Help us help you

Yes, I'd like to be assigned to work on this item

[jmezach]

@ErikEJ Dacpacs have a version number which is stored in the database automatically as part of the deployment, however I don't think there's a mechanism which increases the version number upon changes, or at least not as far as I know

[ErikEJ]

@jmezach Yes, but this advanced functionality is not on by default and as you say, no automatic version number increase.

RegisterDataTierApplication=(BOOLEAN 'False') | Specifies whether the schema is registered with the database server.

[ErikEJ]

https://dba.stackexchange.com/a/148192/9320

[ErikEJ]

@jmezach @aaronpowell @JerryNixon

Proposal

Add .WithSkipWhenDeployed()

Set a database level extended property, with a key of the .dacpac file name (not full path) (max 128 chars)

The value is the .dacpac file checksum.

On deploy, when this is activated:

1 - Calculate .dacpac file checksum and key (warn if .dacpac file name exceeds 128 chars)
2 - Check if extended property exists
3a - if not create it with filename and checksum and deploy
3b - if it exists, compare checksums, if different update extended property and deploy

[JerryNixon]

Pretty clever.

DECLARE @expected NVARCHAR(128) = 'ABC123';

DECLARE @isDeployed BIT =
(
    SELECT CASE 
             WHEN EXISTS (
                 SELECT 1
                 FROM fn_listextendedproperty(
                          'DACPAC_CHECKSUM',
                          DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
                      )
                 WHERE CAST(value AS NVARCHAR(128)) = @expected
             )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
           END
);

IF @isDeployed = 1
BEGIN
    PRINT 'DACPAC already deployed. Skipping.';
END
ELSE
BEGIN
    PRINT 'Deploying new DACPAC.';

    -- run your deployment logic here

    -- update extended property
    IF EXISTS (
        SELECT 1
        FROM fn_listextendedproperty(
                 'DACPAC_CHECKSUM',
                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
             )
    )
        EXEC sp_updateextendedproperty
            @name  = N'DACPAC_CHECKSUM',
            @value = @expected;
    ELSE
        EXEC sp_addextendedproperty
            @name  = N'DACPAC_CHECKSUM',
            @value = @expected;
END

[ErikEJ]

@JerryNixon Thanks for the snippets

[JerryNixon]

How might be tell if the current dacpac deployment remains unspoiled in the SQL volume? What I mean is that the dacpac checksum may not have changed while the database schema has DRAMATICALLY changed in some way.

[ErikEJ]

@JerryNixon Wonder how likely that is to happen?

[jmezach]

@JerryNixon I'm assuming here that you mean that the actual database schema of the target database has changed after the .dacpac has been initially deployed and now we're deploying the same .dacpac again with the exact same checksum? I guess in that scenario nothing will happen, since the checksums still match so nothing would get deployed.

I guess we could perhaps use sqlpackage's drift report generation to let the user figure out the changes in that case maybe?

[ErikEJ]

Ah, I get it. In that case, when this feature is on, nothing would happen. But breaking the rules of the .dacpac owning the schema can break a lot of stuff.

[ErikEJ]

@jmezach If we need to generate a drift report, we might as well just deploy, as we will then loose the benefit of quick startup.

[jmezach]

@ErikEJ Fair. I guess it could also be an opt-in or something. But perhaps we should first wait if it even becomes an issue for folks. Ideally one shouldn't mutate the state of the database from what the .dacpac has deployed anyway.