Detect data changes in a "read-only" database?

Question

Detect data changes in a "read-only" database?

peter 82125

SSCommitted

Points: 1612
More actions
March 24, 2012 at 8:57 pm

#253091

I need to identy records that have changed in a table. The constraints on this task dictate that I can only read from the database. I can't use triggers, I can't enable CDC, I can't do anything to it that is not simply reading from it. Is there a way to do this? I'm thinking of logs in SQL Server, like the transaction logs or others. Can I access them? Will they contain the change data for a table? How do I do this?
Thank you.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, can you explain a little more regarding this read only database? Is it a snapshot of a mirror, a log shipped database in standby?

Jeff Moden SSC Guru Points: 1003927 More actions · Answer 2

Lynn, despite the title, I don't believe that the database is in the "read only" mode because of what the OP asked. I believe the "read only" part of it is that the OP is trying to explain that he can only write queries against the database to find out if any thing has changed. I also believe this is an interview question and maybe a poor one at that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003927 More actions · Answer 3

peter 82125 (3/24/2012)
I need to identy records that have changed in a table.

Do you need to know the actual records that changed or just that the contents of the table have been modified in some way?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (3/24/2012)
Lynn, despite the title, I don't believe that the database is in the "read only" mode because of what the OP asked. I believe the "read only" part of it is that the OP is trying to explain that he can only write queries against the database to find out if any thing has changed. I also believe this is an interview question and maybe a poor one at that.

Agreed, I was just trying to get more information regarding the environment.

peter 82125 SSCommitted Points: 1612 More actions · Answer 5

The database is not in any read only mode, I'm just using that term to explain that I am not allowed to perform any modifications to the database. why? It's a very long story involving company politics, incorrect perceptions by those who manage the database and on and on. Can we just take that part for granted and move on? I can't change the database. Period. Can anyone help me?

peter 82125 SSCommitted Points: 1612 More actions · Answer 6

Regarding the question of whether I know what records have changed... I don't. I am looking for a way to detect inserts, updates, and/or deletes performed on records in any table in the database. I need to get the value of a particular column (it's a unique identifier) for any records that have changed. I'm hoping there's a way to read the transaction log or whatever the log is that holds this information and determine if the change occurred since the last time I checked. Thank you.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Log readers are around $1000 per license. The transaction log is not intended as an audit log.

If you can't make any server or database changes then you're pretty much out of luck. Only way I can think of is to schedule a job and run something like TableDiff on all tables or RedGate's SQL Data Compare and keep a second copy of the database you can compare against or schedule a manual script that checksums the tables (but that will just tell you something changed)

If someone changes Bob Smith to Robert Smith and back to Bob Smith, is that considered a change you want to detect?

Is SQLAudit an option? Or is that considered a change?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003927 More actions · Answer 8

peter 82125 (3/25/2012)
Regarding the question of whether I know what records have changed... I don't. I am looking for a way to detect inserts, updates, and/or deletes performed on records in any table in the database. I need to get the value of a particular column (it's a unique identifier) for any records that have changed. I'm hoping there's a way to read the transaction log or whatever the log is that holds this information and determine if the change occurred since the last time I checked. Thank you.

You can see if any of the indexes have been used from the sys.dm_db_index_usage_stats DMV to see if any insert, update, or delete has occurred (last_user_update column will give you the date and time).

To actually read the ID's of the data changed, that's a whole lot tougher like Gail said. There is a way although I've not personally tested it. Please see the following URL. I make absolutely no claims to its accuracy or viablility because, like I said, I've not tested it (yet). The methods in that article are used to restore rows of deleted data from the log file but the principles are the same. The article also references a book by Kalen Delaney and you should probably get that book if you really want to dev some smart code to do this. Here's the link to the article...

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

Of course, the best thing to do would be to buy a real log reader.

Last but not least, if your database isn't in the FULL recovery mode, you may not be able to find any of the changes. Even if you are, you're going to have to sync up with log file backups or you could be reading empty logs.

Tell whoever asked you to do this that I think it's a really bad task to ask for. They should save their "company politics" for voting day and let you do the bloody job correctly using any of a dozen audit methodologies. Heh... in your words, it's time for them to "take that part for granted and move on."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (3/25/2012)
I make absolutely no claims to its accuracy or viablility because, like I said, I've not tested it (yet). The methods in that article are used to restore rows of deleted data from the log file but the principles are the same. The article also references a book by Kalen Delaney and you should probably get that book if you really want to dev some smart code to do this. Here's the link to the article...
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

It'll work, the theory is sound. That said....

* Deletes are about the simplest operations when it comes to log records. Inserts and updates can be much, much harder as there is not necessarily a single log record per index per row inserted or updated, and that's assuming all operations are fully logged (so full recovery model)

* Log internals can and do change from version to version and they are not documented in any way.

* You have to know when/how to poll the log. If the DB is in simple recovery that has to be before a checkpoint runs (and those don't run on a schedule), in full or bulk-logged recovery that has to be before a log backup

* If the DB is active reading the log could significantly impact performance. I'm not saying that as a theoretical problem, I've seen it happen one time when I was polling the log for page splits. The query took 6 minutes to read over 15 minutes of log records and resulted in a measureable decrease in performance of all data modifications

With what I know of the log structure, I don't think I could put together code that could pull inserts, updates and deletes out of the transaction log.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

peter 82125 SSCommitted Points: 1612 More actions · Answer 10

I very much appreciate all the replies. Thank you, all. I'm starting to get an understanding of the complexity involved in this and am re-evaluating this whole project. Thanks, again.

Eric M Russell SSC Guru Points: 125527 More actions · Answer 11

One option for detecting table inserts, updates, and deletes is to implement a server side trace on perhaps the StmtStarting event and also filtering TextData.

To create a trace, the DBA would first have to grant you "ALTER TRACE" permission.

However, auditing data modification events or change data capture is really something that should be tasked to the DBA. The ideal solution would invove rerofitting to the database.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho