Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 12. Administering SQL Remote for Adaptive Server Enterprise
This section describes how to run the Message Agent for Adaptive Server Enterprise. For information on features of the Message Agent that are common to Adaptive Server Anywhere and Adaptive Server Enterprise, see Running the Message Agent.
The Message Agent and replication securityIn the tutorials earlier in this book, the Message Agent was run using a user ID with system administrator permissions. The operations in the messages are carried out from the user ID specified in the Message Agent connection string; by using a system administrator user ID, you can be sure that the user has permissions to make all the changes.
In practice, you will not use such a user ID, but the Message Agent needs to run using a user ID with replication role. You can grant replication role with the following statement:
sp_role 'grant', replication_role, user_name
Running multiple Message AgentsThe three phases of Message Agent operation are described in the section Message Agent operation phases. To summarize, these phases are:
Receiving messages.
Scanning the transaction log.
Sending messages.
You may wish to run separate copies of the Message Agent to carry out these different phases. You can specify which phases a given Message Agent is to execute on the Message Agent command line.
The command-line options are as follows:
Receive The -r command-line switch instructs the Message Agent to receive messages while it is running. To cause the Message Agent to shut down after receiving available messages, use the -b switch in addition to -r.
Scan log The -i command-line switch instructs the Message Agent to scan the transaction log into the stable queue while it is running.
Send The -s command-line switch instructs the Message Agent to send messages while it is running.
Multiple phases If none of -r, -i, or -s is specified, the Message Agent executes all three phases. Otherwise, only the indicated phases are executed.
There are several circumstances where you may wish to run multiple Message Agents.
Ensuring the transaction log does not run out of space It is important that the transaction log not be allowed to become full. For this reason, you must scan the transaction log frequently enough to ensure that all entries required by SQL Remote are placed in the stable queue. Therefore, you may want to run a Message Agent that scans the transaction log continuously, even if you are only receiving and sending messages in batch mode.
Mixing operating systems If you wish to use a message link supported under Windows 95 or NT, you must use a Message Agent on that platform to send and receive messages. You can do this, while running the log scanning on a UNIX machine, by running two copies of the Message Agent.
The operations of two or more Message Agents are synchronized by a table called sr_marker. This table has a single column called marker, of data type datetime.
When the Message Agent wants to wait for transactions to be scanned into the stable queue, it updates sr_marker and waits for it to work its way through the system. The column in sr_queue_state is also called marker, and contains the most recent marker to be scanned from the transaction log.