• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Detect DB Changes

I have to trace changes in my SQL 2k5 DB.  This could be anything from a SP to an addition to a row, change, modification, etc.

Is there a way to easily do this?  
0
chrisryhal
Asked:
chrisryhal
5 Solutions
 
Kevin3NFCommented:
All DDL or DML (Schema vs. Data), or a mix of the two?

Tracking every data change is going to KILL your performance and is rarely needed except in the highest of secure applications.

If just schema, check out DDL triggers in Books Online:
http://msdn2.microsoft.com/en-us/library/ms190989.aspx
0
 
moss_guruCommented:
The two ways I know of doing this are 1) adding a trigger to each table to write audit records on change, and 2) periodic comparisons to a "Baseline DB" using third party DB comparison tools.  My favorite is SQL Delta (http://www.sqldelta.com/)...
0
 
climbingjaffaCommented:
In short no SQL Server 2008 will have a nice new feature but not for us on SQL 2005

You can track DDL changes using the new DDL triggers available in SQL Server 2005

http://technet.microsoft.com/en-us/library/ms190989.aspx

As for data chnages such as updates , deletes and inserts you would have to set up archive tables and triggers to populate them. See the link below it may be of help

http://www.sqlservercentral.com/articles/Advanced/dataarchivingproblemsandsolutions/1729/
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Kevin3NFCommented:
So..you recommend taking the two previous answers?   ;)
0
 
Faiga DiegelSr Database EngineerCommented:
The SQL Dashboard (http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en) has one report for Schema Changes History. Try installing it to a non production environment and test if it meets your needs for schema changes logs.

For modifications/updates in a row... you should carefully plan this. One thing we did was setup a Report DB or archival DB. This temporary DB is updated every week. We use Red Gate to compare (Red Gate SQL Bundle 5 >> Data Compare) row by row updates/modifications. The SQL Compare (Red Gate product too) is also a very useful tool to compare schema changes (http://www.red-gate.com/products/sql_tools_overview.htm)
0
 
climbingjaffaCommented:
To answer Kevin3NF comment i had to go to a meeting while i was writing the comment when i started no one had added any comments when i came back only after i submitted i realised that i had been pipped to the post...

As for the neccesity of archiving its crucial in just about any enterprise system ..sadly software development isn't infalable (my spelling :( sorry ) developers write code that the customers acccept that can end up running wreck. And if you go auditing processes you'll to track deletes the phrase we do hard deletes can cause heart attacks in some circles. Soft deletes and updates within the table is another approach but do go down this road it takes performance and starts beating it black and blue.
0
 
climbingjaffaCommented:
Typo central above hit Submit by accident oops... please reed this instead

To answer Kevin3NF's comment I had to go to a meeting while I was writing the comment when I started no one had added any comments, when I came back only after I submitted I realised that I had been pipped to the post...

As for the neccesity of archiving its crucial in just about any enterprise system ..sadly software development isn't infalable (my spelling :( sorry ) developers write code that the customers acccept that can end up running wreck.
And if you go auditing processes you'll have to track deletes and updates, the phrase we do hard deletes can cause heart attacks in some circles.

Soft deletes and updates within the table's is another approach but do not go down this road it takes performance and starts beating it black and blue.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You lmay set up WMI alerts for that.

Hope this helps...
0
 
Kevin3NFCommented:
climbingjaffa....I was just messing around of course :)
0
 
climbingjaffaCommented:
No worries dude ..... :)
0
 
chrisryhalAuthor Commented:
All comments were very good, and I greatly appreciate the help with this
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now