MS SQL Server: пошаговая настройка репликации

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

Репликация транзакций проста в настройке и доступна во всех версиях SQL Server. Данный тип репликации используется для двух целей:

  • Репликация данных между несколькими серверами для read доступа (например, для разгрузки серверов OLTP типа);
  • Как решение для избыточности данных отдельных объектов.

Хотя у SQL Server есть много решений для балансировки нагрузки select запросов и средств обеспечения отказоустойчивости, транзакционная репликация это самый простой и быстрый способ, так как вы можете реплицировать отдельные объекты. Так же этот вид репликации полностью доступен в Standard лицензии SQL Server ( в отличии от групп доступности Always On, которые полноценно доступны только в Enterprise).

Преимущество репликации перед Always ON и зеркалированием баз данных в том, что с помощью репликации вы можете скопировать отдельные объекты (отдельные таблицы/представления), а не базу данных целиком.

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

SQL Server: основы технологии репликации

В любом типе репликации SQL Server есть 3 типа серверов:

  • Publisher (издатель) – основной экземпляр-источник, который публикует статьи;
  • Distributor (распространитель) – экземпляр который распространяет статьи на сервера-подписчики. Этот тип экземпляра не хранит у себя данные издателя на постоянной основе, а распространяет их подписчикам;
  • Subscriber (подписчик) – экземпляр который получает распространяемые статьи.

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

Работа репликации транзакций осуществляется через внутренние агенты SQL Server’а:

  • Агент чтения журналов;
  • Агент моментальных снимков;
  • Агент распространения.

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

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

Транзакционная репликация работает асинхронно, так же как и асинхронные режимы Always On и зеркалирования баз данных. То есть, данные, которые были записаны на издатель, будут отправлены на подписчики без гарантии доставки в случае сбоя во время передачи данных. Это нужно учитывать, если вы собираетесь использовать транзакционную репликацию для избыточности и высокой доступности данных в SQL Server.

Схема связи агентов между собой из официальной документации:

схема реализации репликации базы данных в MS SQL Server

Рассмотрим, как настроить репликацию в SQL Server на следующем тестовом стенде:

  • 2 виртуальные машины с Windows Server 2019 в одной сети;
  • 2 установленных экземпляра SQL Server 2019.

Топология репликации:

  • testnode1\node1 – издатель (Publisher);
  • testnode2\node2 – подписчик (Subscriber);
  • testnode2\node2 – распространитель (Distributor).

В этом примере мы будем реплицировать одну таблицу с testnode1\node1 на testnode\node2. В роли распространителя будет выступать testnode2\node2.

Настройка распространителя в SQL Server

Для начала нужно настроить экземпляр распространителя. В разделе Replication, в контекстном меню нажмите Configure Distribution

Configure Distribution - настройка распространителя репликации

Так как мы хотим использовать этот экземпляр в качестве распространителя, выбираем первый пункт (testnove2 will act as its own Distributor; SQL Server will create a distribution databasse and log).

выбор роли replication disrtibutor в sql server

Указываем директорию для моментальных снимков. Я оставлю стандартный путь. путь к каталогу снапшоотв репликации sql server

Указываем директорию для базы данных Distribution. Если есть такая возможность, то лучше разместить файлы базы данных distribution на отдельном массиве дисков. Особенно это важно, если планируется большой объём реплицируемых данных. sql server путь к базе данных Distribution

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

настройка подписчиков репликации sql server

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

пароль для связи между Publisher и Distributor

После этого можно жать Finish. На этом настройка распространителя завершена.

Если вы хотите изменить пароль распространителя или разрешить другим издателям использовать этот распространитель, то можно это сделать через Distributor Properties

Distributor Properties в sql server 2019 настройки распространителя sql репликации

Настройка издателя репликации в SQL Server

Теперь переходим к настройкам издателя репликации. Запустите тот же мастер Configure Distributuin.

Настройка издателя репликации SQL Server
Выберите второй пункт, указываем сервер распространитель – testnode2\node2

указать имя распространителя репликации

После этого введите пароль, который вы указывали при настройке распространителя и нажмите Finish.

Теперь можно создать новую публикацию: Replication -> Local Publication -> New Publication.

создание публикации sql server

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

выберите базу данных для репликации

Выберите тип репликации. Доступны:

  • Snapshot publication;
  • Transactional publication (выберите этот тип репликации);
  • Peer-to-Peer publication;
  • Merge publication.

Transactional publication - транзакционная репликация

Выберите таблицы, которые нужно реплицировать. С помощью транзакционной репликации так же можно реплицировать пользовательские процедуры, функции и представления. Реплицируемые объекты называются Articles (Статьи).

выбор таблицы для репликации sql server

На следующем шаге можете указать фильтр для публикации.

sql фильтр репликации

Чтобы мастер сразу создал моментальный снимок, выберите опцию “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.

создать снимок sql server для инициализации репликации

Укажите аккаунты, из-под которых будут выполняться агенты. Нажмите Security Settings и выберите “Run under SQL Server Agent service account”. выбор сервисных учетных записей

В имени публикации я указываю названия сервера-подписчика. Так легче ориентироваться если публикаций на другие сервера будет много.

настройка публикации

publication создана

Настройка подписчика репликации в SQL

На testnode2\node2 в разделе Replication -> Local Subscriptions создайте новую подписку.

создать подписку репликации -> Local Subscriptions  в sql server

Выберите издателя, базу данных и публикацию в ней.

выбор издателя репликации

Выберите пункт “Run all agents at the Distributor”, чтобы агенты выполнялись на распространителе. В моём случае подписчик и распространитель совпадают, но обычно это разные сервера.

Если выбрать второй пункт (“Run each agent at its Subscriber”), то агенты будут выполняться на подписчике. Этот вариант предпочтителен, если распространитель у вас “формальный” и находится на одном сервере с издателем или подписчиком

Run all agents at the Distributor

Укажите базу данных, в которую будут реплицироваться данные из Subscription Database.

выбор базы даннх, в которую нужно реплицировать данные sql server

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

выбор сервисных аккаунтов

Если вы хотите, чтобы данные реплицировались постоянно, выбирайте режим Agent Schedule -> Run continuously.

тип репликации постоянная Run continuously

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

При включении параметра “Memory Optimized” таблицы на подписчике с этой публикации будут созданы как “In memory”. Если вы не планируете эти таблицы как таблицы для использования в оперативной памяти, то не отмечайте этот параметр. Memory Optimized репликация

Нажмите Finish.

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

Мониторинг и управление репликацией в SQL Server

Практически всю настройку существующих публикаций можно провести через Replication Monitor.

Replication Monitor в sql server

Добавьте издателей через распространителя (Add Publisher -> Specify a Distributor and Add its Publishers).

Specify a Distributor and Add its Publishers

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

список доступных репликаций в sql server

Убедимся, что агент моментальных снимков отработал и доставил снимок на распространителя. В моём случае сначала была ошибки о том, что аккаунту из-под которого работают агенты, не хватает прав на базе TestDatabase1. Для решения этой проблемы я добавил сервисному аккаунту (из-под которого работает SQL Server) роль db_owner в базе TestDatabase1 на обоих экземплярах.

мониторинг репликации sql server

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

процесс выполнения репликации

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

Для начала добавим новую запись в таблицу.

проверка репликации данных в sql server
Проверяем, что эта запись реплицировалась на testnode2\node2.

данные успешно реплицированы sql server 2019

На этом базовая настройка репликации транзакций в SQL Server закончена.

Для диагностики проблем с репликацией в основном используется Replication Monitor, но можно использовать и дополнительные инструменты диагностики SQL Server.


Предыдущая статья Следующая статья


Комментариев: 1 Оставить комментарий

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Я не робот( Обязательно отметьте)