[Из песочницы] Веб-синхронизация слиянием на MS SQL10.03.2016 15:18

Данную статью-инструкцию решено было написать для помощи моим друзьям и коллегам по работе, так как большинство из них не сталкивалось с настройкой веб синхронизации в MS SQL, и я надеюсь, что она позволит избежать некоторых мелких проблем, с которыми столкнулся я сам при настройке. Надеюсь и вам, уважаемое хабрасообщество, оно может пригодится.

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

Зачем и для чего

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

Почему бы не настроить репликацию с белыми IP или через VPN:

Провайдер не предоставляет белый IP.
Используется мобильный интернет с плохой скоростью.
При использовании PPTP провайдеры блокируют GRE.
VPN имеет особенность обрываться, хоть и после восстанавливает соединение.
Из-за незначительной потери связи агент SQL Server не всегда успевает соединиться с подписчиком, и следующая попытка идет по расписанию через некоторое время, при не частой синхронизации это может быть достаточно критично.
При большом количестве публикаций и подписчиков соединяющихся с сервером, опять же агент не всегда «успевает» соединиться с подписчиком. Помогает обычно настройка расписания для того, чтоб синхронизация не проходила для всех в одно время, что тоже не всегда удобно.

При работе с веб-синхронизацией в репликации слиянием синхронизация данных идет по HTTPS и обновления отсылаются в виде XML, это позволит избежать проблемы, описанные выше, возможно не все, но все же:

Нам не требуется постоянный IP и соответственно пользователь не привязан к рабочему месту и может быть со своим ноутбуком где угодно.
Соединение использует безопасный протокол.
Маловероятно, что провайдером будет блокироваться порт 443.
При медленном интернет соединении синхронизация проходит быстрее и обрывов по моим наблюдениям гораздо меньше.
Агент слияния располагается на подписчике, что снижает нагрузку на сам сервер.

Небольшое отступление от основной темы:

По своей работе очень часто приходится сталкиваться с репликацией слиянием, репликацию настраиваем для одной товаро-учетной системы болгарского разработчика. В основном вся настройка репликации у нас сводится с использованием белых IP адресов и пробросом порта 1433 наружу или настройкой VPN канала, а тут уже каждый настраивает по своим возможностям и знаниям.

Первый вариант можно просто и быстро настроить, но в плане безопасности он проигрывает, на него со временем начинают идти атаки в основном с китайских IP, конечно спасает firewall, но «добросовестные» пользователи очень любят его отключать.

Второй вариант уже более безопасный (хоть и не всегда), но требует от партнеров, которые занимаются автоматизацией больших знаний в настройке, в основном используются Hamachi, SoftEther или следующими по полярности это OpenVPN или функционал Windows Server (в основном PPTP).

Сам по большей части до поры до времени использовал при работе с репликацией возможности Windows Server (SSTP) и\или OpenVPN.

Подготовка

Работать с веб-синхронизацией можно начиная с MS SQL 2005 и старше, на центральном сервере потребуется редакция Standard или старше, для подписчика достаточно Express редакции.

В данном примере используются:

Microsoft SQL Server 2014 Standard в роли издателя.
Microsoft SQL Server 2014 Express в роли подписчика.
Установленный на центральный сервер IIS
Развернутая основная база данных нашего магазина на издателе.
Созданная пустая база данных на подписчике.
Пользователь Windows с правами доступа для группы IIS_IUSRS
Немного терпения.

Настройка IIS

В первую очередь подготовим IIS для работы, создадим каталог для сервиса C:\inetpub\wwwroot\WebSQL. Если на сервере предполагается работа с несколькими публикациями создайте отдельные каталоги в WebSQL\RCU.
Скопируйте replisapi.dll из C:\Program Files\Microsoft SQL Server\120\COM в каталог, созданный на первом шаге в WebSQL, при работе с несколькими публикациями скопируйте в WebSQL\RCU.
На многих интернет ресурсах рекомендуется провести регистрацию библиотеки, но в моем случае, как ни странно это не потребовалось. Для регистрации воспользуйтесь командой regsvr32 «C:\inetpub\wwwroot\WebSQL\RCU\replisapi.dll» от имени администратора.
Создадим новый веб-сайт через диспетчер служб IIS. Физический путь указываем каталог, созданный на первом шаге, если есть подкаталоги внутри оставляем в любом случае корневой каталог.
Добавление веб-сайта
После добавим к нашему сайту виртуальный каталог. Псевдоним указываем согласно имени каталога и указываем физический путь до него. Если каталог один путь можно указать на коренной каталог.
Добавление виртуального каталога
Следующим шагом настроим разрешение для исполнения replisapi.dll для этого в диспетчере служб IIS выберем виртуальный каталог и в центральной панели в категории IIS найдем пункт сопоставление обработчиков. Далее в панели действий выберем пункт добавить сопоставление модуля.
Сопоставление обработчика
- В поле путь запроса укажем replisapi.dll
- В списке модулей выберем IsapiModule. Если данного модуля в списке нет тогда в компонентах Windows для IIS служб требуется добавить расширения ISAPI.
Добавление компонентов Windows
- Для исполняемого файла укажем путь до библиотеки в виртуальном каталоге C:\inetpub\wwwroot\WebSQL\RCU\replisapi.dll. Хочу обратить внимание, что опять же если публикаций несколько, тогда и обработчик добавляете для каждого виртуального каталога со своим путем.
- В поле имя укажем Replisapi
- Нажмем кнопку ограничения и перейдем на вкладку доступ и выберем выполнение.
Ограничения запроса
- Подтверждаем изменения в окне ограничения запроса и в сопоставлении модуля. При подтверждении нам будет выведен предупреждение о том действительно ли мы хотим разрешить данное расширение ISAPI, нажимаем да.
Разрешить данное расширение
- Добавим обработчик требуется его включить, так как по умолчанию он будет не запущен, для это найдем и выберем его в списке обработчиков и в панели действий нажмем изменение разрешений функции и в открывшемся окне отметим пункт выполнение и подтвердим изменения.
Изменение разрешений функции
Для правильной работы потребуется настроить проверку подлинности для сайта целиком в IIS. В центральной панели в категории IIS выберем проверку подлинности и отключим анонимную проверку подлинности и включим обычную проверку подлинности. Опять же если данный пункт отсутствует в IIS, требуется добавить данную возможность в компонентах Windows.
Проверка подлинности
Следующим шагом мы должны привязать SSL сертификат к сайту, саму процедура получения сертификата описывать не буду, так как данной информации везде предостаточно, в том числе и на Хабрахабре.
- Получаете SSL сертификат к примеру, на startssl и формируем PFX файл.
- В диспетчере IIS для самого сервера IIS выбираем в центральной панели сертификаты сервера и в панели действий выберем импортировать, укажем путь до сохраненного сертификата и пароль к нему.
Импорт сертификата
- После импорта снова выбираем сайт в диспетчере и в панели действий выбираем привязки и в открывшемся окне выбираем добавить. Выбираем тип https и в списке SSL-сертификат выбираем тот, который импортировали на предыдущем шаге и подтверждаем изменения. Привязку для http можно удалить за ненадобностью.
Добавление привязки
Последним шагом в настройке IIS проверим сервиса для этого перейдем по данному адресу https://server.domain.com/rcu/replisapi.dll? diag и введем логин и пароль пользователя Windows (для каждой публикации рекомендую создавать отдельного пользователя). При успешном подключении должна отобразиться диагностическая информация, как на скриншоте ниже.
Диагностическая информация

Создание и настройка публикации

Подключаемся к центральному серверу с Microsoft SQL Server 2014 Standard через Management Studio в Object Explorer → Replication → Local Publication → New Publication (в контекстном меню), далее следуем по шагам мастера создания публикации. На данном этапе настройка репликации слиянием с использованием веб-синхронизации не отличается от настройки в локальной сети.
- Выбираем базу данных для публикации.
Выбор базы
- Выбираем тип репликации — репликация слиянием (Merge)
Тип репликации
- Выбираем с какими версиями SQL Server будет совместима репликация. У себя обычно использую SQL Server 2005 и SQL Server 2008 и старше, так как бывают совсем слабые машины.
Совместимость
- Указываем, какие таблицы будут участвовать в синхронизации. Хочу дополнительно отметить если синхронизация будет проходить не часто, а записей по каким-либо таблицам будет проходить много, рекомендую увеличить значения для Publisher range size и Subscriber range size к примеру, до 1 000 000. Больше ставить не рекомендуется, так как диапазон может быстро закончится при большом количестве подписчиков и частом их пересоздании, так же хочу отметить если используется автоматическая выдача диапазона (Automatically manage identity ranges), увеличьте процент для выделения нового диапазона при достижении порога (Range threshold percentage), например, 90–95.
Таблицы

Диапазон издателя и подписчика
- Данный шаг оставляем без изменений для всех таблиц будет добавлена колонка с уникальным идентификатором.
Уникальный идентификатор (не обязательно)
- Фильтрацию синхронизируемых данных оставляем на без изменений, если фильтрация потребуется её можно добавить в любое время.
Фильтрация (не обязательно)
- Указание расписания для снятия моментального снимка, данный шаг можно, так же оставить по умолчанию или изменить его расписание по своему усмотрению.
Моментальный снимок (не обязательно)
- Указываем настройки безопасности для агента моментальных снимков. Выбираем в настройках «Выполнять с учетной записью службы агент SQL Server» и соединение с издателем «Путем олицетворения учетной записи процесса» и подтверждаем изменения.
Безопасность агента
- Следующий шаг оставляем без по умолчанию в нем на предлагается подтвердить создание публикации и сгенерировать скрипт по пройденным шагам для создания публикации.
- Указываем имя публикации и нажимаем Finish. Обычно для удобства пишу имя так «имя_базы_pub»
Имя публикации
- Дожидаемся окончания создания публикации и проверяем, чтоб все пункты завершились корректно.
Создание публикации
После создания публикации нам потребуется произвести дополнительные настройки. Для этого выберем созданную публикацию в Object Explorer → Replication → Local Publication → имя_публикации и откроем настройки.
- В основных настройках укажем, что срок действия подписок никогда не истекает, это нужно в том случае если планируется, что подписчики могут находиться продолжительное время в офлайн и операций проходить много не будет. В ином случае укажите через какой срок будут удалены метаданные репликации.
Срок действия подписок
- В настройках моментального снимка (Snapshot) укажем альтернативное место хранения снимка при этом каталог должен быть доступен для общего доступа в сети и путь до каталога должен указываться сетевой. В данном примере это будет \\Replication\ftproot. Так же установим сжатие для снимка в данной папке.
Расположение моментального снимка
- Далее в настройках FTP и интернет для моментального снимка (FTP Snapshot and internet) разрешим синхронизацию подписчиков через web сервер, а также укажем адрес ранее созданного сайта https://server.domain.com/rcu/replisapi.dll
Синхронизация подписчиков
- Последним шагом в настройке публикации будет добавление пользователя в список доступа публикации (Publication Access List). Пользователя заранее создаем для каждой публикации, после добавляем его в MSSQL Server, сопоставляем с базой данных для публикации и указываем членство в роли db_owner.
Сопоставление пользователя

Список доступа к публикации

Создание подписки и синхронизация

Подписку при веб-синхронизации потребуется создавать скриптами, а не через мастер, по причине того, что синхронизация с подпиской у нас будет выполнять по запросу, а также из-за того, что на MSSQL Server Express нельзя запустить агент. Скриптами в любом случае получается на много быстрее.

В начала добавим подписчика на центральном сервере с публикацией

USE имя_базы
GO
EXEC sp_addmergesubscription
@publication = N'Microinvset_pub', -- Указываем имя публикации
@subscriber = 'Kassa01-PC', -- Указываем имя ПК подписчика
@subscriber_db = N'MicroinvestFront', -- Указываем имя пустой базы данных на подписчике
@subscription_type =N'pull';

Далее добавляем подписку по запросу уже на самом подписчике

USE имя_базы
GO
EXEC sp_addmergepullsubscription
@publisher = 'MainServer', -- Указываем имя сервера с публикацией
@publication = N'Microinvset_pub', -- Указываем имя публикации
@publisher_db = N'Microinvest'; -- Указываем имя базы данных публикации

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

USE имя_базы
GO
EXEC sp_addmergepullsubscription_agent
@publisher = 'MainServer', -- Указываем имя сервера с публикацией
@publisher_db = N'Microinvest', -- Указываем имя базы данных публикации
@publication = N'Microinvest_pub', -- Указываем имя публикации
@distributor = 'MainServer', -- Указываем имя сервера с публикацией
@use_web_sync = 1, -- Разрешаем WEB синхронизацию.
@internet_security_mode = 0, -- Указываем проверку подлинности, в данном случае используется обычная проверка подлинности (проверка пароля и имени входа, входящая в протокол HTTP). 
@internet_url = 'https://server.domain.com/rcu/replisapi.dll', -- Указываем адрес ранее созданного сайта
@internet_login = 'Логин пользователя Windows, который мы вводили в браузере при входе в диагностическую информацию',
@internet_password = 'Пароль пользователя Windows, который мы вводили в браузере при входе в диагностическую информацию',
@internet_timeout = 9999; -- Указываем время в секундах до истечения срока действия запроса на веб-синхронизацию

Наконец доходим до самого главного до первой синхронизации, как уже было написано ранее Express редакция MSSQL не позволяет запустить встроенный агент, но никто не запрещает сделать свой агент при помощи RMO, мы же пойдем более простым путем и будем запускать агент слияния вручную при помощи батника, который после просто поместим в планировщик Windows.
```
@echo OFF
SET Publisher=MainServer
SET Subscriber=Kassa01-PC
SET PublicationDB=Microinvest
SET SubscriptionDB=MicroinvestFront
SET Publication=Microinvest_pub
REM -- Путь до расположения REPLMERG.EXE указываем в зависимости от версии MSSQL Server
"C:\Program Files\Microsoft SQL Server\120\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher%  -Subscriber  %Subscriber%  -Distributor %Publisher% -PublisherDB %PublicationDB%  -SubscriberDB %SubscriptionDB% -DistributorSecurityMode 1 -SubscriptionType 1 -SubscriberSecurityMode 1
```
По завершению синхронизации вы увидите примерно следующую информацию в CMD, как на скриншоте.
Итог запуска синхронизации
Контролировать ход репликации можно, как и раньше через монитор репликации.
Монитор репликации

Послесловие

Веб-синхронизация настроена нам остается только разместить созданный выше батник в планировщик Windows и запускать его автоматически, например, раз в 5–10 минут, конечно если это требуется. При запуске батника и синхронизации будет постоянно выскакивать окно с CMD, чтобы этого избежать можно запускать батник через WScript.exe, например, так:

Set WshShell = CreateObject("WScript.Shell")  
WshShell.Run "startsync.bat", 0, false  
Set WshShell = Nothing  
WScript.Quit

После чего данный скрипт указываем для запуска в планировщике. Дополнительно по синхронизации отмечу если планируются довольно объемные передачи данных, возможно потребуется увеличить значение для WebSyncMaxXmlSize. Настраивается данный параметр в реестре HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\Replication\, будьте аккуратнее с данной настройкой, так как при увеличении значения, увеличивается и количество потребляемой виртуальной памяти.

Ссылки

Веб-синхронизация для репликации слиянием
Как синхронизировать подписку по запросу (программирование репликации)
Как синхронизировать подписку по запросу (программирование объектов RMO)

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

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