Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Tip 78: Replication Step By Step

And In Conclusion

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 happen?"

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 future date.
• 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 problems.
• 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 partitioning.
• Using UPDATE PUBLICATION statements to force row replication and deletion.
• Implementing groups of users for the purposes of selective broadcasting.
• Dealing with remote database rows that don't exist in the consolidated database.
• 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 them.
• Message passing via email: MAPI, SMTP, etc.
• Dealing with delivery errors: how to handle lost messages and files.
• Detecting requests to resend.
• Scanning and flagging errors reported by DBREMOTE.
• Using DEFAULT TIMESTAMP and DEFAULT CURRENT TIMESTAMP to help with data partitioning.
• Using CURRENT REMOTE USER to help deal with replication conflicts.
• Designing triggers that are safe for DBXTRACT to extract.
• Using CURRENT PUBLISHER to develop triggers that can run on any database.
• 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 easily.
• Dealing with problems of object ownership and PASSTHROUGH processing.
• Centralized administration of actual end user ids.
• Directing PASSTHROUGH statements to all subscribers to a particular publication.
• Extracting fully-qualified publications for multi-level replication hierarchies.
• Custom extraction procedures, transaction design and the REMOTE RESET statement.
• 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 database design.
• Mixing and matching: extracting and synchronizing between SQL Anywhere and SQL Server.