Posted by Erland Sommarskog on 01/23/07 22:41

My news server had a crash, so I have not been able to access this group
for a week. But I saw on Google that Rico had some questions to my post:

> Erland, always helpful info. I don't know why I didn't think of the
> Transaction Logs (oh, I know, it's my inexpreience!). I will definitely
> take a look at that. One question, can the transaction logs be queried
> and used to update a central repository?

No, transaction logs as such cannot be easily queried. The format is
proprietary. But there are third-party log-reader tools that are able
to display the information in the log, and also more versatile tools
that can present the data for audit purposes. At least Lumigent has
such a tool, and you should definitely check this out.

> One of the requriements of this application is to keep a log of any and
> all changes to the data at each remote location. All remoted databases
> will update a central repository "web" database that will be used to
> report on this info (which is part of a study).

That's a very tall order, and I would suspect that the third-party
tools that I mention do not fit in here. But it also seems that
someone decided for a solution without considering alternatives first.

The problematic things here is that updating remote tables in the
triggers makes me very nervous about performance. If there is frequent
action on these tables, the system may grind to a standstill.

Alternatives? One way is to have the audit tables locally, and then
have some other process that moves the data to the central repository.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]