Мастер-Тур:Настройка репликации

Общая архитектура репликации

Репликация представляет собой набор технологий копирования и распространения данных и объектов баз данных между базами данных.
При репликации изменения, сделанные в одной копии объекта, могут быть распространены в другие копии.
Существует несколько видов репликации:

• Моментальных снимков
• Транзакций
• Слиянием

Компания Мегатек использует репликацию транзакций. Её схема представлена на рисунке:

Инфраструктура состоит из 3 блоков:

• Издатель
• Распространитель
• Подписчик

Репликация транзакций реализуется агентом моментальных снимков, агентом чтения журналов и агентом распространителя.
Прежде чем новый подписчик репликации транзакций сможет получить добавочные изменения от издателя, на подписчике должны находиться таблицы со схемой и данными, совпадающими со схемой и данными в таблицах на издателе. Исходный набор данных обычно является моментальным снимком, созданным агентом моментальных снимков. Исходный набор данных может также предоставляться через резервную копию или другим способом.
Агент моментальных снимков готовит файлы моментальных снимков, содержащие схему, данные публикуемых таблиц и объекты базы данных, хранит файлы в папке моментальных снимков и записывает задания синхронизации в базу данных распространителя на распространителе.
Агент чтения журналов выполняется на распространителе; обычно он выполняется непрерывно, но может также запускаться согласно задаваемому расписанию. При выполнении агент чтения журнала сначала читает журнал транзакций публикации (обычный журнал транзакций базы данных) и выявляет все инструкции INSERT, UPDATE и DELETE или другие изменения данных в транзакциях, отмеченных для репликации. Далее агент копирует эти транзакции в пакетах в базу данных распространителя на стороне распространителя.
Команды транзакций хранятся в базе данных распространителя до тех пор, пока они не будут распространены на все подписчики или пока не закончится максимальный срок хранения на распространителе. Подписчики получают транзакции в том же порядке, в котором они применялись на издателе.
Агент распространителя запускается на распространителе. Агент применяет транзакции из базы данных распространителя к подписчику.

Использование репликации

При больших нагрузках на сервер репликация позволяет задействовать дополнительные мощности. Это достигается отделением механизма поиска и подбора данных в ПК «Мастер-Web» в отдельную базу данных, которая может быть размещена на отдельном сервере, что позволяет значительно увеличить скорость работы как в ПК «Мастер-Web», так и в ПК «Мастер-Тур».
Установку репликации баз данных рекомендуется производить в нерабочее для компании время. Перед началом настройки обязательно необходимо создать резервную копию рабочей базы данных.

Для работы механизма репликации необходимо выполнения условия: 
имя сервера (hostname) и имя sql server instance должны совпадать (как для публикатора, так и для подписчика)

Для настройки репликации необходимо обратиться в Департамент по работе с клиентами Компании Мегатек service@megatec.ru.

ВНИМАНИЕ! Перед установкой новой версии репликации необходимо удалить предыдущую версию, если таковая использовалась.

Удаление репликации

База-публикатор

Задания (Jobs)

Необходимо удалить задания:

• mwReplCleanPublisher
• mwReplDeliverChanges
• mwReplDisableDeletedPrices

Триггеры (Triggers)

Необходимо удалить триггеры:

• Таблица tbl_Costs: все триггеры с префиксом mwRepl в названии;
• Таблица tbl_Quotes: все триггеры с префиксом mwRepl в названии;
• Таблица TurDate: все триггеры с префиксом mwRepl в названии;
• Таблица TP_Tours: все триггеры;
• Таблица TP_Prices: триггер mwReplDeletePrice.

Хранимые процедуры (Stored Procedures)

Необходимо заменить хранимую процедуру dbo.ClearMasterWebSearchFields на релизную.

База-подписчик

Задания (Jobs)

Необходимо удалить задания:

• mwReplApplyChanges
• mwReplCleanSubscriber

Триггеры (Triggers)

Необходимо удалить триггеры:

• Таблица tbl_Costs: все триггеры с префиксом mwRepl в названии;
• Таблица tbl_Quotes: все триггеры с префиксом mwRepl в названии;
• Таблица TurDate: все триггеры с префиксом mwRepl в названии;
• Таблица TP_Tours: триггер mwReplDeletePriceTour;
• Таблица TP_Prices: триггер mwReplDeletePrice.

Установка репликации

Для установки репликации необходимо:

• поднять на сервере 2 копии рабочей базы данных;
• выполнить скрипты на базе-публикаторе (основная база);
• выполнить скрипты на базе-подписчике (поисковая база).

Изменение настроек таблицы SystemSettings

При прогоне скрипта создания публикации изменяются настройки в таблице SystemSettings:

update dbo.SystemSettings
set
ss_parmvalue = 'publisher'
where
ss_parmname = 'MWReplication'



update dbo.SystemSettings
set
ss_parmvalue = @subscriptionDB –- название БД подписчика
where
ss_parmname = 'MWReplSubscriberDB'

Эти настройки влияют на работу хранимых процедур mwIsPublisher() и mwIsSubscriber() и на логику других хранимых процедур, использующих эти процедуры.

Выполнение скриптов на базе-публикаторе

На базе-публикаторе необходимо выполнить следующие скрипты строго в указанном порядке:

• 01. sp_CreateDirectory.sql
• 02. sp_GeneratePassword.sql
• 03. ReplicationPUB_92.sql (необходимо внести изменения в скрипт)

Изменения в скрипте ReplicationPUB_92.sql:

Изменения нужно внести в секции:

/******** НАСТРОЙКИ ********/
set @generateREPLUSERPassword = 0

/******** УКАЗАТЬ ДАННЫЕ ДЛЯ ПОИСКОВОГО СЕРВЕРА ********/
/** СЕРВЕР **/ set @subscriptionServer = 'server'
/** БД **/
set @subscriptionDB = 'testrepl2'
/** ЛОГИН **/
set @subscriptionLogin = 'sa'
/** ПАРОЛЬ **/
set @subscriptionPassword = 'sa'
/******** END ********/


Значение параметра @generateREPLUSERPassword может быть двух видов:

• 0 – в результате прогона скрипта на серверах публикатора и подписчика будет создан пользователь REPLUSER с паролем по умолчанию REPLUSERREPLUSER.
• 1 – в результате прогона скрипта на серверах публикатора и подписчика будет создан пользователь REPLUSER со сгенерированным паролем случайным образом. Данные о созданном логине/пароле будут выводены в результирующем наборе после выполнения скрипта. Если пользователь существовал, то пароль меняться не будет.

Значением параметра @subscriptionServer нужно указать адрес сервера, на котором находится база-подписчик.
Значением параметра @subscriptionDB нужно прописать название базы-подписчика.
Значением параметра @subscriptionLogin нужно указать логин пользователя на сервере подписчике с административными правами (должен иметь серверную роль sysadmin).
Значением параметра @subscriptionPassword нужно указать пароль пользователя на сервере подписчике с административными правами (должен иметь серверную роль sysadmin).

Выполнение скриптов на базе-подписчике

На базе-подписчике необходимо выполнить следующие скрипты строго в указанном порядке:

• 01. ReplicationSUB.sql (необходимо внести изменения в скрипт)
• 02. CheckReplicationTablesIdentity.sql (необходимо внести изменения в скрипт и вручную снять идентификаторы)
• 03. fn_mwGetServiceClassesNames.sql
• 04. Synchronize tables in replication.sql (сам генерирует скрипты для синхронизации, которые необходимо выполнить)
• Jobs_MW.sql

Изменения в скрипте 01. ReplicationSUB.sql

Изменения нужно внести в секции:
/******** УКАЗАТЬ ДАННЫЕ ДЛЯ ОСНОВНОГО СЕРВЕРА ********/
/** СЕРВЕР **/
set @publisherServer = 'server'
/** БД **/
set @publisherDB = 'testrepl'
/** ЛОГИН **/
set @pubLogin = 'sa'
/** ПАРОЛЬ **/
set @pubPassword = 'sa'
/******** END ********/

/******** УКАЗАТЬ ДАННЫЕ ДЛЯ ПОИСКОВОГО СЕРВЕРА ********/
/** ЛОГИН **/
set @subLogin = 'sa'
/** ПАРОЛЬ **/
set @password = 'sa'
/******** END ********/
Значением параметра @publisherServer нужно указать адрес сервера, на котором находится база-подписчик.
Значением параметра @publisherDB нужно прописать название базы-публикатора.
Значением параметра @pubLogin нужно указать логин пользователя в базе-публикаторе, под которым будет работать репликация.
Значением параметра @pubPassword нужно указать пароль пользователя в базе-публикаторе, под которым будет работать репликация.
Значением параметра @subLogin нужно указать логин пользователя в базе-подписчике, под которым будет работать репликация.
Значением параметра @password нужно указать пароль пользователя в базе-подписчике, под которым будет работать репликация.

Изменения в скрипте + ручное снятие идентификаторов: скрипт 02. CheckReplicationTablesIdentity.sql

Изменения нужно внести в секции:

/******** УКАЗАТЬ БАЗУ ДАННЫХ ПУБЛИКАТОРА ********/
declare @publisher_db as nvarchar (100)
set @publisher_db = 'testrepl'

/******** УКАЗАТЬ УКАЗАТЬ ИМЫ ПУБЛИКАЦИИ ********/
declare @publicationName as nvarchar (100)
set @publicationName = 'MW_PUB'

Значением параметра @publisherDB нужно прописать название базы-публикатора.
Значением параметра @publisherName нужно прописать имя публикации.

Ручное снятие идентификаторов:
Примерный результат после прогона скрипта отображен ниже:

Seed	Increment	Table_Name
1	1	HotelOption
1	1	HotelTypeRelations
1	1	HotelTypes
1	1	mwReplTours
1	1	ObjectGroupLinks
1	1	ObjectGroupMembers
1	1	ObjectGroups
1	1	PrtBonusDetails
1	1	PrtBonuses
1	1	stopavia
1	1	tbl_Costs
1	1	TurDate

Необходимо во всех указанных таблицах сбросить значение Increment с 1 на 0. Для этого нужно в каждой таблице выбрать колонку с первичным ключем. В свойствах колонки Identity Specification -> (Is Identity) – значение yes сбросить на no. Далее сохранить.
Для того, чтобы была возможность корректно сохранять изменения, необходимо проверить, чтобы в приложении Microsoft SQL Server Management Studio, экране Tools -> Options, разделе Designers признак Prevent saving changes that require table re-creation – не выбран.
В случае, если в таблице большое количество записей, то при попытке сохранения возникает соответствующее окно с предупреждением, что данная операция займет много времени. В этом случае нужно отказаться от продолжения и сгенерировать скрипт (выбрав в контекстном меню экрана редактирования таблицы пункт Generate Change Script). Далее сгенерированный скрипт прогнать по базе данных (изменения будут внесены не с помощью кнопки «Сохранить», а с помощью скрипта).

Установка заданий (Jobs)

Для корректной работы репликации рекомендуется установить следующие задания.

Название задания (Job Name)	Запускаемая хранимая процедура	Интервал запуска	Где устанавливается	Описание
mwRemoveDeletedNightly	mwRemoveDeleted	1 сутки (ночью)	Основная база или поисковая при репликации	Очищает рассчитанные цены на прошедшие даты, перестраивает индексы в поисковых таблицах.
mwReplDisableDeletedPricesOnline	mwReplDisableDeletedPrices	3 минуты днем	Основная база или поисковая при репликации	Помечает записи в поисковых таблицах как неактивные.
mwReplProcessQueueUpdate	mwReplProcessQueueUpdate	1 минута	Основная база и поисковая	Устанавливается при репликации и ДЦ. Обрабатывает изменения цен в поисковых таблицах.
mwReplQueueManagerDivide_xxx	mwReplProcessQueueDivide	1 минута	Основная база или поисковая при репликации	Устанавливается при репликации. Обрабатывает выставление, снятие и удаление туров из онлайна. Для ускорения выставления туров в интернет рекомендуем устанавливать не больше 5-ти таких заданий. Оптимально – 3.
mwSyncDictionaryData	mwSyncDictionaryData 1	1 сутки (ночью)	Основная база или поисковая при репликации	Устанавливается при репликации. Обновляет справочные данные в поисковых таблицах.
mwCleanAndReindex	mwCleaner mwReindex	1 сутки (ночью)	Основная база и поисковая	Очищает рассчитанные цены на прошедшие даты, перестраивает индексы в основных таблицах.
ClearCacheQuotas	ClearQuotaCache	10 минут	Основная база или поисковая при репликации	Удаляет записи из кеша квот с истекшим сроком хранения.

Включение репликации

После установки репликации необходимо включить задание mwReplQueueManager. Для этого на стороне сервера подписчика в контекстном меню задания SQL Server Agent -> Job -> mwReplQueueManager необходимо выбрать значение Enable.

Остановка/повторное включение

Для остановки работы репликации необходимо на сервере подписчика в контекстном меню Replication -> Local -> <название базы-подписчика> выбрать значение View Synchronization Status и в открывшемся окне нажать кнопку Stop, и на сервере публикатора в контекстном меню Replication -> Local -> <название базы-публикатора> выбрать значение View Log Reader Agent Status и в открывшемся окне нажать кнопку Stop.
Для повторного включения работы репликации необходимо на сервере подписчика в контекстном меню Replication -> Local -> <название базы-подписчика> выбрать значение View Synchronization Status и в открывшемся окне нажать кнопку Start, и на сервере публикатора в контекстном меню Replication -> Local -> <название базы-публикатора> выбрать значение View Log Reader Agent Status и в открывшемся окне нажать кнопку Start.

Пересоздание подписки

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

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


Далее в контекстном меню Local Subscriptions выбрать пункт New Subscriptions....


В подразделе Publication выбрать необходимую публикацию.


В подразделе Distribution Agent Location выбрать пункт Run each agent at its Subscriber (pull subscriptions).


В подразделе Subscribers выбрать базу-подписчик.


В подразделе Distribution Agent Security выбрать данные, как указано на скриншоте ниже, плюс указать логин и пароль пользователя.


В подразделе Sinchronization Schedule выбрать Run continuously.


В подразделе Initialize Subscriptions обязательно снять признак Initialize.


В подразделе Wizard Actions ничего не изменять, нажать кнопку Next.


В подразделе Complete the Wizard нажать кнопку Finish.


После успешного создания подписчика в подразделе Creating Subscription(s)... в колонке Status будет проставлен статус Success.

Для того, чтобы проверить корректность работы репликации после пересоздания подписки необходимо на сервере публикатора в контекстном меню Replication -> Local -> <название базы-публикатора> выбрать значение Replication Monitor, в открывшемся окне выбрать вкладку Tracer Tokens и нажать кнопку Insert Tracer.

Через некоторое время в колонках Publisher to Distributor, Distributor to Subscriber, Total Latency появятся значения. Наличие этих значений свидетельствует о корректности работы репликации после пересоздания подписки.

Настройка репликации

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

Мониторинг репликации

Для просмотра информации по ошибкам необходимо выполнить запрос:

select cast (cmd.Command as nvarchar(max)), * from dbo.MSrepl_errors err
left join dbo.MSrepl_commands cmd
on err.xact_seqno = cmd.xact_seqno
order by time desc

Если известные параметры @xact_seqno_start, @xact_seqno_end, полученные из монитора репликации, то запрос может выглядеть следующим образом:

exec sp_browsereplcmds @xact_seqno_start = '0x000019a0000032e50008'
,@xact_seqno_end = '0x000019a0000032e50008'

где:

'0x000019a0000032e50008', '0x000019a0000032e50008' – значения параметров @xact_seqno_start, @xact_seqno_end, которые можно получить из монитора репликации при возникновении ошибки.

Один издатель – несколько подписчиков

Список подписчиков хранится в таблице SystemSettings на издателе (ss_parmname= ‘subscriptions’ ).
Хранимые процедуры и функции для управления подписчиками:

• mwReplAddSubscription – добавление информации о подписчике
• mwReplRemoveSubscription – удаление информации о подписчике
• mwReplGetSubscriptions – получение списка подписчиков

Каждая процедура оперирует связанным сервером (linked-server), который должен быть создан на издателе с включенной опцией Data access (при создании публикации такой сервер для первого подписчика создается скриптом).

Настройки подключения в ПК «Мастер-Тур» и ПК «Мастер-Web»

ПК «Мастер-Тур»

ПК «Мастер-Тур» после установки репликации должен быть настроен на базу-публикатор.

ПК «Мастер-Web»

После установки репликации в ПК «Мастер-Web» добавляется дополнительная строка подключения:
Строка подключения к базе данных ПК «Мастер-Тур» :

1. <add key = "connectionString" value = "Data Source=ip-адрес сервера основной базы данных ПК «Мастер-тур»</span">;Initial Catalog=название основной базы данных ПК «Мастер-тур»</span">;User ID=логин пользователя ПК «Мастер-тур», под которым работает репликация</span">;Password=пароль пользователя ПК «Мастер-тур», под которым работает репликация</span">"/>
2. <add key = "searchConnectionString" value = "Data Source=ip-адрес сервера поисковой базы данных ПК «Мастер-тур»</span">;Initial Catalog=название поисковой базы данных ПК «Мастер-тур»</span">;User ID=/логин пользователя ПК «Мастер-тур», под которым работает репликация</span">;Password=пароль пользователя ПК «Мастер-тур», под которым работает репликация</span">"/>
3. <add key = "connectionStringShort" value = "Data Source=ip-адрес сервера основной базы данных ПК «Мастер-Тур»</span">;Initial Catalog=название основной базы данных ПК «Мастер-Тур»</span">"/>

Статьи репликации ПК "Мастер-Web"

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

Очередь обработки туров

Очередь содержится в таблице mwReplQueue, детализация – mwReplQueueHistory.
Для отработки очереди используется job mwReplQueueManager, который вызывает хранимую процедуру mwReplProcessQueue. В зависимости от команды выполняется соответствующее действие.

Выставление тура

Процесс выставления тура при репликации представлен на рисунке.

Сегментация поисковых таблиц

Включением управляет настройка MWDivideByCountry в таблице SystemSettings. Происходит сегментирование ценовой таблицы mwPriceDataTable по ключам страны и города вылета. Исходная таблица mwPriceDataTable более не используется.
Имя новой таблицы:
mwPriceDataTable_ключСтраны_ключГородаВылета

Механизм единственной цены

Производит удаление ранее выставленных цен с одинаковыми параметрами:

• PT_CTKEYTO
• PT_hdkey
• PT_hrkey
• PT_PnKey
• PT_Days
• PT_Nights
• PT_ctkeyfrom
• TI_TOKey
• PT_TourDate

Снятие тура

Процесс снятия тура при репликации представлен на рисунке.

Удаление тура

Процесс удаления тура при репликации представлен на рисунке.

Удаление тура с помощью Priceremover

Процесс удаления тура при репликации с помощью Priceremover представлен на рисунке.