When it comes to auditing and logging in SQL Server, there are various options available. In a previous article, we discussed passive logging, which involves using log files and log parsers. In this article, we will explore active logging, which involves logging data into query-able tables.
Active logging, whether done through inline procedures or triggers, offers some advantages over passive logging. One major advantage is the ability to query the log data directly from tables. This can be beneficial for reporting purposes or for creating user-accessible undo buttons or reviews. However, it is important to use active logging correctly to avoid potential disadvantages.
One disadvantage of active logging is the management of log tables. As log data continues to grow, it can become a challenge to handle and maintain these tables. Archiving or dumping solutions can be used to mitigate this issue, but they need to be implemented carefully. Additionally, active logging can slow down transactions, as the server has to perform additional logging tasks.
There are two approaches to active logging: using procedures or triggers. When using procedures, the logging code is written directly into the proc that performs the insert/update/delete operation. This approach offers advantages such as consolidated code, easy testing and debugging, and more control over what actions are logged. However, if the table structure changes, the logging code needs to be updated in multiple places.
On the other hand, triggers provide automatic logging regardless of who or what updates the table. Once a trigger is active, logging will occur for every action on the table. Triggers also have direct access to the inserted and deleted tables, allowing for more control over the logged actions. However, triggers may have limited access to input parameters compared to procedures.
Regardless of whether active logging is done through procedures or triggers, there are some important considerations to keep in mind. It is recommended to keep the log tables in a separate database on the same server as the audited database. This helps prevent the main database from growing excessively and allows for easier management of log data. It is also important to ensure that the log tables have an ascending clustered index for faster insertion of data.
When it comes to what to log and how to log it, there are several options available. One option is to create a parallel table with the same columns as the main table and insert every update/insert/delete action into this table. Another option is to log only the columns that are actually changed using conditional statements in the trigger. Alternatively, a single XML column can be used to store the data from the main table.
Each logging option has its own advantages and disadvantages. The choice of which option to use depends on the specific requirements and needs of the auditing process. It is also worth considering commercially available products that can automate the creation of log triggers.
In conclusion, SQL Server provides various options for logging and auditing data. Passive logging using log files and log parsers is a recommended approach in most cases. However, if the need for query-able log data arises, active logging through procedures or triggers can be considered. It is important to carefully evaluate the requirements and choose the most suitable logging option to ensure efficient and effective auditing.