Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Replication (2005)  Replication with branches

Author  Topic  dtoffe Starting Member 1 Post Posted - 2012-11-22 : 16:33:56 Hi all, We need to have a replication setup between a central Sql Server 2005 and one 2005 Express in every branch. The database structure is the same, but about half the tables need to be partitioned by branch id. We need non partitioned tables to be download only to the branches, but the partitioned ones we need to have bidirectional synchronization. Either transactional with updatable subscribers or merge would be OK for us, we don't need instant updates, just frequently enough will do. For example assume a table Orders, with a BranchId field. We need the central database to be updated with all the orders originating from all branches, but every branch must only be sync'ed with its own orders. My first plan is to have one publication for each branch, exposing all non partitioned tables as download only, and the partitioned tables filtered by BranchId as in "WHERE BranchId = 'Branch1'", etc. I've gone so far as to have one subscription from one branch working OK, but I can't find a way to create similar publications for the other branches, I get errors no matter what I try except if I don't set a filter by branch, and this is not the behaviour I want. I believe I'm having problems with the correct setting of the Article properties "Synchronization Direction" and "Partition Options", but after trying every possible combination, I'm starting to believe the scenario I'm trying to setup is not possible. Is it possible to have two-way synchronization between Sql Server and Express in 2005 ?? Where can I find more detailed explanation of the articles' properties and the setup of different scenarios similar to the one I'm attempting ? Thanks all, Daniel