|
The following system events are supported for database mirroring:
MirrorFailover This event fires each time a database server takes ownership of the mirrored database. For example, it fires when a server first starts and determines that it should own the database. It also fires when a server previously acting as the mirror determines that the primary server has gone down and, after consulting with the arbiter, determines that it should take ownership.
MirrorServerDisconnect When the connection between the primary server and mirror server or arbiter server is lost, the MirrorServerDisconnect event fires. Within the handler for this event, the value of EVENT_PARAMETER( 'MirrorServerName' ) is the name of the server whose connection was lost.
Events are not fired on a server that is currently acting as the mirror server. As well, mirroring events cannot be defined to execute on an arbiter, since events only run in the context of the database in which they are defined, and the arbiter does not use a copy of the database being mirrored.
You can use these events as a mechanism to send notification via email that action may be required on the mirror database. These events may not fire in all situations that result in the database running on the primary server becoming unavailable. For example, a power outage affecting both the primary and mirror servers would prevent either of these events from being fired. If this type of monitoring is required, it can be implemented on a separate computer via a scripting language by calling dbping to periodically connect to the mirror database. See Ping utility (dbping).
The following example creates an event that notifies an administrator when failover occurs:
CREATE EVENT mirror_server_unavailable
TYPE MirrorServerDisconnect
HANDLER
BEGIN
CALL xp_startmail ( mail_user ='George Smith',
mail_password ='mypwd' );
CALL xp_sendmail( recipient='DBAdmin',
subject='Database failover occurred',
"message"='The following server is unavailable in the mirroring system: '
|| EVENT_PARAMETER( 'MirrorServerName' ) );
CALL xp_stopmail ( );
END;