Database Auditing: Method 2 - Replication

Transact replication is another method of auditing tables.

Description:
Modify the stored procedures generated by replication to suit the needs. Optionally you can use triggers discussed in the previous post against the replicated tables as well.

Advantages:

• Replication works in a separate thread. Thus, application will not be affected directly.
• Replication reads from Transaction log. It is much faster as it is sequential.

Disadvantages:

• The replicated data should reside in a separate database. This is the first limitation.
• Some editions do not support transact replication. Merge replication may require complex stored procedures/triggers
• Replication is one of the least published mechanism. Troubleshooting may be a nightmare.

Links:

• I should create some links.

Database Auditing: Method 1 - Triggers

I am going to talk about different methods for database auditing. Auditing could be done at different levels:
1. Capture who did what (e.g. "Jim modified with invoice")
1. Capture the last updated datetime of a record as well. ("Invoice 1003 last modified by Jim at 21 Nov 2005 12:35:42.767")
2. Capture the previous data and the current data with CURRENT_TIMESTAMP.

To do auditing, we need to establish a few priciples.
Any layer can audit the data available only at that layer. For example, real user name (the user who uses the application may be different from the application's user name to connect to the database. The DB won't know the application user unless specified explicitly.
There are different techniques available for auditing.

Today, its about using triggers for database auditing.

Description:
Use either instead of or after triggers to capture the data.

Advantages:

• Easy to write.
• Useful when the database is already in place.

Disadvantages:

• Performance goes down; database needs more resources; application waits until trigger is completed.
• Blob fields are not audited; update to blob field only will not be audited.
• Complexity increases when additional triggers are placed.
• Triggers are fired automatically; application has no control on that.

Some useful links:

• http://www.sqlservercentral.com/scripts/contributions/521.asp This script creates the triggers to audit table
• http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp An article on this subject
• http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=259&messageid=234334 Comments posted on above article including from me.

What's New That's Not So New

SQL Server 2005 is finally out with a Lot of "new" things! People are so exited!
Microsoft has released the list of things that are new in SQL Server. One of the Nice feature is change of definition of Schema. Schema is no more refers to a user.
However, in practice most of the companies used the same technique. They created a dummy user and created the objects under that schema. Somewhere in 2000 when we faced same set of tables for two different - interconnected - systems (Accounts Payables and Accounts receivables) we came up with the mechanism of using schemas.

The application connects to the database based on the application user.

However, we had issues of db users connecting to server for gathering information. Some may search on wrong tables and tell us "The data I stored is missing".
However, those are simple issues and were resolved without much delay.

So is redefining schema a new concept. Yes and No. It is yes as at the database level it is redefined. No as it is the way the industry uses.