Is SQL replication the answer?

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

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.

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