One of the common options to synchronize several distributed sql servers to a central Sql server 2005 is by using Remote Data Access Synchronization (RDA).
I needed to check this option for one of my project.
What does it give us? How does it work?
The RDA tries to give us a solution to access data that stores in a central Sql server form local db's (Sql server mobile and Sql server compact edition databases). It includes a tacking engine that stores all changes in the remote db and pushes it later to the central Sql server. This architecture includes a pull option – when the central Sql server updates the locals databases. The synchronization works over HTTP.
So here are some pros vs. cons of the RDA:
Cons:
· Performance:
There is a performance hit when using RDA, because it works with the OleDB provider (The Sql Server data provider gives us a better performances).
· Security:
The RDA isn't giving us a complete solution for security problems. Although local databases isn't opening a direct connection to the central db, all the local db's hold the central db connection string.
· Support & future development:
The RDA is at a maintenance phase, Microsoft isn't planning to develop it any more (But don't worry it will be supported in VS 2008). The next technology is called "Sync Services".
Pros:
· Easy to use\Rapid development:
The API is very simple, just do this sample and you are in.
· Decrease the development cost:
This is a good & tested technology – so why not?
· Protocols supported:
RDA works on the HTTP and HTTPS, you don't need to implement a special protocol over the network layer.
As I mention before in VS 2008 Microsoft develop a new and a better technique to do this job (that called "Sync Services"). It is based on ADO.NET with caching option and completely supported WCF.
Want to learn more? Read Steve Lasker's Web Log.
Want to see who its work? Look in Steve web cast.