Версионирование схемы базы данных при помощи SQL Server Data Tools (SSDT)

Большинство команд в своих проектах давно научились использовать системы контроля версий для хранения истории изменения кода. Но многие до сих пор не могут четко ответить на вопрос, что в этом случае делать с базой данных. Здесь я расскажу а том, как использовать SSDT для решения этого вопроса.

За время своей работы мне приходилось сталкиваться с различными подходами к версионированию схемы базы данных. Начиная от того, что схема никак не версионировалась, заканчивая фреймворками по миграции данных (DotNetMigrations, EF Migrations). В итоге мы остановились на использовании SQL Server Data Tools.

Давайте разберемся как работают фреймворки миграции. В них всё предельно просто - каждый раз когда меняется схема данных, мы создаем новую миграцию (инкремент схемы данных), в которой указываем что конкретно должно измениться в схеме данных. Например, это может быть создание новой таблицы или изменение существующей. DotNetMigration работают на уровне SQL-скриптов, каждый новый инкремент - новый скрипт. EF Migrations позволяют работать с этим в виде объектов, но идея также. В качестве бонуса при создании миграции можно управлять данными (например, заполнить новую таблицу тестовыми/начальными данными). Для того, чтобы воссоздать схему базы данных, вам нужно последовательно запустить все миграции одну за другой. При этом в системе контроля версий у вас хранится несколько файлов (для каждой миграции) и они неизменны.

В альтернативу этому подходу, SSDT предлагает хранить в системе контроля версий текущую схему базы данных, а все инкременты доступны в виде истории комитов. По сути, SSDT позволяет создавать database-проекты и публиковать их на нужном сервере. Давайте более подробно рассмотрим процесс использования SSDT.

Установка

Для работы с SSDT на компьютер следует установить соответствующий пакет, который можно загрузить с официального сайта. После установки этого пакета, в шаблонах проекта появится новый тип проектов - "SQL Server Database Project".

Создание схемы базы данных

Проект готов и мы можем приступить к созданию схемы БД. Для этого добавим в проект новые таблицы.

Для таблиц существует встроенный редактор, который на лету генерирует SQL-скрипт для создания таблицы (который, впрочем, можно редактировать и руками).

Здесь же можно построить индексы, добавить триггеры, а также связать таблицу с другими таблицами.

В SSDT вы можете работать практически со всеми необходимыми артефактами базы данных Microsoft SQL Server - таблицы, индексы, хранимые процедуры и функции, пользователи и т.д.

Публикация

После того, как проект готов, мы можем опубликовать его на реальном сервере. Для этого кликаем правой кнопкой по проекту и выбираем опцию "Publish".

При публикации SSDT сравнивает схему целевой базы данных и схему нашего проекта и строит инкремент - скрипт, который адаптирует схему данных целевой базы данных в соответствии со схемой нашего проекта (грубо говоря, набор ALTER TABLE и пр.). Логично, что данные при этом на целевом сервере никуда не исчезают, если это не противоречит самой схеме.

Если есть риск потерять данные (например, удалилась колонка, или вы добавляете колонку с не-NULL значением, но строки в этой таблице уже есть), то при публикации SSDT поругается и схема БД развернута не будет. В этом случае нужно что-то предпринимать. В идеале, таких ситуаций лучше избегать. Например, если вы добавляете новую не-NULL колонку к существующей таблице, то указать для неё значение по умолчанию. В некоторых случаях достаточно разрешить SSDT удалить какие-то данные ради успеха общего дела (AdvancedBlock incremental deployment if data loss may occur).

После успешной публикации, обновленная схема БД появится на целевом сервере.

В процессе публикации SSDT также создает инкремент-скрпит (.sql). Некоторые коллеги также сохраняют и его как артефакт процесса сборки.

В нашем случае проект SSDT входит в общий состав Solution'а и собирается вместе с остальными проектами на билд-сервере. Также как и запускается публикация на SQL-сервер в процессе развертывания. Важно, что если у вас есть какие-то ошибки или неточности, например, в описании ваших таблиц, то SSDT-проект не соберется и сборка остановится. Это очень помогает.

Рефакторинг

Схема базы данных - это часть проекта, которая развивается вместе с ним. Появляются новые таблицы, связи, индексы, ключи. И если с добавлением новых сущностей в проект все понятно (они просто появятся в схеме при развертывании), то что делать с изменениями уже существующих сущностей?

Допустим у нас есть таблица и мы хотим добавить в нее новое поле. В этом случае мы просто делаем это и при развертывании мы получим обновленную таблицу.

Сложнее дела обстоят с другими изменениями. Например, если вам нужно переименовать колонку, то вам наверняка захочется, чтобы данные в целевой базе данных сохранились. Для этого нужны рефакторинги в SSDT. Давайте посмотрим на примере. Заполню данными таблицу из предыдущего примера.

После чего в проекте SSDT я изменю название одной из колонок.

Публикую обновленный проект на сервер.

Схема изменилась, данные сохранились.

Происходит это за счет того, что при каждом таком изменении, SSDT хранит лог рефакторинга. Это - файл .refactorlog, который хранится в проекте в формате XML.

В самой базе данных эта информация тоже сохраняется.

Таким образом, SSDT может гарантировать, что ваши данные не потеряются.

Сам по себе инструмент рефакторинга - это мощный инструмент. Однако, набор рефакторингов пока крайне ограничен. Например, вы не можете удалить колонку из одной таблицы, поместить её в дургую и при этом сохранить данные. Полный список рефакторингов вы можете увидеть по этой ссылке.

Итого

В итоге мы получаем инструмент со своими плюсами и минусы, которые в заключение я хочу перечислить.

Плюсы:

  • Понятное управление схемой базы данных.
  • Хранение истории изменений в естественном виде (история комитов в системе контроля версий).
  • Максимальная интеграция с SQL Server.
  • Автоматическое построение инкремента для обновления базы данных.
  • Прозрачная интеграция в процесс сборки.

Минусы:

  • Невозможность использования с иными СУБД, кроме SQL Server (для нас это не существенное ограничение).
  • Трудности при сложных рефакторингах.
  • Требуются дополнительные усилия, когда необходимо наполнить таблицы начальными данными.