Руководство по синхронизации данных в Microsoft SQL Server

Совместное использование связанной информации между изолированными системами становится все более важным для организаций, поскольку позволяет им повышать качество и доступность данных. Во многих ситуациях полезно иметь доступный и согласованный набор данных более чем на одном сервере каталогов. Вот почему важно знать общие методы выполнения синхронизации данных SQL Server.

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

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

• Миграция базы данных
• Регулярная синхронизация между информационными системами
• Импорт данных из одной информационной системы в другую
• Перемещение наборов данных между различными этапами или средами
• Импорт данных из источника, не являющегося базой данных

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

Мы можем внедрить архитектуру для задач синхронизации данных вручную, возможно, с помощью Microsoft Sync Framework, либо воспользоваться уже созданными решениями в рамках инструментов для управления Microsoft SQL Server. Мы постараемся описать наиболее распространенные методы и инструменты, которые можно использовать для решения проблемы синхронизации данных в базах данных Microsoft SQL Server, и постараемся дать некоторые рекомендации.

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

Источник и назначение имеют очень похожие структуры

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

Источник и назначение имеют разные структуры

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

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

Независимо от того, насколько похожи структуры, мы можем выбрать четыре различных способа решения проблемы синхронизации данных:

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

Источник и назначение имеют одинаковую или очень похожую структуру

Использование сценариев SQL, созданных вручную

Самое простое и утомительное решение — вручную написать SQL-скрипты для синхронизации.

Преимущества

• Может выполняться бесплатными инструментами с открытым исходным кодом (FOSS).
• Если в таблице есть индексы, это очень быстро.
• Сценарий SQL можно сохранить в хранимой процедуре или периодически запускать как задание для SQL Server.
• Может использоваться как автоматический импорт даже для постоянно меняющихся данных.

Недостатки

• Создание такого сценария SQL довольно утомительно, поскольку для каждой таблицы обычно требуется три сценария: INSERT , UPDATE и DELETE .
• Вы можете синхронизировать только те данные, которые доступны через SQL-запросы, поэтому вы не можете импортировать их из таких источников, как файлы CSV и XML.
• Его сложно поддерживать — при изменении структуры базы данных необходимо модифицировать два-три скрипта ( INSERT , UPDATE , а иногда и DELETE ).

Пример

Мы выполним синхронизацию между таблицей Source со столбцами ID и Value и таблицей Target с теми же столбцами.

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

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Использование метода сравнения данных

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

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

Ниже приведены наиболее популярные инструменты, использующие подход сравнения данных:

• Сравнение данных dbForge для SQL Server
• Сравнение данных RedGate SQL
• Разница данных Apex SQL

На первом этапе считываются данные или просто считываются контрольные суммы больших данных из источника и из цели. Затем выполняется процесс сравнения.

Эти инструменты также предлагают дополнительные настройки для синхронизации.

Нам нужно настроить следующие параметры конфигурации, необходимые для синхронизации данных:

Ключ синхронизации

По умолчанию используется первичный ключ или ограничение UNIQUE . Если первичного ключа нет, можно выбрать комбинацию столбцов. Клавиша Sync используется для сопряжения строк источника со строками цели.

Сопряжение столов

По умолчанию таблицы объединяются в пары по имени. Вы можете изменить это и соединить их в соответствии с вашими потребностями. В программном обеспечении dbForge Data Compare вы можете выбрать SQL-запрос в качестве источника или назначения.

Процесс синхронизации

После подтверждения инструмент сравнивает исходные и целевые данные. Весь процесс состоит из загрузки всех исходных и целевых данных и их сравнения на основе заданных критериев. По умолчанию сравниваются значения из таблиц и столбцов с одинаковыми именами. Все инструменты поддерживают сопоставление имен столбцов и таблиц. Кроме того, есть возможность исключить столбцы IDENTITY (автоинкремент) или выполнить некоторые преобразования перед сравнением значений (округление типов с плавающей запятой, игнорирование регистра символов, обработка NULL как пустой строки и т. д.). Загрузка данных оптимизирована. Если объем данных большой, загружаются только контрольные суммы. Эта оптимизация полезна в большинстве случаев, но время, необходимое для выполнения операций, увеличивается с увеличением объема данных.

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

Преимущества

• Расширенные знания SQL не требуются и могут быть выполнены с помощью графического интерфейса.
• У вас есть возможность визуально проверить различия между базами данных перед синхронизацией.

Недостатки

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

Ниже вы можете увидеть типичный пользовательский интерфейс этих инструментов.

Синхронизировать с автоматически сгенерированным SQL

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

Синхронизация с помощью автоматически сгенерированного SQL обеспечивается только SQL Database Studio.

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

Преимущества

• Дополнительные знания SQL не требуются.
• Вы можете настроить все в графическом интерфейсе довольно быстро.
• Полученный сценарий SQL можно сохранить в хранимой процедуре.
• Может использоваться как автоматический импорт - как задание для SQL Server.

Недостатки

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

Тесты производительности

Прецедент

Две базы данных (A и B), каждая из которых содержит одну таблицу с 2 000 000 строк. Таблицы находятся в двух разных базах данных на одном сервере SQL. Этот тест охватывает два крайних случая: 1) Исходная таблица содержит все 2 000 000 строк, а целевая таблица пуста. Синхронизация должна обеспечить множество INSERTS . 2) Исходная и целевая таблицы содержат 2 000 000 строк. Разница только в одном ряду. Синхронизация должна предоставить только один UPDATE .

Для сравнения данных RedGate требуется 3 шага:

• Сравнивать
• Создать скрипт
• Запустить скрипт в целевой базе данных

Для ApexSQL Data Diff требуется 2 шага:

• Сравнивать
• Генерация скрипта и запуск скрипта за один шаг

SQL Database Studio выполняет всю синхронизацию за один шаг. Ниже указано время синхронизации в секундах. В столбце с пометкой «Отдельные шаги» указана продолжительность шагов синхронизации, перечисленных выше.

Ниже - лучше.

Тот же тест, но базы данных находятся на разных SQL-серверах, которые не связаны через связанный сервер.

Ниже - лучше.

Резюме

Из результатов видно, что RedGate и Apex не волнует, находятся ли базы данных на одном и том же сервере SQL, поскольку алгоритм синхронизации не зависит от SQL Server. SQL Database Studio использует собственные функции SQL Server; поэтому результат лучше, когда базы данных находятся на одном сервере.

Источник и место назначения имеют разную структуру

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

Этот пример состоит из одной широкой таблицы SourceData, которую необходимо синхронизировать с небольшими таблицами Continent , Country и City . Схема приведена ниже.

Данные в SourceData могут быть такими, как на изображении ниже.

Использование вручную созданных сценариев SQL

Скрипт синхронизации таблицы континентов

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Скрипт синхронизации таблицы городов

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

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

Сценарии UPDATE и DELETE также могут быть написаны таким же образом, если это необходимо.

Преимущества

• Вам не нужны никакие коммерческие продукты.
• Сценарий SQL можно сохранить в хранимой процедуре или периодически запускать как задание для SQL Server.

Недостатки

• Создать такой SQL-скрипт сложно и сложно (для каждой таблицы обычно необходимы три скрипта — INSERT , UPDATE и DELETE ).
• Это очень трудно поддерживать.

Использование внешних инструментов

Этот тип синхронизации (широкая таблица во множество связанных таблиц) не может быть выполнен с помощью метода сравнения данных, поскольку он ориентирован на разные варианты использования. Поскольку метод сравнения данных создает сценарий SQL с данными, которые необходимо вставить, он не имеет прямой возможности поиска ссылок в связанных таблицах. По этой причине нельзя использовать приложения, использующие этот метод (сравнивание данных dbForge для SQL Server, сравнение данных RedGate SQL, сравнение данных Apex SQL).

Однако SQL Database Studio может помочь вам автоматически создавать сценарии синхронизации. На рисунке ниже показан элемент под названием Editor for Data Synchronization в SQL Database Studio.

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

Столбцы

• Идентификатор — этот столбец не отображается, поскольку он является первичным ключом (создается автоматически).
• CountryId — этот столбец определяется как ссылка для таблицы.
• Имя — этот столбец заполняется из столбца Город в исходной таблице (широкая таблица).

В качестве ключей синхронизации выбраны столбцы CountryId и Name . Ключ синхронизации — это набор столбцов, которые однозначно идентифицируют строку в исходной и целевой таблицах. Вы не можете использовать Id первичного ключа в качестве ключа синхронизации, поскольку его нет в исходной таблице.

После синхронизации таблицы выглядят так:

В приведенном выше примере в качестве источника использовалась одна широкая таблица. Также распространен сценарий, когда исходные данные хранятся в нескольких связанных таблицах. Отношения в SQL Database Studio определяются не внешними ключами, а именами столбцов. Таким же образом можно импортировать из файлов CSV или Excel (файл загружается во временную таблицу, и синхронизация запускается из этой таблицы). Хорошей практикой является использование уникальных имен столбцов. Если это невозможно, вы можете определить псевдонимы для этих столбцов.

Преимущества

• Легко и быстро создать
• Простота обслуживания
• Может быть сохранен в хранимой процедуре (хранимая процедура сохраняется с данными, необходимыми для последующего открытия синхронизации в редакторе)

Недостатки

• Коммерческое решение

Сравнение решений

Синхронизация данных состоит из последовательности команд INSERT , UPDATE или DELETE . Существует несколько способов создания последовательностей этих команд. В этой статье мы рассмотрели три варианта создания SQL-скриптов синхронизации. Первый вариант — создать все вручную. Это осуществимо (но занимает слишком много времени), требует сложного понимания SQL, и его сложно создавать и поддерживать. Второй вариант — использовать коммерческие инструменты. Мы рассмотрели следующие инструменты:

• Сравнение данных dbForge для SQL Server
• Сравнение данных RedGate SQL
• Разница данных Apex SQL
• Студия базы данных SQL

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

• Базы данных не синхронизированы из-за различных ошибок.
• Вам нужно избегать репликации при передаче данных между средами.
• Необходимы отчеты о сравнении данных в Excel или HTML.

Каждый инструмент любим по той или иной причине: у dbForge отличный пользовательский интерфейс и множество опций, ApexSQL работает лучше остальных, а RedGate — самый популярный.

Четвертый инструмент, SQL Database Studio, работает немного иначе. Он генерирует сценарии SQL, содержащие логику синхронизации, а не изменения. Производительность также высока, поскольку вся работа выполняется непосредственно на сервере базы данных, поэтому передача данных между сервером базы данных и инструментом синхронизации не требуется. Этот инструмент полезен для следующих случаев использования:

• Автоматическая миграция баз данных, когда базы данных имеют другую структуру
• Импорт в несколько связанных таблиц
• Импорт из внешних источников XML, CSV, MS Excel