Is SQL replication the answer?

Question

Hi,

For our application(desktop in .net), we want to have 2 databases in 2 different remote places(different countries).Is it possible to use replication to keep the data in sync in both the databases while application changes data?. What other strategies can be used? Should the sync happen instantaneously or, at a scheduled time? What if we decide to keep one database 'readonly'?

thanks

Answer 1

You need to go back to your requirements I think.

1. Does data need to be shared between two sites?
2. Can both sites update the same data?
3. What's the minimum acceptable time for an update in one location to be visible in another?
4. Do you need failover/disaster recovery capability?
5. Do you actually need two databases? (e.g is it for capacity, for failover or simply because the network link between the two sites is slow? etc)
6. Any other requirements around data access/visibility?

Real-time replication is one solution, an overnight extract-transform-load process could be another. It really depends on your requirements.

Answer 2

I think the readonly question is key. If one database is readonly then you can use mirroring to sync them, assuming you have a steady connection.

What is the bandwidth and reliability of connection between the sites?

If updates are happening at both locations (on the same data) then Merge Replication is a possibility. It's really designed for mobile apps where users in the field have some subset of the data and conflicts may need to be resolved at replication time.

High level explanation of the various replication types in SQL Server including the new Sync Framework in SQL Server 2008 can be found here: http://msdn.microsoft.com/en-us/library/ms151198.aspx

-Krip

Answer 3

Should the sync happen instantaneously or, at a scheduled time?

I think you need to revisit this part of the question and make a decision on what your going to do. After that, we should be able to answer your question better.