12

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?

larrydice
  • 321
  • 2
  • 9
  • Don't know enough to put an answer, but my first thought is put an If then statement before to check if the tables/index already exist. – Holmes IV Apr 02 '18 at 22:53
  • Well the DacPac is generated by msbuild. So we could manually add the if statement to the DacPac after it is built, but we'd like everything to be automated for continuous integration. – larrydice Apr 03 '18 at 15:11
  • So basically you're using database project to define the db schema... why not add the history table itself as a table to it so that when the dacpac is built it is created as a user-defined history table with index being created only once? I don't see a CI problem if you approach it like that? – MK_ Apr 05 '18 at 21:18
  • @MK_ That is an option. And it seems at this point it is what we will end up doing. The down side I see is anytime we need to make schema changes we have to make it to two tables now. Which at the end of the day isn't a huge deal, it just feels like maybe I am missing something that would save us from doing that. – larrydice Apr 06 '18 at 12:15
  • Making changes to one and copy/paste it to another table doesn't seem like so much of an overhead and the similar names making the scripts being next to one another in your object explorer will make you remember to do that. At this stage that's probably the best option you have until they release a version of SSDT in which this is fixed. – MK_ Apr 06 '18 at 12:56

2 Answers2

14

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!

Travis Manning
  • 320
  • 1
  • 12
  • As you and @MK_ mentioned we ended up going with the user-defined tables. It works and doesn't add too much overhead, though it would have been nice to not have to maintain a duplicate schema. – larrydice Apr 09 '18 at 12:51
0

One potential hacky work around you can try is pre-deployment scripts;

https://msdn.microsoft.com/en-us/library/jj889461(v=vs.103).aspx

They are executed between 'Generation of deployment script' & 'Execution of the deployment script'. So if you can't avoid collision on index name, you can probably rename existing index before upgrade, This is hacky and i am assuming you are deploying/updating schema of a live DB and not creating a new DB

BTW, Where are the column names 'ValidFrom' & 'ValidTo' found in error message coming from?, If it is auto generated then it should be 'SysEnd' & 'SysStart'

shankar_pratap
  • 943
  • 7
  • 7
  • Yes, the ValidFrom & ValidTo is a typo. I copied and pasted from a different schema. I have edited the question to correct this. – larrydice Apr 06 '18 at 12:16