Dynamic Filters

Dynamic filters allow you to create a merge publication and then filter data from the publishing table providing different partitions of data to different Subscribers. Benefits of using dynamic filters in merge publications are:

• Fewer publications stored at the Publisher. This reduces the overhead of administering multiple publications.
  
  
• Employing user-defined functions in the dynamic filter enables you to filter criteria.
  
  
• The Subscriber receives only the information needed because data is filtered based onthe connection properties of the Merge Agent for the subscription.

In the dynamic filter, you specify a Microsoft® SQL Server™ 2000 function or a user-defined function that is evaluated differently for each Subscriber based on the connection properties of the Merge Agent when the merge process is replicating data between the Subscriber and Publisher. The most common system functions used for this purpose are SUSER_SNAME() and HOST_NAME(). You can use a user-defined function in a dynamic filter, but unless the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), or the user-defined function evaluates one of these system functions in the filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function will be static.

Dynamic filters are row filters (restricting rows of data) and are created on a single table basis (they do not cross or join tables). You can, however, use both dynamic filters and join filters in the same publication and on the same published tables.

Dynamic filters are available only with merge replication, so when using them, you should consider employing a dynamic snapshot as well. By default, dynamic filtered publications rely on INSERTs from the Publisher to apply data to the Subscriber as part of the initial snapshot. Dynamic snapshots provide the performance advantage of using SQL bulk copy program (bcp) files to apply data to a specific Subscriber when applying the initial snapshot while using dynamic filters. For more information, see Dynamic Snapshots.

If you are using snapshot replication or transactional replication, you can create custom filters using transformable subscriptions, which will filter data based on individual Subscriber requirements. For more information, see Transforming Published Data.

Example

Instead of creating a separate publication for each sales representative, Northwind will use the SUSER_SNAME() function in the dynamic filter on the CUSTOMERS table article to return the user ID of the sales representative assigned to each customer and filter published data based on it. The SALES_REP column could be added to the CUSTOMERS table to identify the sales representative responsible for servicing each customer.

The CUSTOMERS table at the Publisher.

The row filter for the CUSTOMERS article in the Northwind merge publication is:

WHERE SALES_REP = SUSER_SNAME()

If the merge process is initiated using the WestRegion\Robert King integrated security account, the SUSER_SNAME() function evaluates to this account in the dynamic filter only when the Merge Agent is run by the user WestRegion\Robert King. As a result, Robert King receives only data regarding the customers for which he is assigned as a sales representative.

The CUSTOMERS table at the Subscriber after using the dynamic filter when publishing data.

The behavior of dynamic filters is different depending on whether you use Windows Authentication or SQL Server Authentication. With SQL Server Authentication, the –PublisherLogin parameter specified in the Merge Agent command line (or PublisherLogin property in the SQL Merge ActiveX® Control) is the key property returned when using SUSER_SNAME() in a dynamic filter.

With Windows Authentication, SQL Server Agent initiates the merge process and the SUSER_SNAME() function in SQL Server 2000 returns the account under which the SQLServerAgent service is running. This may be different from the Microsoft Windows NT® security account of the user. If the merge process is initiated using the Microsoft ActiveX control or by calling Replmerge.exe independently of SQL Server Agent, the SUSER_SNAME() function in SQL Server 2000 returns the login account of the user.

When using dynamic filters, the filtering logic expression is evaluated within the context of the merge connection to the Publisher, not the connection to the Subscriber. If the merge process uses the SQL Server 2000 login Janet Leverling to connect to the Publisher, and the sa login to connect to the Subscriber, the SUSER_SNAME() function will evaluate to Janet Leverling in the filtering logic.

The CUSTOMERS table at the Subscriber (using the dynamic filter).