Как развернуть временные таблицы с помощью dacpac и SqlPackage.exe

Мы пытаемся работать с темпоральными таблицами в SQL Server 2016. Мы разрабатываем сценарии SQL в SSDT 15.1.6 в Visual Studio 2017, но у нас возникают проблемы при попытке развернуть dacpac, который создается во время сборки.

Наш dacpac развертывается с использованием SqlPackage.exe, и мы сталкиваемся с этой ошибкой при попытке развернуть dacpac:

Создание [dbo]. [TestHISTORY]. [Ix_TestHISTORY] ...
Произошла ошибка во время выполнения пакета.
Обновление базы данных (сбой)
Не удалось развернуть пакет.
Ошибка SQL72014: .Net Поставщик данных SqlClient:

Msg 1913, уровень 16, состояние 1, строка 1
Операция завершилась неудачно, поскольку индекс или статистика с именем «ix_TestHISTORY» уже существует в таблице «dbo.TestHistory».

Ошибка SQL72045: ошибка выполнения сценария. Выполняемый скрипт:
СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС [ix_TestHISTORY] НА [dbo]. [TestHistory] ([SysStart] ASC, [SysEnd] ASC);

Когда мы создаем темпоральную таблицу в SSDT, мы получаем следующее:

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))

Насколько я могу судить, проблема связана с созданием dacpac. После сборки проекта созданный дакпак выглядит так:

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

Я подозреваю, потому что мы используем темпоральная таблица с таблицей истории по умолчанию мы не можем заставить dacpac создавать эти дополнительные операторы создания. Поскольку это фактически заставляет SQL Server дважды пытаться создать эти элементы, что приводит к указанной выше ошибке.

Кто-нибудь знает, что нам может не хватать? Или, если вы развертываете временные таблицы с помощью dacpac, единственный вариант - использовать определяемые пользователем таблицы истории?


person larrydice    schedule 30.03.2018    source источник
comment
Не знаю достаточно, чтобы дать ответ, но моя первая мысль - поставить оператор If then, прежде чем проверить, существуют ли уже таблицы / индекс.   -  person Holmes IV    schedule 03.04.2018
comment
Ну, DacPac генерируется msbuild. Таким образом, мы могли бы вручную добавить оператор if в DacPac после его сборки, но мы хотели бы, чтобы все было автоматизировано для непрерывной интеграции.   -  person larrydice    schedule 03.04.2018
comment
Итак, в основном вы используете проект базы данных для определения схемы db ... почему бы не добавить к ней саму таблицу истории как таблицу, чтобы при создании dacpac она создавалась как определяемая пользователем таблица истории с созданным только индексом однажды? Я не вижу проблемы с CI, если вы подойдете к этому так?   -  person MK_    schedule 06.04.2018
comment
@MK_ Это вариант. И, похоже, на этом этапе мы этим и займемся. С другой стороны, я вижу, что всякий раз, когда нам нужно внести изменения в схему, мы должны сделать это в двух таблицах. Что, в конце концов, не имеет большого значения, просто кажется, что, возможно, я упускаю что-то, что спасло бы нас от этого.   -  person larrydice    schedule 06.04.2018
comment
Внесение изменений в один и копирование / вставка его в другую таблицу не похоже на такие большие накладные расходы, и похожие имена, делающие сценарии рядом друг с другом в вашем проводнике объектов, заставят вас не забыть сделать это. На данном этапе это, вероятно, лучший вариант, пока они не выпустят версию SSDT, в которой это исправлено.   -  person MK_    schedule 06.04.2018


Ответы (2)


У нас был ряд проблем между темпоральными таблицами и DACPAC. Несколько полезных советов:

  • Явное объявление таблиц истории - это намного больше, чем можно было бы подумать. При добавлении / удалении столбцов вы можете определить значение по умолчанию для таблиц истории, что позволит вам обойти ряд проблем, которые возникают, когда данные уже находятся в таблицах.
  • Добавьте значения по умолчанию ко ВСЕМ - это невозможно переоценить. Значения по умолчанию - лучший друг DACPAC.
  • Просмотрите сценарии. Приятно думать о DACFx как о свободном доступе, но это не так. Время от времени просматривайте сценарии, и вы получите много информации (похоже, вы уже это делаете!)
  • Явно назовите свои индексы - DACFx иногда использует временные имена для индексов / таблиц / другого материала. Последовательность - это король, правда?
  • Просмотрите ВСЕ параметры профиля публикации - Иногда в профиле есть настройки, о которых вы не задумывались. Нам потребовалось много ручного вмешательства, прежде чем мы поняли, что в профиле публикации есть настройка для транзакционных скриптов.

Также посмотрите, кто превращает ваш DACPAC в сценарий. VS использует SqlPackage.exe, но иногда я получаю разные результаты от DACFx DLL. Скорее всего, это разные вещи в конфигурации, но это сложно выяснить. Просто попробуйте оба варианта и посмотрите, работает ли один из них лучше.

Удачи! Надеюсь это поможет!

person Travis Manning    schedule 06.04.2018
comment
Как вы и @MK_ упомянули, мы остановились на пользовательских таблицах. Это работает и не добавляет слишком много накладных расходов, хотя было бы неплохо не поддерживать дублирующую схему. - person larrydice; 09.04.2018

Один из возможных хакерских приемов, который вы можете попробовать, - это сценарии перед развертыванием;

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

Они выполняются между «Генерацией сценария развертывания» и «Выполнением сценария развертывания». Поэтому, если вы не можете избежать столкновения с именем индекса, вы, вероятно, можете переименовать существующий индекс перед обновлением. Это взломано, и я предполагаю, что вы развертываете / обновляете схему живой БД, а не создаете новую БД.

Кстати, где имена столбцов «ValidFrom» и «ValidTo», найденные в сообщении об ошибке ?, если оно создается автоматически, оно должно быть «SysEnd» и «SysStart»

person shankar_pratap    schedule 04.04.2018
comment
Да, ValidFrom & ValidTo - это опечатка. Я скопировал и вставил из другой схемы. Я отредактировал вопрос, чтобы исправить это. - person larrydice; 06.04.2018