Real-time synchronization of database data across all the clients

Question

What's the best strategy to keep all the clients of a database server synchronized?

The scenario involves a database server and a dynamic number of clients that connect to it, viewing and modifying the data.

I need real-time synchronization of the data across all the clients - if data is added, deleted, or updated, I want all the clients to see the changes in real-time without putting too much strain on the database engine by continuous polling for changes in tables with a couple of million rows.

Now I am using a Firebird database server, but I'm willing to adopt the best technology for the job, so I want to know if there is any kind of already existing framework for this kind of scenario, what database engine does it use and what does it involve?

Answer 1

SQL Server 2005 and higher support notification based data source caching expiry.

Answer 2

Firebird has a feature called EVENT that you may be able to use to notify clients of changes to the database. The idea is that when data in a table is changed, a trigger posts an event. Firebird takes care of notifying all clients who have registered an interest in the event by name. Once notified, each client is responsible for refreshing its own data by querying the database.

The client can't get info from the event about the new or old values. This is by design, because there's no way to resolve this with transaction isolation. Nor can your client register for events using wildcards. So you have to design your server-to-client notification pretty broadly, and let the client update to see what exactly changed.

See http://www.firebirdsql.org/doc/whitepapers/events%5Fpaper.pdf

You don't mention what client platform or language you're using, so I can't advise on the specific API you would use. I suggest you google for instance "firebird event java" or "firebird event php" or similar, based on the language you're using.

---

Since you say in a comment that you're using WPF, here's a link to a code sample of some .NET application code registering for notification of an event:

http://www.firebirdsql.org/index.php?op=devel&sub=netprovider&id=examples#3

---

Re your comment: Yes, the Firebird event mechanism is limited in its ability to carry information. This is necessary because any information it might carry could be canceled or rolled back. For instance if a trigger posts an event but then the operation that spawned the trigger violates a constraint, canceling the operation but not the event. So events can only be a kind of "hint" that something of interest may have happened. The other clients need to refresh their data at that time, but they aren't told what to look for. This is at least better than polling.

So you're basically describing a publish/subscribe mechanism -- a message queue. I'm not sure I'd use an RDBMS to implement a message queue. It can be done, but you're basically reinventing the wheel.

Here are a few message queue products that are well-regarded:

• Microsoft MSMQ (seems to be part of Windows Professional and Server editions)
• RabbitMQ (free open-source)
• Apache ActiveMQ (free open-source)
• IBM WebSphere MQ (probably overkill in your case)

This means that when one client modifies data in a way that others may need to know about, that client also has to post a message to the message queue. When consumer clients see the message they're interested in, they know to refresh their copy of some data.