Merge Replication Filtering Strategy

• Ken Parchinski

  SSChasing Mays

  Points: 632

  More actions

  March 25, 2008 at 9:48 am

  #180351

  I have an environment that consists of approximately 15 production and test

  2000 and 2005 servers each with 3 (production) to 8 (test) instances. I am

  trying to create a management application whereby one server (central) (SQL

  Server 2005) aggregates information from all "member" servers with the

  central server only pushing member-speciifc data to each member server.

  Changes to these tables can be made from either a member or the central

  server. Each member server instance will only have data populated for itself

  and the central server instance will have all data from all servers. In each

  of the tables to be replicated, a column called ServerID has been created

  with a default value or binding of ([dbo].[fn_AMapGetServerID]()), which

  evaluates to:

  FUNCTION [dbo].[fn_AMapGetServerID]()

  RETURNS [UNIQUEIDENTIFIER] WITH EXECUTE AS CALLER

  AS

  BEGIN

  DECLARE @ID AS UNIQUEIDENTIFIER

  SELECT

  @ID = ServerID

  FROM

  dbo.AMapServers

  WHERE

  (ServerHostName = @@SERVERNAME);

  RETURN @ID

  END

  I created a publication on for these tables on the central server and

  specified ([dbo].[fn_AMapGetServerID]()) as the filter, specified that each

  row could only be replicated to one server, and created a subscription to

  another SQL 2005 member server via a SQL 2005 remote distributor.

  Unfortunately, the filtering is not working as I expected as information from

  each memeber servers is residing on all member servers.

  Long story short, my question is how do I configure the publication so that

  each member server can push its information to the central server and the

  central server pushes only member specific information to each member?

  Thanks in advance.