2016-06-26

Introduction To SQL Server Event Handling

This is part of a series of posts that explores Event Handling for the SQL Server Database Engine:

When I think about SQL Server "events", I can't help but approach it from a developer's perspective. Consider this example of connecting to SQL Server from SSMS:


There's a couple of "things" going on here:
  1. The Authentication Type is changed from "Windows Authentication" to "SQL Server Authentication".
  2. The Login name and Password are entered. Then one of two things happens:
    1. The Connect button is clicked.
    2. The Enter key is pressed.

In .Net developer speak, those "things" (1, 2a, and 2b) are called Events. An event is a message sent by an object to signal the occurrence of an action. When a specific event occurs, the developer typically wants her application to respond by running code. This is accomplished via an Event Handler.

Below is an example of what the underlying pseudo-code might look like. Each of the event handler methods (highlighted), responds to a specific event:

  1. cboAuthenticationType_SelectedIndexChanged: responds to the SelectedIndexChanged event, which occurs when the value of the Authentication field changes.
  2. btnConnect_Click: responds to the Click event, which occurs when the Connect button is clicked.
  3. txtPassword_KeyDown: responds to the TextChanged event, which occurs when the value of the Password text field is changed.


The .Net developer has a great deal of control when handling events. She can declare event handlers for any of the specific events she wants to handle, and she can write whatever code she wants as a response. She can use pre-defined events and event handlers, or craft her own.


Not Just For The GUI

Although the SQL database engine has no GUI, SQL Server has "events" too. (To be clear, I'm referring to "SQL Server" the compiled application, not the code base that comprises it.) For instance: a login is created, a data file grows, the owner of a database is changed, SQL Server writes an entry to the Windows event log, etc.

While event handling for .Net developers is implemented in a unified way, this is not the case for SQL Server. Event handling for SQL Server lacks the "one stop shopping" afforded to .Net developers. *If* we had access to the code base for SQL Server and wanted to handle a specific event, we could add our own code, recompile sqlservr.exe, and be on our way. But since we don't have this ability, we use SQL Server's run-time hooks. Consider the following:

  • DDL Triggers: handles Data Definition Language events (synchronously).
  • Event Notifications: handles a wide swath of SQL Server events via Service Broker (asynchronously).
  • SQL Alerts: handles the following events:
    1. Events with a specific error number or severity level that are written to the Windows Event Log.
    2. Events for a specific performance condition.
    3. WMI events.
  • sp_procoption: handles the startup event by specifying a stored procedure to run when the database engine service starts.
  • SQL Agent jobs: handles time-based events defined by user-specified job schedules (ie daily, hourly).

These "event handlers" for SQL Server are arguably disparate. Nonetheless, they are excellent tools to help SQL professionals (especially DBA's) proactively monitor SQL instances in an automated way. They let us know when something important happens. They give us the opportunity to take further action. They may even give us a chance to "undo" the event.


Notes From Dave

This was a challenging post for me. The topic had been on my mind for many weeks before I was able to attempt the first write-up. I hope the material has been presented in an organized and meaningful way. Special thanks to these members of the SQL community for proofreading, technical assistance, and general input.

  • Robert L Davis (b|t)
  • Andy Yun (b|t)
  • Stuart Ainsworth (b|t)
  • Stephanie Lock (b|t)
  • Elizabeth Block (t)


SHARE