Manuals
/
Brands
/
Personal Care
/
Microscope & Magnifier
/
Sybase
/
Personal Care
/
Microscope & Magnifier
Sybase
DC38133-01-0902-01 manual
1
1
485
485
Download
485 pages, 1.6 Mb
SQL Remote
™
User’s Guide
Partnumber:
DC38133-01-0902-01
Lastmodified:
October 2004
Contents
Main
ii
Contents
I Introduction to SQL Remote 1
II Replication Design for SQL Remote 71
III SQL Remote Administration 183
IV Reference 289
Page
V Appendices 435
Page
About This Manual
SQL Anywhere Studio documentation
Page
http://eshop.sybase.com/eshop/documentation
Documentation conventions
table-name
savepoint-name
column-constraint
owner
Page
The Adaptive Server Anywhere sample database
xv
asademo.db
asademo.db
Finding out more and providing feedback
http://www.ianywhere.com/developer/
forums.sybase.com
Page
Page
Page
Page
Page
About SQL Remote
About this manual
Product installation
setup
AdaptiveServer AnywhereRead Me First
Page
CHAPTER 2
SQL Remote Concepts
SQL Remote components
Data server
Message Agent
Message system client
The data server
Client applications
dbremote.exe
The Message Agent
Message system client
Publications and subscriptions
A two-table synchronization definition
+
Publish Subscribe Publish
Subscribe
SQL Remote features
Page
Some sample installations
Server-to-laptop replication for mobile workforces
Server-to-server replication among ofces
Page
Page
CHAPTER 3
Setting Up SQL Remote
Setup overview
Preparing your Adaptive Server Enterprise server
Ensuring TEMPDB is large enough
Installing the SQL Remote system objects
Page
Command-line installation of the stable queue
logle
password
login_id
server-name
Page
Upgrading SQL Remote for Adaptive Server Enterprise
ssupdate.sql
squpdate.sql
ssupdate.sql
Uninstalling SQL Remote
CHAPTER 4
Tutorials for Adaptive Server Anywhere Users
Introduction
Goals
The database
Page
Replication goals
Sybase Central or command-line utilities
Tutorial: Adaptive Server Anywhere replication using Sybase Central
Preparing for the Sybase Central replication tutorial
c:\tutorial\HQ.db
Setting up a consolidated database
Page
Page
Page
Set up the remote database in Sybase Central
c:\tutorial\eld.db
Tutorial: Adaptive Server Anywhere replication using Interactive SQL and dbxtract
Preparing for the replication tutorial
Set up the consolidated database
Page
Page
Set up the remote database
Page
Page
Start replicating data
Enter data at the consolidated database
Send data from the consolidated database
Receive data at the remote database
Replicate from the remote database to the consolidated database
Page
A sample publication
salespub.sql
path
Page
Page
Introduction
Goals
The database
Replication goals
55
Page
Tutorial: Adaptive Server Enterprise replication
First steps
server-name
Page
Setting up the consolidated database
Page
Page
Page
Page
c:\tutorial\eld
rtsql
Page
Start replicating data
Enter data at the consolidated database
Send data from the consolidated database
Receive data at the remote database
Replicate from the remote database to the consolidated database
Page
Page
Page
Page
CHAPTER 6
Principles of SQL Remote Design
Ensuring compatible databases
test.db
Using compatible sort orders and character sets
locales.dat
Page
Chapter 6. Principles of SQL Remote Design
77
How statements are replicated
SQL Remote replicates SQL statements that modify data
Replication of inserts and deletes
Replication of updates
>
Replication of procedures
SQL Remote replicates procedures by replicating the actions of a procedure.
Replication of triggers
Page
Page
How data types are replicated
Replication of blobs
Replication of dates and times
language-name
Who gets what?
subscriptions
Page
Replication errors and conicts
Replication errors
Replication conicts
TrackingSQL errors
procedure-name
Page
CHAPTER 7
SQL Remote Design for Adaptive Server Anywhere
Page
Publishing data
Publishing whole tables
Publishing only some columns in a table
Publishing only some rows in a table
Publishing only some rows using a WHERE clause
Publishing only some rows using a subscription expression
Page
Altering existing publications
Dropping publications
Notes on publications
Publication design for Adaptive Server Anywhere
Design issues overview
Conditions for valid articles
Design tips for performance
Page
Partitioning tables that do not contain the subscription expression
The Contact example
Page
Partitioning the Customer table in the Contact example
Partitioning the Contact table in the Contact example
Territory realignment in the Contact example
Page
table-name
publication-name
subscription-expression
Page
Page
Sharing rows among several subscriptions
The Policy example
Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere
Page
Territory realignment with a many-to-manyrelationship
Page
Page
Using the Subscribe_by_remote option with many-to-many relationships
Page
Managing conicts
How SQL Remote handles conicts
Implementing conict resolution
Using conict resolution triggers
table-list
Conict resolution examples
Page
Page
Designing to avoid referential integrity errors
Designing triggers to avoid errors
Page
Ensuring unique primary keys
Using global autoincrement default column values
Page
Page
Page
Using primary key pools
The primary key pool table
Replicating the primary key pool
Filling and replenishing the key pool
Page
Adding new customers
Primary key pool summary
Creating subscriptions
Page
CHAPTER 8
SQL Remote Design for Adaptive Server Enterprise
Page
Creating publications
Creating whole-table articles
Creating articles containing some of the columns in a table
Creating articles containing some of the rows in a table
column
Notes on articles
Publication design for Adaptive Server Enterprise
Design issues overview
Conditions for valid articles
Page
Partitioning tables that do not contain the subscription column
The Contact example
Page
Territory realignment in the Contact example
Partitioning the Customer table in the Contact example
Adding a subscription-list column to the Contact table
Page
Contact
Customer
Maintaining the subscription-list column
Tuningextraction performance
Page
Sharing rows among several subscriptions
The Policy example
Page
rep_key
Maintaining the subscription-list column
Page
Page
Tuningextraction performance for shared rows
Page
Using the Subscribe_by_remote option with many-to-many relationships
Managing conicts
How SQL Remote handles conicts
Implementing conict resolution
old_row_table
remote_row_table
old_row_table
remote_row_table
A rst conict resolution example
after
A second conict resolution example
Page
Page
Designing to avoid referential integrity errors
Page
Ensuring unique primary keys
The primary key pool
Replicating the primary key pool
Filling and replenishing the key pool
Adding new customers
Testing the keypool
Primary key pool summary
Creating subscriptions
Page
Page
Page
CHAPTER 9
Deploying and Synchronizing Databases
Deployment overview
Test before deployment
Changes to avoid on a running system
Page
Synchronizing databases
Donot execute SYNCHRONIZE SUBSCRIPTION or sp_subscription
synchronize
at a remote database.
Mixed operating systems and database extraction
Notes on synchronization and extraction
Using the extraction utility
Creating a database from the reload les
Before extracting a database
Using the extraction utility from Sybase Central
Designing an efcient extraction procedure
Page
Extracting groups
Limits to using the extraction utility
Using the extraction utility for Adaptive Server Enterprise
Transact-SQLCompatibility
AdaptiveServer Anywhere Users Guide
Synchronizing data over a message system
dbunload
CHAPTER 10
SQL Remote Administration
Management overview
Managing SQL Remote permissions
Granting and revoking PUBLISH permissions
Page
Page
Granting and revoking REMOTEand CONSOLIDATE permissions
Page
Page
Page
Assigning permissions in multi-tier installations
Page
Using message types
Working with message types
Page
Page
address-string
Setting message type control parameters
smtp
user:\dbr43
sqlremote
The le message system
The ftp message system
Page
The SMTP message system
Page
The MAPI message system
The VIM message system
Page
dbremote.exe
Message Agent batch and continuous modes
Connections used by the Message Agent
Replication system recovery procedures
Ensuring consistent Message Agent settings
Troubleshootingerrors at remote sites
nnn
Tuning Message Agent performance
Tuningthroughput by controlling Message Agent threading
Tuningthroughput by caching messages
Tuningincoming message polling
Page
userX.n
Tuningthe message sending process
Page
Page
Encoding and compressing messages
The encoding scheme
dbencod.dll
ssencod.dll
h
Creating custom encoding schemes
The message tracking system
Status information in the remoteuser table
Trackingmessages by transaction log offsets
Page
Handling of lost or corrupt messages
Page
Page
Starting the Message Agent
Running the Message Agent as a service
Page
Page
Error reporting and handling
Default error handling
Ignoring errors
Implementing error handling procedures
Chapter 11. Administering SQL Remote for AdaptiveSer ver Anywhere
Thestored procedure calls another stored procedure to manage the sending ofEmail:
247
Anaudit table Anaudit table could be dened as follows:
Thecolumns have the following meaning:
Page
Transaction log and backup management
e:\archive
Setting the transaction log directory
Backup utility options
consol.db
c:\archive
c:\live
c:\archive\consol.log
xx
c:\live
consol.db
c:\archive
xx
ZZ
AA
yymmdd01.log
Managing old transaction logs
Recovery from database media failure for consolidated databases
X
Page
Page
c:\dbdir\cons.log
c:\recover\cons.log
d:\mirdir\cons.mlg
c:\dbdir\cons.log
Backup procedures at remote databases
Upgrading consolidated databases
Unloading and reloading a database participating in replication
dbtran
dblog
Using passthrough mode
before
after
Uses and limitations of passthrough mode
Operations not replicated in passthrough mode
CHAPTER 12
Administering SQL Remote for Adaptive Server Enterprise
How the Message Agent for Adaptive Server Enterprise works
Scanning the transaction log
The stable queue
Message Agent operation phases
Message Agent
Page
Page
squeue.sql
@data
Running multiple Message Agents
Page
Error reporting and handling
Default error handling
Implementing error handling procedures
Adaptive Server Enterprise transaction log and backup management
Protecting against media failure on the transaction log
System Administration Guide
Stable queue recovery issues
Transactionlog management
Page
Making schema changes
Using passthrough mode
Schema modications
Page
When you need to use the SQL Remote Open Server
Architecture for Replication Server/SQL Remote installations
How the pieces t together
Page
Message Agent
Message system
ssqueue
ssqueue.exe
Setting up SQL Remote Open Server
Page
Page
Conguring Replication Server
Set the dsi_xact_group_size parameter
Set the dsi_num_threads parameter
Create replication denitions for SQL Remote data
Suspend and restart the connection
Other issues
Page
Page
Page
CHAPTER 14
Utilities and Options Reference
The Message Agent
trackingsystem to ensure message delivery.
292
Chapter 14. Utilities and Options Reference
le
size
n
time
sybase\locales\locales.dat
charset_name
sort_order
language_name
The maximum message length must be the same at all sites in an installa- tion.
yymmddxx.dbr
yymmddxx.ss
yymmdd
Page
Page
size
The Database Extraction utility
Extracting a remote database in Sybase Central
The extraction utility
Page
Page
Extraction utility options
asademo.db
asacopy.db
newdemo.db
asademo
c:\unload
sybase\locales\locales.dat
charset_name
sort_order
language_name
Page
Page
Page
The SQL Remote Open Server
sql.ini
interfaces
yymmddxx.dbr
yymmddxx.ss
yymmdd
Chapter 14. Utilities and Options Reference
SQL Remote options
integer
timestamp-string
time-string
date-string
Page
Page
Page
Page
SQL Remote event-hook procedures
sp_hook_dbremote_begin and sp_hook_ssrmt_begin
sp_hook_dbremote_end and sp_hook_ssrmt_end
sp_hook_dbremote_shutdown and sp_hook_ssrmt_shutdown
sp_hook_dbremote_receive_begin and sp_hook_ssrmt_receive_begin
sp_hook_dbremote_receive_end and sp_hook_ssrmt_receive_end
Page
Page
Page
Page
SQL Remote system tables
326
Thesetables are described in more detail in the following sections.
SYSARTICLE table
Function Eachrow describes an article in a SQL Remote publication.
SYSARTICLECOL table
itis in, and the publication it is part of.
327
SYSPUBLICATIONtable
SYSREMOTEOPTION table
328
SYSREMOTEOPTIONTYPE table
SYSREMOTETYPE table
publisheraddress.
329
SYSREMOTEUSER table
togetherwith the status of SQL Remote messages sent to and from that user.
Page
SYSSUBSCRIPTION table
REMOTEpermissions) to one publication.
331
Page
SQL Remote system views
SYSARTICLES view
SYSARTICLECOLS view
SYSPUBLICATIONSview
SYSREMOTEOPTIONS view
theSYSREMOTEOPTION and SYSREMOTEOPTIONTYPE system tables,in more readable form.
334
SYSREMOTEUSERS view
SYSSUBSCRIPTIONS view
335
Page
Page
SQL Remote system tables
These tables are for use only by SQL Remote. Do not alter these tables or their contents directly.
#remote table
sr_article table
sr_articlecol table
itis in, and the publication it is part of.
339
sr_marker table
databasesin the same session.
sr_object table
sr_option table
sr_passthrough table
sr_publication table
sr_publisher table
sr_remoteoption table
sr_remoteoptiontype table
sr_remotetable table
342
sr_remotetype table
publisheraddress.
sr_remoteuser table
343
togetherwith the status of SQL Remote messages sent to and from that user.
Page
sr_subscription table
REMOTEpermissions) to one publication.
345
SQL Remote system views
sr_articles view
sr_articlecols view
sr_publications view
sr_remoteoptions view
sr_remotetables view
sr_remotetypes view
sr_remoteusers view
348
sr_subscriptions view
349
Stable Queue tables
sr_queue_state table
sr_transaction table
351
sr_conrmed_transaction table
sr_queue_coordinate table
352
sendingthread to access the stable queue and related tables.
CHAPTER 17
Command Reference for Adaptive Server Anywhere
353
Page
ALTER REMOTE MESSAGE TYPE statement
CREATE PUBLICATION statement
CREATE REMOTE MESSAGE TYPE statement
CREATE SUBSCRIPTION statement
CREATE TRIGGER statement
column-list
Page
DROP PUBLICATION statement
DROP REMOTE MESSAGE TYPE statement
DROP SUBSCRIPTION statement
GRANT CONSOLIDATE statement
GRANT PUBLISH statement
GRANT REMOTE statement
GRANT REMOTE DBA statement
PASSTHROUGH statement
REMOTE RESET statement
REVOKE CONSOLIDATE statement
REVOKE PUBLISH statement
REVOKE REMOTE statement
REVOKE REMOTE DBA statement
SET REMOTE OPTION statement
START SUBSCRIPTION statement
STOP SUBSCRIPTION statement
SYNCHRONIZE SUBSCRIPTION statement
UPDATE statement
CHAPTER 18
Command Reference for Adaptive Server Enterprise
379
executingSQL Remote commands.
Page
sp_add_article procedure
Page
sp_add_article_col procedure
sp_add_remote_table procedure
Page
sp_create_publication procedure
sp_drop_publication procedure
sp_drop_remote_type procedure
sp_drop_sql_remote procedure
sp_grant_consolidate procedure
390
frequency
address
type_name
send_time
Page
Chapter 18. Command Referencefor Adaptive Server Enterprise
sp_grant_remote procedure
393
frequency
address
type_name
send_time
Page
sp_link_option procedure
ftp.mycompany.com
sp_modify_article procedure
Page
sp_modify_remote_table procedure
Page
sp_passthrough procedure
sp_passthrough_piece procedure
Page
sp_passthrough_stop procedure
sp_passthrough_subscription procedure
subscribe_by
publication_name
sp_passthrough_user procedure
sp_populate_sql_anywhere procedure
sp_publisher procedure
user_name
sp_queue_clean procedure
sp_queue_conrmed_delete_old procedure
sp_queue_conrmed_transaction procedure
sp_queue_delete_old procedure
sp_queue_drop procedure
sp_queue_dump_database procedure
sp_queue_dump_transaction procedure
sp_queue_get_state procedure
sp_queue_log_transfer_reset procedure
sp_queue_read procedure
sp_queue_reset procedure
sp_queue_set_conrm procedure
sp_queue_set_progress procedure
sp_queue_transaction procedure
sp_remote procedure
remote_user
sp_remote_option procedure
Page
sp_remote_type procedure
publisher:
sp_remove_article procedure
sp_remove_article_col procedure
sp_remove_remote_table procedure
sp_revoke_consolidate procedure
sp_revoke_remote procedure
sp_subscription procedure
operation
Page
Page
Page
Page
Types of difference
Differences in functionality
Differences in approach
Adaptive Server Enterprise procedures and Adaptive Server Anywhere statements
Appendix A. SQL Remote forAdaptive Ser verEnter prise and Adaptive Server Anywhere: Differences
441
Limitations for Enterprise to Enterprise replication
Page
Page
Page
Supported message systems
Supported operating systems
\My Documents\Synchronized Files
SQL AnywhereStudio Read Me First for UNIX
ce-machine-name
Page
Index
Symbols
A
B
C
D
E
F
G
H
I
L
M
N
O
P
Page
Q
R
Page
S
Page
Page
Page
Page
Page
T
U
V
W