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?
Best Answer
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