2

I am not a database experts but part of the project I am currently interested in accepting requires to do a DB replication.

The system will have many outlets... says Site A, Site B, Site C, etc..

All sites will have a local DB, but only one of the site (says Site A) DB will be a central DB..

Other sites will push their data to Site A once a day (through intranet)....and it's only one way replication.. i.e. Site B does not need to receive data/update/etc from Site A or Site C... so basically Site A will be a hub and other sites will be client to Site A and they only need to push their data to Site A....

What kind of SQL Server edition for each of the sites (assuming that the size of the db is not so big - 10GB will be enough) I am thinking to use SQL Server 2008 R2.

Also, What kind of replication does it require? Merge-replication?

from my understanding so far, I need

  • Site A: SQL Server Standard (or Enterprise)
  • Site B, C, etc.: SQL Server Express
  • 4
    Why would you use 2008 R2? It's almost 2015. Why not use a more recent version that might keep you in mainstream support long enough for it to matter? 2008 R2 recently received its final service pack... – Aaron Bertrand Dec 15 '14 at 15:33
3

Indeed Merge Replication seems to be the best fit for this scenario. You can also check Transactional Replication with Updateable Subscriptions with queued updates, but it is less suited for situations where the servers are mostly disconnected, and the conflict detection is quite limited.

You are also correct about the versions you need: Standard or Enterprise for the main server, Express for the subscribers.

0

Features Supported by the Editions of SQL Server 2008 R2 states that SQL Server Express edition can only act as a subscriber. Am I correct in thinking that that sites B & C will actually act as the publishers and site A the subscriber, in which case you will not be able to implement any type of replication. Will any DDL changes be made at site A?

If sites B & C had a standard edition of SQL Server, based on complexity and what you have said then I would recommend that you consider log shipping, replication, mirroring. In that order. You may still be able to implement log shipping even with SQL Server express edition by rolling your own transaction log backup and move code from sites B and C.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.