Client-Server database application: how to notify

Question

Is it possible to know when and if the contents of certain tables in a database has changed? How can my SQL Server notify the client applications that the data was changed by another user? How to implement query notifications with dbGo ?

Do my clients need to poll the database, or is there a callback mechanism for this?

My client is a Delphi application with TADODataSet, and my server is SQL Server 2005/2008, serving multiple clients.

Answer 1

The Delphi dbGo controls does not support Query Notifications, so either poll the database or try to check e.g. some of the following:

• WMI Provider for Server Events - MSSQL Service Broker
• DA-SOFT AnyDAC TADEventAlerter component - Query Notifications mechanism
• Devart SDAC TMSChangeNotification component - Query Notifications mechanism

Answer 2

No, you would have to build your own "middle tier" to do this. The simplest way is to poll the server. A more complex solution is to build your own "middle tier" server that polls the database and pushes notifications to the clients (with your own client connection).

A middle ground solution is to have a separate client on the server poll the tables you need to "watch" and write "notfication flags" into another database table that can be polled by the clients. At least this way the clients can poll a simple table with minimal data that lists what has changed which can then be read by another select query. A lot less effort on the part of the clients.