пятница, 25 ноября 2016 г.

Настройка репликации из MS SQL Server в DB2

Недавно была задача настроить MS SQL Server репликацию на сервер IBM DB2 AS/400, задача получилась непростая и интересная. В процессе настройки репликации было много проблем, описание которых в Интернете было довольно мало. Ниже постараюсь описать проблемы и шаги настройки данной репликации:

Настройку репликации можно разделить на несколько шагов:
1)  Установка драйвера провайдера Microsoft OLE DB Provider for DB2
2)  Настройки на стороне DB2  
3) Получить строку подключения к DB2 AS/400
4) Настройка самой репликации и ее проверка


Имеем:
1) Сервер MS SQL Server 2014 12.0.5000.0 Enterprise Edition
2) Таблицу для репликации с первичным ключом

3) Подписчик в виде сервера IBM DB2 AS/400

Начинаем:

1)      Скачиваем и устанавливаем драйвера DB2OLEDB

К сожалению, найти их в Интернет была проблем, ссылка в поиске вела на сайт Microsoft https://www.microsoft.com/en-us/download/details.aspx?id=29100 но там была документации по ним, да и многие ссылки в msdn-е были битые и вели на несуществующие страницы. Драйвера я нашел у себя на сервере, когда –то давно скаченные. Если найдете где они сейчас, сообщите, укажу адрес.
У меня они были версии V3.0
Установка простая, ничего сложного.
После установки имеем кроме провайдера в MS SQL Server, еще и приложение Data Access Tool, которое нам очень пригодится.

2)      Настройки на стороне DB2  

Тут работы администраторы DB2 AS/400, что он должен выполнить:
Создать отдельно выделенного пользователя и указать ему схему по умолчанию.
Указать для библиотеки, что данные логируются, или проще говоря включить журналирование для объектов библиотеки пользователя в DB2 AS/400. Без этого репликация не поднимется. Данный параметр был получен случайно, т.к для первоначального пользователя журналирование было отключено и были ошибки.

3)      Получаем строку подключения к DB2 AS/400.

После установки выше драйверов имеем приложение Data Access Tool, с помощью данного приложения получаем строку подключения:
Создаем новый Data Source

Выбираем платформу DB2, у нас DB2/AS400


Далее имя AS400 и порт подключения AS/400:


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

Данные параметры необходимо получать от администраторов DB2 AS/400, к сожалению, у меня не все администраторы точно могли сказать, что нужно указать.

Initial catalog” я указал так же как у нас называется сервер
Package Collection” и “Default schema” данные параметры соответствуют понятию названию библиотеки пользователя в DB2 AS/400.

В следующем окне выбирается кодировка, - у себя оставил по умолчанию.
В настройках безопасности, как обычно, указываем пользователя и пароль к DB2 AS/400:


В следующем окне – без изменений.

Проверяем настройки   и доходим сохранения информации по коннекту:

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


 
Должны быть результаты:


 
а на тестовый запрос должны отобразиться элементы библиотеки


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


Копируем и используем далее.

4)      Настройка самой репликации и ее проверка

Если все выше сделано и получено, можно приступить к самой настройке репликации, если выше указанное не получилось или ошибки, не стоит начинать работы.

Итак, настройка репликации как обычно
,
Предварительно у вас должен быть установлен компонент репликации MS SQL Server и активирован Distributor, я использовал Distributor-а на том же сервере, где и источник данных.

Делать удобнее Wizard-ом, далее можно получить скрипт и его поправить при необходимости:

Выбираем нашу базу данных, где находятся таблицы для репликации, выбираем «Transaction publication», выбираем таблицу, одну или несколько:



И выбираем «Article Properties» для выделенной таблицы:

Обратите внимание на то, как и что я указал:

Поле «Destination object name» указал имя нашей таблицы с БОЛЬШОЙ буквы – это очень важно, что будет если оставить по умолчанию, опишу ниже. В MS SQL Server таблица определена с маленькой буквы, как видите

Обязательно указать владельца назначения «Destination object owner” и тоже с большой буквы, мы ее определили для пользователя в п. 2 настоящего руководства. В DB2 as/400 под этим подразумевается библиотека пользователя.

Поле «Action if name is in use» я указал оставить существующую без изменений, у меня подразумевает не трогать таблицу в DB2 AS/400 если она там есть. Другие значения можно выбирать, с ними репликация так же работала.

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

Инструкции доставки данных – тестировал только на указанных в скриншоте, другие способы не получилось проверить:

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

Создаем подписчика Non-SQL подписчика:

Указываем произвольно имя источника:

В окне «Distribution Agent Security» необходимо указать учетную запись с паролем

И самое, главное строку подключения к DB2 AS/400, полученная в п. 3, ее вставляем без изменений. У себя на стенде я скопировал и с учетной запись и паролем в строке

Далее, необходимо быть аккуратным в параметрах инициализации:

У меня выбрано, что я не инициализирую репликацию, почему объясню ниже. Если у вас таблица небольшая и таблица не существует на подписчике, то инициализация нужна.

После этого подписчик создан.

Если после создания репликации «Монитор репликации» показывает статус ОК, это не значит, что все с репликацией хорошо.
После создания репликация обязательно сделайте тестовые транзакции: Insert\Update\Delete. Если транзакции успешно доставляются, то только тогда можно сказать, что репликация из MS SQL Server в DB2 AS/400 РАБОТАЕТ!

А теперь самое интересное, с чем пришлось столкнуться и решать методом тестирования:
НЮАНСЫ:
1)      Почему использовать большие буквы:

Есть три таблицы определения:

Самым правильным и удобным для DB2 AS/400 будет таблица 2, где указано имя таблицы с большой таблицы и столбцы с большой таблицы.

Для таблицы 3, где имя таблицы с маленькой буквы, нужно в свойствах таблицы репликации указать таблицу с большой буквы, тогда все работать будет штатно.

Таблицы 1, не будет работать в репликации.

А что будет:

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

К примеру:

на MS SQL Server таблица tbl на DB2 as/400 она будет “tbl
поля в таблице id, val на DB2 AS/400 они будут: “id”, “val”.

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

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

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

Эти параметры были получены экспериментально, натолкнул ответ в форуме, но немножко по другому вопросу.

2)      Указать пользователя с единственной схемой.

Если у пользователя доступ к несколько схемам(библиотекам) на DB2 AS/400 и указана нужная схема по умолчанию, даже в этом случае репликация не будет работать. Да, при инициализации таблицы MS SQL Server будут созданы в нужной схеме, а вот служебную таблицу MSREPL7 будет создавать в схеме QSYS.

Ошибки по нюансам 1 и 2 будут вида:
user?COLLECTION in QSYS type *N not found. SQLSTATE: 42704, SQLCODE: -204
Column FTSUB00001 not in table *N in *N. SQLSTATE: 42703, SQLCODE: -205 (Source: MSSQL_REPL_DB2, Error number: -205)

Кстати, ошибка «Column FTSUB00001 not in table *N in *N ..», значение  FTSUB00001 это не поле таблицы, а название таблицы, данного поля в таблице нет, и эта ошибка будет как раз из-за регистра создаваемых таблиц.

3)     Инициализации таблиц


    Т.к DB2 AS/400 не родная СУБД для MS SQL Server, инициализации больших данных будет происходит очень долго. Таблица сначала выгружается в файл, далее построчно заливается в DB2 AS/400, что очень долго.

     К примеру, таблица с 6 мл строк заливалась около часа, а у нас были таблицы с 120 млн строками и более, считали это будет более 10 дней).
  
    В данном случае поступается следующим образом, вы другим способом передаете данные в уже существующие таблицы в DB2 AS/400, а затем как у меня выше, в свойствах репликации указывать не трогать данные при наличии таблицы и не проводить инициализацию репликации. Для этого, кстати, можно использовать драйвера iSeries и с помощью запросов OPENQUERY залить данные в DB2 AS/400.

    На небольшие таблицы вы спокойно можете производить инициализацию данных.

4)     Проблемы со схемой назначения в репликации.
     В студии MS SQL Server есть небольшой баг при настройке репликации. Вы создали публикацию, указали конечную схему назначения и сохранили репликацию. Все ОК. Но после того как вы добавили подписчика или сделали изменения в репликации, схема по умолчанию слетает и все скрипты таблиц для инициализации снова без схемы. Даже если у пользователя в Db2 AS/400 указана схема по умолчанию, таблицы создаются в схеме QSYS.
   Это было замечено на 10-х тестах при создании этой репликации.
   Возможно это издержки студии, хотя использовал родную 2014 студию с последним SP.
   По нюансам вроде все.

И ниже предоставляю работающий скрипт создания репликации на DB2 AS/400 без инициализации:
USE [testDb] -- my test database
GO
--create my test table
CREATE TABLE [dbo].[TBL2](
        [ID] [int] NOT NULL, ---!!! CASE sensitive is important
        [VAL] [nchar](10) NULL,  ---!!!CASE sensitive is important
 CONSTRAINT [PK_TBL2] PRIMARY KEY CLUSTERED
(
        [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'testDb', @optname = N'publish', @value = N'true'
GO
exec [testDb].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
-- Adding the transactional publication
use [testDb]
exec sp_addpublication @publication = N'tbl2',
  @description = N'Transactional publication of database ''testDb'' from Publisher ''SERVER1SQL''.',
   @sync_method = N'character', @retention = 0, @allow_push = N'true', @allow_pull = N'false',
   @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
   @compress_snapshot = N'false', @ftp_port = 0, @allow_subscription_copy = N'false', @add_to_active_directory = N'false',
   @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false',
   @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false',
   @replicate_ddl = 0, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'true'
GO
exec sp_addpublication_snapshot @publication = N'tbl2', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null,
@job_password = null, @publisher_security_mode = 1
GO
-- Adding the transactional articles
use [testDb]
exec sp_addarticle @publication = N'tbl2', @article = N'TBL2', @source_owner = N'dbo', @source_object = N'tbl2',
 @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none',
 @schema_option = 0x0000000000004071, @identityrangemanagementoption = N'none', @destination_table = N'TBL2',
 @status = 8, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL'
GO
-- Adding the transactional subscriptions
use [testDb]
exec sp_addsubscription @publication = N'tbl2', @subscriber = N'TBL2',
@destination_db = N'(default destination)', @subscription_type = N'Push', @sync_type = N'replication support only',
@article = N'all', @update_mode = N'read only', @subscriber_type = 3

exec sp_addpushsubscription_agent @publication = N'tbl2', @subscriber = N'TBL3',
@subscriber_db = N'(default destination)',
@job_login = null, @job_password = null, @subscriber_security_mode = 0,
@subscriber_login = N'mdmbuf3', @subscriber_password = null,
@subscriber_provider = N'DB2OLEDB', @subscriber_datasrc = N'tbl2',
@subscriber_provider_string =
N'Provider=DB2OLEDB;User ID=MDMBUF2;Password=PASSWORD1;Initial Catalog=ALFA;Network Transport Library=TCPIP;Host CCSID=37;
PC Code Page=1252;Network Address=172.10.10.111;Network Port=446;Package Collection=MDMBUF2;Default Schema=MDMBUF2;
Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;
DateTime As Char=False;Rowset Cache Size=0;DateTime As Date=False;Auth Encrypt=False;AutoCommit=True;
Authentication=Server;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;',
@frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO


По настройке репликации вроде все. Есть вопросы, комментарии пишите. Возможно есть недочеты, особенно в частности Db2, так же сообщайте, поправлю.
Спасибо.

Использованные источники:
http://www.dbforums.com/showthread.php?1662981-Must-double-quote-table-name

p.s: Из-за служебных символов съезжает форматирование текста. движок автоматически создает страшных код, который трудно читать, так что пока так.


Комментариев нет :

Отправить комментарий