Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.

On SQL Server 2005, we currently log certain table changes via triggers using the inserted/deleted tables. Currently, our log tables exists in the same database as the primary tables and I was thinking that from a management perspective, moving the log tables to a different database has advantages. In the very least, log tables tend to grow at a factor of 10 for the source table, so managing the different types of tables is along different paths and separating them by db may be helpful.

If we go this route, the trigger has to log across db boundaries (same server though). What problems would this create that I don't have now?

So far, the main one seems to be that someone could take down a db independently of another (rare), and then the logging would be lost and insert would be kept, or inserts would fail b/c the trigger fails, depending on how the trigger was written.

Are there other risks?

Another solution that was offered was to log to the same db and have a job move (delete and copy) the records over to the other db. However, I still don't know why that's a better solution.

share|improve this question
    
Depending on the logic in your trigger, and keys on your tables, there is also the possibility that you could experience unexpected locks. –  RoKa May 1 '13 at 14:00
    
why logging outside the database ? databases are , after all , made for keeping data.... –  haki May 1 '13 at 15:39
add comment

1 Answer

There aren't many issues beyond what you have now that I can think of off-hand (I'm sure someone will correct me though!). Since you are proposing to have your audit DB on the same server, and, I presume, instance, then by using three part naming you are all set. You've already noted the unlikely possibility of someone removing the audit DB and likewise there's a slim chance someone might re-name it, move it, leave it in an uncertain state by some failed maintenance and so on, but again these aren't that likely. If all your DBs are in the same file group or all on oen disk/array of disks then there might be a performance implication, depending on the level of traffic you expect so you might want to consider where your audit DB physically resides quite carefully: Same SQL instance doesn't have to mean the same physical disk.

With regards to trigger design, you would most likely be using AFTER INSERT and you could handle exceptions inside the trigger so that they are not raised to the caller and therefore the transaction completes on the parent table. Of course, you wouldn't be getting audit data then and you must decide if that is a problem for you and if so you'll need a suitable strategy for raising an alert of some kind. But you'll need to consider if it is right and acceptable to swallow exceptions in that way: Most developers I know (and I am one!) would look like you'd asked them to do something un-natural with a chicken if you asked them to bury exceptions in that way.

I'm not sure I understand the rationale for a job which copies and deletes records: That's a programming smell to me. Why over-complicate things? You're right to question it.

share|improve this answer
2  
+KARMA for the chicken statement –  RoKa May 1 '13 at 14:05
1  
What is it with people down-voting and not explaining why? If there's a problem with an answer then correct it with a comment or your own answer! –  Steve Pettifer May 1 '13 at 16:25
1  
I Like the answer. I have also to say that updating not working without audit db is not a negative in my eyes. If you set up those triggers, some idiot removing the audit db - you may legally have to keep any change audited, so no audit = no change. –  TomTom May 31 '13 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.