Sql-server – Send a trigger to an application upon a specific SQL Server server usage pattern

application-designsql serversql-server-2005sql-server-2008sql-server-2008-r2

I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its data access is extremely predictable allows me to infer reliably from query patterns what I need to know to provide a user with additional relevant information pertaining to their current view within the application. Specifically, it always executes TSQL statement (which is the easily identifiable part) with a named parameter (which is the part that defines what the user is viewing) that I'd very much like to get at.

So, in short, I'm looking to create a mechanism that:
– Hook the query pattern (trivially expressible via wildcards)
– Extract the named parameter value
– Sends a message to the (remote) overlay application with the parameter value

Does SQL server have any facilities for any portion of this undertaking? Is there a better way I should be going about this? (Possibly lower down in the network stack.)

Edit: We're using SQL Server 2005, but upgrading to SQL Server 2008/2008R2 isn't out of the question.

Best Answer

There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier. This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy. I'm going to just talk theoretically about something you could build, but it ain't gonna be easy.

Part 1: capturing the query data as it happens. For this, SQL Server's Extended Events are probably the way to go. Think of it as debug points in the SQL Server engine code where you can insert your own code. As you learn about it, you'll find that you can gather all kinds of cool information about queries, but keep in mind that this is blocking code, and the more information you want to gather, the slower your queries go. You won't be able to push data outside of SQL Server in this part - you're going to have to either write the data to an XML file, or put it in the ring buffer. The ring buffer makes the most sense here since you'll need to query it in near-real-time.

Part 2: getting the data from the SQL Server's ring buffer to the client. You could build something to push the data from SQL Server to the overlay tier, but this screams danger to me. It couldn't be single-threaded, because you could hang on trying to send a notification to a client that's running slow, and miss everybody else's notifications. Instead, what I think you want to do is have the overlay app detect on the client side when it needs to check the ring buffer, and then do that via a query. You don't want all the clients continuously polling the ring buffer - performance nightmare there.