Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 10. SQL Remote Administration
The SQL Remote Message Agent is a key component in SQL Remote replication. The Message Agent handles both the sending and receiving of messages. It carries out the following functions:
It processes incoming messages, and applies them in the proper order to the database.
It scans the transaction log or stable queue at each publisher database, and translates the log entries into messages for subscribers.
It parcels the log entries up into messages no larger than a fixed maximum size (50,000 bytes by default), and sends them to subscribers.
It maintains the message tracking information in the system tables, and manages the guaranteed transmission mechanism.
The Message Agent for Adaptive Server Enterprise is named ssremote.exe, and the Message Agent for Adaptive Server Anywhere is named dbremote.exe.
The Message Agent for Adaptive Server Enterprise uses a stable queue to hold transactions until they are no longer needed. For more information on the stable queue, see How the Message Agent for Adaptive Server Enterprise works.
Message Agent batch and continuous modesThe Message Agent can be run in one of two modes:
Batch mode In batch mode, the Message Agent starts, receives and sends all messages that can be received and sent, and then shuts down.
Batch mode is useful at occasionally-connected remote sites, where messages can only be exchanged with the consolidated database when the connection is made: for example, when the remote site dials up to the main network.
Continuous mode In continuous mode, the Message Agent periodically sends messages, at times specified in the properties of each remote user. When it is not sending messages, it receives messages as they arrive.
Continuous mode is useful at consolidated sites, where messages may be coming in and going out at any time, to spread out the workload and to ensure prompt replication.
The options available depend on the send frequency options selected for the remote users. Sending frequency options are described in Selecting a send frequency.
To run the Message Agent in continuous mode:Ensure that every user has a sending frequency specified. The sending frequency is specified by a SEND AT or SEND EVERY option in the GRANT REMOTE statement (Adaptive Server Anywhere) or sp_grant_remote procedure (Adaptive Server Enterprise).
Start the Message Agent without using the -b command-line switch.
To run the Message Agent in batch mode:Either:
Have at least one remote user who has neither a SEND AT nor a SEND EVERY option in their remote properties, or
Start the Message Agent using the -b command-line switch.
Connections used by the Message AgentThe Message Agent uses a number of connections to the database server. These are:
One global connection, alive all the time the Message Agent is running.
One connection for scanning the log. This connection is alive during the scan phase only.
One connection for executing commands from the log-scanning thread. This connection is alive during the scan phase only.
One connection for the stable queue (Adaptive Server Enterprise only). This connection is alive during the scan and send phases.
One connection for processing synchronize subscription requests. This connection is alive during the send phase only.
One connection for each worker thread. These connections are alive during the receive phase only.
Replication system recovery proceduresSQL Remote replication places new requirements on data recovery practices at consolidated database sites. Standard backup and recovery procedures enable recovery of data from system or media failure. In a replication installation, even if such recovery is achieved, the recovered database can be out of synch with remote databases. This can require a complete resynchronization of remote databases, which can be a formidable task if the installation involves large numbers of databases.
In short, recovery of the consolidated database from a failure at the consolidated site is only part of the task of recovering the entire replication installation.
Protection of the replication system against media failures has two aspects:
Backup and log management Solid backup procedures and log management procedures for the consolidated database server are an essential part of recovery plans. Backup procedures protect against media failure on the database device. Using a transaction log mirror protects against media failure on the transaction log device.
For more information about backup and log management procedures, see the sections Transaction log and backup management and Adaptive Server Enterprise transaction log and backup management.
Message Agent configuration The Message Agent command-line options provide ways for you to tune Message Agent behavior to match your backup and recovery requirements.
Message Agent configuration is discussed in the following pages.
By default, the Message Agent processes all committed transactions. When the Message Agent is run with the -u command-line switch, only transactions that have been backed up by the database backup commands are processed.
For Adaptive Server Anywhere, transaction log backup is carried out using Sybase Central or the dbbackup command-line utility, or off-line copying and renaming of the log file. For Adaptive Server Enterprise, transaction log backup is carried out using the dump transaction statement.
By sending only backed-up transactions, the replication installation is protected against media failure on the transaction log. Maintaining a mirrored transaction log also accomplishes this goal.
The -u switch provides additional protection against total site failure, if backups are carried out to another site.
Ensuring consistent Message Agent settingsSome Message Agent settings need to be the same throughout an installation, and so should be set before deployment. This section lists the settings that need to be the same.
Maximum message length The maximum message length for SQL Remote messages has a default value of 50K. This is configurable, using the Message Agent -l command-line switch. However, the maximum message length must be the same for each Message Agent in the installation, and may be restricted by operating system memory allocation limits.
Received messages that are longer than the limit are deleted as corrupt messages.
For details of this setting, see The Message Agent.
The Message Agent and replication securityMessages sent by the SQL Remote Message Agent have a very simple encryption that protects against casual snooping. However, the encryption scheme is not intended to provide full protection against determined efforts to decipher them.