sttke


Hi all,

I think i have a quite specific problem that we have to resolve with replication. however, i don't know which type of replication to use and how to configure it for this:

To simplify the case a bit:
We have one central server which has a table Sales(id, shopid, article).
Also our 50 shops have the same table Sales(id, shopid, article).

spec 1:
When a shop sells an article, the row of the Sales table at the local shop has to be replicated to the central server. This way, the central server its Sales table has all sold articles from all shops.

spec2:
But now we have also an internet shop. All e-orders arrive immediately in the table Sales at the Central Serve with a specific shopid where the article will arrive. Now i have to find a way to replicate the new rows from the Central Server to the specific Shop.... Remark: the other shops should not have rows of other shops.

which type of replication would i use the best and how

Thanks !



Re: what is the best solution?

sttke


I've read that I can use a filter with HOST_NAME or SUSER_NAME in the where-clause, but the problem is that I have to use the shopId's. the shopId's are configured in another table:

ShopId(id, name).

Each shop has one row in this table, containing its shopname and its id name.
The central server has 50 rows in this table, containing all shopnames and id's.

Is there still a way to use merge replication with a filter, based upon the shopId in the table ShopId and Sales

How do I need to do it then (in my book is only the example with SUSER_NAME and HOST_NAME)

Thanks





Re: what is the best solution?

sttke

currently i tried the following and this works fine but is a lot of work to configure.

At each shop a transactional publication for that table to the central server. Thus at the central server i have to create a subscription for each of this publication.
>>> 50 publications (at each shop one)
>>> 50 subscriptions (at the central server).

Then, to forward the e-orders to the specified shop, I created at the central server for each shop a filtered transactional publication (filter on shopId). Thus at each shop i have to install a subscription to each publication. This means again:
>>> 50 filtered publications (at the central server)
>>> 50 subscriptions (at each shop).

In total it means I have to configure 200 publications or subscriptions. This would cost me weeks.

Isn't there an easier solution
(remark, I can not use SUSER_NAME because each PC is the same at each shop, and usses the same login) (the HOST_NAME() i can neither use, because it is not configured in my database and client will not like to add tables).






Re: what is the best solution?

sttke

are there other solutions
it work fine now, but it's soooo much work to configure each server.




Re: what is the best solution?

Greg Y

host_name() should work just fine for you. Merge agent has a parameter -HostName which you can provide a value for, in your case you would set it to the store name for the given subscription. That will allow you to filter using host_name().



Re: what is the best solution?

sttke

Then I also have to add a column with the hostname to my tables , isn't it



Re: what is the best solution?

Greg Y

You already have hostid value in table Shopid, right You filter from this table, and add join filters to the remaining tables.