Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 <- regions selected <- STREET LIGHTS -> 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 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.

share|improve this question
    
I am not quite getting the problem here. I understand the rules/limitations of merge replication, but I don't quite get what your relationships are (the arrows aren't very clear) and how that is affecting your sync needs. Could you give a concrete example of a change that cannot be handled correctly by a compliant join filter? –  RBarryYoung Jul 2 '12 at 22:04
    
Let me just say that I am pretty sure that what you need can be accomplished through one technique or another, I just don't follow your problem well enough to know which technique or where to apply it. –  RBarryYoung Jul 2 '12 at 22:07
    
Let me know if that clarifies it for you. I am finding it hard to explain. –  peter Jul 2 '12 at 23:19
    
OK, I think I have it. I should have something later today ... –  RBarryYoung Jul 3 '12 at 14:58
    
What's your tolerance for latency? Can you put the records you want in a another object(s) on some schedule and replicate that(those) object(s) instead of trying to manipulate the whole dataset out of the gate? –  Eric Higgins Jul 3 '12 at 22:36
show 3 more comments

2 Answers

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.

share|improve this answer
    
Hmm, I am not 100% sure that the Trigger INSERTs are entirely correct. They may need a check to insure that the rows they are trying to insert are not already there. But I think they do not need this, because the prior deletes should have handled that... –  RBarryYoung Jul 4 '12 at 16:57
    
Oops, there is a mistake in the last FilterQuery statement (there's no userid in the StreetLight table) fixing ... –  RBarryYoung Jul 4 '12 at 17:34
    
I was thinking something along these lines. I'll look into it in more detail, but this is likely to be perfect. Well done. Do you think this is the only way to solve it? –  peter Jul 4 '12 at 20:31
    
I am not sure where this leaves us. I can only answer questions that are are asked. My post above represents mt answer to the question that you actually asked, also above. I'll go ahead and post the procedure for synching the UserFilter periodically. –  RBarryYoung Jul 5 '12 at 1:27
    
The question has been more than sufficiently answered. I can fill in the gaps and try out the other stuff mentioned. –  peter Jul 5 '12 at 22:22
add comment
up vote 1 down vote accepted

I must put out a warning. We have now moved away from merge replication, and I would have to suggest that the scheme above could be a major performance issue.

The exception to that would require you to have a small publication with either small amounts of filtering and/or filters that are not multiple levels deep. For instance 10 - 100 articles might work fine. If you push the above scheme too much you could have performance / locking issues.

Every time you insert a record into the top filtering table the merge replication trigger has to process all the child tables. It also adds records to MSMerge_contents and MSMerge_genhistory for all the child tables. The more child tables you have, and if they have a big amount of records in them then the more processing power will be required.

We had a problem with sp_MSsetupbelongs being too slow and timing out. In the end we came to the conclusion we were pushing merge replication too much and that this technology would not work for us.

This leads me to suggest that if the filtering scheme in merge replication out of the box is not flexible enough for your situation then either don't filter, or don't use merge replication. Test Test Test though of course, every situation is different.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.