1

I'm looking for advice on how to achieve a bi-directional replication which only replicates specific rows in SQL Server 2008.

For example, I would like to replicate Table A between two databases (Europe & Asia). I would like the table to contain both local and shared data. This would be indicated using a field, "region" with values of

  1. Global
  2. Europe
  3. Asia

I've set up a merge replication with Europe being the publisher and Asia the subscriber and a filter of

WHERE region = 1

in an attempt to only replicate rows with region = 1

Adding global rows to Europe or Asia DBs works great with the rows appearing in the opposite table.

Adding Europe rows to the Europe DB works great by not replicating to the Asia DB (as desired)

Adding Asia rows to the Asia DB however doesn't do what I want as the row is replicated to the Europe DB and removed from the Asia DB.

I understand why this would be the case with the publisher / subscriber relationship, but it's frustrating.

I then thought peer to peer might be the solution but I can't filter peer to peer.

Is there a way to achieve what I'm looking for?

Thanks everyone

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mojo
  • 21
  • 3
  • That's not how the filters work - you're not saying "only replicate data for region 1", you're saying "the subscriber should only contain data for region 1". – Damien_The_Unbeliever Mar 12 '12 at 14:27
  • Damien, thanks for the response. So, I could change my filter to be region IN (1,3)? Which would mean the Asia DB would have global and asia data and the Europe DB would have global, europe and asia data. Any better ideas as to how I could achieve what I'm attempting? – Mojo Mar 12 '12 at 15:57

2 Answers2

1

I think that you must use a dynamic filter. When you create a filter you could type more complex queries after WHERE clause. You can see this tutorial, it's in spanish but you can see the query. http://www.monografias.com/trabajos97/guia-crear-replica-mezcla-filtros-dinamicos-parametrizados-sql-server/guia-crear-replica-mezcla-filtros-dinamicos-parametrizados-sql-server.shtml

Dahi
  • 11
  • 1
1

A better way to handle this is to use a 3rd instance to publish the data which contains Global, Europe, and Asia data. Use a Merge publication using parameterized row filters that filter based on HOST_NAME and region 1. Setup the Europe and Asia subscriptions overriding the HOST_NAME() value for each.

This way, the Europe and Asia subscriptions only receive Global and region specific data.

Brandon Williams
  • 3,695
  • 16
  • 19
  • Thanks Brandon... it does beg the question "where to host the 3rd instance?" If it was hosted on the Europe server, for example, wouldn't the Asia users experience potential latency issues? – Mojo Mar 14 '12 at 20:15
  • The Publisher could go in either Europe or Asia. If you think about it in terms of latency, it would be no different from Europe being the Publisher and Asia being the Subscriber. Data still has to flow from Europe -> Asia either way. You are right, the Asia users will experience potential latency issues - but this will always be the case. – Brandon Williams Mar 15 '12 at 05:46
  • What I was hoping to achieve was only having latency on the global data replication. If I set a 3rd instance in Europe, wouldn't Asia data have to be sent across to Europe and then back again? I was hoping to just keep this "local" data on each side and just move the global slice. Perhaps it would be better to split the data into duplicate tables in the DB and then just UNION it when I want the full set and just replicate the global tables? – Mojo Mar 15 '12 at 09:17