(originally published at www.sybase.com in August 2011; moved to SCN in November 2012)
Over the past 2 years, I’ve talked to lots of customers about the ASE in-memory database (IMDB) feature that was introduced in ASE 15.5.
One of the questions I got most often was whether replication was supported with ASE IMDB. Up till now, the not-so-clear answer was both ‘yes’ and ‘no’. But as of July 2011, with ASE 15.5 ESD#4 and RepServer 15.6 ESD#1 released, the answer is now just ‘yes’ : replication is supported. I thought some explanation would be helpful as to what this all means…
First, a quick IMDB recap: the virtue of the ASE IMDB is that there is no persistent storage: an IMDB database lives entirely in cache (=memory). If there’s no disk, then there’s no disk I/O either. Since the contents of an IMDB are lost after an ASE reboot, there is no need to do a transaction logging for the purpose of recovering the database when ASE start up again. This allows all sorts of internal optimizations around disk I/O and buffer cache management, as well as around transaction logging. Otherwise, an ASE IMDB is pretty much identical to a fully cached classic ASE database.
This all means that if you run workload that performs heavy disk write activity, and/or has many concurrent transactions, then the ASE IMDB may deliver better performance since these two areas were optimized. For other workload (like select statements), you should not expect significant performance gains from an IMDB.
Sybase’s message has always been that an ASE IMDB is functionally equivalent to any other ASE database, except that its transactions are not durable (i.e. not retained over an ASE reboot due to absence of persistent storage. This was usually the point where the question came up whether you could use ASE IMDBs with Replication Server.
As RepServer users know, replication involves a number of things in each primary or replicate database, such as: the ASE RepAgent configuration for that database, the secondary truncation point in the transaction log, the maintenance user and the rs_lastcommit table. RepServer expects these items to always be present, also when the ASE server containing the primary or replicate ASE database has been shut down and rebooted.
So far it’s all pretty obvious, but now we’ve gotten to the point where the subtleties start to kick in.
To use an IMDB database as a replication source, its secondary truncation point, rs_lastcommit table etc. need to be still present after an ASE reboot. But since we don’t have any persistent storage in an IMDB, that information will be gone after ASE restarts; consequently replication will then no longer work. To make replication to or from an IMDB possible, some non-standard setup steps are required. In addition, some internals had to be changed for use with IMDBs, both in ASE and in RepServer.
With ASE version 15.5 ESD#4 and Replication Server version 15.6 ESD#1, which were both released in July 2011, the situation is now that replicating to and from IMDBs is supported (note: you need both the ASE ESD and the RepServer ESD). Those non-standard setup steps are still required — basically this involves using a template database for the IMDB in which the truncation point, rs_lastcommit etc. are stored. This template database gets copied into the IMDB, thus providing it with the maintenance user etc. (details are described in a whitepaper; see below).
So, before ASE 15.5 ESD#4 and Replication Server 15.6 ESD#1, did replication work for IMDBs? As I mentioned earlier, the answer is both ‘yes’ and ‘no’.
The answer is ‘yes’ because when you set up the replication system, everything will keep working fine as long as ASE is not rebooted. The answer is ‘no’ because after an ASE reboot, you will need to setup the replication configuration for the IMDBs in that ASE server all over again, which is hardly how most customers would qualify as ‘replication works’.
Another ‘yes’ lies in the fact that prior to ASE 15.5 ESD#4 + RS 15.6 ESD#1, you could also use the template database to contain the truncation point etc. But, there’s another ‘no’ as well: in certain -rather specific- situations, replication from an IMDB might not always work as you would expect, and could for example cause significant delays in replicating data out of an IMDB (for internal technical reasons).
This mix of “yes, it works” and “no, it doesn’t quite work” is just a different way of saying that you really need ASE 15.5 ESD#4 as well as Replication Server 15.6 ESD#1 in order to use IMDBs in a replication system.
The details of how to set up replication for IMDBs, including actual code examples, are described in a technical whitepaper that was just published at sybase.com at this location: http://scn.sap.com/docs/DOC-30661.
With that, all questions on IMDB and replication are now answered, I hope…