6

Recently my clients have asked me if they can use they’re application remotely, disconnected from the local network and the company server.

One solution is to place the database in the cloud, but a connection to the database, and the cloud and an internet connection must be always available. There not always the case.

So my question is - Is there any database sync system, or a synchronization library so that I can work disconnected with local database and when I connect synchronize the changes I have made and receive changes others have made?

Update:

The application is under Windows (7/xp) ( for now )

It's in Delphi 2007 win32

All client need to have Read/Write access

All Clients have internet connection, but not always ON

Security is not critical, but the Sync service should encrypt the communication

When in the presence of the companies network the system should sync and use the Server Database and not the local one.

DavidG
  • 72
  • 1
  • 1
  • 7
Jlouro
  • 4,515
  • 9
  • 60
  • 91
  • 1
    Yes, many. I think you need to ask a more specific question.... – Mitch Wheat May 27 '12 at 10:20
  • 6
    Conflict resolution is a hard problem. In the end, with a disconnected system, two people could modify the same field in the same record, or do a series of edits which can not be reconciled (delete a record in one place, modify it in another) easily. Disconnected databases ought to have very severe restrictions on what can be done when disconnected, or else, things like Deletion should be replaced with things like Tombstoning, so that synchronization can be made "less difficult". – Warren P May 27 '12 at 16:24
  • 1
    The more I think about it, the more things break. For example, Identity/Autoincrement primary key fields don't work well in a distributed DB where creation of new records is allowed on disconnected clients. You'd need a unique identifier that is globally unique across all disconnected systems, so goodbye autoincremented 32 bit integer identity columns, and hello GUIDs for all primary keys. Pretty much the only thing that works cleanly is edits, and even there, you get conflicts. – Warren P May 27 '12 at 18:24
  • Yes that is why I am asking. I see all that and more. But is there any way manage it or reconcile it after ? – Jlouro May 27 '12 at 21:41
  • Hello @Jlouro! @WarrenP is correct, but for what I have experienced in this problem, there are levels of uncertainty that the system architect is prepared to deal with. I made an answer to this question, witch I use in many systems I sold. Until now it worked fine for my clients for months! – NaN Aug 12 '12 at 13:26

5 Answers5

1

You have a host of issues with thinking about such a solution. First, there are lots of possible solutions, such as:

  • Using database replication within a database, to mimic every update (like a "hot" backup)
  • Building an application to copy the database periodically (every night)
  • Using a third-party tool (which is what you are asking, I think)

With replication services, the connection does not have to always be up. Changes to the database are logged when the connection is not available and then applied when they can be sent.

However, there are lots of other issues when you leave a corporate network. What about security of the data and access rights? Do you have other options, such as making it easier to access the database from within the network? Do the users need only read-access to the database or read-write access? Would both versions need to be accessed at the same time. Would there be updates to both at the same time?

You may have other options that are more secure than just moving a database to the cloud.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

I believe RemObjects DataAbstract allows offline mode and synchronization by using what they call Briefcases. All your other requirements (security, encrypted connections, etc.) are also covered.

This is not a drop-in replacement, thought, and may need extensive rewrite/refactoring of your application. There are lots of upsides, thought; business rules can/should be enforced on the server (real security), scriptable business rules, multiplatform architecture, etc.

Leonardo Herrera
  • 8,388
  • 5
  • 36
  • 66
1

There are some products available in the Java world (SymmetricDS lgpl license) - apart from actually being a working system it is documents how it achieved synchronization. Connects to any db with jdbc support. . There is a pro version but the user guide (downloadable pdf) gives you the db schema plus rules on push pull syncing. Useful if you want to build your own.

Btw there is a data replication so tag that would help.

DavidG
  • 72
  • 1
  • 1
  • 7
0

One possibility that is free is the Microsoft Sync Framework: http://msdn.microsoft.com/en-us/sync/bb736753.aspx

It may be possible for you to use it, but you would need to provid some more detail about your application and operating environment to be sure.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
0

IS it possible to share a database like .mdb and work fine? i try but sometimes the file where the databse is changes from DB to DB1 i use delphi Xe4 and Google Drive . Thank´s

ml.
  • 163
  • 1
  • 4
  • 15