Problem
My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server – OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
Solution
Being in the position to start to capture historical data is a good one. This gives you the opportunity to build a solution that can be used across various applications in the enterprise. As such, this is a big undertaking so an appropriate amount of time should be spent planning, building, testing and implementing the solution. If you agree with that premise, then this need should be considered a project and a formal process should be followed if the decisions you make have a wide scope across your applications and business processes.
Requirements
If you agree that capturing auditing data is a project, then it is appropriate to start with defining the requirements for the project. Consider gathering the following information as a baseline set of requirements:
- Find out what data needs to be audited
- Find out why the data is going to be audited
- Find out how the data is going to be used
- Reporting and\or Business Intelligence
- Historical record of changes for customer service purposes
- Legal or regulatory requirements
- Find out which databases and applications need to be audited
- Find out what type of meta data needs to be stored with the audited data
- Start date
- End date
- Start date and end date
- Date the record was captured
- User making the change
- Corresponding application, database, SQL Server instance, etc.
- Reporting needs
- Line of business reporting
- Monthly reporting
- Business process reporting
- Reporting frequency
- Reporting users
- Line of business application access to the auditing data
- Data collection
- Determine if triggers, the OUTPUT clause, stored procedure logic, application logic, Service Broker or some combination of these approaches will be responsible for writing the data to the audit tables
- Data consistency requirements
- Based on how the data is going to be used determine if all of the audit data should\could be stored in the same format
- Audit data location
- Stored with the base data i.e. same database or in a separate file group on a different disk sub system
- Stored in a specific database on the instance i.e. separate database
- Stored in a centralized SQL Server database i.e. remote instance
Design Options
As indicated above, a couple of key technical design decisions relate to how the data is written to the auditing tables, where the auditing data is stored and if a consistent approach can be used to store the data for reporting purposes. With different answers to these questions leading to a different solution, let’s outline a few different sample ideas to see if any of these could resolve your issue:
Base Table Copy |
| This approach could be considered the traditional auditing technique where an auditing table is created for each base table that needs to be audited. The design from the base table to the audit table are very similar, but the audit table has some additional columns to support managing the historical data (i.e. AuditID to serve as the identity, ModifiedBy to serve as a means to capture who made the change, etc.). Let’s use a sample AdventureWorks table ([HumanResources].[EmployeePayHistory]) in the SQL Server 2005 database as an example: |
| USE [AdventureWorks] GO CREATE TABLE [HumanResources].[EmployeePayHistory_Audit]([AuditID] [int] IDENTITY (1,1) NOT NULL, [EmployeeID] [int] NOT NULL, [RateChangeDate] [datetime] NOT NULL, [Rate] [money] NOT NULL, [PayFrequency] [tinyint] NOT NULL, [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePayHistory_Audit_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](100) NOT NULL CONSTRAINT [DF_EmployeePayHistory_Audit_ModifiedBy] DEFAULT (SUSER_SNAME()) CONSTRAINT [PK_EmployeePayHistory_Audit_AuditID] PRIMARY KEY CLUSTERED ( [AuditID] ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO |
Generic Design with Lookup Table |
| Another approach to auditing is with a generic table to store the data needed in conjunction with a lookup table to identify the original column for the audited data. The value in this design is the same table design can be used for each base table, the lookup table can be extended to support new columns and reporting is consistent. In addition, this design can be further extended to support a single database, an instance, an application or an entire environment with a few extra columns and the needed supporting infrastructure. Below is one example for a single table to support auditing for a few columns in the SQL Server 2005 AdventureWorks database: |
| USE [AdventureWorks] GO CREATE TABLE [HumanResources].[EmployeePayHistory_Audit_Generic]([AuditID] [int] IDENTITY (1,1) NOT NULL, [AuditColumnID] [int] NOT NULL, [BaseTableUniqueKey_Value] [int] NOT NULL, [AuditColumnID_Value] [varchar](500), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePayHistory_Audit_Generic_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](100) NOT NULL CONSTRAINT [DF_EmployeePayHistory_Audit_Generic_ModifiedBy] DEFAULT (SUSER_SNAME()) CONSTRAINT [PK_EmployeePayHistory_Audit_Generic_AuditID] PRIMARY KEY CLUSTERED ( [AuditID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [HumanResources].[EmployeePayHistory_AuditLookup_Generic]([AuditColumnID] [int] IDENTITY (1,1) NOT NULL, [AuditColumnName] [varchar](150) NOT NULL, [ActiveFlag] [bit] NOT NULL CONSTRAINT [PK_EmployeePayHistory_AuditLookup_Generic_AuditID] PRIMARY KEY CLUSTERED ( [AuditColumnID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [HumanResources].[EmployeePayHistory_Audit_Generic] ADD CONSTRAINT[FK_EmployeePayHistory_Audit_Generic_EmployeePayHistory_AuditLookup_Generic] FOREIGN KEY ( AuditColumnID ) REFERENCES [HumanResources].[EmployeePayHistory_AuditLookup_Generic] ( AuditColumnID ) GO INSERT INTO [HumanResources].[EmployeePayHistory_AuditLookup_Generic] ([AuditColumnName],[ActiveFlag])VALUES(‘RateChangeDate‘,1) GO INSERT INTO [HumanResources].[EmployeePayHistory_AuditLookup_Generic] ([AuditColumnName],[ActiveFlag])VALUES(‘Rate‘,1) GO SELECT *FROM [HumanResources].[EmployeePayHistory_AuditLookup_Generic] GO |
Let’s explain each of the columns to help illustrate the point:
|
XML Design – Per Database Design |
| USE [AdventureWorks] GO CREATE TABLE [dbo].[Database_Audit_XML]([AuditID] [int] IDENTITY (1,1) NOT NULL, [TableName] [varchar](100) NOT NULL, [ColumnName] [varchar](100) NOT NULL, [AuditData] [xml] NOT NULL, [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Database_Audit_XML_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](100) NOT NULL CONSTRAINT [DF_Database_Audit_XML_ModifiedBy] DEFAULT (SUSER_SNAME()) CONSTRAINT [PK_Database_Audit_XML_AuditID] PRIMARY KEY CLUSTERED ( [AuditID] ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO |
Let’s explain each of the columns to help illustrate the point:
|
Depending on where the data is stored (i.e. instance, centrally, etc.) it is necessary to store additional data for reporting purposes. These columns may include:
- Per Instance – Centralized auditing on a per SQL Server instance perspective
- Source database
- Per Application – Centralized auditing per line of business application
- Source database
- Source application
- Per Environment – Centralized auditing across all production SQL Server instances
- Source database
- Source application
- Source SQL Server instance
In addition to adding columns to the tables as the suggestions above outline, it is also necessary to setup the infrastructure from a data perspective to replicate the data or be able to write the data directly to the tables. This could mean taking one of more of the following approaches:
- Adding permissions to accounts
- Coding the logic in a trigger to write centrally
- Including a separate auditing connection string in the application
- Setting up a Linked Server to enable writing the data
- Writing the data locally then replicating centrally
- Using SQL Server Integration Services to capture the data and write it locally on a specific interval
- Use Service Broker to write data to a queue for centralized processing
Next Steps
- As you are faced with auditing needs, consider the information in this tip as a jumping off point to determine what information is needed when planning, designing, developing, testing and implementing a solution.
- The code examples in this tip are just that, examples. As you begin to define your auditing infrastructure, consider how these tables took advantage of a generic means to stored audited data either via table structures or an XML data type. If these solutions only meet 80% of the auditing need in your environment, research how these general ideas can be modified to meet your needs, whether it is including multiple [BaseTableUniqueKey_Value] columns or having different data types for the columns or whatever the case may be.
- If you choose to have some sort of centralized solution be sure to setup the needed infrastructure to support the centralized solution. Be sure this does not cause any sort of performance or security issue. In addition, consider getting creative with some of the new SQL Server 2005 features such as Service Broker or SQL Server Integration Services.
- Depending on the particular auditing need, consider using columns with the XML data type to store the data. This may be a simple and elegant way to support a number of different audited data sets from different base tables, if needed.
- Check out these additional resources:
