How to create sql transaction audit software without using fn_dblog or fn_dump_dblog directly on database

Question

I am working on a sql transaction audit software

initial i am using fn_dblog on database to read sql transactions and write them in a file on regular time interval but then i find that it's risky to use fn_dblog on production database

so please suggest me any other alternative way to get data from production database

Note - I am already try to get sql transaction history from backup file(.bak) by reading transaction from it (using fn_dblog on my system not on database) but that does not help me (because it not contains all transaction history)

Answer 1

fn_dblog() is an undocumented SQL Server function so you are wise to be careful using it.

Your question on how to audit transactions really depends on what you want to audit. Is it really every single transaction that occurs? Or, is it specific types of transactions like DDL and DML operations? There are a few options depending on the answer to this, yoru specific business case, etc.

• Change Data Capture was introduced in 2008 and captures insert, update, and delete activity. For many cases, this is what folks want to edit. What user (usually from an application) edited data in the database.
• DDL and DML Triggers can be created to fire when a specific event occurs. For DML this includes actions like INSERT, UPDATE, DELETE, etc. You can create the trigger to log the action after it occurs as an auditing feature. These types of triggers can lead to performance issues, so be sure to read up on them. I'd start with Aaron Bertrand's blog on them. DDL triggers depend on certain DDL events like creating and dropping a database, creating encryption, altering indexes, creating users, etc.
• SQL Server Audit is a built in mechanism that create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files. Note a big difference from Change Data Capture is that it isn't stored in clean relational format like CDC is.
• Change your application. This would be an application and database design change, but this method is commonly implemented. Essentially each time a change is made to a data row, instead of editing it, a new row is inserted with a timestamp and the user that made the change. This record becomes the "current truth" record and all other records area history of that record. SQL Server has methods to make this integrated using ROWVERSION and TEMPORAL TABLES
• Use a paid option like Redgate, Solarwinds, etc...