How many times have you needed to debug an application and wished, just wished, you had put better instrumentation in your code? It would have been brilliant if you had logged the key database fields before and after the update. It would have been killer if you had recognized exactly which rows and columns were in play and had a way of confirming that your stored proc is doing what you thought it would.
But you didn’t.
Your app has no logging at all, and you have no ability to see what’s happening in real-time other than firing up ol’ SQL Server Management Studio and attempting to run queries before and after every action in your app. Now there is a better way.
What is this?
SqlWatcher lets you use your web browser to query a SQL Server 2005/2008 database. Big deal so far. But after returning your results to you, SqlWatcher watches the database and notifies you instantly when your query results change, re-running your query and appending the latest results to your web browser as needed.
SqlWatcher does not poll for changes, nor does it leave a database connection open, both of which could hammer your database performance. It uses the power of SQL Server’s Query Notifications to let the database engine itself track data changes that would impact your query results. Whether data was added, deleted, or modified, if it touches data that was in your results, or adds new data to it, Query Notifications sees it and pushes a notification out to a SqlDependency object in SqlWatcher. When SqlWatcher receives a notification, it re-runs your query and appends the latest results to your web browser, allowing you to see your query’s results before and after every database change.
Query Notifications relies on SQL Server’s Service Broker for reliable asynchronous delivery of these notifications to clients such as SqlWatcher. As a result, Service Broker must be enabled on the database you are trying to watch. If it’s not, SqlWatcher will attempt to run
ALTER DATABASE yourdatabase SET ENABLE_BROKER
beforehand, and
ALTER DATABASE yourdatabase SET DISABLE_BROKER
afterwards, but you will need to provide administrator-level credentials in the connection string for this to work.
How do I use it?
- Download SqlWatcher
- Unzip the /distribution folder to a new folder named /SqlWatcher on your web server and create a new application in IIS MMC
- Point your browser to http://(your server)/SqlWatcher
- Enter the server, database, login, and password to connect to your database
- Enter a SELECT query, or a stored procedure call that performs a SELECT. See Special Considerations Using Query Notifications (ADO.NET) for the fine print, but wildcards are forbidden and table names must use two-part names. In other words,
SELECT * FROM customers WHERE last_name LIKE 'Pot%'
will fail, but
SELECT id, first_name, last_name FROM dbo.customers WHERE last_name LIKE 'Pot%'
will work.
- Enter the number of updates you wish to see, or 0 for unlimited. Your browser will automatically append each database update as it occurs until Max Notifications is received or you hit ESC in your browser, at which point SqlWatcher will automatically clean up.
- Run your application. As you make changes, SqlWatcher will add more data snapshots to your browser. For clarity my example client code is extremely bare-bones, so feel free to fancy it up with some AJAX.
Documentation
Source code is fully documented. CodeProject: Using SqlDependency for data change events provides an excellent introduction to, and concrete examples of, working with the SqlDependency class, and was instrumental in getting SqlWatcher operational. SqlWatcher was written in C# in Visual Studio 2008 and requires ASP.NET 2.0. It has no database of its own but can watch any SQL Server 2005/2008 database with Service Broker enabled, or can enable it automatically with an admin-level connection string.
Download the code
Click on the icon to go to the download directory then select the latest version:
Licensing
This arc90 tool is licensed under the BSD license.
Discuss
Feel free to offer feedback on the blog, or feature requests/bugs on Google Code.