Push-based synchronization with Sync Pull Notify

The advanced replication feature offers pull-based synchronization in which the replica decides when to pull a copy of updated data from the master. However, in many situations, users might prefer a push-based model, where the master determines when to send data to the replicas. For example, in a push-based model, the master can send data as soon as that data is updated, rather than waiting for replicas to request an updated copy.

The advantage of the pull-based model is that it tends to semi-randomly distribute requests to the master; this keeps the master from being overloaded with a large number of update requests at once. The pull-based model also works well when replicas are connected to the network only irregularly. For example, data on mobile devices might need to be synchronized only occasionally. The advantages of the push-based model are that out-of-date data is updated rapidly and replicas do not have to waste time polling for new data when none has arrived.

The advanced replication feature provides push-based synchronization using the Sync Pull Notify capability. With the Sync Pull Notify capability, the master notifies the replica that there is updated data, and the replica can then choose whether to download the updated data.

The Sync Pull Notify capability is implemented by using a combination of the following functionality:

▪START AFTER COMMIT statement, which specifies an SQL statement (such as a call to a stored procedure) that will be executed when the current transaction commits. For example, if a specific transaction updates information on the master, then the START AFTER COMMIT can tell the master to do a remote procedure call to the replica after the update has been successfully committed.

▪Remote stored procedures, which provide a way to call a stored procedure in another database. Specifically, a stored procedure is created on the replica, and the master calls that stored procedure. For example, the replica might create a stored procedure that contains a REFRESH command, and the master can then call that procedure, thus causing the replica to request the refresh.