How Replication Server Topologies Affect SQL Statement Replication

To use SQL statement replication, you must take into account the underlying Replication Server topology.

Replication Server supports a wide range of topologies, including “basic primary copy” models that may include several Replication Servers, warm standby configurations, and multisite availability (MSA) configurations.

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 Replication Agent, or other applications read the transaction log to notify Replication Server about modifications to a replicated table.

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 replicate table.

Note: SQL statement replication applies only to DML statements.

Identical Data in Replicate Sites

This figure 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.

Basic Primary Copy Model: Identical Data in Replicate Sites
Figure 2-1 shows a Replication Server topology with a single primary server in New York. Tables are replicated to three other sites: London, Tokyo, and San Francisco. All tables are fully replicated.

Figure 2-1 shows a Replication Server topology with a single primary server in New York. Tables are replicated to three other sites: London, Tokyo, and San Francisco. All tables are fully replicated.

Consider the following statement executed by a client connected to the New York site:

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.

Nonidentical Data in Replicate Sites

This figure represents a system wherein the replicated site Tokyo subscribes only to a subset of data where the site is equal to “Tokyo”.

Basic Primary Copy Model: Nonidentical Data in Replicate Sites
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”.

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 replicate tables may also be affected when the primary and replicate 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 may not 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 Adaptive Server that holds primary data, and in the Replication Server. Once you enable SQL statement replication on the primary Adaptive Server, Adaptive Server logs additional information in the transaction log for each executed DML statement for which SQL statement replication was activated. The Replication Agent or other log readers deliver individual log record modifications and information for SQL statement replication to the Replication Server.

Note: The Sybase Replication Agent sends SQL statement replication information for Replication Server 15.2 and later.

Adaptive Server disallows SQL statement replication in situations where the statement may affect a different data set when applied on the replicate site.