What are the risks for logging across databases via a trigger?

Question

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.

Answer 1

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.