Synchronisable CRM System

Question

Hi,

I need to develop a CRM system which will allow users to have a local copy of the DB which can then be synched with the main server system. The idea is that a sales team can travel to non-internet enabled areas and still operate with relatively up to date information and then synch up when they get back to the office.

Ive never done something like this before, can anyone recommend a synch solution?

Answer 1

You might want to take a look at the Microsoft Synch Framework. I haven't used it yet, so I cannot give a personal opinion on it though.

Answer 2

Having written just such a system myself, I'd recommend avoiding it if possible. The ways that users can mess up such a system are legion, particularly when the users are the sales team. A CRM system named Act! (you are probably familiar with) has offered such a sync option in the past, and maybe they still do. Whenever my company is selling to a company that has been using Act!'s sync system, the company always complains of corrupted and mis-synced databases.

If you really need to do such a thing, then I'd recommend database-level replication (which wasn't available at the time we wrote our sync application). Many databases offer replication, including MS-SQL Server. You can eliminate many of the problems we experienced by tracking changes at the database level, and making the database responsible for transporting those changes to a remote database.

Answer 3

Why reinvent the wheel? There are many good CRM applications available that offers this functionality. Some are even open-source so you can extend them. But if you just need a client database with offline capabilities and sync then many CRM applications offer that. For just the client database Highrise would be perfect but I think there are no offline/sync features. Maybe look at SugarCRM, 24SevenOffice or Salesforce?

And building a sync application is difficult as there are heaps of issues to resolve with data merging. If you still need to do it then have a look at SyncML which is an open standard for syncing. Some ready to install tools are available from Funambol.

Answer 4

MS CRM 4.0 has offline capabilities when coupled with Outlook:

http://www.microsoft.com/dynamics/crm/product/overview.mspx

Going offline has its own set of oddities (make sure you go offline before you leave your good network connection), but on the whole it's working well here. The mobile client stuff still isn't out, though.

Answer 5

First off - if the CRM is going to be a product you sell, by all measn build it - if it is for internal use I'd say let soemone else endure the pain of solving these questions and just buy a CRM.

Other than database level replication, which I don't think is sufficient for solving everything, you'll really need to 'roll your own'.

I came into a team planning some CRM-like components of a system, and initially they'd planned to use database replication because it looked easy - until you hit the conflict resolution.

In the end we changed to using GUIDs as unique identifiers because it made the handling of new records created by the users much simpler - otherwise you end up with these awkward numeric ID partitioning schemes for each client system. Road to Hell.

Nothing helps with conflict resolution where changes get made between syncs, its an application level concern. The database might get a list of transactions to replay from Bob, but if he edited the same stuff as Alice - which version should it use? timestamps aren't enough because what if Alice fills hers out as she goes and has an early timestamp, but Bob waits till he's having lunch and gets a later timestamp.

I'd really suggest reading all the articles/blog entries you can on synchronisation and conflict resolution - sync is simple from a height of 10,000ft where you can be all hand wavy, but its a different story when you're down in the muck.

Answer 6

Here's my strategy in when I rolls my own synch solution in the past.

1. Limiting the number of change operation that can happened when the system is offline.
2. Avoid using auto increment integer as primary key as this will leads to a nasty problem. In the past, I've assigned every disconnected client with some unique ID, and uses that ID in conjunction to auto generated number to form a nice and readable primary key (Like XXX-YYYYYYYY). This is easy to do because creating a sequential number is trivial in non concurrent environment.
3. Keep a transaction log, such as every DML operation that you do during offline can be "replayed" to the server. You can optimized around this log later when your basic synchronization needs has been fulfilled.
4. You have to make a decision on how to prepare your client application before they go offline. A typical solution is forcing your user to push a button to trigger the application to enter "offline mode". I found this solution to be quite problematic, especially when your user is careless and forgetfull. A quick slap in the head from their managers usually suffice on the early stages, but you can opt to implement more intelligen intelligent solution later.
5. Build my solution from the simplest operation first, and then work my way up to a more complex scenario. (Single table single record operation, multiple tables multiple records operations, series of operation that must acts as a transaction)
6. Make a conflict resolutions scenario. Be sure the scenario is approved by your users.

Needless to say, it's a painful journey. If you can outsource the pain to another party (like purchasing available CRM solutions), that will be the best.