Sql-server – How to get server event notification

You can use tools which are very efficient in migrating the data from one type of the database to other.

There are many types of tools some of them are freely available.I have used Talend which is very efficient in doing this.

It is also freely available.

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.