Merge Replication (push). Don't want subscriber updating publisher

Question

Merge Replication (push). Don't want subscriber updating publisher

tvp1125

Old Hand

Points: 383
More actions
December 12, 2008 at 2:34 pm

#197382

I need help with merge replication. Can we use push without the subscriber updating the publisher?
If I add records to the subscriber table, they are copied over to the pub.
logistic Scenario: we have 3 remote offices. each office has 2 sqlservers (server A and B). We want to use one sql at corp. office for reporting (server Z). all use a single table copied across all servers for this example. Data is inserted into both servers A and B at different stations throughout the office.
We want server A to push data to Server B. Corp Server Z cannot access Server A so Server B holds all the office data we need. then I want to pull the data into Corp. At Server Z from the three offices.
I have a merge repl, for (A) to push to the subscriber (B), I have the subscription destination object set to Download to subscriber, prohibit subscriber changes.
The problem right now is Server A publishes to B, but then the data inserted into Server B data is replicated to Server A. So we have the same data in both Servers.
I just want a one-way From A to B. no further updates I only want Server A to push new records.
Any ideas?
thanks in advance
Thomas

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 1

Repeating what I think you want to make sure I understand: Server A only holds the data that was entered at Server A, but data can be inserted\updated\deleted on A or B. Z is a central subscriber to three of the A/B combinations so it's got a copy of everything. What I didn't catch is if a change made to A/B in one office should show up at the other two remote offices or just back at server Z.

Nevertheless, for the A/B bit of it you can make server B your publisher and use a parameterized row filter to control what gets sent back to Server A. Start by reading these BOL articles:

Parameterized Row Filters

Snapshots for Merge Publications with Parameterized Filters

I set up a test to answer this question and here's the important things worth noting:

1. I used HOST_NAME() in my WHERE clause for the row filter, like this: WHERE HOST_NAME() != 'Server A' Substitute 'Server A' for your server's real name (really you can use any value you want because when you set up your subscription you'll have the chance to override the value used for HOST_NAME()). When I did it this way any data entered into Server A was uploaded back to server B and then deleted from Server A (because of the filter), so you will probably be better off if you have a column that you can use for the filter; your best bet would be a column that holds the name of the server where the data was entered, then filter on that column.

2. I set my articles to bidirectional and used "overlapping" for the partition option. If your partition option is set to non-overlapping you'll get conflicts when you enter data into Server A.

3. In the publication properties, data partitions page, select the checkbox to automatically define a partition and generate the snapshot when a new subscriber tries to synchronize. That will create subscriber specific snapshots for each distinct parameter used in the row filter.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

tvp1125 Old Hand Points: 383 More actions · Answer 2

kendal.vandyke (12/14/2008)
Repeating what I think you want to make sure I understand: Server A only holds the data that was entered at Server A, but data can be inserted\updated\deleted on A or B. Z is a central subscriber to three of the A/B combinations so it's got a copy of everything. What I didn't catch is if a change made to A/B in one office should show up at the other two remote offices or just back at server Z.
.

first ..thanks for the response...

No we don't want changes (Server A /B) to existing data to show up (Server Z). We only want new records to move. There are no changes as far as updating. The data is results of software tests. These records are inserted basically as pass or fail after each test. If it fails it is reworked and tested again.

we want all data going one way ----> A---->B----->Z.

We want the results sent to corporate (server Z) and then purge the office data (Server A and B) every x number of days.

But if data purged at A and B , purges the data at Z ( and B) then maybe replication is not best for what we're trying to do.

If I understand your sugesstion for A/B, it is to move the publication from A to B and do a pull instead of a push.

I will read the links you provided and let you know.

Any other tips are welcome.

Thanks

Thomas

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 3

So the B servers don't need to know anything about the data that's been entered at the other B's?

Are you bound to a particular schema, or do you have the flexibility to introduce schema changes to help your situation out?

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

tvp1125 Old Hand Points: 383 More actions · Answer 4

kendal.vandyke (12/15/2008)
So the B servers don't need to know anything about the data that's been entered at the other B's?
Are you bound to a particular schema, or do you have the flexibility to introduce schema changes to help your situation out?

You are correct about the B servers..they don't need to know. Flow is like:

office 1: A--->--->B ----> |

office 2: A--->--->B ----> | --> Z

office 3: A--->--->B ----> |

Office location is one of the fields in the tables that local reports are filtered by.

Yes I do have some flexibility at this time.

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 5

What you're describing is a central subscriber model that sounds more appropriate for transactional replication. If you're using identity columns as your primary key then you have two choices to avoid overlapping ranges on your B and Z servers:

1) Use DBCC CHECKIDENT to seed each table with a value that gives it a range to use (e.g. server A starts at 1, server B starts at 1 million)

2) Add a column that represents the servername that the data was entered on, give it a default of @@servername, and add the column to your primary key.

In either scenario you'd set up a publication on Server A with Server B as the subscriber, then another publication on Server B with Server Z as the subscriber. Each Server B is known as a publishing subscriber and Server Z is your central subscriber.

When you set up your publications, set the article properties to "Keep existing object unchanged" for when the name is already in use - that keeps you from deleting any data on servers B and Z when you add them as subscribers.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

tvp1125 Old Hand Points: 383 More actions · Answer 6

kendal.vandyke (12/15/2008)
What you're describing is a central subscriber model that sounds more appropriate for transactional replication. :
....

I tried some other setting, but still no success. I am now going to test/try using tranactional.

thanks

Thomas

tvp1125 Old Hand Points: 383 More actions · Answer 7

Kendal, Transactional seems to fit my needs best.

thanks very much for your help.

I'm sure I'll have other questions.

Thanks again

Thomas

tvp1125 Old Hand Points: 383 More actions · Answer 8

kendal.vandyke (12/15/2008)
....2) Add a column that represents the servername that the data was entered on, give it a default of @@servername, and add the column to your primary key.
.....

Kendal, I am going with this option.

With Transactional one of the property setting is Manage Identity. The default is manual.

Shoud I change this to automatic? If I have the id and server as set as primary key should it matter?

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 9

No, leave it manual. Identity Range management only applies to Merge and Transactional with Updateable Subscribers. BOL details how to manage identity columns in replication here: http://msdn.microsoft.com/en-us/library/ms151736(SQL.90).aspx

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

tvp1125 Old Hand Points: 383 More actions · Answer 10

Thanks for the info and the help.....

Transactional was the way to go for my situation.

this website has been few useful in the short time I've been here.

thanks to all:)