what kind of replication should I use?

Question

I can't understand the difference between transactional replication and merge replication. This is my scenario: In an organization I have a SQL server which need to collect information from different sql servers which are located in different parts of organization or around the city and some report will create according to gathered information. Data in different SQL servers update every 5 or 6 minutes. I don't know should I use transactional or merge replication?

Answer 1

Merge. Each site is a master of it's own data.

Transactional is one way usually.

You need to share information so merge it is...

Edit, after comment

In which case, yes. Your question implies reporting at each location

However, for performance, I'd consider pushing all updates into a queue using a trigger and Service broker. This way, the write to the remote server is decoupled from the local transaction.

Answer 2

Transactional replication delivers incremental changes from a single publisher to one or more subscribers.

Merge replication brings changes from multiple subcribers together into a central publisher.

It sounds like you'll want merge replication in your scenario.