With SQL Remote, messages are always sent two ways. The consolidated database sends messages containing publication updates to remote databases, and remote databases send updated data and receipt confirmation messages to the consolidated database.

When remote database users modify data, their changes are replicated to the consolidated database. When these changes are applied at the consolidated database, they become part of the consolidated database's publication, and are included with the updates sent to all remote databases (except the one the update came from). In this way, replication from remote database to remote database takes place via the consolidated database.

For example, if data in a publication at a consolidated database is updated, those updates are sent to the remote databases. Even if the data is never updated at the remote database, confirmation messages are still sent back to the consolidated database to keep track of the status of the replication.

Steps involved in the SQL Remote replication process

1. At each consolidated and remote database participating in replication, there is a Message Agent and a transaction log that manages replication. All committed changes are recorded and stored in the transaction log.

2. Periodically, the Message Agent on the consolidated database scans the transaction log and packages all the committed transactions made to each publication (section of data) into messages. The consolidated database's Message Agent then sends the relevant changes to remote users who subscribe to those publications. The Message Agent sends the changes using a messaging system. SQL Remote supports SMTP email systems, FTP, and FILE.

3. The Message Agent at the remote database accepts the messages sent from the consolidated database and sends a confirmation to the consolidated database that the messages have been received. Then, the Message Agent applies the transactions to the remote database.

4. At any time, a remote user can run the Message Agent to package the transactions made at the remote database into messages and send them back to the consolidated database.

5. The Message Agent at the consolidated site processes the messages from the remote database and applies the transactions to the consolidated database.