Steve Jones - SSC Editor (5/5/2015)
Eric M Russell (5/5/2015)
In most scenarios the auditing operation should be done asynchonously, if possible, for performance reasons. But you can't do that if the auditing operation is bundled within batch transaction.Another reason for separating the auditing operation from the batch transaction is that in the event the batch transaction fails, so does the audit event. I'm sure we're all familiar with the scenario where an error occurs, the error is logged to an audit table... but then the audit table insert is rolled back along with the rest of the batch transaction thus leaving no evidence that the error occurred.
This this isn't auditing, nor is it architected well. If I wanted to teach someone about auditing, this would be the issue. Need a variable that can be logged at the end storing the actions.
I guess I don't quite see the difference between using a variable that later gets stored from the requirement you're surprised by. It's not logged synchronously, and can fail just as easily. If the server were to lose power 2 hours into a job and the auditing is being logged to a variable - guess what you don't have when you fire up the server again....
This is the kind of requirement where service broker might really shine, since it does take the auditing of the actions out of the main process AND has guaranteed delivery. You don't want every process you have logging to a central audit table and all be held up waiting for some other process to finish the recording of whatever audit is required. Just because it doesn't happen synchronously doesn't have to mean you can't guarantee that it happens.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?