User ManualTable of ContentsSQL Remote User's Guide1Contents3About This Manual9SQL Anywhere Studio documentation10Documentation conventions13The Adaptive Server Anywhere sample database15Finding out more and providing feedback16Introduction to SQL Remote19Welcome to SQL Remote21About SQL Remote22About this manual23Product installation23SQL Remote Concepts25SQL Remote components26The data server27Client applications27The Message Agent27Message system client28Publications and subscriptions29SQL Remote features31Some sample installations33Server-to-laptop replication for mobile workforces33Server-to-server replication among offices34Setting Up SQL Remote37Setup overview38Preparing your Adaptive Server Enterprise server39Ensuring TEMPDB is large enough39Installing the SQL Remote system objects39The SQL Remote system objects40Command-line installation of the stable queue41Upgrading SQL Remote for Adaptive Server Enterprise43Uninstalling SQL Remote44Tutorials for Adaptive Server Anywhere Users45Introduction46Goals46The database46Replication goals48Sybase Central or command-line utilities49Tutorial: Adaptive Server Anywhere replication using Sybase Central50Preparing for the Sybase Central replication tutorial50Setting up a consolidated database52Add a SQL Remote message type52Add the publisher and remote user to the database53Add publications and subscriptions55Set up the remote database in Sybase Central56Tutorial: Adaptive Server Anywhere replication using Interactive SQL and dbxtract58Preparing for the replication tutorial58Set up the consolidated database59Create a SQL Remote message type59Grant PUBLISH and REMOTE at the consolidated database60Create publications and subscriptions61Set up the remote database62Extract the remote database information63Load the remote database information63Start replicating data65Enter data at the consolidated database65Send data from the consolidated database65Receive data at the remote database66Replicate from the remote database to the consolidated database67A sample publication69A Tutorial for Adaptive Server Enterprise Users71Introduction72Goals72The database72Replication goals73Tutorial: Adaptive Server Enterprise replication75First steps75Setting up the consolidated database77Create the message links and addresses78Create the necessary users and permissions78Create the publication and subscription80Extract the remote database81Start replicating data84Enter data at the consolidated database84Send data from the consolidated database84Receive data at the remote database85Replicate from the remote database to the consolidated database86Replication Design for SQL Remote89Principles of SQL Remote Design91Design overview92Ensuring compatible databases92Using compatible sort orders and character sets93How statements are replicated96Replication of inserts and deletes96Replication of updates96Replication of procedures98Replication of triggers98Replication of data definition statements100How data types are replicated101Replication of blobs101Replication of dates and times102Who gets what?104Replication errors and conflicts106Replication errors106Replication conflicts106Tracking SQL errors107SQL Remote Design for Adaptive Server Anywhere109Design overview110Publishing data111Publishing whole tables111Publishing only some columns in a table112Publishing only some rows in a table113Publishing only some rows using a WHERE clause114Publishing only some rows using a subscription expression115Altering existing publications117Dropping publications118Notes on publications119Publication design for Adaptive Server Anywhere120Design issues overview120Conditions for valid articles121Design tips for performance121Partitioning tables that do not contain the subscription expression123The Contact example123Partitioning the Customer table in the Contact example125Partitioning the Contact table in the Contact example125Territory realignment in the Contact example125Sharing rows among several subscriptions130The Policy example130The publication132Territory realignment with a many-to-many relationship133Using the Subscribe_by_remote option with many-to-many relationships136Managing conflicts138How SQL Remote handles conflicts139Implementing conflict resolution140Using conflict resolution triggers140Conflict resolution examples142Resolving date conflicts142Resolving inventory conflicts143Reporting conflicts144Designing to avoid referential integrity errors145Designing triggers to avoid errors145Ensuring unique primary keys147Using global autoincrement default column values147Declaring default global autoincrement148Setting the Global_database_id value148Setting unique database identification numbers when extracting databases149How default values are chosen150Using primary key pools151The primary key pool table152Replicating the primary key pool152Filling and replenishing the key pool153Adding new customers155Primary key pool summary156Creating subscriptions157SQL Remote Design for Adaptive Server Enterprise159Design overview160Creating publications161Creating whole-table articles161Creating articles containing some of the columns in a table161Creating articles containing some of the rows in a table162Creating an article using a WHERE clause163Creating an article using a subscription column163Notes on articles164Publication design for Adaptive Server Enterprise165Design issues overview165Conditions for valid articles165Partitioning tables that do not contain the subscription column167The Contact example167Territory realignment in the Contact example169Partitioning the Customer table in the Contact example170Adding a subscription-list column to the Contact table170Maintaining the subscription-list column172Tuning extraction performance173Sharing rows among several subscriptions175The Policy example175Solving the problem175The publication176Maintaining the subscription-list column177Tuning extraction performance for shared rows180Using the Subscribe_by_remote option with many-to-many relationships182Managing conflicts183How SQL Remote handles conflicts184Implementing conflict resolution184A first conflict resolution example186A second conflict resolution example188Designing to avoid referential integrity errors191Ensuring unique primary keys193The primary key pool193Replicating the primary key pool194Filling and replenishing the key pool195Adding new customers196Testing the key pool197Primary key pool summary198Creating subscriptions199SQL Remote Administration201Deploying and Synchronizing Databases203Deployment overview204Test before deployment205Changes to avoid on a running system205Synchronizing databases207Mixed operating systems and database extraction207Notes on synchronization and extraction208Using the extraction utility209Creating a database from the reload files209Before extracting a database210Using the extraction utility from Sybase Central210Designing an efficient extraction procedure211Extracting groups213Limits to using the extraction utility213Using the extraction utility for Adaptive Server Enterprise214Adaptive Server Enterprise features unsupported in Adaptive Server Anywhere214Customizing the system tables215Synchronizing data over a message system216SQL Remote Administration217Management overview218Managing SQL Remote permissions219Granting and revoking PUBLISH permissions219Granting and revoking REMOTE and CONSOLIDATE permissions222Granting REMOTE permissions222Selecting a send frequency224Granting CONSOLIDATE permissions225Revoking REMOTE and CONSOLIDATE permissions225Assigning permissions in multi-tier installations226Using message types228Working with message types228Using Sybase Central to work with message types229Using commands to work with message types230Setting message type control parameters232The file message system233The ftp message system234Troubleshooting ftp problems235The SMTP message system236Sharing SMTP/POP addresses238The MAPI message system238The VIM message system239Running the Message Agent241Message Agent batch and continuous modes241Connections used by the Message Agent242Replication system recovery procedures242Ensuring consistent Message Agent settings243The Message Agent and replication security244Troubleshooting errors at remote sites244Tuning Message Agent performance246Tuning throughput by controlling Message Agent threading246Tuning throughput by caching messages247Tuning incoming message polling248Polling interval248Requesting resends249Example249Tuning the message sending process250Polling interval251Resending messages251Encoding and compressing messages253The encoding scheme253Creating custom encoding schemes254The message tracking system255Status information in the remoteuser table255Tracking messages by transaction log offsets255Handling of lost or corrupt messages257Administering SQL Remote for Adaptive Server Anywhere259Running the Message Agent260Starting the Message Agent260Running the Message Agent as a service260The Message Agent and replication security261Error reporting and handling263Default error handling263Ignoring errors263Implementing error handling procedures264Example: e-mailing notification of errors264Transaction log and backup management267Setting the transaction log directory267Backup utility options268Using the live directory as the transaction log directory268Using the backup directory as the transaction log directory269Managing old transaction logs271Recovery from database media failure for consolidated databases271Recovery with a single transaction log272Recovery with multiple transaction logs273Backup procedures at remote databases275Upgrading consolidated databases276Unloading and reloading a database participating in replication276Using passthrough mode278Uses and limitations of passthrough mode279Operations not replicated in passthrough mode280Administering SQL Remote for Adaptive Server Enterprise281How the Message Agent for Adaptive Server Enterprise works282Scanning the transaction log282The stable queue283Message Agent operation phases284Running the Message Agent287The Message Agent and replication security287Running multiple Message Agents287Error reporting and handling289Default error handling289Implementing error handling procedures289Adaptive Server Enterprise transaction log and backup management290Protecting against media failure on the transaction log290Stable queue recovery issues291Transaction log management291Making schema changes293Using passthrough mode294Schema modifications294Using SQL Remote with Replication Server295When you need to use the SQL Remote Open Server296Architecture for Replication Server/SQL Remote installations297How the pieces fit together297Setting up SQL Remote Open Server300Configuring Replication Server303Set the dsi_xact_group_size parameter303Set the dsi_num_threads parameter303Create replication definitions for SQL Remote data303Suspend and restart the connection304Other issues305Reference307Utilities and Options Reference309The Message Agent310The Database Extraction utility320Extracting a remote database in Sybase Central320The extraction utility321Extraction utility options324The SQL Remote Open Server330SQL Remote options333SQL Remote event-hook procedures338sp_hook_dbremote_begin and sp_hook_ssrmt_begin338sp_hook_dbremote_end and sp_hook_ssrmt_end338sp_hook_dbremote_shutdown and sp_hook_ssrmt_shutdown339sp_hook_dbremote_receive_begin and sp_hook_ssrmt_receive_begin339sp_hook_dbremote_receive_end and sp_hook_ssrmt_receive_end339sp_hook_dbremote_send_begin and sp_hook_ssrmt_send_begin340sp_hook_dbremote_send_end and sp_hook_ssrmt_send_end340sp_hook_dbremote_message_sent and sp_hook_ssrmt_message_sent340sp_hook_dbremote_message_missing and sp_hook_ssrmt_message_missing340sp_hook_dbremote_message_apply_begin and sp_hook_ssrmt_message_apply_begin340sp_hook_dbremote_message_apply_end and sp_hook_ssrmt_message _apply_end341System Objects for Adaptive Server Anywhere343SQL Remote system tables344SYSARTICLE table344SYSARTICLECOL table345SYSPUBLICATION table346SYSREMOTEOPTION table346SYSREMOTEOPTIONTYPE table346SYSREMOTETYPE table347SYSREMOTEUSER table347SYSSUBSCRIPTION table349SQL Remote system views351SYSARTICLES view351SYSARTICLECOLS view351SYSPUBLICATIONS view351SYSREMOTEOPTIONS view352SYSREMOTEUSERS view352SYSSUBSCRIPTIONS view353System Objects for Adaptive Server Enterprise355SQL Remote system tables356##remote table356sr_article table356sr_articlecol table357sr_marker table357sr_object table358sr_option table358sr_passthrough table358sr_publication table359sr_publisher table359sr_remoteoption table359sr_remoteoptiontype table360sr_remotetable table360sr_remotetype table360sr_remoteuser table361sr_subscription table363SQL Remote system views364sr_articles view364sr_articlecols view364sr_publications view364sr_remoteoptions view365sr_remotetables view365sr_remotetypes view365sr_remoteusers view366sr_subscriptions view367Stable Queue tables368sr_queue_state table368sr_transaction table369sr_confirmed_transaction table370sr_queue_coordinate table370Command Reference for Adaptive Server Anywhere371ALTER REMOTE MESSAGE TYPE statement373CREATE PUBLICATION statement374CREATE REMOTE MESSAGE TYPE statement375CREATE SUBSCRIPTION statement376CREATE TRIGGER statement377DROP PUBLICATION statement379DROP REMOTE MESSAGE TYPE statement380DROP SUBSCRIPTION statement381GRANT CONSOLIDATE statement382GRANT PUBLISH statement383GRANT REMOTE statement384GRANT REMOTE DBA statement385PASSTHROUGH statement386REMOTE RESET statement387REVOKE CONSOLIDATE statement388REVOKE PUBLISH statement389REVOKE REMOTE statement390REVOKE REMOTE DBA statement391SET REMOTE OPTION statement392START SUBSCRIPTION statement393STOP SUBSCRIPTION statement394SYNCHRONIZE SUBSCRIPTION statement395UPDATE statement396Command Reference for Adaptive Server Enterprise397sp_add_article procedure399sp_add_article_col procedure401sp_add_remote_table procedure402sp_create_publication procedure404sp_drop_publication procedure405sp_drop_remote_type procedure406sp_drop_sql_remote procedure407sp_grant_consolidate procedure408sp_grant_remote procedure411sp_link_option procedure414sp_modify_article procedure416sp_modify_remote_table procedure418sp_passthrough procedure420sp_passthrough_piece procedure421sp_passthrough_stop procedure423sp_passthrough_subscription procedure424sp_passthrough_user procedure425sp_populate_sql_anywhere procedure426sp_publisher procedure427sp_queue_clean procedure428sp_queue_confirmed_delete_old procedure429sp_queue_confirmed_transaction procedure430sp_queue_delete_old procedure431sp_queue_drop procedure432sp_queue_dump_database procedure433sp_queue_dump_transaction procedure434sp_queue_get_state procedure435sp_queue_log_transfer_reset procedure436sp_queue_read procedure437sp_queue_reset procedure438sp_queue_set_confirm procedure439sp_queue_set_progress procedure440sp_queue_transaction procedure441sp_remote procedure442sp_remote_option procedure443sp_remote_type procedure445sp_remove_article procedure446sp_remove_article_col procedure447sp_remove_remote_table procedure448sp_revoke_consolidate procedure449sp_revoke_remote procedure450sp_subscription procedure451sp_subscription_reset procedure452Appendices453SQL Remote for Adaptive Server Enterprise and Adaptive Server Anywhere: Differences455Types of difference456Differences in functionality457Differences in approach458Adaptive Server Enterprise procedures and Adaptive Server Anywhere statements458Limitations for Enterprise to Enterprise replication460Supported Platforms and Message Links463Supported message systems464Supported operating systems465Index467Size: 1.6 MBPages: 485Language: EnglishOpen manual
