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

  • Статья

Применимо к:SQL Server

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

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

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

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

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

Параметризованный фильтр строк использует предложение 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. По умолчанию при использовании в параметризованном фильтре она возвращает имя компьютера, на котором запущен агент слияния. Для подписки по запросу это имя подписчика; для принудительной подписки это имя распространителя.

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

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

Примечание.

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

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

Рассмотрим таблицу сотрудников в примере базы данных 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() по умолчанию возвращает имя компьютера, подключающегося к экземпляру SQL Server. Если используются параметризованные фильтры, это значение обычно переопределяется путем указания значения при создании подписки. В этом случае функция HOST_NAME() будет возвращать указанное значение вместо имени компьютера.

Примечание.

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

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

EmployeeID = CONVERT(int,HOST_NAME())

Например, сотруднику Pamela Ansman-Wolfe присвоен идентификационный номер 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()

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

  • SQL Server Management Studio: укажите значение на странице значений HOST_NAME() мастера создания подписки. Дополнительные сведения о создании подписок см. в статье Подписка на публикации.

  • Программирование Репликации Transact-SQL: укажите значение параметра @hostnamesp_addmergesubscription (Transact-SQL) (для push-подписок) или sp_addmergepullsubscription_agent (Transact-SQL) (для подписок на вытягивание).

  • Агент слияния: задайте значение параметра -Hostname в командной строке или с помощью профиля агента. Дополнительные сведения об агенте слияния см. в разделе Replication Merge Agent. Дополнительные сведения о профилях агентов см. в разделе Replication Agent Profiles.

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

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

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

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

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

  • совместным использованием данных подписчиками, которое отражается в настройке статьи partition options.

Чтобы задать параметры фильтрации, см. раздел Optimize Parameterized Row Filters.

Установка параметров 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и диалогового окна Свойства статьи может быть задано одно из четырех значений. Свойству можно задать одно из двух значений с помощью диалогов Добавление фильтра или Изменение фильтра , которые доступны из мастера создания публикаций и диалога Свойства публикации . Следующая таблица содержит сводку возможных значений.

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

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

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

Важно!

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

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

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

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

  • Adventure Works имеет мобильный отдел продаж с каждым сотрудником по продажам, ответственным за клиентов в заданном почтовом индексе. Если клиент перемещается с одной территории продаж на другую, то в соответствии с требованиями приложения почтовый индекс должен обновляться, передавая клиента другому продавцу. Параметризованный фильтр основан на почтовом индексе клиента, поэтому при изменении индекса строка будет удалена из секции одного продавца и вставлена в секцию другого. Для этого нужны перекрывающиеся секции с возможностью обновления столбцов, на которые имеются ссылки в параметризованном фильтре. Этот метод обеспечивает максимальную гибкость, но иногда может давать не столь большую производительность, как неперекрывающиеся секции.

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

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

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

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

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

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

  • Строка должна принадлежать только одной секции.

  • Статьи не могут быть частью логической записи.

  • Альтернативные участники синхронизации не поддерживаются (эта функция устарела).

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

  • Если вставка на подписчике не принадлежит секции, она не удаляется. Однако она не будет реплицироваться на другие подписчики.

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

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

  • Статьи могут присутствовать только в одной публикации; статьи не могут переиздаваться.

  • Публикация должна позволять подписчикам инициировать создание моментального снимка. Дополнительные сведения см. в статье Snapshots for Merge Publications with Parameterized Filters.

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

  • В иерархии фильтров соединения статья с перекрывающейся секцией не может находиться выше статьи с неперекрывающейся секцией. Другими словами, родительская статья должна использовать неперекрывающиеся секции, если их использует дочерняя секция. Сведения о фильтрах соединения см. в разделе Join Filters.

  • Фильтр соединения, в котором неперекрывающаяся секция является дочерней, должен иметь свойство join unique key со значением 1. Дополнительные сведения см. в статье Join Filters.

  • Статья должна иметь только один параметризованный фильтр или фильтр соединения. Разрешается иметь параметризованный фильтр и быть родительским элементом в фильтре соединения. Не разрешается иметь параметризованный фильтр и быть дочерним элементом в фильтре соединения. Также нельзя иметь более одного фильтра соединения.

  • Если пара таблиц издателя связана фильтром соединения и в дочерней таблице есть строки, которым не соответствуют никакие строки таблицы-родителя, то вставка недостающих родительских строк не приведет к загрузке связанных строк в подписчик (строки будут загружены с перекрывающимися секциями). Например, если в таблице SalesOrderDetail есть строки, для которых нет соответствий в таблице SalesOrderHeader , то при вставке недостающей строки в таблицу SalesOrderHeaderона будет загружена на подписчик, а соответствующие строки таблицы SalesOrderDetail — нет.

См. также

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