Рекомендации по изменению рабочих процессов в базе данных SQL Server

Question

Фон

В моей группе 4 базы данных SQL Server:

• производство
• UAT
• Тестовое задание
• Dev

Я работаю в среде Dev. Когда приходит время продвигать объекты, над которыми я работал (таблицы, представления, функции, хранимые процедуры), я делаю запрос моего менеджера, который продвигает в Test. После тестирования она отправляет запрос администратору, который продвигает UAT. После успешного пользовательского тестирования тот же администратор продвигает в производство.

Эта проблема

Весь процесс неудобен по нескольким причинам.

1. Каждый человек должен вручную отслеживать свои изменения. Если я обновляю, добавляю, удаляю любые объекты, которые мне нужны, чтобы отслеживать их, чтобы мой запрос на продвижение содержал все, что я сделал. Теоретически, если я что-то пропускаю, тестирование или UAT должны его поймать, но это не совсем точно, и в любом случае это пустая трата времени тестера.
2. Множество изменений, которые я делаю, являются итеративными и выполняются в графическом интерфейсе, что означает, что нет записи о том, что я сделал, только конечный результат (по крайней мере, насколько я знаю).
3. Мы находимся на довольно ранних этапах построения витрины данных, поэтому большинство внесенных изменений, по крайней мере, в подсчете, незначительны: изменение типа данных для столбца, изменение имен таблиц по мере кристаллизации они будут использоваться для настройки функций, сохраненных процедур и т. д.

Вопрос

Люди занимались такой работой десятилетиями, поэтому я думаю, что должен быть намного лучший способ управлять процессом. Что бы мне понравилось, если бы я мог запустить diff между двумя базами данных, чтобы увидеть, как структура отличается, использовать этот diff для генерации сценария изменений, использовать этот сценарий изменений в качестве запроса на продвижение. Это возможно? Если нет, есть ли другие способы организовать этот процесс?

Напомним, что мы на 100% работаем в Microsoft, только что обновив все до SQL Server 2008, поэтому любые инструменты, доступные в этом пакете, будут честной игрой.

---

Я должен уточнить, я не обязательно ищу инструменты сравнения. Если это лучший способ для синхронизации наших сред, то это хорошо, но если есть лучший способ, я ищу это.

Примером того, что я действительно хочу, являются миграции в Ruby on Rails. Простой синтаксис, все изменения хорошо документированы автоматически, и по умолчанию определить, какие миграции необходимо выполнить, почти тривиально легко. Я хотел бы, чтобы было что-то подобное для SQL Server.

Мое идеальное решение: 1) легко и 2) трудно испортить. Рельсы Миграции оба; все, что я до сих пор делал на SQL Server, не является ни тем, ни другим.

Answer 1

Контроль версий и ваша база данных

Корень всего зла вносит изменения в пользовательский интерфейс. SSMS - это инструмент DBA, а не разработчик. Разработчики должны использовать сценарии для внесения любых изменений в модель / схему базы данных. Управление версиями ваших метаданных и обновление сценария с каждой версии N до версии N+1 - это единственный надежный способ работы. Это решение, которое развертывает сам SQL Server, чтобы отслеживать изменения метаданных (изменения базы данных ресурса).

Инструменты сравнения, такие как SQL Compare или файлы vsdbcmd и.dbschema из проектов VS Database, являются лишь последними прибежищами для магазинов, которые не могут сделать правильный версионный подход. Они работают в простых сценариях, но я вижу, что все они проваливаются в серьезных развертываниях. Просто нельзя доверять инструменту для внесения изменений в таблицу +5 ТБ, если инструмент пытается скопировать данные...

Answer 2

В нашей команде мы обрабатываем изменения базы данных следующим образом:

• Мы (повторно) создаем сценарий, который создает полную базу данных и проверяет ее на управление версиями вместе с другими изменениями. У нас есть 4 файла: таблицы, пользовательские функции и представления, хранимые процедуры и разрешения. Это полностью автоматизировано - для создания сценария требуется только двойной щелчок.
• Если разработчик должен внести изменения в базу данных, он делает это на своей локальной базе данных.
• Для каждого изменения мы создаем сценарии обновления. Их легко создать: разработчик восстанавливает сценарий БД своего локального БД. Все изменения теперь легко идентифицировать благодаря контролю версий. Большинство изменений (новые таблицы, новые представления и т. Д.) Можно просто скопировать в сценарий обновления, другие изменения (например, добавление столбцов) необходимо создать вручную.
• Сценарий обновления тестируется либо в нашей общей базе данных разработчиков, либо путем отката локальной базы данных до последней резервной копии, которая была создана перед началом изменения базы данных. Если это пройдет, пришло время зафиксировать изменения.
• Сценарии обновления следуют соглашению об именах, поэтому все знают, в каком порядке их выполнять.

Это работает довольно хорошо для нас, но все еще нуждается в некоторой координации, если несколько разработчиков сильно изменяют одни и те же таблицы и представления. Это случается не часто, хотя.

Важными моментами являются:

• Структура базы данных модифицируется только скриптами, за исключением базы данных локального разработчика. Это важно.
• Сценарии SQL управляются версиями с помощью системы контроля версий - базу данных можно создать, как это было в любой момент в прошлом.
• резервные копии базы данных создаются регулярно - по крайней мере, до внесения изменений в базу данных
• Изменения в БД могут быть сделаны быстро - потому что сценарии для этих изменений создаются относительно легко.

Однако, если у вас есть много веток разработки для ваших проектов, это может не сработать.

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

В отличие от рельсовых миграций, мы не создаем сценарии для отмены изменений обновления. Но это не всегда возможно, по крайней мере, в отношении данных (содержимое отброшенного столбца теряется, даже если вы воссоздаете столбец).

Answer 3

Есть несколько инструментов, доступных для вас. Один из Red-Gate называется SQL Compare. Потрясающе и очень рекомендуется. SQL Compare позволит вам различать схемы между двумя базами данных и даже создавать сценарии изменения SQL для вас.

Обратите внимание, что они уже некоторое время работают над продуктом управления версиями SQL Server.

Другой (если вы магазин Visual Studio) - это функции сравнения схем и данных, которые являются частью Visual Studio (не знаю, какие версии).

Answer 4

Согласитесь, что SQL Compare - удивительный инструмент.

Однако мы не вносим никаких изменений в структуру базы данных или объекты, которые не являются сценариями и не сохраняются в системе контроля версий, как и весь другой код. Тогда вы точно знаете, что принадлежит той версии, которую вы продвигаете, потому что у вас есть сценарии для этой конкретной версии.

В любом случае, вносить структурные изменения через графический интерфейс - плохая идея. Если у вас много данных, это намного медленнее, чем использовать alter table, по крайней мере, в SQL Server. Вы хотите использовать только проверенные скрипты, чтобы вносить изменения и в продукт.

Answer 5

RedGate продает SQL Compare, отличный инструмент для создания сценариев изменений.

Visual Studio также имеет выпуски, которые поддерживают сравнение баз данных. Это раньше называлось Database Edition.

Там, где я работаю, мы давно отменили разделение Dev/Test/UAT/Prod в пользу очень быстрого цикла выпуска. Если мы поместим что-то сломанное в производство, мы быстро это исправим. Наши клиенты, конечно, счастливее, но в корпоративном предприятии, предотвращающем риск, это может быть трудно продать.

Answer 6

Я согласен с комментариями, сделанными marapet, где каждое изменение должно быть написано в сценарии.
Однако проблема, с которой вы можете столкнуться, заключается в создании, тестировании и отслеживании этих сценариев.
Посмотрите на механизм исправлений, используемый в DBSourceTools.
http://dbsourcetools.codeplex.com/

Он был специально разработан, чтобы помочь разработчикам получить базы данных SQL-сервера под контролем исходного кода.

Этот инструмент позволит вам создать базу данных в определенной точке и создать именованную версию (v1).
Затем создайте цель развертывания и увеличьте указанную версию до версии v2.
Добавьте сценарии исправления в каталог исправлений для любых изменений в схеме или данных.
Наконец, проверьте базу данных и все исправления в контроле исходного кода, чтобы распространять их вместе с разработчиками.

Это дает вам повторяющийся процесс тестирования всех исправлений, которые будут применены от v1 до v2.
DBSourceTools также имеет функциональные возможности, помогающие вам создавать эти сценарии, то есть инструменты сравнения схем или данных сценариев.

Как только вы закончите, просто отправьте все файлы из каталога патчей вашему администратору баз данных для обновления с версии v1 до версии v2.

Повеселись.

Answer 7

Еще один инструмент Diff для баз данных:

http://www.xsqlsoftware.com/Product/Sql_Data_Compare.aspx

Answer 8

Вот рабочий процесс, который мы успешно использовали:

• Экземпляр разработки: объекты SQL создаются / обновляются / удаляются в БД с помощью MSSQL Studio, и все операции сохраняются в сценариях, которые мы включаем в каждую версию нашего кода.
• Переход к производству: мы сравниваем схему между dev и prod db с помощью SQL Schema Compare в Microsoft Visual Studio. Обновляем prod с помощью того же инструмента.

Answer 9

1. Хранить версию базы данных в таблице версий
2. Сохранить имя файла сценария, который был успешно применен
3. Сохраняйте сумму md5 для каждого примененного сценария sql. При вычислении суммы md5 он должен игнорировать пробелы. Должно быть эффективным.
4. Хранить информацию о том, кто применил сценарий Хранить информацию о том, когда был применен сценарий
5. База данных должна быть проверена при запуске приложения
6. Новый скрипт SQL должен применяться автоматически
7. Если md5 сумма уже примененного скрипта изменяется, должна появиться ошибка (в рабочем режиме)
8. Когда скрипт выпущен, его нельзя менять. Он должен быть неизменным в производственной среде.
9. Скрипт должен быть написан таким образом, чтобы его можно было применять к различным типам баз данных (см. Liquibase)
10. Поскольку большинство операторов ddl автоматически фиксируются в большинстве баз данных, лучше всего иметь один оператор ddl для каждого сценария SQL.
11. Оператор DDL sql должен выполняться таким образом, чтобы его можно было выполнить несколько раз без ошибок. Действительно помогает в режиме разработки, когда вы можете редактировать скрипт несколько раз. Например, создайте новую таблицу, только если она не существует, или даже удалите таблицу перед созданием новой. Это поможет вам в режиме разработки, со скриптом, который не был выпущен, измените его, очистите сумму md5 для этого скрипта, запустите его снова.
12. Каждый сценарий sql должен быть запущен в отдельной транзакции.
13. Триггеры / процедуры должны быть удалены и созданы после каждого обновления БД.
14. Скрипт Sql хранится в системе управления версиями, такой как SVN
15. Имя скрипта содержит дату, когда он был зафиксирован, идентификатор существующей (jira) проблемы, небольшое описание
16. Избегайте добавления функциональности отката в скриптах (liquibase позволяет это делать). Это усложняет их написание и поддержку. Если вы используете ровно один оператор ddl для каждого сценария, а операторы dml выполняются внутри транзакции, даже неудачный сценарий не будет большой проблемой для его решения.