Home Getting started Inserting a row

How SQL Retroview works

SQL Retroview was designed to provide extremely flexible, powerful, and efficient database auditing. Unlike other auditing solutions, in which data is only available to administrators through a specialized tool, SQL Retroview provides programmatic access to previous information using the .NET DataInterface module, or even directly from native SQL code and procedures.  This means you can give the end users of your system a query interface which includes the time dimension!  As SQL Retroview's functions can be used exactly as a table, you can build complex queries (involving joins, grouping, aggregates, etc.), and simply specify the historical time from which to retrieve the data.

When making data changes to an audited table, you need to use the functions provided by SQL Retroview.  When you make an AuditedInsert, metadata (Who and when a row was created, updated, or deleted) will be saved for the row.  When you make an AuditedUpdate, the old values will be stored by SQL Retroview for historical purposes, and the new values updated in the table.  When you make an audited delete, the row is not actually removed from the table. Rather, SQL Retroview flags the row as being deleted, so when you access the "Current" view for that table, the row will not appear.

Using the information saved through these operations, SQL Retroview's can easily provide the data which was in a table at any given historical time. You can also access powerful queries to show the list of all changes for a given row/column, undelete rows, and even roll back rows to a given time.

Deploying SQL Retroview on your database

The first step to using SQL Retroview is to launch the Configurator and deploy SQL Retroview for the tables/columns you wish to audit. Begin by establishing a connection with the SQL Server 2005 database on which you wish to setup auditing. The logon you specify will need to have permissions to create objects such as tables, user-defined functions, and procedures, as well as inserting data. The following requirements apply, and you will be notified if your database does not meet these requirements:

  1. The database must be SQL Server 2005 or later.
  2. The database must be in compatibility level '90' or higher. You can change the compatibility level in the database properties window using SQL Server Management Studio.

You should also be aware of the following general guidelines.

  1. Each table you wish to audit must contain an Identity (auto-number) primary key column.  If it does not, the table will not appear on the list of availabe tables to audit.  You can easily add an Identity column even after the table has been populated with data.
  2. Users in your system should also have a unique integer identifier, as you will need to specify this when performing an audited action. If you are currently using a string-based identifier, you will need to add an additional integer identifer.
  3. Although SQL Retroview supports large columns (such as varbinary(max) and varchar(max)), they decrease performance for that table. Consider whether auditing is necessary on such columns.

Once you have established the connection, simply select the tables/columns you wish to audit, and press "Full Deploy". That's it! After executing the deployment, your database is setup for auditing.

Enabling the CLR functions

To reap the full benefits of SQL Retroview, we recommend that you setup your database to use SQL Retroview's CLR functions. These will give you access to many of the core functions directly from native SQL code or procedures. To do this, choose "Enable CLR" from the Deployment menu. Several steps may be necessary to enable the CLR functions, depending on the current setup of your database, and you may require elevated privelages to perform these steps. For this reason, the SQL statements are supplied so you may execute them manually if necessary. The steps follow:

  1. Ensure that CLR is enabled for the database.
  2. Create an asymmetric key for the assembly ("InfoMason.SqlRetroview.Clr.dll"), create a login for the key, and grant permissions to the login.
  3. Create a reference to the assembly.
  4. Create references to each of the functions in the assembly.

Auditing additional tables/columns

Once you have deployed a set of tables/columns, those items will be grayed out in the Configurator, indicating that they are already deployed. However, your table structure may change over time, or you may decide to audit more tables or columns. To do this simply launch the Configurator again, select the desired columns, and run a Full Deploy. This will refresh the definitions for already deployed tables/columns, and add the additional ones you checked.