Что такое репликация в Microsoft SQL Server?
Репликация — один из способов создания копии SQL-базы на основном или резервном сервере. Бывает как с полной, так и с выборочной (в отличие от зеркалирования) синхронизацией данных в реальном времени. В SQL Server эта технология решает несколько задач: от бэкапирования данных до распределения нагрузок между серверами.
Типы репликации:
Для Microsoft SQL Server предусмотрено три основных вида репликаций:
- Транзакционная. Изменения в базе на мастер-сервере транслируются на slave-сервер с минимальной задержкой, зависящей от скорости сети и аппаратных мощностей. При появлении транзакций в объектах, участвующих в репликации, на издателе, агент чтения журналов копирует эти транзакции на экземпляр-распространитель, затем агент распространитель копирует данные на подписчиков. Помимо стандартной имеет такие разновидности, как одноранговая, с обновляемыми подписками и т.д.
- Слияния. Отличается от стандартной транзакционной своей многонаправленностью. Репликация слиянием использует агента слияния для репликации изменений данных на издателе и подписчике.
- Снимками. На подписчике применяется снапшот данных из публикации без задействования журналов транзакций и триггеров.
Транзакционная репликация считается самой популярной, поскольку элементарно настраивается и позволяет быстро реплицировать нужные фрагменты SQL-базы, решив таким образом вопрос с правами доступа, а также избыточностью данных в рамках OLTP. Поэтому в этой статье мы рассматриваем преимущественно её настройку.
Подготовка к репликации
Репликация данных SQLServer работает по подписочному принципу, который предполагает разделение серверов на три группы: издателей (мастер-сервер), распространителей и подписчиков (slave). Резервные серверы подписываются на части базы на мастере и применяет у себя все изменения, которые передаются дистрибьютором. Реплицируемые объекты называются articles (статьи).
Перед настройкой репликации специалисты «ДБ-сервис», опираясь на свой опыт, рекомендуют учесть ряд особенностей SQLServer и совершить несколько подготовительных этапов, которые редко упоминаются в интернет-публикациях.
Перед настройкой репликации специалисты «ДБ-сервис», опираясь на свой опыт, рекомендуют учесть ряд особенностей SQLServer и совершить несколько подготовительных этапов, которые редко упоминаются в интернет-публикациях.
- Первым делом преднастроим распространение репликации.
- Администратору будет удобнее работать с push-моделью репликации, когда мастер-сервер помимо обработки транзакций администрирует slave-серверы. Рекомендуем отдать ей предпочтение
- Также для всех агентов репликации стоит настроить Windows-аутентификацию. В отличие от второго (смешанного) типа проверки подлинности, он дает возможность создавать группы Windows на доменном уровне, а имя входа - на SQLServer для всей группы. Это делает администрирование более интуитивным.
- Проследите, чтобы владельцем всех заданий агентов был сисадмин, а не пользователь, который настраивает репликацию.
2. Теперь преднастроим издателя.
- При создании публикации добавляйте в нее объекты постепенно, а не разом.
- Если планируется съём снэпшотов, проверьте, соответствует ли директория ряду требований. Она должна быть доступна на всех серверах кластера, иметь достаточно свободного места для всех таблиц в репликации, а также быть размещена на диске с размером кластера 4 KB, чтобы можно было включить компрессию т.к. никому не нужны диски с размером кластера 64 KB. Объем свободного места на диске, где размещается снэпшот должен составлять 2x размера реплицируемых таблиц в несжатом виде.
- Т.к. применение снэпшота удаляет объекты в реплицируемой базе, поэтому необходимо заранее сохранить все, что может понадобится после восстановления — индексы, триггеры, права доступа к объектам.
3. Наконец, необходимо подумать и о подписке.
- Снэпшот удаляет объекты в реплицируемой базе, так что сохраните все, что может вам понадобится: индексы, триггеры, права доступа к объектам.
- Во время снятия снэпшота мониторьте блокировки в базе на сервере с публикацией, свободное место на диске, куда снэпшот выполняется, нагрузку на диск (при необходимости снижайте сжатием), блокировки в базе-подписчике и, наконец, место в базе данных на подписчике.
- Следить за процессом применения снэпшота нужно через Replication Monitor, sys.sysprocesses и трассировку ошибок.
Настройка репликации в Microsoft SQL Server
Репликация данных SQLServer работает по подписочному принципу, который предполагает разделение серверов на три группы: издателей (мастер-сервер), распространителей и подписчиков (slave) Резервные серверы подписываются на части базы на мастере и применяет у себя все изменения, которые передаются дистрибьютором. Реплицируемые объекты называются articles (статьи).
Настройка распространителя
- В контекстном меню переходим по пути Replication => Configure Distribution. В открывшемся окне из двух пунктов выбираем верхний «[имя сервера-распространителя] will act as its own Distributor; SQL Server will create a distribution databasse and log»
- Выбираем папку, где будет храниться БД distribution, учитывая планируемый объем передаваемых данных.
- При желании отмечаем экземпляры БД, для которых этот сервер сможет выступать распространителем (при необходимости вернуться к этому можно будет и позднее).
- Задаем пароль для связи с выбранными SQL-базами и кликаем на «Завершить».
Настройка издателя
- В Configure Distribution теперь кликните на второй пункт “use the following server as the Distributer”и в качестве распространителя выберете нужный сервер. Введите пароль, который задавали при настройке дистрибьютера и завершите работу мастера настройки.
- Перейдите в контекстном меню Replication => Local Publication => New Publication и выберите из списка БД, которую собираетесь реплицировать.
- Перед вами появится список из четырех типов репликаций — снимочной, слияния, одноранговой и стандартной транзакционной — из которых нужно выбрать Transactional publication.
- Теперь выбираем статьи, которые будем реплицировать и по желанию применяем нужные фильтры. В нашем случае речь идет о таблицах, однако транзакционная репликация может работать и с пользовательскими процедурами, функциями и представлениями.
- В настройках безопасности (Security Settings) нажимаем «Run under SQL Server Agent service account» и выбираем аккаунты, с которых будет происходить выполнение агентов.
Настройка подписчика
- На слэйве создаем новую подписку, перейдя по Replication => Local Subscriptions и выбрав сервер-дистрибьютер, БД и нужную публикацию.
- Для выполнения агентов на распространителе кликните на «Run all agents at the Distributor».
- Выберите БД, куда из Subscription Database будут транслироваться данные.
- В настройках безопасности снова укажите нужные аккаунты для выполнения агентов.
- Снова укажите аккаунт, из-под которого будут выполняться агенты репликации. Чтобы репликация была непрерывной активируйте режим Agent Schedule => Run continuously.
- Для активации подписки поставьте галку в параметре Initialize и завершите работу мастера настройки. Все, настройка репликации MS SQL закончена.
Поддержка репликации Microsoft SQL Server
Инструменты для транзакционной репликации доступны во всех версиях SQL Server и полностью включены в самую массовую лицензию Standard, в отличие от других репликаций. Так, для настройки публикаций используется Replication Monitor. Перейдя по пути Add Publisher => Specify a Distributor and Add its Publishers вы увидите, какие издатели работают с конкретным распространителем и сможете просмотреть логи на предмет того, появилась ли нужная таблица в базе и передаются ли транзакции.
Частые ошибки
Среди немногих минусов транзакционной репликации MS SQL принято называть ее асинхронность: в случае сбоя на одном из задействованных в репликации серверов, транзакция может произойти с ошибками, часть данных потеряться, а синхронизация между издателем и подписчиком — нарушиться. Мы советуем обращать на это внимание при настройке репликации, для повышения доступности данных в SQL Server и работе с их избыточностью.
Хотя в интернете есть много мануалов по работе с разными типами репликаций, при высокой цене ошибки мы советуем бизнесу обращаться за помощью к профессионалам. В «ДБ-сервис» мы обеспечим администрирование и поддержку Microsoft SQL Server надежно защитив ваши данные и бизнес-процессы.
Хотя в интернете есть много мануалов по работе с разными типами репликаций, при высокой цене ошибки мы советуем бизнесу обращаться за помощью к профессионалам. В «ДБ-сервис» мы обеспечим администрирование и поддержку Microsoft SQL Server надежно защитив ваши данные и бизнес-процессы.
Эксперт ДБ-сервис
Список литературы:
- MS SQL Server: настройка репликации // Linuxshop: интернет-магазин. – URL: MS SQL Server: настройка репликации (linuxshop.ru) – Дата публикации: 14.04.2022.
- MS SQL Server: пошаговая настройка репликации. – Текст: электронный // WINITPRO: интернет-портал. – URL: MS SQL Server: пошаговая настройка репликации | Windows для системных администраторов (winitpro.ru) – Дата публикации: 14.02.2020.
- 13-я статья о типичных ошибках DBA MS SQL Server. – Текст: электронный // Хабр: интернет-портал. – URL: 13-я статья о типичных ошибках DBA MS SQL Server / Хабр (habr.com) – Дата публикации: 24.09.2017.
- Руководство. Настройка репликации между двумя полностью подключенными серверами (репликация транзакций). – Текст: электронный // Microsoft: интернет-портал. – URL: Учебник. Настройка репликации транзакций - SQL Server | Microsoft Learn – Дата публикации: 27.04.2023.