Working Around Filtering Problems in Merge Replication I am in the process of defining filters on a merge replication publication for our database.

The problem I am coming up against is that merge replication has these rules, I don't like to call them limitations because I can understand the purpose.

1) When creating an article filter you cannot include a subquery, or at least you shouldn't. If you do then it will appear to work the first time you sync, but if something changes in the subquery table the filter will not be re-evaluated.

2) When using a join filter you can only join two tables together.

The problem I am coming across is that the relationships in our database are more complicated than that. For instance here is one of our relationships,

user &lt;- regions selected &lt;- STREET LIGHTS -&gt; settings (are we syncing street lights) -&gt; user


Here is an example table structure to explain the above in more detail,

User Table,

Id    Username
1     petermc


UserRegion Table, (think of this as a security table, which region is a particular user allowed to see)

Id    UserId    RegionId
1     1         2
1     2         4


SyncSetting Table,

Id    UserId    Table
1     1         StreetLight


Region Table (this is just a look up table to show some example regions)

Id    Region Name
1     North Auckland
2     South Auckland
3     Central Auckland
4     Great Barrier Island


Road Table,

Id    RegionId    Name
1     1           Rosedale Rd
2     1           North Shore Rd


StreetLight Table

Id    RoadId    Last Replaced
1     1         2012-05-01
2     1         2009-06-03
3     2         2001-06-08


So in this case if I wrote out a select statement to apply my filtering for petermc it would look like this,

select * from StreetLight where
roadId in (select Id from Road where RegionId in (select regionId from UserRegion where userid = 1))
and exists (select 1 from syncsetting where userid = 1 and [table] = 'StreetLight')


So I am doing two things there. First filtering based on region to reduce a very large table into a smaller more manageable subset.

The second is specifying whether the subscriber is interested in the StreetLight table at all. If they are not then the subscriber should have an empty StreetLight table. This part is important because the publication will have a large number of tables in it, so it doesn't make sense to include stuff that the subscriber is not going to use.

Our biggest database has millions of records in some tables, and there will be a moderate amount of updates of these records too. We must get this filtering correct. The option to not filter those tables is not feasible.
This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead.  This UserFilters table would need to be maintained by triggers on the constituent tables so that when rows in Road, UserRegion or SyncSetting change, the rows in UserFilters would be automatically changed to match.  I will demonstrate how to do all of this below.

First a note; This table (UserFilters) is a de-Normalized table that is adjunct to your normal application data schema.  That is, it is an operational artifact that is only used to manipulate the Replication technology into doing what you want, and is not part of your application's data design/schema.  This use of denormalization is considered acceptable as it is solely to address technology limitations and is not directly used by your applications.

OK, here's how to do it:

1. Write Your Desired Filter Query In JOIN Form:

Here's your original filter query (unrolled, to make it easier to decompose):

select  * 
from    StreetLight 
where   roadId in 
    (
    select  Id 
    from    Road 
    where   RegionId in 
        (
        select  regionId 
        from    UserRegion 
        where   userid = 1
        )
    )
  and   exists 
    (
    select  1 
    from    syncsetting 
    where   userid  = 1 
      and   [table] = 'StreetLight'
    )


We want to re-write this into a format with only JOINs and WHEREs, but no subQueries.  (This can always be done but I won't go into how in this article.)  Like so:

SELECT  StreetLight.* 
FROM    StreetLight 
JOIN    Road        ON  Road.Id             = Streetlight.roadId
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
                    And syncsetting.[table] = 'StreetLight'
Where   UserRegion.userid = 1


2. Create A View  Consolidating All of the Filter Conditions

We use the JOIN clauses above to make a view that contains only the columns relevant to the Replication Filter, and that projects all of the rows necessary to completely describe all of the filter conditions.  This is easier than it sounds, it should look like this:

CREATE VIEW vwUserFilters As
SELECT
        Road.Id             As RoadId,
        Road.RegionId       As RegionId,
        UserRegion.userid   As UserId,
        syncsetting.[table] As syncTable
FROM    Road
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
--  Comment the next line to generalize for all tables
--                  And syncsetting.[table] = 'StreetLight'


Note that we have also generalized the View's expression to cover all users.  Also, we have commented out the SyncSetting "[table] = 'StreetLight'", in order to generalize this for all tables covered by SyncSetting and Road (which may or may not be valid, you'll have to decide that).

3. Create The UserFilters Table

Create the UserFilters table using the view as a guide for the columns.  However, we'll want to add an IDENTITY column for key/index performance reasons:

CREATE TABLE adjUserFilters 
(
    filterId    INT Identity(1,1) PRIMARY KEY,
    RoadId      INT         NOT NULL,
    RegionID    INT         NOT NULL,
    userid      INT         NOT NULL,
    syncTable   VARCHAR(32) NOT NULL
)


Then populate it using the View:

INSERT INTO adjUserFilters 
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters


For performance and locking reasons you'll want a lot of index paths.  I cannot say for sure what they should be, but here's what I would start with:

CREATE UNIQUE INDEX IX_adjUserFilters 
    ON dbo.adjUserFilters(userid, syncTable, RoadId)
CREATE INDEX IX_adjUserFilters_1 
    ON dbo.adjUserFilters(RegionID, userid)
CREATE NONCLUSTERED INDEX IX_adjUserFilters_2 
    ON dbo.adjUserFilters(RoadId, userid)


4. Add Maintenance Triggers to the Constituent Tables

You will need to add triggers to the Road, UserRegion and SyncSetting tables to keep the contents of [adjUserFilters] in synch with these tables when they are modified.  They should look like this:  First the Road table:

CREATE TRIGGER dbo.trRoad_MaintainUserFilters_IUD 
   ON  dbo.Road
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   RoadId IN(Select d.Id From deleted As d)

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  ON i.Id = v.RoadId
END


The UserRegion table:

CREATE TRIGGER dbo.trUserRegion_MaintainUserFilters_IUD 
   ON  dbo.UserRegion
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d 
        Where   d.UserId    = adjUserFilters.userid
          And   d.RegionId  = adjUserFilters.RegionId
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  
        ON  i.UserId    = v.userid
        And i.RegionId  = v.RegionId
END


And finally, the SyncSetting table:

CREATE TRIGGER dbo.trSyncsetting_MaintainUserFilters_IUD 
   ON  dbo.syncsetting
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d 
        Where   d.UserId    = adjUserFilters.userid
          And   d.[table]   = adjUserFilters.syncTable
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable 
    FROM    vwUserFilters As v
    JOIN    inserted      As i  
        ON  i.UserId    = v.userid
        And i.[table]   = v.syncTable
END


5. Create Your Replication Filter Query

Now you can create your Replication Query Filter for "user1" like so:

select  StreetLight.* 
from    StreetLight     As t
JOIN    adjUserFilters  As f  
    ON  f.syncTable = 'StreetLight'
    And f.RoadId    = t.RoadId
Where   f.userid    = 1 
-- (corrected)




As requested, this is the script you would use to synchronize the adjUserFilters on a periodic basis, instead of from Triggers:

DELETE FROM adjUserFilters;

INSERT INTO adjUserFilters 
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters


Off hand, I am not sure how you would insure that this runs right before or as part of the Merge Synchronization Job, but it probably can be done.