One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to manage that). But I have some doubts:

  • How can I be sure that my databases are totally merged? I mean, we are in a real time environment with n/sec web and local requests, it's impossible to share table updates/merges with the same speed.

  • Is this a good architecture in spite of a master/slaves one (with an unique DB) topology? I'm pretty sure sync both databases each other will cost too much since our tables store a lot of data in terms of rows and information.

  • Can anyone provide a good solution to build the best architecture possible?

migrated from stackoverflow.com May 3 '13 at 13:26

This question came from our site for professional and enthusiast programmers.

  • 4
    IS there some reason why you haven't tried using replication? – HLGEM May 3 '13 at 13:58
  • @HLGEM our customer has this awful request, we tried to explain that there's no advantage, but he's pretty sure this is the best way to achieve replication and independent clients. – Francesco De Lisi May 3 '13 at 14:08
  • 2
    @Barabba - SQL Server 2005, 2008, 2008 R2, or 2012. Standard or Enterprise? – Cougar9000 May 10 '13 at 14:17
  • 1
    Could you please provide more details about the architecture involved here? What is on these different servers? For what purpose will the data be merged together? Does it really have to be real-time (clients will ask for that whether it's reasonable or not)? – Jon Seigel May 10 '13 at 17:24

This sounds like your customer does not entirely understand what is possible with SQL Server. It is also possible that he is confusing SQL Server with Oracle. With Oracle, you can spin up one database and have two database instances running independantly and operating of the same data set.

With SQL Server, the best you are going to probably do is some sort of Merge replication with two application servers and a distribution database. I would re-visit the customer requirements, determine exactly what he wants, not what he thinks he wants, and not how he wants to do it, and then come up with a solution to provide to the customer.

Unless you or your customer are willing to buy a third party product, some form of SQL Server replication is going to have to be used. Here are some links (2008 R2):

Merge replication

Replication overview

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.