Newbie: which replication strategy should I use?

Hi all
My company is going to open new working centers on different locations of my region. One of the problems we are suffering is that at some locations, the network communications infrastructure is very very very poor. So, in that locations we work with low bandwidths, and connections usually break down.

Because of this, we are thinking in distributing our database. I have been doing some tests on replication, reading the docs, etc... But I am still not sure which replication strategy should we use, and how to organize our database tables to allow replication work properly.

Our offices are going to share some data (a product catalogue, for example) which could be updated an queried from any of the offices. But there is also data which is not going to be shared, as product stocks for each location.

We were thinking on using transactional peer-to-peer replication. But now we are having serious doubts about this since in a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525643&SiteID=1) we were told we can't store non-shared data (stock) in the same table as the shared data (products) due to how database behaves when a publication is restarted. We know it would not be usual to republish, but we were thinking on crash recovery.

It would be fantastic if somebody could help us to decide what should we do to organize our database to allow the use of a proper replication scheme.

Thanks in advance,

Rubn D. M.

Considering your low bandwidths, have you looked at merge replication? It works best in a disconnected environment and over unreliable links as it has retry capabilities built in. you can also run agents at scheduled intervals. This would work provide you don't have low-latency requirement.

As far as your scenario goes where you want to replicate a table but not track changes in certain columns at both ends, this really won't work. In its simplest form you can do vertical partitioning at the publisher side, but that means any columns you choose to not replicate will not exist at the subscriber.

|||Hi Greg,
thank you very much for your help.

I thought transactional replication has also retry capabilities... So, what happens in a "transactional replication environment with updatable queued subscriptions" if the connection between publisher and subscriber is lost? Would it be needed to "restart" the subscription, or it will continue working correctly when connection is stablished again?

Thanks again ;-)

Regards

|||It does, but it's not as robust as merge replication, which is designed for the scenario you described.