SQL SERVER – Auditing and Profiling Database Made Easy with SQL Audit and Comply
- by Pinal Dave
Do you like auditing your database, or can you think of about a million other things you’d rather do? Unfortunately, auditing is incredibly important. As with tax audits, it is important to audit databases to ensure they are following all the rules, but they are also important for troubleshooting and security.
There are several ways to audit SQL Server. There is manual auditing, which is going through your database “by hand,” and obviously takes a long time and is quite inefficient. SQL Server also provides programs to help you audit your systems. Different administrators will have different opinions about best practices and which tools to use, and each one will be perfected for certain systems and certain users.
Today, though, I would like to talk about Apex SQL Audit. It is an auditing tool that acts like “track changes” in a word processing document. It will log what has changed on the database, who made the changes, and what effects these changes have had (i.e. what objects were affected down the line). All this information is logged, and can be easily viewed or printed for easy access.
One of the best features of Apex is that it is so customizable (and easy to use!). First, start Apex. Then you can connect to the database you would like to monitor.
Once you select your database, you can select which table you want to audit.
You can customize right down to the field you’d like to audit, and then select which types of actions you’d like tracked – insert, delete, or update. Repeat these steps for every database you want monitored.
To create the logs, choose “Create triggers” in the menu. The script written here will be what logs each insert, delete, and update function. Press F5 to execute. All this tracking information will be stored in AUDIT_LOG_DATA and AUDIT_LOG_TRANSACTIONS tables. View these tables using ApexSQL Audit reports.
These transaction logs can be extremely detailed – especially on very busy servers, where every move it traced. Reading them can be overwhelming, to say the least. Apex has tried to make things easier for the average DBA, though.
You can read these tracking logs in Apex, and it will display data and objects that affect your server – even things that were happening on your server before you installed Apex!
To read these logs, open Apex, and connect to that database you want to audit.
Go to the Transaction Logs tab, and add the logs you want to read.
To narrow down what results you want to see, you can use the Filter tab to choose time, operation type, name, users, and more.
Click Open, and you can see the results in a grid (as shown below). You can export these results to CSV, HTML, XML or SQL files and save on the hard disk.
One of the advantages is that since there are no triggers here, there are no other processes that will affect SQL Server performance. Using this method is also how to view history from your database that occurred before Apex was installed. This type of tracking does require storage space for the data sources, as the database must be fully running, and the transaction logs must exist (things not stored in the transactions logs will not be recoverable).
Apex can also replace SQL Server Profiler and SQL Server Traces – which are much more complex and error-prone – with its ApexSQL Comply. It can do fault tolerant auditing, centralized reporting, and “who saw what” information in an easy-to-use interface. The tracking settings can be altered by the user, or the default options will provide solutions to the most common auditing problems.
To get started: open ApexSQL Comply, and selected Database Filter Settings to choose which database you’d like to audit. You can select which tracking you’re like in Operation Types – DML, DDL, queries executed, execute statements, and more. To get started, click Start Auditing.
After this, every action will be stored in the central repository database (ApexSQLCrd). You can view the audit and create a report (or view the standard default report) using a wizard.
You can see how easy it is to use ApexSQL Comply. You can easily set audits, including the type and time, and create customized reports. Remote users can easily access the reports through the user interface (available online, as well), and security concerns are all taken care of by the program.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology
