Last modified: February 23, 1998
mail to: firstname.lastname@example.org
Tip 78: Replication Step By Step
Replication is hard. When creating an ordinary database you ask
yourself "What do I want to do?" With a replication setup you must ask that
same question plus two more: "Where does it get done?" and "When does it
And when errors happen it's no longer simply a matter of "What
happened?" but where and when as well. Because the repair requires
decisions about where and when as well as what.
Replication is also new. The application of general-purpose
replication solutions to problems involving complex business rules is
not obvious or easy, and "best practices" are still emerging. SQL Remote
may no longer a "Release 1.0" product but some of the recent
enhancements are, especially SQL Server support. And as with all new and
useful products it's the customers who find new uses and new solutions.
This document has dealt with replication by going through the basic
steps in great detail. If you've followed along then it should be clear
there really are a lot of steps. It should also be clear how each step
works and how they are related to one another. And that means you're
ready to move on to the fun stuff, figuring out how to make replication
work on your application.
On The Next Geraldo...
The real fun begins after the basics have been mastered. Here's a
list of some interesting topics not covered by this document:
- Replication between remote sites via the consolidated database.
- Time-delayed replication; e.g., insert now but don't copy until some
- Moving data between databases; i.e., copy from A to B, then delete
from A but not B.
- Using subscription_idx columns to solve data partitioning
- Non-disjoint partitioning and SUBSCRIBE BY result sets.
- Getting Weird: using multi-valued subscription expressions.
- Getting Nasty: using subqueries in SUBSCRIBE BY clauses.
- The Territory Realignment problem: using triggers to implement data
- Using UPDATE PUBLICATION statements to force row replication and
- Implementing groups of users for the purposes of selective
- Dealing with remote database rows that don't exist in the
- The Six-Way Challenge: designing and coding to avoid and handle
replication errors and conflicts.
- "Don't worry, be happy": conflict resolution by default.
- Using RESOLVE UPDATE triggers to handle conflicts.
- Handling out-of-date-order conflicts.
- Handling incrementing and decrementing column conflicts.
- Dealing with errors that stop replication.
- File transfer in the real world, where shared files aren't available
but FTP might be.
- The trouble with triggers: when and why trigger actions aren't
replicated, and when they are.
- How to tell when old DBREMOTE output files should be deleted.
- Managing log files, and how to know when it's safe to delete
- Message passing via email: MAPI, SMTP, etc.
- Dealing with delivery errors: how to handle lost messages and
- Detecting requests to resend.
- Scanning and flagging errors reported by DBREMOTE.
- Using DEFAULT TIMESTAMP and DEFAULT CURRENT TIMESTAMP to help with
- Using CURRENT REMOTE USER to help deal with replication
- Designing triggers that are safe for DBXTRACT to extract.
- Using CURRENT PUBLISHER to develop triggers that can run on any
- Exploiting the various replication-related SET OPTION values.
- Using the other columns in sysremoteuser, and the other
replication-related catalog tables.
- Using the registry and the SQLANY.INI and DBREMOTE.INI files.
- Setting up user ids and groups to make it easy to develop, deploy
and administer a replication application.
- Revoking and changing user ids: what can and cannot be done
- Dealing with problems of object ownership and PASSTHROUGH
- Centralized administration of actual end user ids.
- Directing PASSTHROUGH statements to all subscribers to a particular
- Extracting fully-qualified publications for multi-level replication
- Custom extraction procedures, transaction design and the REMOTE
- Naming the same table in more than one publication.
- What happens when publications don't match.
- How and why to run DBREMOTE in continuous mode.
- Scheduled replication with SEND EVERY and SEND AT.
- Extreme Machines: recovery requirements and techniques.
- How replication affects server performance via the log.
- How to improve DBREMOTE performance.
- How DBXTRACT is affected by the missing components of an unfinished
- Mixing and matching: extracting and synchronizing between SQL
Anywhere and SQL Server.