The Before-After Data tab of the Audited Database Properties window allows you to select the tables for which you want to collect before and after data. You can collect before and after data for DML events generated by DELETE, INSERT, and UPDATE commands.
Collect before and after data when it is critical to capture the exact data change in a table column. When this feature is enabled, you can evaluate the before value and after value for each change in the Audit Events view. Enabling this feature can impact your Collection Server and Management Console performance.
If you want to collect before and after data , verify that you are auditing DML events on this database and that common language runtime (CLR) is enabled on the corresponding SQL Server instance.
IDERA provides limited support for before-after data auditing of the publisher database in SQL Servers with replication. However, this scenario is supported only when the publisher database with transaction replication is set to replicate data ONLY. If the target database uses SQL Server replication set to replicate more than data , do not enable before-after auditing. Before and after data collection does not support SQL Server replication in that situation. For more information, see Microsoft Books Online for the version of SQL Server you are using.
To successfully audit specific columns on a table, ensure the table name does not contain the following special characters: \ / : * ? "
Available actions
Specify tables for before and after data collection
Use Add and Remove to specify the tables for which you want to collect before and after data.
Specify which columns to audit
Use Edit to specify which columns you want to audit. You can audit all columns or individual columns that do not contain BLOB data.
Select the maximum number of rows to collect
Use Edit to select the number of rows per transaction that you want to audit from this table. For example, if you select 100 rows, the SQLcompliance Agent will capture the first 100 rows of each DML transaction, and collect all column updates for each captured row. By default, the first 10 rows per DML transaction are captured.
Enable Now
Allows you to enable CLR on the instance hosting this database.
CLR is required by .NET Framework to access details about DML events on the SQL Server database. For more information, see Microsoft Books Online.
Available fields
Table Name
Provides the name of the table you are auditing on this database.
Maximum Rows
Provides the maximum number of rows that the SQLcompliance Agent will capture of each DML transaction.
Columns
Indicates the status of the columns associated with the audited tables. Typically, this field displays All Columns or lists the individual columns that are audited for before-and-after data.
If the audited table contains BLOB data and individual columns that are not selected , the status will display as Not Configured. SQL Compliance Manager does not support auditing BLOB data. To audit data changes on this table, click Edit and then choose the available columns that do not contain BLOB data.
Set up auditing before and after data
Auditing before and after data is an extension of DML event auditing at the table column level.
- Ensure Database Modification (DML) activity is selected on the Audited Activities tab.
- Ensure the appropriate tables are specified on the DML/SELECT Filters tab.
- On the Before-After Data tab, click Add to choose which audited tables should also be audited at the column level for before and after data.
- Choose the appropriate tables, and then click OK. By default, all columns are audited.
- If you want to audit specific columns , select the table, and then click Edit.