User-defined stored procedures always perform their tasks in the primary database. For example, whether you issue user_created_proc from the pubs2 primary database or from the pubs2 proxy database, it executes on the pubs2 primary database.
System procedures issued from a proxy database are handled based on these criteria:
A request to execute a user-defined stored procedure in a high availability system proxy database is transformed into a remote procedure call (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 in sybsystemprocs, then in master. If a procedure is not found, the request is converted to a remote procedure call (RPC) and forwarded to the server that owns the original database (as is the case with user-defined 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 an 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 high availability configuration. User proxy databases do not exhibit this behavior.
System procedures issued in a companion configuration are processed using the same rules as a single server. For a description of how system procedures are processed, see the Reference Manual: Procedures.
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.
