Menu
Is there a way of notifying a web server (in my case, a web server hosting a MVC2 webapp in C#) of the fact that there was a change to the database? The purpose is to keep the web server cache synchronized with the database.
There are polling constructs in ASP.NET that allow this, but I prefer a push system. I'm also under the assumption that the database can be manipulated by elements other than the web server itself.
My limited know-how of SQL Server/ASP MVC says that one way is through creating a table TRIGGER that pretty much hits a url which will force an update.
971
Right click on the table you want to track changes. Click Properties, click Change Tracking, then in the right pane set Change Tracking to TRUE.
Create a trigger and track the username into a log table. (SELECT APP_NAME() or SELECT program_name from sys. dm_exec_sessions where session_id=@@SPID). Try with SQLAudit functionality which will give you some great granualar information about who is touching your tables, and the commands that are being executed.
If a user wants to find out when was the last table updated he can query dynamic management view (DMV) – sys. dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use DMV to determine when it was updated last.
SqlDependency is the way to go, if the rate of change is moderate. For high rates of change you better poll for changes. To understand how SqlDependency works, read The Mysterious Notification. ASP already has built-in support for SqlDependency, namely the SqlCacheDependency. There is also LinqToCache which adds SqlDependency capability to any arbitrary LINQ query, if possible.
Trigger is absolutely a big no-no. You cannot have database transactions wait on some URL to respond, your performance will collapse to basically nothing. Not to mention the issue of availability (updates will fail if the URL does not respond, for whatever reason).
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
