Параметризованные фильтры строк

Статья
12/15/2008

Параметризованные фильтры (в предыдущих версиях SQL Server они назывались динамическими фильтрами) позволяют отсылать разным подписчикам разные секции данных, что исключает необходимость создания множества публикаций. Секция — это просто подмножество строк таблицы; в зависимости от настроек, выбранных при создании параметризованного фильтра, каждая строка в опубликованной таблице может принадлежать только одной секции (что порождает неперекрывающиеся секции) или двум и более секциям (что порождает перекрывающиеся секции).

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

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

Определение или изменение параметризованного фильтра строк

SQL Server Management Studio: Как определить и изменить параметризованный фильтр строк для статьи слияния (среда SQL Server Management Studio)
Программирование репликации на языке Transact-SQL: How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming)
Программирование объектов RMO: How to: Define an Article (RMO Programming)

Как работают параметризованные фильтры

Параметризованный фильтр строк использует предложение WHERE, чтобы выбрать нужные для публикации данные. Вместо того чтобы задавать буквенное значение в предложении (так, как в случае статического фильтра строк), вы указываете одну или обе следующие системные функции: SUSER_SNAME() и HOST_NAME(). Можно использовать и пользовательские функции, но они должны содержать в своем теле SUSER_SNAME() или HOST_NAME() либо вычислять одну из этих системных функций (например, MyUDF(SUSER_SNAME()). Если пользовательская функция содержит в своем теле SUSER_SNAME() или HOST_NAME(), функции невозможно передать параметры.

Системные функции SUSER_SNAME() и HOST_NAME() не характерны для репликации слиянием, но они используются репликацией слиянием для параметризованной фильтрации.

Функция SUSER_SNAME() возвращает сведения об именах входа для соединений с экземпляром SQL Server. При использовании в параметризованном фильтре функция возвращает имя входа, используемое агентом слияния для подключения к издателю (имя входа указывается, когда создается подписка).
HOST_NAME() возвращает имя компьютера, подключающегося к экземпляру SQL Server. По умолчанию при использовании в параметризованном фильтре она возвращает имя компьютера, на котором запущен агент слияния. Для подписки по запросу это имя подписчика; для принудительной подписки это имя распространителя.
Можно также переопределить эту функцию с помощью значения, отличного от имени подписчика и распространителя. Обычно приложения переопределяют эту функцию более осмысленными значениями, такими как имя или идентификатор (ID) продавца. Дополнительные сведения см. в подразделе «Переопределение значения функции HOST_NAME()» этого раздела.

Возвращаемое системной функцией значение сравнивается со столбцом, указываемым в фильтруемой таблице, и соответствующие данные загружаются на подписчик. Сравнение производится при инициализации подписки (поэтому в исходном моментальном снимке содержатся только подходящие данные) и при каждой синхронизации подписки. По умолчанию, если изменение на издателе приводит к удалению строки из секции, эта строка удаляется на подписчике (это поведение контролируется с помощью параметра @allow_partition_realignment хранимой процедуры sp_addmergepublication (Transact-SQL)).

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

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

Фильтрация с помощью SUSER_SNAME()

Рассмотрим таблицу Employee в образце базы данных Adventure Works. Эта таблица содержит столбец LoginID, содержащий имя входа каждого сотрудника в формате Домен\ИмяВхода. Чтобы отфильтровать эту таблицу таким образом, чтобы сотрудникам были доступны только относящиеся к ним данные, укажите следующее предложение фильтрации:

LoginID = SUSER_SNAME()

Например, для одного из сотрудников значение равно adventure-works\john5. Когда агент слияния подключается к издателю, он использует имя входа, указываемое при создании подписки (в данном случает adventure-works\john5). Затем агент слияния сравнивает значение, возвращаемое SUSER_SNAME(), со значениями в таблице и загружает только ту строку, которая содержит в столбце LoginID значение adventure-works\john5.

Фильтрация с помощью HOST_NAME()

Рассмотрим таблицу HumanResources.Employee. Предположим, в этой таблице столбец ComputerName содержит имя компьютера каждого из сотрудников в формате Имя_Тип. Чтобы отфильтровать эту таблицу таким образом, чтобы сотрудникам были доступны только относящиеся к ним данные, укажите следующее предложение фильтрации:

ComputerName = HOST_NAME()

Например, для одного из работников значение равно john5_laptop. Когда агент слияния подключается к издателю, он сравнивает значение, возвращаемое HOST_NAME(), со значениями в таблице, и загружает только ту строку, которая содержит в столбце ComputerName значение john5_laptop.

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

LoginID = SUSER_SNAME() AND ComputerName = HOST_NAME()

Если значение HOST_NAME() не переопределяется, фильтрация с помощью HOST_NAME() используется обычно только с подписками по запросу. Значение, возвращаемое функцией — это имя компьютера, на котором выполняется агент слияния. Для подписок по запросу значение отличается для каждой подписки, а для принудительных подписок значения одинаковы (все агенты слияния запускаются на распространителе для принудительных подписок).

Примечание безопасности.
Значение функции HOST_NAME() может быть переопределено, поэтому фильтры, содержащие HOST_NAME(), нельзя использовать для управления доступом к секциям данных. Для управления доступом к секциям данных используйте функции SUSER_SNAME(), SUSER_SNAME() вместе с HOST_NAME() или статические строковые фильтры.

Значение функции HOST_NAME() может быть переопределено, поэтому фильтры, содержащие HOST_NAME(), нельзя использовать для управления доступом к секциям данных. Для управления доступом к секциям данных используйте функции SUSER_SNAME(), SUSER_SNAME() вместе с HOST_NAME() или статические строковые фильтры.

Переопределение значения HOST_NAME()

Как упоминалось ранее, по умолчанию HOST_NAME() возвращает имя компьютера, подключающегося к экземпляру SQL Server. Если используются параметризованные фильтры, это значение обычно переопределяется путем указания значения при создании подписки. В этом случае функция HOST_NAME() возвращает указанное значение, а не имя компьютера.

Примечание.
Если переопределить функцию HOST_NAME(), все вызовы этой функции будут возвращать указанное вами значение. Убедитесь, что другие приложения не зависят от функции HOST_NAME(), возвращающей имя компьютера.

Рассмотрим таблицу HumanResources.Employee. Эта таблица включает в себя столбец EmployeeID. Для фильтрации этой таблицы так, чтобы каждый сотрудник получал только данные, относящиеся к нему, укажите следующее предложение фильтра:

EmployeeID = CONVERT(int,HOST_NAME())

Например, Памеле Ансман-Вольф присвоен идентификационный номер 280. При создании подписки для этой сотрудницы задайте значение идентификационного номера (в данном примере это 280) в качестве значения функции HOST_NAME(). Когда агент слияния подключается к издателю, он сравнивает значение, возвращаемое функцией HOST_NAME(), со значениями в таблице и загружает только ту строку, которая содержит в столбце EmployeeID значение 280.

Важно!
Функция HOST_NAME() возвращает значение типа nchar, поэтому следует использовать CONVERT, если столбец в предложении фильтра имеет числовой тип (как в приведенном выше примере). Из соображений производительности функции к именам столбцов в предложениях параметризованных фильтров строк применять не рекомендуется, например: `CONVERT(nchar,EmployeeID) = HOST_NAME()`. Вместо этого рекомендуется использовать показанный в примере подход: `EmployeeID = CONVERT(int,HOST_NAME())`. Это предложение может быть передано в качестве параметра @subset_filterclause хранимой процедуры sp_addmergearticle, но обычно в мастере создания публикации оно не применяется (мастер выполняет предложение фильтрации для его проверки, что приводит к ошибке, поскольку имя компьютера невозможно преобразовать в тип int). В мастере создания публикации рекомендуется указать `CONVERT(nchar,EmployeeID) = HOST_NAME()`, а затем перед созданием моментального снимка публикации воспользоваться функцией sp_changemergearticle для замены предложения на `EmployeeID = CONVERT(int,HOST_NAME())`.

Важно!

Функция HOST_NAME() возвращает значение типа nchar, поэтому следует использовать CONVERT, если столбец в предложении фильтра имеет числовой тип (как в приведенном выше примере). Из соображений производительности функции к именам столбцов в предложениях параметризованных фильтров строк применять не рекомендуется, например: CONVERT(nchar,EmployeeID) = HOST_NAME(). Вместо этого рекомендуется использовать показанный в примере подход: EmployeeID = CONVERT(int,HOST_NAME()). Это предложение может быть передано в качестве параметра @subset_filterclause хранимой процедуры sp_addmergearticle, но обычно в мастере создания публикации оно не применяется (мастер выполняет предложение фильтрации для его проверки, что приводит к ошибке, поскольку имя компьютера невозможно преобразовать в тип int). В мастере создания публикации рекомендуется указать CONVERT(nchar,EmployeeID) = HOST_NAME(), а затем перед созданием моментального снимка публикации воспользоваться функцией sp_changemergearticle для замены предложения на EmployeeID = CONVERT(int,HOST_NAME()).

Переопределение значения HOST_NAME()

Используйте один из следующих методов, чтобы переопределить значение HOST_NAME().

Среда SQL Server Management Studio: укажите значение на странице Значения HOST_NAME() мастера создания подписки. Дополнительные сведения о создании подписок см. в разделе Подписка на публикации.
Программирование репликации на языке Transact-SQL: задайте значение параметра @hostname хранимой процедуры sp_addmergesubscription (Transact-SQL) (для принудительных подписок) или sp_addmergepullsubscription_agent (Transact-SQL) (для подписок по запросу).
Агент слияния: задайте значение параметра -Hostname в командной строке или с помощью профиля агента. Дополнительные сведения об агенте слияния см. в разделе Replication Merge Agent. Дополнительные сведения о профилях агентов см. в разделе Профили агента репликации.

Инициализация подписки на публикацию с параметризованными фильтрами

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

Использование подходящих параметров фильтрации

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

порядком обработки фильтров репликацией слиянием, которая управляется одной из двух настроек публикации: use partition groups и keep partition changes;
совместным использованием данных подписчиками, которое отражается в настройке статьи partition options.

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

SQL Server Management Studio: Как оптимизировать параметризованные фильтры строк (среда SQL Server Management Studio)
Программирование репликации на языке Transact-SQL: How to: Optimize Parameterized Row Filters (Replication Transact-SQL Programming)

Установка параметров use partition groups и keep partition changes

Оба параметра (use partition groups и keep partition changes) улучшают производительность синхронизации публикаций с фильтрованными статьями путем сохранения дополнительных метаданных в базе данных публикации. Параметр use partition groups обеспечивает более высокую производительность за счет использования предварительно вычисляемых секций. Если статьи публикации соответствуют ряду требований, то по умолчанию этот параметр имеет значение true. Дополнительные сведения об этих требованиях см. в разделе Оптимизация производительности параметризованного фильтра с помощью предварительно вычисляемых секций. Если статьи не удовлетворяют условиям применения предварительно вычисляемых секций, то параметр keep partition changes установлен в значение true.

Установка параметра partition options

Значение свойства partition options указывается при создании статьи в соответствии с тем, как данные фильтрованной таблицы будут совместно использоваться подписчиками. Этому свойству при помощи хранимых процедур sp_addmergearticle, sp_changemergearticle и диалогового окна Свойства статьи может быть задано одно из четырех значений. Свойству можно задать одно из двух значений с помощью диалогов Добавление фильтра или Изменение фильтра, которые доступны из мастера создания публикаций и диалога Свойства публикации. Следующая таблица содержит сводку возможных значений.

Описание	Значение в диалоговых окнах «Добавить фильтр» и «Изменить фильтр»	Значение в диалоговом окне «Свойства статьи»	Значение в хранимых процедурах
Данные в секциях перекрываются, и подписчик может обновлять столбцы, на которые имеются ссылки в параметризованном фильтре.	Строка из этой таблицы будет отправлена нескольким подпискам	Перекрывающиеся	0
Данные в секциях перекрываются, и подписчик не может обновить столбцы, на которые имеются ссылки в параметризованном фильтре.	н/д1	Перекрывающиеся, с запретом на изменение данных вне секции	1
Данные в секциях не перекрываются, и данные используются подписками совместно. Подписчик не может обновить столбцы, на которые имеются ссылки в параметризованном фильтре.	н/д1	Неперекрывающиеся, общие для нескольких подписок	2
Данные в секциях не перекрываются, и на каждую секцию приходится одна подписка. Подписчику не удается обновить столбцы, на которые имеются ссылки в параметризованном фильтре.2	Строка из этой таблицы будет отправлена только одной подписке	Неперекрывающаяся, одиночная подписка	3

1 Если основной параметр фильтрации установлен в 0, или 1, или 2, то диалоговые окна Добавление фильтра и Изменение фильтра покажут Строка из этой таблицы будет отправлена нескольким подпискам.

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

Важно!
Значение partition options должно быть установлено в соответствии со способом совместного использования данных подписчиками. Например, если указать, что секция является неперекрывающейся с одной подпиской на секцию, а данные затем обновятся на другом подписчике, то во время синхронизации может произойти сбой агента, и возникнет расхождение в данных.

Значение partition options должно быть установлено в соответствии со способом совместного использования данных подписчиками. Например, если указать, что секция является неперекрывающейся с одной подпиской на секцию, а данные затем обновятся на другом подписчике, то во время синхронизации может произойти сбой агента, и возникнет расхождение в данных.

Выбор подходящего параметра секции

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

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

В компании Adventure Works имеются мобильные продавцы, причем каждый продавец отвечает за клиентов определенного района (с заданным почтовым индексом). Если клиент перемещается с одной территории продаж на другую, то в соответствии с требованиями приложения почтовый индекс должен обновляться, передавая клиента другому продавцу. Параметризованный фильтр основан на почтовом индексе клиента, поэтому при изменении индекса строка будет удалена из секции одного продавца и вставлена в секцию другого. Для этого нужны перекрывающиеся секции с возможностью обновления столбцов, на которые имеются ссылки в параметризованном фильтре. Этот метод обеспечивает максимальную гибкость, но иногда может давать не столь большую производительность, как неперекрывающиеся секции.
Правительственное агентство имеет данные, которые предоставляет своим региональным офисам в каждом округе штата. Данные не перекрываются, каждая строка таблицы в штаб-квартире агентства включена только в одну секцию, но эта секция отправляется в несколько офисов одного округа. Наиболее подходящим может оказаться метод неперекрывающихся секций, где секции разделяются между несколькими подписками, обеспечивая более высокую производительность по сравнению с перекрывающимися секциями, в то же время удовлетворяя требованиям приложения.
Если имеются неперекрывающиеся секции и только одна подписка получает и обновляет данные в секции, можно реализовать дополнительные преимущества по производительности. Этот сценарий характерен для систем точки продажи и приложений выездной торговли, в которых данные первоначально собираются на подписчике, а затем передаются на издатель. Рассмотрим таблицу Пакет в приложении доставки: при загрузке каждого пакета в грузовик состояние пакета в таблице Пакет изменится, и это изменение будет реплицировано обратно в штаб-квартиру. Водители не обновят состояние одного и того же пакета в двух разных грузовиках, так что таблица Пакет — неплохой кандидат для использования неперекрывающихся секций с одной подпиской на секцию.

Аспекты использования неперекрывающихся секций

При использовании неперекрывающихся секций учитывайте следующие аспекты.

Общие рекомендации

Публикация должна использовать предварительно вычисляемые секции.
Строка должна принадлежать только одной секции.
Статьи не могут быть частью логической записи.
Альтернативные участники синхронизации не поддерживаются (эта функция устарела).
Подписчик не может обновить столбцы, на которые имеются ссылки в параметризованном фильтре.
Если вставка на подписчике не принадлежит секции, она не удаляется. Однако она не будет реплицироваться на другие подписчики.
В некоторых случаях при работе с перекрывающимися секциями диапазоны идентификаторов настраиваются при вставке данных агентом слияния. В случае же неперекрывающихся секций диапазоны при вставках могут настраиваться пользователями, которые обладают разрешениями на настройку диапазонов идентификаторов в базе данных подписок. Пользователь должен быть владельцем таблицы, членом фиксированной серверной роли sysadmin, фиксированной роли базы данных db_owner или фиксированной роли базы данных db_ddladmin.

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

Статьи могут присутствовать только в одной публикации; статьи не могут переиздаваться.
Публикация должна позволять подписчикам инициировать создание моментального снимка. Дополнительные сведения см. в разделе Моментальные снимки для публикаций слиянием с параметризованными фильтрами.

Дополнительные соображения по фильтрам соединения

В иерархии фильтров соединения статья с перекрывающейся секцией не может находиться выше статьи с неперекрывающейся секцией. Другими словами, родительская статья должна использовать неперекрывающиеся секции, если их использует дочерняя секция. Сведения о фильтрах соединения см. в разделе Фильтры соединения.
Фильтр соединения, в котором неперекрывающаяся секция является дочерней, должен иметь свойство join unique key со значением, равным 1. Дополнительные сведения см. в разделе Фильтры соединения.
Статья должна иметь только один параметризованный фильтр или фильтр соединения. Разрешается иметь параметризованный фильтр и быть родительским элементом в фильтре соединения. Не разрешается иметь параметризованный фильтр и быть дочерним элементом в фильтре соединения. Также нельзя иметь более одного фильтра соединения.
Если пара таблиц издателя связана фильтром соединения и в дочерней таблице есть строки, которым не соответствуют никакие строки таблицы-родителя, то вставка недостающих родительских строк не приведет к загрузке связанных строк в подписчик (строки будут загружены с перекрывающимися секциями). Например, если в таблице SalesOrderDetail есть строки, для которых нет соответствий в таблице SalesOrderHeader, то при вставке недостающей строки в таблицу SalesOrderHeader она будет загружена на подписчик, а соответствующие строки таблицы SalesOrderDetail — нет.

См. также

Основные понятия

Оптимальные методы для фильтров строк на основе времени
Фильтрация опубликованных данных
Фильтрация опубликованных данных для репликации слиянием

Справка и поддержка

Получение помощи по SQL Server 2005

Поделиться через