SQL Remote replication is based on the transaction log, enabling it to replicate only changes to data, rather than all data, in each update. When SQL Remote replicates data, it replicates SQL statements that modify data.

Only committed transactions are replicated

SQL Remote only replicates statements in committed transactions. This is done to ensure proper transaction atomicity throughout the replication setup and to maintain consistency among the databases involved in the replication, albeit with some time lag while the data is replicated.

Primary keys

When an UPDATE or a DELETE is replicated, SQL Remote uses the primary key columns to uniquely identify the row being updated or deleted. All tables being replicated must have a declared primary key or uniqueness constraint. A unique index is not sufficient. The columns of the primary key are used in the WHERE clause of replicated updates and deletes. If a table has no primary key, the WHERE clause refers to all columns in the table.

An UPDATE is not always an UPDATE

When a simple INSERT statement is entered at one database, it is sent to other databases in the SQL Remote setup as an INSERT statement. However, not all statements are replicated exactly as they are entered by the client application. This section describes how SQL Remote replicates SQL statements. It is important to understand this material if you are to design a robust SQL Remote installation.

The Message Agent is the component that carries out the replication of statements.