January 2, 2013 at 12:01 pm
Hello SQL Gurus,
I'm tasked to capture records that change with specific status(es) from a SQL DB. Based on my research, SQL trigger is how I can extract the data . However, that SQL server is an ERP system, and I don't like to alter anything on that database.
My question: What's the best way to capture changed data from outside the ERP SQL database?
I appreciate your input and suggestions
rchafei
January 2, 2013 at 12:20 pm
Can you change how the data is put in? If so, you could capture at that level.
Have you looked into Change Data Capture? No object/code level changes needed for that, if it's available to you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 2, 2013 at 12:55 pm
Thank you for your quick response.
Sorry, I'm not clear on your question; so I'll give it a shot: the users change the data via windows user interface
I've actually considered CDC. and yes, it's available. But wouldn't enabling CDC modify the DB schema? Again, we can't modify the schema on the ERP DB or we can lose maintenance support.
Thanks again,
rchafei
January 2, 2013 at 1:33 pm
rchafei (1/2/2013)
Thank you for your quick response.Sorry, I'm not clear on your question; so I'll give it a shot: the users change the data via windows user interface
I've actually considered CDC. and yes, it's available. But wouldn't enabling CDC modify the DB schema? Again, we can't modify the schema on the ERP DB or we can lose maintenance support.
Thanks again,
rchafei
Enabling CDC modify DB schema "When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database." http://msdn.microsoft.com/en-us/library/cc627369%28v=sql.105%29.aspx so you should first ask the company which give you support if they do not mind. But CDC need the Enterprise license, so maybe the company have cheaper $ solution for your needs? If not, you could use the SQL Trace or buy a product that can analyze a log file if the DB is in full recovery mode.
January 2, 2013 at 2:14 pm
CDC won't modify the tables, like a trigger would, but it does add system objects to the database. If you cannot modify the database at all, then the only option for reading data changes is a log parser. I believe ApexSQL produces one of those.
But those are a pain to use. The more active the database, the more pain.
As already suggested, contact the company that produced the database and application. It's possible (likely) they have a logging/auditing module/option.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 2, 2013 at 4:36 pm
Thank you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply