Особенности бесконфликтного гетерогенного тиражирования между SQL Server 7.0 и Access 2000

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

В то же время некоторые системы баз данных, в частности SQL Server 7.0 и Access 2000, позволяют за счет применения полей типа ROWGUIDCOL в качестве первичных ключей однозначно идентифицировать любую запись в таблице базы данных. Причем, что немаловажно, независимо от того, где были сделаны эти записи, т. е. в пределах локальной сети или удаленными клиентами.

Это естественное усиление первичных ключей полями GUID обеспечивает бесконфликтное тиражирование распределенной базы данных.

Предположим, требуется создать такую информационную систему, в которой существует центральная общая база данных, а ее клиентами являются филиалы, удаленные подразделения или мобильные пользователи. В первую очередь необходимо определиться с клиентской платформой базы данных. С учетом простоты и удобства выберем для этой цели Access 2000, поскольку процессор базы данных Jet еще достаточно долго будет использоваться в качестве простого, удобного и неприхотливого клиентского средства, и поэтому репликационное решение на его базе актуально. Учтем, что в этом случае нам придется задействовать гетерогенное тиражирование данных.

Количество клиентов распределенной базы данных, как правило, неограниченно, и, кроме того, они должны действовать независимо друг от друга, следовательно, мы будем использовать тиражирование слиянием (Merge) с поддержкой анонимных клиентов центральной базы данных.

Создав табличную структуру своего решения, подготовим ее для применения в распределенной среде SQL Server 7.0.

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

Поскольку процесс тиражирования на клиенте происходит с применением ODBC, клиентские значения Currency полей могут вызывать ошибки тиражирования, если на уровне операционной системы клиента в качестве разделителя дробной части используется иное значение, чем «.»(точка). Это связано с методом приведения типов в ODBC. Для обеспечения целостности соответствующих полей таблиц базы данных можно задействовать, например, тип Float. Также при выборе длины символьных полей необходимо помнить о том, что в Access 2000 она ограничена, и обязательно учитывать это в табличной структуре центральной БД.

Кроме того, лучше избегать применения в табличной структуре альтернативных ключей (Unique Indexes), так как они могут стать явным источником конфликтов при конвергенции данных на сервере. Это не относится к таблицам справочного характера, так как для пользователей они в нашем случае все равно будут доступны только для чтения. При явной необходимости задействовать альтернативные ключи можно, например, создавать эти ключи вручную на машине клиента, таким образом они будут существовать только у клиентов, а не в общей базе данных сервера, и в пределах каждого удаленного подразделения полностью выполнять свою задачу.

Создание публикации

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

Во-первых, в свойствах издателя (Publisher) необходимо указать полный сетевой путь для папки копий (Snapshot folder), например \SERVERReplData, вместо указанного пути по умолчанию. ReplData – это тот каталог на жестком диске, из которого все клиенты будут черпать информацию для создания у себя реплик центральной базы данных. Права доступа на использование этого каталога устанавливают таким образом, что группа Administrators имеет полные права, а все остальные – только на чтение.

Кроме того, в разделе Distributor при настройке тиражирования на сервере в разделе Merge в свойствах Agent Profiles нужно выбрать пункт Slow link agent profile, так как подразделения могут быть подключены через модем. И в завершение следует уточнить режим лицензирования для SQL Server 7.0. В рассматриваемом примере это будет Per Seat. Режим можно определить, выбрав на сервере в панели управления значок Licensing.

Как уже говорилось выше, в процессе создания публикации был выбран тип Merge. Тип подписчика – Microsoft Access. Далее выбираем статьи подписки (таблицы), оставляя без изменения все их свойства. Назначив имя публикации, переходим к определению фильтров тиражирования, указывая возможность динамической фильтрации. Для автоматического построения необходимых в этом случае связей можно выбрать в качестве базовой таблицу с именами всех наших подразделений. В такой таблице необходимо в записи наряду с полем с именем подразделения иметь и поле, в котором отражено имя компьютера (Host Name), который будет подключен к центральной базе данных сервера со стороны клиента. Также нужно отметить, что таблица, по которой «расслаивается» наша база данных, должна быть как бы вершиной пирамиды табличной структуры и все связи от нее и до самых нижних таблиц в этой иерархии должны быть обязательными (атрибут для Foreign Key полей NOT NULL). Предположим, что эта таблица называется DIVISION, тогда критерием для динамической фильтрации будет: DivHOSTNAME = HOST_NAME().

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

Но сперва нужно убедиться, что только необходимые для однозначного «расслоения» табличные отношения включены в список задействованных связей (JOIN filter clause), и не более того. Так, например, нет смысла включать в этот список отношения от большинства справочных таблиц, так как они не являются определяющими при логическом «расслоении» нашей центральной БД, тем более что их данные потребуются для всех подразделений целиком и полностью. Далее, указав возможность доступа к данной публикации анонимных клиентов, завершим процесс создания публикации для центральной базы данных. Чтобы можно было применять созданную публикацию в дальнейшем, нужно сохранить ее в виде файла сценария, открыв свойства публикации на закладке Scripts.

При работе с распределенной базой данных все наши подразделения будут подключаться через одну учетную запись на сервере. Эта учетная запись создается с аутентификацией на уровне SQL Server. Базой по умолчанию для нее станет только что сформированная база данных, а Server Role нужно определить как System Administrators. Базой, к которой можно можно подключаться, будет та же база данных, но с ролью db_owner. Теперь, открыв свойства созданной публикации, следует убедиться, что с этой учетной записью можно получить доступ к ней, просмотрев свойства в разделе Publication Access List.

Для продолжения работы нужно перевести Snapshot Agent созданной публикации в ручной режим. Для этого, открыв свойства публикации, обратимся к закладке Status и к свойствам самого агента, из которых в разделе Schedules удалим назначенную к выполнению задачу, так как мы будем всегда самостоятельно создавать рабочий снимок (Snapshot) для базы данных, и, значит, этой задачей можно пренебречь.

Теперь следует запустить вручную агента тиражирования снимка (Snapshot Agent) для созданной публикации. После завершения работы агента нужно обратиться к каталогу ReplData, в котором будут сформированы все необходимые сценарии для создания клиентских реплик нашей базы данных.

Вся сложность гетерогенного тиражирования в SQL Server 7.0 при использовании полей ROWGUIDCOL в качестве первичных ключей заключается в том, что, оперируя только SQL-сценариями, на клиентах невозможно создать реплику структуры базы данных. Так, если в качестве клиентской платформы используется Access 2000, необходимо непосредственно устанавливать соответствующие свойства полей первичных ключей, например посредством DAO(Jet)-вызовов.

Для того чтобы эту задачу выполняла Access 2000, необходимо выполнить ряд действий. Для этого необходимо в каталоге ReplData найти все файлы *.SCH, соответствующие нашим таблицам, и скопировать их к себе в рабочий каталог на жестком диске. Затем нужно открыть sysmergeschemachange из центральной базы данных и, обратившись к последней колонке schematext, посмотреть, какая таблица первой появляется среди DRI файлов (пусть это будет таблица Assets). Тогда, возвращаясь в каталог ReplData и найдя там файл Assets.DRI, копируем его к себе на жесткий диск к остальным SCH-файлам. Теперь необходимо из каждого SCH-файла вырезать часть, создающую первичный ключ, и перенести ее в виде законченного SQL-выражения в Assets.DRI, дополняя этот файл с начала, например:

Теперь мы имеем должным образом скорректированные сценарии для прозрачного создания на клиентских местах таблиц с полями ROWGUIDCOL в качестве первичных ключей. Все измененные SCH файлы и Assets.DRI можно переписать в каталог ReplData на сервере поверх оригиналов, так как они нам в своем прежнем виде больше не нужны. Если понадобится, их всегда можно создать заново, запустив в любое время Snapshot Agent вручную для публикации.

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

В этом месте происходит проверка на соответствие уже созданного агента слияния Merge Agent клиенту, который подключается при очередном сеансе. Выполняемый в этом случае SELECT не сможет отличить нового анонимного клиента от того, которым был первоначально создан текущий агент, и в результате вместо добавления нового агента Merge Agent, соответствующего конкретному клиенту, пользователь всегда будет получать сообщение: «Another merge agent for the subscription(s) is running».

Для корректировки этой неточности в алгоритме работы хранимой процедуры нужно добавить еще один критерий для данного SELECT, а именно subscriber_name = @subscriber_name, т. е. как раз тот параметр, по которому собственно и отличаются друг от друга все анонимные клиенты в режиме MERGE.

Теперь можно не сомневаться, что каждому клиенту системой будет назначен свой агент слияния Merge Agent, и все подразделения смогут работать параллельно, независимо друг от друга.

После такой корректировки данная системная хранимая процедура будет иметь тип User. Для возврата ей типа System следует выполнить:

Справочные таблицы

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

Нам необходимо создать файл рабочей группы, в котором присутствовал бы пользователь из группы Admins (в Access 2000), с такими же атрибутами (имя и пароль), какие были заданы для учетной записи на сервере для работы с распределенной базой данных наших подразделений. Для этого следует сформировать новую пустую базу данных Access 2000. Откроем ее и с помощью мастера защиты создадим файл рабочей группы. Теперь нужно пройти все шаги мастера до «… добавления пользователей…» без внесения изменений, а на этом этапе добавить имя и пароль нашего специального пользователя с такими же атрибутами, как и в учетной записи на сервере центральной базы данных. Пройдя все остальные шаги без изменения, завершим процесс создания файла рабочей группы. Полученный файл рабочей группы Secured.mdw необходимо переименовать в system.mdb, для дальнейшего программного использования. Кроме того, на этапе создания клиентской реплики центральной базы данных этот файл должен располагаться в том же каталоге, что и сама база данных клиента. Имея такой файл на этапе первоначального создания реплики общей базы данных, можно будет программно установить необходимые права для всех справочных таблиц. Так как стандартный пользователь Admin в базах данных Access включен в группу Users, при обычном открытии нашей клиентской реплики общей базы данных будут доступны только те операции с базой и таблицами, которые мы в дальнейшем определим программно. Теперь невозможно будет что-либо несанкционированно изменить: ни в структуре базы, ни в справочных таблицах, ни в правах доступа, так как база будет полностью принадлежать только пользователю с нашей учетной записью. Обычный клиент сможет производить операции вставки, изменения, удаления только с определенными таблицами, которые будут заданы программным способом.

В заключение следует добавить, что после установки пакета исправлений Service Pack 2 для SQL Server 7.0, во избежание ошибок при создании первоначальных клиентских реплик центральной базы данных (неполном копировании данных с сервера), для клиентской стороны следует оставлять файлы от предыдущей версии, от Service Pack 1, а именно:

Это никак не повлияет на изменения, сделанные на сервере после установки Service Pack 2, и логику его работы в процессе тиражирования данных, но даст возможность нормально инициализировать локальные базы данных клиентов. Замену файлов можно применять только для первоначального создания клиентских реплик центральной базы данных, а в дальнейшем при загрузке и выгрузке данных можно использовать оригинальные файлы от Service Pack 2.

Кроме того, в том случае, если необходимо иметь доступ к центральной базе не только со стороны удаленных клиентов, но и локально, например из отделов центрального офиса, то следует воздержаться от совершения прямых транзакций с опубликованной для тиражирования центральной базой данных. В таком случае имеет смысл представить центральный офис как клиента опубликованной базы. Это будет уже подписка в режиме оповещения (Push subscription) однотипной базы SQL Server 7.0.