Setting up SQL Remote on ASE

If you work in the IT industry for any amount of time, you learn many axioms, one of which is: nature abhors information that is not in a relational database. Inexorable pressure will come from management (due to a need to be able to control everything) and IT staff (due to a need to play with new toys).

A corrollary to this axiom is: every database wants to be replicated. In the Sybase family, there are generally two ways provided to do this: SQL Remote and Replication Server. SQL Remote is generally for discrete (non-continuous) replication and can work well over slow links -- even links that are down most of the time. Replication Server is a much more robust solution that allows continuous replication, replication among different DBMS (Oracle, DB2, Sybase, etc.). Of course, it also requires more resources -- in terms of both computer (memory, CPU time) and money.

Since I have not been able to work on any projects for people with lots of resources, all my replication experience has been with SQL Remote. One problem is that a lot of the documentation assumes that if you're using SQL Remote you're either replicating data from Adaptive Server Enterprise (ASE) to Adaptive Server Anywhere (ASA), or from ASA to ASA. The literature rarely discusses ASE to ASE, presumably because most people use Replication Server for such tasks.

Using SQL Remote for ASE to ASE does provide some difficulties. For instance, replicating schema changes is impossible. However, it's not very difficult to create a script to:

  • copy the data from a table to a temporary table,
  • remove the table from the publication,
  • drop the table,
  • create the table with the new schema
  • move the data from the temporary table into the new table
  • mark the new table for replication
  • add the new table back into the publication

You must also ensure that the table is quiet when you make these changes. That is, if there are messages floating around with modifications to the table and you make this change from under them, then when ssremote tries to apply the messages you'll get bad things happening.

It's a little onerous, but not impossible. However, it's a good idea to have as stable a set of tables and procedures as possible before plunging in. Particularly those that get updated often.

Well, it can definitely be done. In a nutshell, my procedure has been this:

  1. Drop the database
  2. Create the users in the database (not including the user who will publish the database
  3. Create the main tables
  4. Create the stored procedures/triggers
  5. Set permissions on everything
  6. Bring in the data
  7. Create any indices
  8. Dump the database and the transaction log (to a disk or tape somewhere), then truncate the transaction log
  9. Run the SQL Remote setup scripts (there are four: ssremote, ssupdate, stableq and squpdate)
  10. Create the replication users and assign appropriate roles
  11. Create a publication
  12. Set up the tables for replication and assign them to the publication
  13. Start the publication for a particular user
  14. Run the sp_remote reset <user> command; this is really important, as the replication process will not start without it.