0

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?

4
  • 4
    IS there some reason why you haven't tried using replication?
    – HLGEM
    May 3, 2013 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. May 3, 2013 at 14:08
  • 2
    @Barabba - SQL Server 2005, 2008, 2008 R2, or 2012. Standard or Enterprise?
    – Cougar9000
    May 10, 2013 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, 2013 at 17:24

1 Answer 1

2

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 and acknowledge you have read our privacy policy.

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