How to deploy temporal tables with dacpac and SqlPackage.exe

Question

We are trying to work with temporal tables in SQL Server 2016. We are developing the SQL scripts in SSDT 15.1.6 in Visual Studio 2017, but we are experiencing issues when trying to deploy the dacpac that is generated during the build.

Our dacpac is deployed using SqlPackage.exe, and we encounter this error when attempting to deploy the dacpac:

Creating [dbo].[TestHISTORY].[ix_TestHISTORY]...
An error occurred while the batch was being executed.
Updating database (Failed)
Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider:

Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'ix_TestHISTORY' already exists on table 'dbo.TestHistory'.

Error SQL72045: Script execution error. The executed script:
CREATE CLUSTERED INDEX [ix_TestHISTORY] ON [dbo].[TestHistory]([SysStart] ASC, [SysEnd] ASC);

When we create the temporal table in SSDT we have the following:

CREATE TABLE [dbo].[Test]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[TestHISTORY], DATA_CONSISTENCY_CHECK=ON))

As far as I can tell the issue is with the dacpac creation. After the project is built, the dacpac created looks like this:

CREATE TABLE [dbo].[test]  
(
    [Id]       INT NOT NULL PRIMARY KEY CLUSTERED ([Id] ASC),
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd]   DATETIME2 (7) GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[testHISTORY], DATA_CONSISTENCY_CHECK=ON));
GO

CREATE TABLE [dbo].[testHISTORY] 
(
    [Id]       INT           NOT NULL,
    [SysStart] DATETIME2 (7) NOT NULL,
    [SysEnd]   DATETIME2 (7) NOT NULL
);
GO

CREATE CLUSTERED INDEX [ix_testHISTORY]
    ON [dbo].[testHISTORY]([SysEnd] ASC, [SysStart] ASC);
GO

I suspect because we are using a temporal table with a default history table we can't have the dacpac create those extra creation statements. Since this is effectively causing SQL Server to try to create those items twice, leading to the above error.

Does anyone know what we might be missing? Or if you are deploying temporal tables using a dacpac, is your only option to use user-defined history tables?

Answer 1

We've had a number of issues between temporal tables and DACPAC's. A few tips that will go a long way:

• Explicitly declare history tables - This goes way further than one would think. When adding/removing columns, you can define a default on history tables, allowing you to bypass a number of issues that arise when data is already in the tables.
• Add defaults to EVERYTHING - This cannot be overstated. Defaults are the best friend of a DACPAC.
• Review the scripts - It's nice to think of DACFx as hands off, but it's not. Review the scripts once in a while, and you'll gain a ton of insight (it appears you already are!)
• Explicitly name your indices - DACFx sometimes uses temporary names for indices/tables/other stuff. Consistency is king, right?
• Review ALL publish profile options - Sometimes, there are settings you didn't think of in the profile. It took us a lot of manual intervention before we realized there was a setting for transactional scripts in the publish profile.

Also look into who is turning your DACPAC into a script. VS uses SqlPackage.exe, but I sometimes get different results from the DACFx DLLs. It's likely a config thing that's different between the two, but it's tough to find out. Just try both, and see if one works better.

Best of luck! Hope this helps!