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  settings (are we syncing street lights) -> 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 reduc

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

1. 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).

1. 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)

1. 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_

StackExchange Database Administrators Q#20132, answer score: 4