3

I have a couple of system_versioned tables and deploy the project via SSDT to an Azure SQL Managed Instance. For many of these system_versioned tables SSDT always recreates the publish code or shows these tables as differing in schema compare (see below image).

To me it looks like SSDT cannot update the history_retention_parameter. This has changed for some tables but not all tables for which this deployment behaviour occurs. The publish script for this table consists of the following steps:

  • dropping the system_versioning
  • print "ALTER TABLE XY"
  • do nothing
  • add system_versioning , see below excerpt:
GO
PRINT N'Dropping system-versioning from [adf].[sourceConfig]...';

GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [sys].[tables]
           WHERE  [name] = N'sourceConfig'
                  AND SCHEMA_NAME(schema_id) = N'adf'
                  AND temporal_type = 2)
    BEGIN
        ALTER TABLE [adf].[sourceConfig] SET (SYSTEM_VERSIONING = OFF);
    END

GO
PRINT N'Altering [adf].[sourceConfig]...';

GO
PRINT N'Adding system-versioning to [adf].[sourceConfig]...';

GO
ALTER TABLE [adf].[sourceConfig] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[adf].[sourceConfig_HISTORY], DATA_CONSISTENCY_CHECK=ON));

GO
PRINT N'Update complete.';

Schema Compare looks as follows (it appears as the history_retention_period-parameter is not recognized): However, if I check sys.tables

schema compare for a system_versioned table

In general the tables are defined as follows:

CREATE TABLE [ecc].[A0] AS
    [someColumnes] DATATYPE NOT NULL [...],
    [odsValidFrom] DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    [odsValidTo] DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PRIMARY KEY CLUSTERED ([hashKey] ASC),
    PERIOD FOR SYSTEM_TIME([odsValidFrom], [odsValidTo])
    )
    WITH (
            SYSTEM_VERSIONING = ON (
                HISTORY_TABLE = [ecc].[A0_HISTORY],
                DATA_CONSISTENCY_CHECK = ON,
                HISTORY_RETENTION_PERIOD = 7 DAY
                )
            );

Visual Studio: 2019 Enterprise 16.8.1

Dale K
  • 25,246
  • 15
  • 42
  • 71
tim
  • 86
  • 7
  • try to align your actual database properties with the project file. you can also check the publish profile properties, there could be some options to ignore retention or some kind of related properties. – Dmitrij Kultasev Dec 07 '20 at 11:56
  • I dont see anything in the publish profile relatated to system versioning or this exact property. I ignore ANSI Nulls, file and log file path, file size, filgroup placement, fill factor, full t ext catalog file path, index padding, keyword casing, login SIDs, object placement on partition schemes, quoted identitfiers, route lifetime, semicolon between statements and whitespace. In the general tab i also don't see a setting related to this – tim Dec 07 '20 at 15:23
  • as I said that you need to try to align database properties in the "Advanced Database Properties" or something like that. You can find this button when you double click on the publish profile. – Dmitrij Kultasev Dec 07 '20 at 16:38
  • Yes, i checked the Advanced settings and i don't see anything related to system_versioning or this specific property. – tim Dec 08 '20 at 12:38
  • I'm saying that you can try to align all database properties, not something `system_versioning` related only. Another thing you can try is to set the same retention period and make sure that the problem disappeared. – Dmitrij Kultasev Dec 08 '20 at 12:58
  • sadly this didnt work :( thanks for your efforts – tim Dec 09 '20 at 12:37
  • Hi Tim, did you find any solution on this? – CodeNewa Jan 04 '23 at 12:13

0 Answers0