У цій статті ми розглянемо, як налаштувати самий розповсюджень тип реплікації в SQL Serverтрансакціонної реплікацію. Цей тип реплікації SQL Server використовується для копіювання даних в режимі реального часу, тобто дані на передплатників з’являються практично відразу (з урахуванням часу який витрачається на копіювання даних по мережі).

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

  • Реплікація даних між декількома серверами для read доступу (наприклад, для розвантаження серверів OLTP типу);
  • Як рішення для надмірності даних окремих об’єктів.

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

Перевага реплікації перед Always ON і дзеркалюванням баз даних в тому, що за допомогою реплікації ви можете скопіювати окремі об’єкти (окремі таблиці / уявлення), а не базу даних цілком.

Єдиний мінус транзакционной реплікації -вона не може працювати в синхронному режимі – тобто, чекати завершення транзакції на передплатників. Тому в разі форс-мажорного виключення будь-якого учасника реплікації, дані можуть бути втрачені або може статися рассинхронизация між видавцем і передплатниками.

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

У будь-якому типі реплікації SQL Server є 3 типи серверів:

  • Видавництво (Видавець) – основний примірник-джерело, який публікує статті;
  • Розповсюджувач (Розповсюджувач) – примірник якого поширює статті на сервера-передплатники. Цей тип примірника не зберігає у себе дані видавця на постійній основі, а поширює їх передплатникам;
  • Абонент (Передплатник) – примірник якого отримує поширювані статті.

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

Робота реплікації транзакцій здійснюється через внутрішні агенти 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

Для початку потрібно налаштувати екземпляр розповсюджувача. В розділі Реплікація, В контекстному меню натисніть Налаштуйте розподіл

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 на окремому масиві дисків. Особливо це важливо, якщо планується великий обсяг реплицируемой даних. sql server шлях до бази даних Distribution

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

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

Вкажіть пароль для зв’язку з екземплярами, які будуть зв’язуватися з розповсюджувачем.

пароль для зв'язку між Publisher і Distributor

Після цього можна тиснути Finish. На цьому настройка розповсюджувача завершена.

Якщо ви хочете змінити пароль розповсюджувача або дозволити іншим видавцям використовувати цей розповсюджувач, то можна це зробити через Властивості дистриб’ютора

Властивості дистрибутора в SQL Server 2019настройки розповсюджувача sql реплікації

Налаштування видавця реплікації в SQL Server

Тепер переходимо до налаштувань видавця реплікації. Запустіть той же майстер Налаштуйте Distributuin.

Налаштування видавця реплікації SQL Server
Виберіть другий пункт, вказуємо сервер розповсюджувач – testnode2 node2

вказати ім'я розповсюджувача реплікації

Після цього введіть пароль, який ви вказали при налаштуванні розповсюджувача і натисніть Finish.

Тепер можна створити нову публікацію: Реплікація -> Місцева публікація -> Нова публікація.

створення публікації sql server

Вкажіть базу даних, яка буде брати участь в реплікації.

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

Виберіть тип реплікації. доступні:

  • Знімок публікації;
  • Транзакційне видання (Виберіть цей тип реплікації);
  • Публікація однорангової мережі;
  • Публікація об’єднання.

Транзакційна публікація - транзакційна реплікація

Виберіть таблиці, які потрібно реплицировать. За допомогою транзакционной реплікації так само можна реплицировать призначені для користувача процедури, функції і уявлення. Реплицируемой об’єкти називаються Статті (Статті).

вибір таблиці для реплікації sql server

На наступному кроці можете вказати фільтр для публікації.

sql фільтр реплікації

Щоб майстер відразу створив моментальний знімок, виберіть опцію “Негайно створіть знімок і залиште знімок доступним для ініціалізації підписок“.

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

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

В імені публікації я вказую назви сервера-передплатника. Так легше орієнтуватися якщо публікацій на інші сервера буде багато.

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

publication створена

Налаштування передплатника реплікації в SQL

На testnode2 node2 в розділі Реплікація -> Місцеві підписки створіть нову підписку.

Виберіть пункт “Запустіть усіх агентів у дистриб’юторі“, Щоб агенти виконувалися на розповсюджувача. У моєму випадку передплатник і розповсюджувач збігаються, але зазвичай це різні сервера.

Якщо вибрати другий пункт ( “Запустіть кожного агента у свого абонента“), То агенти будуть виконуватися на передплатника. Цей варіант кращий, якщо розповсюджувач у вас “формальний” і знаходиться на одному сервері з видавцем або передплатником

Запустіть усіх агентів у дистриб’юторі

Вкажіть базу даних, в яку будуть реплицироваться дані з База даних передплати.

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

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

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

Якщо ви хочете, щоб дані реплицироваться постійно, вибирайте режим Agent Schedule -> Бігайте безперервно.

тип реплікації постійна Run continuously

Увімкніть опцію Ініціалізувати, Щоб ініціалізувати підписку після завершення роботи майстра.

При включенні параметра “Оптимізована пам’ять“Таблиці на передплатника з цієї публікації будуть створені як” In memory “. Якщо ви не плануєте ці таблиці як таблиці для використання в оперативній пам’яті, то чи не відзначайте цей параметр. Оптимізована реплікація пам'яті

Натисніть Finish.

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

Моніторинг та управління реплікацією в SQL Server

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

Монітор реплікації в SQL-сервері

Додайте видавців через розповсюджувача (Add Publisher -> 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.

Leave a Reply

Your email address will not be published. Required fields are marked *