Whether you issue a user-defined stored procedure from the primary database or the proxy database, the user-defined stored procedure performs its tasks within the primary database. That is, regardless of whether you issue user_created_proc on the pubs2 primary database or from the pubs2 proxy database, it always executes on the pubs2 primary database.
If you issue a system procedure from within a proxy database, it is treated differently depending on the following:
A request to execute a user-stored procedure in a high availability system proxy database is transformed into an RPC request and sent to the server that owns the original database.
For system procedures, search rules are invoked such that the procedure is looked for first in the current database, then sybsystemprocs, then master. If a procedure is not found, the request is converted to an RPC and forwarded to the server that owns the original database (as is the case with user-created stored procedures).
CIS first looks for the system procedure in the local server. If it finds the system procedure locally, it is executed as a local stored procedure.
If the system procedure cannot be found locally, it is forwarded to the primary companion as a remote procedure call (RPC).
If it is a user-defined stored procedure, it is turned into an RPC and forwarded to the primary companion.
This behavior applies only to “system” proxy databases – that is, those that are created automatically by the HA configuration. User proxy databases do not exhibit this behavior.
Generally, users do not see any difference whether they issue the system procedure from within the proxy or primary database.
System procedures issued in a companion configuration are processed according to the same rules as for a single server. For a description of how system procedures are processed, see the Adaptive Server Reference Manual.
If you use sp_dboption to change the database options on the primary database, these changes are not automatically forwarded to the proxy databases on the secondary companion. You must set the sp_dboption on the proxy database as well.
For example, if you use sp_dboption to change the pubs2 database so that select into bulkcopy/pllsort is on the primary companion, the pubs2 proxy database on the secondary companion is not set.
