Like traditional replication, SQL statement replication is log-based; the information needed to replicate SQL statements (executed in the primary databases) is stored in the transaction log. The log reader, the Sybase Mirror Replication Agent, or other applications read the transaction log to notify Replication Server about modifications to a replicated table.
Replication Server supports a wide range of topologies, including “basic primary copy” models that may include several Replication Servers, warm standby configurations, and multi site availability (MSA) configurations. To use SQL statement replication, you must take into account the underlying Replication Server topology.
In simple MSA or warm standby configurations, source and destination data are identical, and a DML statement executed on the primary table affects the same data set on the standby table.
SQL statement replication applies only to DML statements.
Figure 2-1 shows a Replication Server topology with a single primary database in New York. Tables are replicated to three other sites: London, Tokyo, and San Francisco. All tables are fully replicated.
Figure 2-1: Basic primary copy model: identical data in standby sites
If a client connected to New York executes:
delete t1 where a >5
If this command is executed at Tokyo, London, and San Francisco, the same data set is affected at all the replicated sites, as data is identical in all the sites. In this case, all replicated sites can be configured to use SQL statement replication.
Figure 2-2 represents a system wherein the replicated site Tokyo subscribes only to a subset of data where the site is equal to “Tokyo.”
Figure 2-2: Basic Primary Copy model: nonidentical data in standby sites
Consider the following statement executed at the New York site:
delete t1 where a>5
Replication Servers can execute the same statement in London and San Francisco, but not in Tokyo, as this site subscribes only to a subset of data. If SQL statement replication is used in this case, some replicated databases, like the Tokyo site, receive individual log record modifications from the primary transaction log, based on traditional replication. Other replicated databases, like the London site, receive the SQL statement.
Different sets of data on the primary and standby tables may also be affected when the primary and standby databases have different object schema, or the user executes a DML statement using a join with another table. In these situations, different data is affected on the primary and replicate. The table used for the join cannot be marked for replication, or values in that table may be partial or different from the primary database.
You must activate SQL statement replication in the ASE that holds primary data, and in the Replication Server. Once you enable SQL statement replication on the primary ASE, ASE logs additional information in the transaction log for each executed DML statement for which SQL statement replication was activated. The Mirror Replication Agent or other log readers deliver individual log record modifications and information for SQL statement replication to the Replication Server.
The Sybase Mirror Replication Agent sends SQL statement
replication information for Replication Server 15.2 and later.
ASE does not allow SQL statement replication when the statement may affect a different data set when applied on the standby site.
