Detecting SqlServr.exe WriteFile Operation Within C#

Question

Hello everyone,

There's a requirement that we will need to support querying a local SQL Server database for new data when the database is updated. Since these are external SQL Server databases, we may not be able to use SQL Server Notification Services nor can we make any changes to the database.

My basic idea is to watch for data being written to the database to trigger a query (instead of polling at set intervals). However, I'm having a tough time trying to figure out how I could get the WriteFile call from the SqlServr.exe process (as can be monitored in tools such as Sysinternals' Process Monitor). Can anyone direct me to which Win32 functions may be able to help me with this?

Answer 1

I don't think going through the file system would be the best option. Even if you could get access to file system activity, I would think it would be difficult to discern what file operation added data vs any other type of file activity like maintenance, index defrags, etc.

A possible alternative would be to look into the Microsoft.SqlServer.Management.Trace Namespace as a mechanism to hook into SQL Server and look for the specific events that you want to respond to. I would be cautions however of the performance impact this may cause.

Answer 2

There's probably no way for you to intercept a FileWrite() call being made by the SQL Server process - if there was, it would be a massive security flaw.

Fortunately, you don't need to, as the filesystem provides you with exactly what you are needing.

The System.IO.FileSystemWatcher class allows you to monitor a file or directory for activity, firing events when changes occur. You should be able to get a prototype up and running with this pretty quickly to (dis)prove the viability of your approach.

However, I think you'll find this approach to be less effective than simple polling - as I suspect you'll end up triggering your own updating process rather frequently.

Answer 3

As other answers mentioned, there is also some internal housekeeping that SQL performs, that might trigger your WriteFile or other low-level monitoring process.

Is it possible to write a wrapper database, that you connect to, store all changes, or timestamp of latest change. And sends them to the real storage. I think you could do something like this with views. Performance will not be optimal.