Menu
Using .Net 4.0 and SQL Server 2008 R2
I have been looking into utilizing SqlDependency / SqlNotifications for monitoring some database structures. Is the best process / practice for this to utilize the SqlDependency / SqlNotifications with SQL Server Service broker?
Are folks using other methods to accomplish a similar task? I could always poll for changes, but I would rather not have to do that. On the other hand I don't want to introduce a complex scenario into our existing environment either.
EDIT (Potential Options):
637
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.
Inspect the "TextData" column for the events: CREATE DATABASE, DROP DATABASE, ALTER DATABASE in order to know what database was changed. To find out who changed it and when it was changed, refer to the "LoginName" and "StartTime" columns respectively.
At the basic database level you can track changes by having a separate table that gets an entry added to it via triggers on INSERT/UPDATE/DELETE statements. Thats the general way of tracking changes to a database table. The other thing you want is to know which user made the change.
Query the sys. objects table to find the objects that changed and filter by modify_date and type ; U = User table, P = Stored procedure. This approach will tell you what objects have changed, but not the specific changes.
Check out event notifications:
http://msdn.microsoft.com/en-us/library/ms175854.aspx
63
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
