Как сравнить и синхронизировать две базы данных в Microsoft SQL Server?

Приветствую Вас на сайте Info-Comp.ru! В этом материале я расскажу, как сравнить две базы данных в Microsoft SQL Server с возможностью дальнейшей их синхронизации, при этом я покажу простой способ, который не требует написания сложных инструкций на T-SQL.

Скриншот 1

Зачем сравнивать и синхронизировать базы данных?

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

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

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

Сравнение и синхронизация баз данных в Microsoft SQL Server

Для того чтобы сравнить и синхронизировать базы данных в Microsoft SQL Server, необходимо написать определенные инструкции на языке T-SQL, что может вызвать проблемы, так как многие просто не знают в достаточной степени T-SQL. Даже программистам, которые способны это сделать, будет достаточно сложно это реализовать за счет трудоемкости такой задачи. А если такая задача одноразовая, то вообще тратить столько усилий на сравнение может показаться даже бессмысленным.

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

Программы для сравнения и синхронизация баз данных в Microsoft SQL Server

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

Заметка! Обзор инструментов для работы с Microsoft SQL Server.

У компании Devart есть следующие программы для сравнения и синхронизация баз данных в Microsoft SQL Server:

  • dbForge Data Compare for SQL Server – это инструмент сравнения и синхронизации данных в базах данных Microsoft SQL Server;
  • dbForge Schema Compare for SQL Server – это инструмент сравнения и синхронизации схем баз данных Microsoft SQL Server;
  • dbForge Compare Bundle for SQL Server – это пакет инструментов, который содержит две вышеперечисленные программы для комплексного сравнения баз данных, при этом покупка этого пакета обойдётся дешевле.

Примечание! Если кому интересно, то у Devart есть инструменты и для других СУБД, Oracle, MySQL, PostgreSQL.

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

Сегодня в качестве примера я расскажу, как сравнить данные двух баз данных Microsoft SQL Server, использовать я буду программу dbForge Compare Bundle for SQL Server, так как она включает, как я уже отметил, и Data Compare, и Schema Compare, лично мне оба инструмента будут полезны, поэтому я буду скачивать и устанавливать именно пакет программ Compare Bundle for SQL Server. Кроме всего прочего в этот пакет включены еще и плагины для SQL Server Management Studio, что добавляет еще один плюс к этому пакету.

Исходные данные для примера

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

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

   
   --Создание базы данных 1
   CREATE DATABASE TestDB1;
   GO
   USE TestDB1;
   GO
   --Создание таблицы Goods 
   CREATE TABLE Goods (
        ProductId       INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY,
        Category        INT NOT NULL DEFAULT (1),
        ProductName     VARCHAR(100) NOT NULL,
        Price           MONEY NULL,
   );
   GO
   --Создание таблицы Categories
   CREATE TABLE Categories (
        CategoryId   INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CategoryId PRIMARY KEY,
        CategoryName VARCHAR(100) NOT NULL
   );
   GO
   --Добавление ограничения внешнего ключа (FOREIGN KEY)
   ALTER TABLE Goods ADD CONSTRAINT FK_Category 
   FOREIGN KEY (Category) 
        REFERENCES Categories (CategoryId)
        ON DELETE SET DEFAULT
        ON UPDATE NO ACTION;
   GO
   --Добавление строк в таблицу Categories
   INSERT INTO Categories(CategoryName)
        VALUES ('Комплектующие ПК'),
                   ('Мобильные устройства'),
                   ('Бытовая техника');
   GO
   --Добавление строк в таблицу Goods
   INSERT INTO Goods(Category, ProductName, Price)
        VALUES (1, 'Системный блок', 300),
                   (1, 'Монитор', 200),
                   (2, 'Смартфон', 100);
   GO
   -------------------------------------------------------------------------
   --Создание базы данных 2
   CREATE DATABASE TestDB2;
   GO
   USE TestDB2;
   GO
   --Создание таблицы Goods
   CREATE TABLE Goods (
        ProductId       INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY,
        Category        INT NOT NULL DEFAULT (1),
        ProductName     VARCHAR(100) NOT NULL,
        Price           MONEY NULL,
   );
   GO
   --Создание таблицы Categories
   CREATE TABLE Categories (
        CategoryId   INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CategoryId PRIMARY KEY,
        CategoryName VARCHAR(100) NOT NULL
   );
   GO
   --Добавление ограничения внешнего ключа (FOREIGN KEY)
   ALTER TABLE Goods ADD CONSTRAINT FK_Category 
   FOREIGN KEY (Category) 
        REFERENCES Categories (CategoryId)
        ON DELETE SET DEFAULT
        ON UPDATE NO ACTION;
   GO
   --Добавление строк в таблицу Categories
   INSERT INTO Categories(CategoryName)
        VALUES ('Комплектующие ПК'),
                   ('Мобильные устройства');
   GO
   --Добавление строк в таблицу Goods
   INSERT INTO Goods(Category, ProductName, Price)
        VALUES (1, 'Системный блок', 300),
                   (1, 'Монитор', 250);
   GO
   --Выборка данных
   SELECT * FROM TestDB1.dbo.Goods;
   SELECT * FROM TestDB2.dbo.Goods;

Скриншот 2

Как видите, данные в таблицах Goods отличаются двумя строками, одной строки просто нет во второй базе данных, а второе отличие — это то, что в столбце Price отличается значение (в одной базе 200, в другой 250). Кроме того, таблица Categories также отличается одной строкой.

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

Установка dbForge Compare Bundle for SQL Server

Сначала давайте скачаем и установим эту программу, хотя процесс установки Compare Bundle для SQL Server не сложный.

Шаг 1 – Скачивание дистрибутива программы

Скачать программу можно на официальном сайте, на странице загрузки, вот она — https://www.devart.com/dbforge/sql/compare-bundle/download.html

Я выбираю версию Professional, и нажимаю «Скачать».

В итоге загрузится файл размером около 70 мегабайт.

Шаг 2 – Установка Compare Bundle for SQL Server на Windows 10

Установку я буду производить на операционную систему Windows 10.

Запускаем скаченный файл, и в первом окне нажимаем кнопку «Install».

Примечание! Так как пакет инструментов dbForge Compare Bundle for SQL Server включает расширения для SSMS, среду SQL Server Management Studio во время установки необходимо закрыть.

Скриншот 3

Шаг 3 – Выбор каталога для установки

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

Скриншот 4

Шаг 4 – Выбор компонентов

Здесь оставляем по умолчанию, так как нам предлагают выбрать программы, которые необходимо установить, нам нужны все, поэтому жмем «NEXT».

Скриншот 5

Шаг 5 – Завершение установки

Установка будет завершена, когда появится соответствующее сообщение. Нажимаем «Finish».

Скриншот 6

Сравнение и синхронизация данных с помощью dbForge Data Compare for SQL Server

Для того чтобы начать сравнение данных, запускаем программу dbForge Data Compare for SQL Server, она умеет сравнивать: таблицы, представления, резервные копии БД и даже произвольные запросы.

Запустить программу можно из меню «Пуск». Также в обозревателе объектов SSMS в контекстном меню баз данных появятся новые пункты, с помощью которых можно запустить расширения для работы с этими программами в среде SQL Server Management Studio.

Примечание!

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

Настройка подключения к Microsoft SQL Server

Сначала давайте настроим подключение к Microsoft SQL Server, для этого в меню «Database» нажмите «New Connection…».

Затем в открывшемся окне настраиваем подключение, в моем случае все достаточно просто, я выбираю локальный сервер, и указываю проверку подлинности Windows. Нажимаем «OK» (предварительно, для проверки подключения, можете нажать Test Connection).

Скриншот 7

Кстати, программа dbForge Data Compare for SQL Server поддерживает практически все версии SQL Server, начиная с 2000. Здесь я использовал Microsoft SQL Server 2017 в редакции Express.

Сравнение баз данных

Чтобы запустить сравнение данных, нажимаем кнопку «New Data Comparison…».

Далее указываем базу данных источник (Source) и целевую базу данных (Target), которые мы будем сравнивать.

  • База данных источник (Source)  — это основная база, с которой будет производиться сравнение;
  • Целевая база данных (Target) — это база, которую нужно сравнить и синхронизировать с базой данных источника, т.е. сделать такой же, как Source.

База данных источник настраивается слева, целевая база данных настраивается справа. Для настройки необходимо указать:

  • Type – что мы сравниваем, по умолчанию базы данных;
  • Connection – указываем подключение к серверу, так как базы данных могут находиться на разных серверах;
  • Database – выбираем нужную базу данных.

У меня база источник будет TestDB1, а целевая база данных будет TestDB2.

Нажимаем «Next».

Скриншот 8

Далее мы можем указать необходимые опции для сравнения, нажимаем «Next».

Скриншот 9

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

Нажимаем «Compare».

Скриншот 10

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

В итоге на экране отобразится результат сравнения.

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

Скриншот 11

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

Там 4 вкладки.

Only in Source – эти данные есть только в источнике, в целевой БД их нет. В моем случае, если помните, в таблице Goods базы TestDB1 на одну строку больше. В результате мы видим на этой вкладке одну эту строку.

Скриншот 12

Different Records – это строки, которые отличаются. У нас отличалось одно значение в столбце Price, поэтому на этой вкладке отображена эта строка, причем показано значение, как в таблице источника, так и в целевой таблице, что очень удобно.

Скриншот 13

Only in Target – эти данные есть только в целевой таблице, т.е. в источнике их нет. У меня там пусто, так как такой сценарий я не воспроизводил.

Identical Records – это одинаковые строки, т.е. они абсолютно идентичны в обеих базах данных. У меня такие строки есть, точнее одна строка, она есть и в TestDB1, и в TestDB2.

Скриншот 14

Формирование и выгрузка отчета

Чтобы сформировать отчет и выгрузить, например, в Excel, нажмите в меню «Comparison -> Generate Comparison Report…». Потом выберите формат, в котором необходимо сформировать отчет, также можете указать имя файла и каталог для выгрузки. На следующем шаге можете отметить опции, для старта нажимайте «Generate».

Скриншот 15

Синхронизация данных

Базы данных мы сравнили, теперь в случае такой необходимости мы можем автоматически синхронизировать эти базы данных, т.е. TestDB2 сделать идентичной TestDB1. Для этого мы можем нажать на зеленую стрелочку в верхней части программы или на панели инструментов нажать «Synchronize».

Скриншот 16

Сначала указываем способ вывода скрипта, нам доступно три способа: открыть непосредственно в SQL редакторе, сохранить в файле или сразу выполнить. Я оставлю по умолчанию, т.е. открыть в этой же программе в новой вкладке.

Нажимаем «Next».

Скриншот 17

Затем указываем опции синхронизации, по умолчанию выбраны самые необходимые, я оставлю по умолчанию, жмем «Next».

Скриншот 18

Программа dbForge Data Compare for SQL Server проанализирует указанные опции, и в случае необходимости предупредит о проблемах.

В моем случае меня предупреждают, что внешний ключ может не восстановиться после синхронизации, а также что у меня выбрана опция «Reseed identity columns», которая означает, что будет выполнена инструкция DBCC CHECKIDENT с опцией RESEED для принудительной установки нового значения текущему значению идентификатора на целевой базе данных, что при определенных обстоятельствах может вызвать проблемы. Сразу скажу, что у меня проблем не было.

Нажимаем «Synchronize».

Скриншот 19

После чего программа сформирует необходимый скрипт, который синхронизирует TestDB2 с TestDB1. Чтобы его выполнить, можете нажать кнопку «Execute».

Скриншот 20

После этого две базы данных будут абсолютно одинаковыми.

На этом у меня все, надеюсь, статья была Вам полезна и интересна, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 1
  1. Евгений

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

Добавить комментарий для Евгений Отменить ответ

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.