Bye, bye, ASE Replicator...
Update: As of ASE 15.7, ASE Replicator is end-of-life'd, and no longer part of the ASE installation. For data replication, use Sybase Replication Server instead (with hindsight, that was always a better idea anyway).
With the release of ASE 126.96.36.199 IR in March 2002, a new ASE feature called "ASE Replicator" has become available to ASE users.
This document covers some of the basics of ASE Replicator.
What is ASE Replicator ?
"ASE Replicator" is a new ASE feature for implementing data replication. ASE Replicator was introduced in ASE 188.8.131.52 IR (released 15 March 2002). ASE Replicator is available on most platforms, but not currently on Linux (to use ASE Replicator on Linux anyway, go here). ASE Replicator supports ASE-to-ASE, log-based,publish/subscribe replication of DML and stored procedures.
The big advantage of ASE Replicator is that it's free: it is an ASE feature which comes with ASE at no additional cost. Also, ASE Replicator is quite simple to use. For these reasons, ASE Replicator can be a useful solution for relatively simple, low-volume data replication requirements.
Note that ASE Replicator is not intended as a replacement for RepServer.
While the underlying principles of ASE Replicator are very similar to those of Sybase Replication Server, they're aimed at different types of replication systems:
ASE Replicator is really a "light-weight" replication solution; in contrast, RepServer is the product you need for doing enterprise-level replication. So when there's a high volume of replicated transactions, or when performance is very important, you should probably not try to do this with ASE Replicator (also see the section on performance below).
How does ASE Replicator work ?
In a nutshell, ASE Replicator works as follows:
- The actual ASE Replicator engine is a Java application which runs as a separate program outside ASE, and which must be started explicitly. This program is located in $SYBASE/RPL-12_5.
(BTW: the letters "RPL" or "RL" appear in various places when you're working with ASE Replicator; these come from the name "RepLite", which seems to be the name under which it was originally developed).
- When ASE Replicator is running, it will scan the transaction log of all primary databases and pick up transactions which must be replicated. This is essentially the same as the way RepServer does it (using dbcc logtransfer). The issues with the secondary truncation point are also similar.
- Transactions which have been picked up are stored in the "Distribution Database" (an ASE user database exclusively used by ASE Replicator), specifically, in the "shadow table" for the corresponding primary object (this is a table with the same schema as the primary object, containing all replicated columns; to find the name of the shadow table for a primary object, run sp_helpprimaryart)
- In the Distribution Database, a transaction is 'copied' to all replicate databases which have a subscription for this transaction. This has been implemented by creating a proxy table for each replicate table (=each subscription); a transaction is applied to the replicate table by inserting it into the proxy table (to find the name of the proxy table for a replicated object, run sp_helpreplicateart).
The ASE Replicator Userís Guide is part of the 184.108.40.206 installation and is located in $SYBASE/docs/rl125ug.pdf. The online version is located at
Configuration and administration
ASE Replicator can either be configured manually using a set of stored procedures which are located in the Distribution Database. This can also be done through Sybase Central; the Java version that comes with 220.127.116.11 has a wizard-style plug-in for managing ASE Replicator (I personally prefer the non-GUI method, but that's probably because I have a RepServer background...). When you wan to use the GUI in Sybase Central, make sure you use the right ASE login (see the Tips & & Troubleshooting section below for more information).
One of the attractions of ASE Replicator is that it's quite easy to set up and configure: starting from scratch, I had a working replication system (albeit with very simple primary and replicate tables) in less than 30 minutes. The setup procedure is described quite well in the ASE Replicator Userís Guide (see above). As always with replication, make sure you have a clear idea of the replication logic you want to implement before you start...
Features of ASE Replicator vs. RepServer
Because ASE Replicator and RepServer are both replication products, there's quite a bit of similarity between them.
For example, both use log-based replication (ASE Replicator uses dbcc logtransfer, like the good-old LTMs), and both have a publish/subscribe replication model.
There are also some major differences with RepServer, however. These are some RepServer features for which ASE Replicator has no direct equivalent:
- Probably most importantly, ASE Replicator is not only free, but also relatively easy to use. This is a difference with RepServer, which is known to be.... well, a beast.
- ASE Replicator does not support a "warm standby" (i.e. replicating all activity in an entire database).
- ASE Replicator does not support DDL replication, and also does not replicate truncate table.
- ASE Replicator does not have function strings or error classes.
- ASE Replicator only supports ASE-to-ASE replication (having said that, as the interface to a replicate table is a proxy table, it should technically be possible to replicate to a table in, for example, an Oracle database; however, this would require DirectConnect for Oracle, which is a separate (and not free) Sybase product).
- ASE Replicator does not support routes like RepServer.
- ASE Replicator does not support parallel DSIs.
Architecture of ASE Replicator vs. RepServer
Although ASE Replicator and RepServer are implemented rather differently from a technical point of view, there are some similarities in the overall architecture:
- The replication "engine" itself: ASE Replicator is a Java application located in $SYBASE/RPL-12_5; RepServer is an OpenServer application located in $SYBASE/REP-12_x.
- Picking up replicated transactions is done via dbcc logtransfer by both (actually, the internal ASE RepAgent threads don't use this dbcc command, but something very similar; the old-style external LTMs indeed use dbcc logtransfer). However, while RepServer uses a separate Replication Agent inside ASE to perform this function, the ASE Replicator process performs this function directly itself.
- RepServer stores its transactions in stable queues; ASE Replicator stores this in various tables (among others, a table called tran_log, plus one table (the "shadow table") for each primary object, in the Distribution Database).
- The ASE Replicator equivalent of the RepServer DSI (dataserver interface) is a proxy table in the Distribution Database (there's one proxy table for each subscribed replicate table).
- The ASE Replicator equivalent of the RepServer RSSD is a set of tables in the Distribution Database (DDB). Each ASE Replicator instance requires its own DDB.
- ASE Replicator uses a table called rl_lastcommit in each replicate database to keep track of replicated transactions. RepServer uses a table called rs_lastcommit for the same purpose. Because these tables are different, a database can be a replicate database for both ASE Replicator and RepServer at the same time (in contrast, a database can be primary for only ASE Replicator or RepServer, because there is only one seondary truncation point in a database).
Performance of ASE Replicator vs. Replication Server
ASE Replicator is a free utility that comes with ASE 12.5. Given its architecture (an external Java application, and using CIS for connectivity), it seems unreasonable to expect screaming performance from ASE Replicator. However, ASE Replicator is very new, and there isn't much performance-related information from practical applications yet.
Out of curiosity, I have therefore conducted a performance test myself, comparing ASE Replicator and RepServer under identical circumstances.
This comparison should not be seen as a full-blown benchmark, but only as a rough indication of the relative capabilities of both products:
- The setup was as follows: on one ASE server, two primary databases were created, each containing the same table (with a fixed row size of 118 bytes). These tables were then replicated to a third database; for one table, replication was done by Replication Server, and for the other by ASE Replicator. The maximum sustained throughput was then measured for each of these replication systems (during which the other replication system was shut down).
- The results: with RepServer, a maximum of 150 rows per second (end to end) could be replicated before latency started to increase; ASE Replicator was capable of replicating a maximum of 4 rows per second. While these absolute numbers are meaningless, it clearly indicates that there is a difference in capability between RepServer and ASE Replicator, because all other components (hardware, network, OS and ASE server) were exactly identical for both tests.
- During the tests, there also appeared to be a strong difference in the minimum latency: when using Replication Server, the latency was never more than 2-3 seconds; with ASE Replicator, the latency was always 2-3 minutes at least, even during tests where only very few rows were inserted in the primary table. This may be related to the behaviour of ASE Replicator, which seems to "wake up" only every now and then to processes replicated transactions (although setting the parameter scan_sleep_max to 5 (default=60) did not seem to make a difference), while Replication Server processes transactions in a more continuous fashion; this suggests that the scalability of ASE Replicator is significantly less than that of Replication Server.
Summarising, it seems that when you have a significant volume of transactions, many primary and/or replicate databases, or high performance requirements, ASE Replicator is probably not the appropriate replication solution, and Replication Server should be considered instead.
- My conclusion: the performance which can be achieved with Replication Server is at least one, and possible two, orders of magnitude larger what is possible with ASE Replicator.
Another way of looking at this is that ASE Replicator is free (and RepServer certainly is not), so you should probably adjust your expectations with this in mind...
Some things to be aware of
Here are some things you should keep in mind; some of these points are not described clearly, or not at all, in the ASE Replicator documentation:
- All stored procedures needed to manage ASE Replicator from the command line (such as sp_configrep and sp_addprimaryart) exist in the Distribution Database only, so all these procedures must be executed from within the Distribution Database.
- A step is missing from the setup procedure described in the ASE Replicator Userís Guide: in the section "Setting up the sp_helpddb system procedure" (page 26/27), it is assumed that the 'rep_user' login has been added to the sybsystemprocs database, but this is not described explicitly. Therefore, execute "sp_adduser rep_user" in the sybsystemprocs database before executing the actions described in this section of the user guide.
- In the "Setup" chapter, the ASE Replicator Userís Guide regularly specifies queries which are invalid T-SQL syntax. For example, it often mentions things like this:
sp_adduser rep_login, rep_user
Obviously, this wouldn't work when executed literally. You'll need to stick in a few go commands like this:
sp_adduser rep_login, rep_user
- In the "Setup" chapter, the ASE Replicator Userís Guide does not mention that you need to grant create procedure permission to the ASE Replicator user in each primary database -- if you don't grant this permission, there will be an error as soon as you execute sp_addprimaryconn to add a primary database.
- In chapter "Administering ASE Replicator", the ASE Replicator Userís Guide specifies incorrect syntax for specifying connections, because essential quotes are omitted. For example, the docs seem to suggest the proper syntax should be sp_addprimaryconn MY_SERVER.my_db, but this should really be sp_addprimaryconn 'MY_SERVER.my_db" instead.
- When you create your replicate objects before creating the replicate articles (which is recommended, see below), you must grant select permission to the ASE Replicator user on the syspartitions table in the replicate database; otherwise sp_addreplicateart will fail (this is not in the documentation).
- Important (BIG gotcha!): When creating the replicate articles for a table publication, and the replicate table does not yet exist, it will be created as part of sp_addreplicateart. While this seems very convenient, it's actually also very dangerous: if you ever (most likely, *much* later) drop the replicate article, the replicate table will be dropped as well!. This is probably not what you expected -- and it will probably cause trouble because other applications expect that just-dropped table to exist (not to mention the data in the table that's gone as well).
Even though this behaviour is documented, do yourself and your company a favor and avoid all possible confusion here: first create your replicate tables, then create the replicate articles.
- On NT, ASE Replicator is currently not installed as an NT service (like RepServer), so it will always run in the foreground in a DOS box. This means that you cannot log out the NT user, because all its foreground processses, including ASE Replicator, will then be terminated!
Tips & Troubleshooting
When you're in trouble (or when trying to avoid getting there), try these tips:
- Get the latest patch for ASE 12.5.x. You'll need the latest bugfixes for ASE Replicator problems.
- Before running ASE Replicator, make sure you have installed the most recent EBF for jConnect EBF (EBF 10000 is recommended in the cover letter of ASE 18.104.22.168). Installing this EBF may save you some problems.
- When creating the Distribution Database (DDB), make it big: there will be a lot of activity in this database, and there should be sufficient space for both the data and the log.
There is much more activity in the DDB than in the RSSD in a RepServer system: the equivalent of the RepServer stable queues is tran_log table plus the shadow tables in the DDB, so the full volume of replicated transactions passes through the DDB. At some point, the rows in these tables are deleted again, causing more load on the DDB. As part of all this processing, there will also be a lot of transaction log activity in the DDB, so I'd recommend using as much space for the log as for the data.
How much space you'll need depends on the volume of your replicated data, but I'd stay on the safe side: 100Mb data + 100Mb log would be a good starting point.
- A common problem is the following: in the Distribution Database, you execute an ASE Replicator stored procedure (for example sp_shutdownrep or sp_configrep), and you get an error message like this:
Msg 11216, Level 16, State 1
Server 'S125', Line 1
Internal Ct-Lib/Cs-Lib error 67175468:
'ct_connect(): protocol specific layer:
external error: The attempt to connect
to the server failed.'.
Msg 11206, Level 16, State 1
Server 'S125', Line 1
Unable to connect to server 'ASEREP'.
(return status = -6)
When this error occurs, this is because the ASE Replicator stored procedure is not executed by the dedicated ASE Replicator login:
this ASE login, and its password, is specified (with the "-u" and "-p" command-line parameters at the ASE Replicator startup) when the ASE Replicator process is started.
ASE Replicator will only accept RPCs originating from this login (all those ASE Replicator stored procs issue an RPC),
and the above error will result for all other logins, including the "sa" login.
Therefore: remember to execute all ASE Replicator activities as the ASE Replicator login. Note that logging in as "sa" and using set proxy will not work.
(NB: the docs always use the name "rep_user" for this login, but I personally prefer "rpl", as this is much quicker to type)
- After ASE Replicator is started, the first time an ASE Replicator stored proc is executed, there may be an error message like this:
Msg 11216, Level 16, State 1
Server 'S125', Line 1
Internal Ct-Lib/Cs-Lib error 84083972:
'ct_connect(): network packet layer:
internal net library error: Net-Lib
protocol driver call to connect two endpoints failed'.
Msg 11206, Level 16, State 1
Server 'S125', Line 1
Unable to connect to server 'ASEREP'.
(return status = -6)
This often seems to happen for the first execution of an ASE Replicator stored proc only; subsequent executions work fine.
I don't know why this error occurs, but I presume it's a bug (which will hopefully be fixed at some point).
When ASE Replicator is replicating data, this will cause activity in the sybsystemdb database (due to the fact that ASE Replicator uses CIS). This can lead to weird problems once sybsystemdb gets full: all ASE Replicator activity will come to a mysterious-looking standstill for which there seems to be no solution.
I found that the default sybsystemdb (2Mb) can actually fill up quite quickly. Because most of the space used in sybsystemdb seems to be transaction log activity, it's a good idea to enable "trunc. log on chkpt" for sybsystemdb. Also, making sybsystemdb a bit larger than the default 2Mb may be a good idea if your sybsystemdb keeps filling up.
In any case, always check whether sybsystemdb is full when ASE Replicator doesn't seem to proceed anymore.
When modifying replicated data (or executing a replicated stored procedure) in the primary database,
this must NOT be done with the "maintenance user" login for that database, because any transactions by that login will not be replicated.
The maintenance user login can be specified explicitly when creating the primary connection, but when this is omitted, the ASE Replicator login will be used as the maintenance user login by default. This behaviour is mentioned in the ASE Replicator documentation, but it's not described very clearly, so this may easily escape your attention. sp_helpconn displays the maintenance user login for each database.
(thanks to Juan C. Avila for reporting this)
Note the -probably unexpected- behaviour when using set proxy or set session authorization (let's assume the maintenance user login is rpl):
primary transaction executed by the maintenance user login rpl : transaction is not replicated.
primary transaction executed by a login other than rpl : transaction is replicated.
primary transaction executed by a login who performed set proxy rpl first : transaction is replicated (!).
A common problem: you've set up a publication and subscription, added the articles, and validated/materialised the subscription. Everything looks fine, yet no replication is taking place.
In this case, always check first if any connections are down by running sp_helpconn (from within the DDB); to resume all suspended connections, run sp_resumerep (see... it's just like RepServer...).
If connections keep going down, check the log files system.log and trace.log in the $SYBASE/RPL-12_5/XXX/log directory (XXX = name of your ASE Replicator); these log files may contain messages indicating what's going wrong.
ASE Replicator on Linux
For reasons unknown to me, ASE Replicator is not included with the Linux version of ASE 22.214.171.124. Still, with some manual intervention you can still use ASE Replicator on Linux (it's a Java application, after all). Note that this requires you have a 126.96.36.199 installation available for a platform which does include ASE Replicator.
Follow these steps (your mileage may vary...):
Copy the RPL-12_5 directory from an ASE installation on another platform to your Linux installation
This should get you going. It has been demonstrated to work when using the ASE Replicator files from an NT installation (you'll have to edit the .bat files and turn 'm into a format that can be used by your Linux command shell). Copying ASE Replicator from Unix platforms to Linux should work as well, but AFAIK this has not yet been tried (if you do, please share your experience!).
Copy the .jar files in the Java subdirectory of the Sybase Central environment from an ASE installation on another platform to your Linux installation
(thanks to Joop Bruggink for contributing this information)
Here's an example of how to set up replication for a table named PDS.primdb..my_tab to RDS.repdb..my_tab via ASE Replicator. Prerequisites: the tables are expected to exist in both databases, and ASE Replicator has been installed as described in the documentation. The replication can then be set up as follows (all commands below must be executed within the Distribution Database for this ASE Replicator instance, and by the ASE login used by ASE Replicator):
-- primary database
-- replicate database
-- create publication
sp_addpub pub1, "PDS.primdb"
-- create subscription
sp_addsub sub1, pub1, "RDS.repdb"
-- add articles to publication
sp_addprimaryart pub1, my_tab
-- add articles to subscription
sp_addreplicateart sub1, my_tab
-- also possible: sp_materializesub
-- activate publication & subscription
sp_helpconn -- check status
sp_helprep -- check status
-- ready !