Real time syncrhonization of transactions of two database servers

I have a mobile application connecting to a database and would like to do the following.

- Detect the IP address.  If it's within certain range, it will connect to the database server A in country A.  Else, it will connect to the database server B in country B.
- Since the some applications may write data to database of server A and some to that of server B.  I would like to synchronization the changed data on both side in real time.  How can I do that if I'm using MS SQL server?

Many thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For your first question, you would need to use a Geography based dns service using something like GeoAware or AnyCast DNS. This would handle the balancing of traffic to each server based off location. You can check out or for services.

To handle the synchronization your best bet would be to use SQL Server Replication between the two servers. This would allow your clients to update and see real time information on either server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
You can take the advantage of updatable subscription of transactional replication. But this option will not be longer supported in future version of sql server.

If you are using sql server 2005/2008/2008R2 then you can configure it through GUI(using new publication wizard) but not in SQL server 2012. However this option is deprecated in sql server 2012 but still supported and can be configure by using T-sql scripts as new publication wizard does not provide this option.

Also you can consider Peer to Peer replication for the same.

For throughout information please check the below link.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.