April 4, 2012 at 4:39 pm
Any of you folks know of a third party tool that serves up DATA that’s in the transaction log? I’m working on a project where I need to respond to data changes, but I can’t do it by modifying the database. In other words, no triggers, etc. I’m turning to reading the transaction log to find out what’s changed. I’ve tried assembling a query to do that, mostly using system views, but that is proving to be one royal headache. I’m hoping that wheel’s been invented. I'll also take a query that does this if anyone has one that works.
Thank you,
April 4, 2012 at 4:43 pm
There are log readers. ApexSQL is one, can't recall the other. They're both about $1000/license.
Any reason you can't use CDC or Change Tracking?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 4:50 pm
Thanks for the reply. CDC changes the database by adding tables. That's not an option on this project.
OK. ApexSQL. If anyone knows others, I'm still interested in hearing about them. Thanks.
April 4, 2012 at 5:11 pm
peter 82125 (4/4/2012)
Thanks for the reply. CDC changes the database by adding tables. That's not an option on this project.
Why not? What about change tracking? SQLAudit?
We had this discussion with someone else a week or so ago, trying to track changes without any changes made to DB/server is like working with both hands tied.
OK. ApexSQL. If anyone knows others, I'm still interested in hearing about them. Thanks.
The other one is Luminata or something like that, if they're still in business.
It'll require that the DB is in full recovery and that the log be read before any backup, and it may hinder performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 5:15 pm
Yeah, that was me. Not sure why people just can't accept the limitations expressed. Making changes to the database is not an option. Period. Get over it. And, yes, it's like working with your hands tied. That's the nature of this particular beast. No one is forcing anyone to respond here. Having said that, thanks for the tip on the 3rd party tool 🙂
April 4, 2012 at 5:16 pm
p.s. Have you considered extended events?
Edit: or query notifications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 5:21 pm
Change tracking? SQL Audit? Extended events? Don't these things need to be enabled and therefore require a change to the database? Hopefully I'm wrong, but what I've seen from looking them up is that they will require a change. The solution has to be read only.
April 4, 2012 at 5:21 pm
Change tracking? SQL Audit? Extended events? Don't these things need to be enabled and therefore require a change to the database? Hopefully I'm wrong, but what I've seen from looking them up is that they will require a change. The solution has to be read only.
April 4, 2012 at 5:55 pm
Have you looked at database firewall products. Products like "SecureSphere Database Firewall" will monitor all the commands which are sent to the SQL Server. Is this what you are looking for?
April 4, 2012 at 5:59 pm
Can you define "change to the database"? Not being smart or sassy, just trying to get a handle on your restriction. Is it no new objects, no new indexes, no change to anything?
There is DBCC LOG, but that can be a hassle.
The Apex tool is here: http://www.apexsql.com/sql_tools_log.aspx
April 4, 2012 at 8:50 pm
That handles deletions, but it can be adapted for anything else.
I've never tested it so I can't garantee how good it works.
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
April 4, 2012 at 9:13 pm
mrdenny (4/4/2012)
Have you looked at database firewall products. Products like "SecureSphere Database Firewall" will monitor all the commands which are sent to the SQL Server. Is this what you are looking for?
I think this is the best option - monitor all commands coming and going and log that to a separate location.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2012 at 10:00 pm
Right. No changes to the database. Nothing. Zilch. Nada. No new tables, new indexes, triggers, nothing. All I can do is read what's in there. Absolutely nothing else. That's the challenge, here.
Thanks for the additional replies! I'll check out some of the options suggested since my last reply (as long as they don't involve any changes of any kind to the database) and get back to everyone. 😉
April 4, 2012 at 10:05 pm
peter 82125 (4/4/2012)
Right. No changes to the database. Nothing. Zilch. Nada. No new tables, new indexes, triggers, nothing. All I can do is read what's in there. Absolutely nothing else. That's the challenge, here.Thanks for the additional replies! I'll check out some of the options suggested since my last reply (as long as they don't involve any changes of any kind to the database) and get back to everyone. 😉
You've told us that you can't make any changes to the database, but other than being told you can't the big question we still have is simply why? Can you elucidate on this for us?
April 4, 2012 at 10:32 pm
The reason is part technical and even more political. This is a database that is maintained by a third party and their involvement includes the possible scenario where they can make structural and other changes to the database, so anything I put in there can disappear tomorrow. But, as I say, it's a bit more complicated than that. There is also a political component that is more difficult to change than threading a needle with an elephant. The decision not to change the database was made by three IT managers and signed off by two technical VPs who absolutely consider this decision as written in stone--believe me, I've tried to push back. After that miserable experience, I'd rather sit naked in a room full of scorpions with "I Want My Baby Back" blaring over loudspeakers in an infinite loop with progressive volume increase than revisit that (no offense to anyone who enjoys that). I just can't go there. Since I'm not an employee there, I have the option to ditch this project, but changing the database is just not an option.
Hope that helps 🙂
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply