Что такое репликация sql server
MS SQL Server: пошаговая настройка репликации
В этой статье мы рассмотрим, как настроить самый распространений тип репликации в SQL Server – транзакционную репликацию. Этот тип репликации SQL Server используется для копирования данных в режиме реального времени, то есть данные на подписчиках появляются практически сразу (с учетом времени которое тратится на копирование данных по сети).
Репликация транзакций проста в настройке и доступна во всех версиях SQL Server. Данный тип репликации используется для двух целей:
Хотя у SQL Server есть много решений для балансировки нагрузки select запросов и средств обеспечения отказоустойчивости, транзакционная репликация это самый простой и быстрый способ, так как вы можете реплицировать отдельные объекты. Так же этот вид репликации полностью доступен в Standard лицензии SQL Server ( в отличии от групп доступности Always On, которые полноценно доступны только в Enterprise).
Преимущество репликации перед Always ON и зеркалированием баз данных в том, что с помощью репликации вы можете скопировать отдельные объекты (отдельные таблицы/представления), а не базу данных целиком.
Единственный минус транзакционной репликации —она не может работать в синхронном режиме – то есть, дожидаться завершения транзакции на подписчиках. Поэтому в случае форс-мажорного выключения любого участника репликации, данные могут быть потеряны или может произойти рассинхронизация между издателем и подписчиками.
SQL Server: основы технологии репликации
В любом типе репликации SQL Server есть 3 типа серверов:
Роли могу пересекаться между собой. Например, один экземпляр может быть и издателем, и подписчиком (но не самого себя).
Работа репликации транзакций осуществляется через внутренние агенты SQL Server’а:
При появлении транзакций в объектах, участвующих в репликации, на издателе, агент чтения журналов копирует эти транзакции на экземпляр-распространитель, затем агент распространитель копирует данные на подписчиков. Агент моментальных снимков участвует только тогда, когда нужно скопировать новый моментальный снимок (обычно это происходит при инициализации и реинициализации репликации).
Транзакции доставляются на подписчиков в той последовательности, в которой они были отправлены на издателя. Если транзакций слишком много, образуется очередь.
Транзакционная репликация работает асинхронно, так же как и асинхронные режимы Always On и зеркалирования баз данных. То есть, данные, которые были записаны на издатель, будут отправлены на подписчики без гарантии доставки в случае сбоя во время передачи данных. Это нужно учитывать, если вы собираетесь использовать транзакционную репликацию для избыточности и высокой доступности данных в SQL Server.
Схема связи агентов между собой из официальной документации:
Рассмотрим, как настроить репликацию в SQL Server на следующем тестовом стенде:
В этом примере мы будем реплицировать одну таблицу с testnode1\node1 на testnode\node2. В роли распространителя будет выступать testnode2\node2.
Настройка распространителя в SQL Server
Для начала нужно настроить экземпляр распространителя. В разделе Replication, в контекстном меню нажмите Configure Distribution…
Так как мы хотим использовать этот экземпляр в качестве распространителя, выбираем первый пункт (testnove2 will act as its own Distributor; SQL Server will create a distribution databasse and log).
Указываем директорию для моментальных снимков. Я оставлю стандартный путь. 
Указываем директорию для базы данных Distribution. Если есть такая возможность, то лучше разместить файлы базы данных distribution на отдельном массиве дисков. Особенно это важно, если планируется большой объём реплицируемых данных. 
На этом шаге можно указать экземпляры, которые смогут использовать данный сервер как распространитель. Я сразу добавлю testnode1\node1. Это можно сделать и позже, после начальной конфигурации.
Укажите пароль для связи с экземплярами, которые будут связываться с распространителем.
После этого можно жать Finish. На этом настройка распространителя завершена.
Если вы хотите изменить пароль распространителя или разрешить другим издателям использовать этот распространитель, то можно это сделать через Distributor Properties…
Настройка издателя репликации в SQL Server
Теперь переходим к настройкам издателя репликации. Запустите тот же мастер Configure Distributuin.
Выберите второй пункт, указываем сервер распространитель – testnode2\node2
После этого введите пароль, который вы указывали при настройке распространителя и нажмите Finish.
Укажите базу данных, которая будет участвовать в репликации.
Выберите тип репликации. Доступны:
Выберите таблицы, которые нужно реплицировать. С помощью транзакционной репликации так же можно реплицировать пользовательские процедуры, функции и представления. Реплицируемые объекты называются Articles (Статьи).
На следующем шаге можете указать фильтр для публикации.
Чтобы мастер сразу создал моментальный снимок, выберите опцию “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.
Укажите аккаунты, из-под которых будут выполняться агенты. Нажмите Security Settings и выберите “Run under SQL Server Agent service account”. 
В имени публикации я указываю названия сервера-подписчика. Так легче ориентироваться если публикаций на другие сервера будет много.
Настройка подписчика репликации в SQL
Local Subscriptions в sql server» width=»339″ height=»256″ srcset=»https://winitpro.ru/wp-content/uploads/2020/02/sozdat-podpisku-replikacii-greater-local-subscriptions.png 366w, https://winitpro.ru/wp-content/uploads/2020/02/sozdat-podpisku-replikacii-greater-local-subscriptions-300×226.png 300w» sizes=»(max-width: 339px) 100vw, 339px» />
Выберите издателя, базу данных и публикацию в ней.
Выберите пункт “Run all agents at the Distributor”, чтобы агенты выполнялись на распространителе. В моём случае подписчик и распространитель совпадают, но обычно это разные сервера.
Если выбрать второй пункт (“Run each agent at its Subscriber”), то агенты будут выполняться на подписчике. Этот вариант предпочтителен, если распространитель у вас “формальный” и находится на одном сервере с издателем или подписчиком
Укажите базу данных, в которую будут реплицироваться данные из Subscription Database.
Снова укажите аккаунт, из-под которого будут выполняться агенты репликации.
Включите опцию Initialize, чтобы инициализировать подписку после завершения работы мастера.
При включении параметра “Memory Optimized” таблицы на подписчике с этой публикации будут созданы как “In memory”. Если вы не планируете эти таблицы как таблицы для использования в оперативной памяти, то не отмечайте этот параметр. 
На этом настройка подписки завершена. Теперь необходимо проверить работоспособность публикации и корректность выполнения репликации таблицы.
Мониторинг и управление репликацией в SQL Server
Практически всю настройку существующих публикаций можно провести через Replication Monitor.
После того, как вы соединитесь с распространителем, у вас появится список издателей, которые работают с этим распространителем.
Убедимся, что агент моментальных снимков отработал и доставил снимок на распространителя. В моём случае сначала была ошибки о том, что аккаунту из-под которого работают агенты, не хватает прав на базе TestDatabase1. Для решения этой проблемы я добавил сервисному аккаунту (из-под которого работает SQL Server) роль db_owner в базе TestDatabase1 на обоих экземплярах.
Так же убедимся, что распространитель доставил транзакции на подписчика.
В логах агентов ошибок нет, проверим что наша таблица действительно появилась в базе.
Для начала добавим новую запись в таблицу.
Проверяем, что эта запись реплицировалась на testnode2\node2.
На этом базовая настройка репликации транзакций в SQL Server закончена.
Для диагностики проблем с репликацией в основном используется Replication Monitor, но можно использовать и дополнительные инструменты диагностики SQL Server.
Типы репликации
Microsoft SQL Server поддерживает следующие типы репликации для использования в распределенных приложениях.
| Тип | Описание |
|---|---|
| Репликация транзакций | Изменения на издателе доставляются подписчику по мере их появления (почти в реальном времени). Изменения данных применяются на подписчике в том же порядке и в тех же рамках транзакций, в которых они выполнялись у издателя. |
| Репликация слиянием | Данные можно изменять как на издателе, так и на подписчике, а также отслеживать с помощью триггеров. Подписчик синхронизируется с издателем при подключении к сети и обменивается с ним всеми строками, которые изменились со времени последней синхронизации издателя и подписчика. |
| Репликация моментальных снимков | Моментальный снимок издателя применяется к подписчику. Данные распространяются точно в том виде, в котором они были представлены в определенный момент времени. Обновление данных не отслеживается. Во время синхронизации формируется моментальный снимок и отсылается подписчикам целиком. |
| Одноранговая репликация | Одноранговая репликация, основанная на репликации транзакций, распространяет согласованные на уровне транзакций изменения между несколькими экземплярами сервера почти в реальном времени. |
| Двунаправленная репликация | Двунаправленная репликация транзакций представляет собой особую топологию репликации транзакций, которая позволяет двум серверам обмениваться изменениями друг с другом: каждый сервер публикует данные, после чего подписывается на публикацию с теми же данными от другого сервера. |
| Обновляемые подписки | Основаны на репликации транзакций. Когда данные для обновляемой подписки обновляются на подписчике, они сначала распространяются на издателя, а затем на других подписчиков. |
Тип репликации, которая выбирается для приложения, зависит от многих факторов, в том числе от физической среды репликации, типа и объема реплицируемых данных, а также от того, обновляются данные на подписчике или нет. Физическая среда включает в себя количество и расположение компьютеров, участвующих в репликации, и зависит от того, являются эти компьютеры клиентами (рабочие станции, переносные или карманные компьютеры) или серверами.
Репликация любого типа обычно начинается с начальной синхронизации опубликованных объектов между издателем и подписчиками. Эта начальная синхронизация может быть выполнена репликацией при помощи моментального снимка, который является копией всех объектов и данных, заданных публикацией. После создания моментального снимка он доставляется подписчикам. Для некоторых приложений репликация моментальных снимков полностью покрывает их потребности. Для приложений других типов важно, чтобы последующие изменения данных доставлялись подписчику дополнительными порциями с течением времени. Для некоторых приложений также нужно, чтобы изменения переносились также от подписчика обратно к издателю. Такие приложения могут использовать как репликацию транзакций, так и репликацию слиянием.
Репликация и доставка журналов (SQL Server)
Доставка журналов использует две копии одной базы данных, которые обычно хранится на разных компьютерах. В любой момент только одна копия базы данных доступна клиентам. Эта копия известна как база данных-источник. Обновления, вносимые клиентами в базу данных-источник, распространяются при помощи доставки журналов в другую копию базы данных, которая называется база данных-получатель. Доставка журналов включает применение каждой вставки, обновления или удаления, сделанных в базе данных-источнике, к базе данных-получателю с помощью журнала транзакций.
Доставка журналов может использоваться совместно с репликацией со следующими особенностями.
Репликация не продолжается после отработки отказа доставки журналов. В случае отработки отказа агенты репликации не подключаются к базе данных-получателю, поэтому транзакции не реплицируются подписчикам. Если происходит автоматический возврат на базу данных-источник, репликация возобновляется. Все транзакции, которые при доставке журналов копируются из базы данных-получателя в базу данных-источник, реплицируются подписчикам.
Если база данных-источник потеряна безвозвратно, базу данных-получатель можно переименовать, чтобы продолжить репликацию. Далее описываются требования и процедуры, которым необходимо следовать при обработке данного случая. В качестве примера описывается база данных публикации, которая чаще всего используется для доставки журналов, но такой же процесс может быть применен и к базе данных подписки, и к базе данных распространителя.
Сведения о восстановлении баз данных, задействованных в процессе репликации, без изменения настроек репликации см. в статье Создание резервной копии и восстановление из копий реплицируемых баз данных.
В целях обеспечения доступности базы данных публикации рекомендуется использовать зеркальное отображение базы данных, а не доставку журналов. Дополнительные сведения см. в статье Зеркальное отображение и репликация баз данных (SQL Server).
Требования и процедуры для репликации из базы данных-получателя в случае утери базы данных-источника
Учитывайте следующие требования и замечания.
Если на сервере-источнике содержится несколько баз данных публикации, доставьте журналы всех баз данных публикации на один сервер-получатель.
Путь установки экземпляра сервера-получателя должен совпадать с путем установки сервера-источника. Места нахождения баз данных пользователя на сервере-получателе должны совпадать с местами нахождения на сервере-источнике.
Создайте резервную копию главного ключа службы на сервере-источнике. Данный ключ будет восстановлен на сервере-получателе. Дополнительные сведения см. в статье BACKUP SERVICE MASTER KEY (Transact-SQL).
Доставка журналов не гарантирует сохранности данных. Сбой базы данных-источника может вызвать потерю тех данных, для которых не была создана резервная копия, а также данных, резервные копии которых были утеряны из-за сбоя.
Доставка журналов при репликации транзакций
Установка этого параметра для базы данных публикации гарантирует, что транзакции не будут доставлены в базу данных распространителя до тех пор, пока не будет создана их резервная копия в базе данных публикации. Затем последняя резервная копия базы данных публикации может быть восстановлена на сервере-получателе, при этом в базе данных распространителя не будет транзакций, отсутствующих в восстановленной базе данных публикации. Данный параметр гарантирует, что в случае перехода издателя на сервер-получатель обеспечивается согласованность между издателем, распространителем и подписчиком. Затрагиваются задержка и пропускная способность, так как транзакции нельзя доставить в базу данных распространителя до тех пор, пока для них не созданы резервные копии на издателе. Если ваше приложение может работать с такой задержкой, рекомендуется установить этот параметр в базе данных публикации. Если параметр sync with backup не установлен, подписчики могут получать изменения, которые более не включены в восстановленную базу данных на сервере-получателе. Дополнительные сведения см. в статье Стратегии резервного копирования и восстановления из копии репликации моментальных снимков и репликации транзакций.
Настройка репликации транзакций и доставки журналов с параметром «sync with backup»
Если параметр «sync with backup» не установлен в базе данных публикации, выполните sp_replicationdboption ‘
Настройте доставку журналов для базы данных публикации. Дополнительные сведения см. в разделе Настройка доставки журналов (SQL Server).
Если издатель поврежден, восстановите последний журнал базы данных на сервере-получателе, используя параметр KEEP_REPLICATION инструкции RESTORE LOG. Это действие сохранит все настройки репликации для базы данных. Дополнительные сведения см. в статьях Переход на вторичный сервер доставки журналов (SQL Server) и RESTORE (Transact-SQL).
Восстановите и перенесите базы данных msdb и master с сервера-источника на сервер-получатель. Дополнительные сведения см. в статье Резервное копирование и восстановление системных баз данных (SQL Server). Если сервер-источник был также и распространителем, необходимо восстановить и перенести базу данных распространителя с сервера-источника на сервер-получатель.
Эти базы данных должны быть согласованы по конфигурации и настройкам репликации с базой данных публикации сервера-источника.
На сервере-получателе восстановите главный ключ службы, резервная копия которого была получена с сервера-источника. Дополнительные сведения см. в статье RESTORE SERVICE MASTER KEY (Transact-SQL).
Настройка репликации транзакций и доставки журналов без параметра «sync with backup»
Настройте доставку журналов для базы данных публикации. Дополнительные сведения см. в разделе Настройка доставки журналов (SQL Server).
Если издатель поврежден, восстановите последний журнал базы данных на сервере-получателе, используя параметр KEEP_REPLICATION инструкции RESTORE LOG. Это действие сохранит все настройки репликации для базы данных. Дополнительные сведения см. в статьях Переход на вторичный сервер доставки журналов (SQL Server) и RESTORE (Transact-SQL).
Восстановите и перенесите базы данных msdb и master с сервера-источника на сервер-получатель. Дополнительные сведения см. в статье Резервное копирование и восстановление системных баз данных (SQL Server). Если сервер-источник был также и распространителем, необходимо восстановить и перенести базу данных распространителя с сервера-источника на сервер-получатель.
Эти базы данных должны быть согласованы по конфигурации и настройкам репликации с базой данных публикации сервера-источника.
Возможно, будет получено сообщение об ошибке от агента чтения журнала, в котором указывается на отсутствие синхронизации базы данных публикации с базой данных распространителя.
На сервере-получателе восстановите главный ключ службы, резервная копия которого была получена с сервера-источника. Дополнительные сведения см. в статье RESTORE SERVICE MASTER KEY (Transact-SQL).
Выполните sp_replrestart. Данную хранимую процедуру можно использовать для принудительного пропуска агентом чтения журнала всех предыдущих реплицированных транзакций в журнале базы данных публикации. Транзакции, примененные после завершения хранимой процедуры, обрабатываются агентом чтения журнала. Дополнительные сведения см. в статье sp_replrestart (Transact-SQL).
Перезапустите агент чтения журнала после того, как хранимая процедура будет успешно выполнена. Дополнительные сведения см. в статье Запуск и остановка агента репликации (среда SQL Server Management Studio).
Транзакции, которые уже распространены на подписчике, могут применяться на издателе. Чтобы агент распространителя при повреждении не выдал сообщения об ошибке при попытке повторного применения этих транзакций на подписчике, укажите профиль агента Продолжать при возникновении ошибок согласованности данных.
Доставка журналов при репликации слиянием
Выполните указанную ниже последовательность действий, чтобы настроить репликацию слиянием и доставку журналов.
Настройка репликации слиянием и доставки журналов
Настройте доставку журналов для базы данных публикации. Дополнительные сведения см. в разделе Настройка доставки журналов (SQL Server).
Если издатель завершает работу по ошибке, переименуйте компьютер сервера-получателя, а затем переименуйте экземпляр SQL Server, чтобы имя совпадало с именем сервера-источника. Сведения о переименовании компьютера см. в документации по операционной системе Windows. Сведения о переименовании сервера см. в разделах Переименование компьютера, на который установлен изолированный экземпляр SQL Server и Переименование экземпляра отказоустойчивого кластера SQL Server.
Восстановите последний журнал базы данных на сервере-получателе, используя параметр KEEP_REPLICATION инструкции RESTORE LOG. Это действие сохранит все настройки репликации для базы данных. Дополнительные сведения см. в статьях Переход на вторичный сервер доставки журналов (SQL Server) и RESTORE (Transact-SQL).
Восстановите и перенесите базы данных msdb и master с сервера-источника на сервер-получатель. Дополнительные сведения см. в статье Резервное копирование и восстановление системных баз данных (SQL Server). Если сервер-источник был также и распространителем, необходимо восстановить и перенести базу данных распространителя с сервера-источника на сервер-получатель.
Эти базы данных должны быть согласованы по конфигурации и настройкам репликации с базой данных публикации сервера-источника.
На сервере-получателе восстановите главный ключ службы, резервная копия которого была получена с сервера-источника. Дополнительные сведения см. в статье RESTORE SERVICE MASTER KEY (Transact-SQL).
Синхронизируйте базу данных публикации с одной или несколькими базами данных подписки. Это позволит провести передачу изменений, сделанных в базе данных публикации, но не представленных в восстановленной резервной копии. Передаваемые данные зависят от того, как отфильтрованы публикации:
Если публикации не отфильтрованы, должна существовать возможность обновления базы данных публикаций посредством синхронизации с самым обновленным подписчиком.
Если же публикация отфильтрована, то базу данных публикации, возможно, не удастся обновить. Продумайте таблицу, которая разделена так, что каждая подписка получает данные клиентов только из одного региона: север, восток, юг и запад. Если существует по крайней мере один подписчик из каждой секции данных, синхронизация с подписчиком из каждой секции должна обновить базу данных публикаций. Однако если данные в западной секции, например не были реплицированы на какие-либо подписчики, эти данные на издателе невозможно будет обновить. В таком случае рекомендуется провести повторную инициализацию всех подписок, чтобы добиться конвергенции данных на издателе и подписчиках. Дополнительные сведения см. в статье Повторная инициализация подписок.