Audit In SQL Server

Question

Audit In SQL Server

Admingod

SSCertifiable

Points: 5997
More actions
September 13, 2018 at 3:11 pm

#377305

I need to save an audit trail of changes made to some tables.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Create a history table with the same schema and an after insert update trigger to write deleted into the history. If your table has modified date and user columns write the modified date from deleted as valid from and transaction datetime to valid to in the history table.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Changes made to data in tables or changes made to the tables themselves? If the latter, get your database into source control and deploy changes from there. That will be the best way to track modifications to the structure over time. If the former, do what Joe says. There's also this.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Admingod SSCertifiable Points: 5997 More actions · Answer 3

We don't want to implement this using triggers. This could impact performance.There are lot of tables in the database that needed to be Audited. Is there a in built Audit feature in SQL Server 2008 that you can advise? What is C2 audit mode? Is there any other options you can suggest?

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

Admingod - Monday, September 17, 2018 10:07 AM
We don't want to implement this using triggers. This could impact performance.There are lot of tables in the database that needed to be Audited. Is there a in built Audit feature in SQL Server 2008 that you can advise? What is C2 audit mode? Is there any other options you can suggest?

Triggers aren't going to cause performance problems here if someone actually knows how to write them correctly. Also, some of the built in methods also use triggers that you're probably not aware of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

Jeff Moden - Tuesday, September 18, 2018 3:47 PM
Admingod - Monday, September 17, 2018 10:07 AM
We don't want to implement this using triggers. This could impact performance.There are lot of tables in the database that needed to be Audited. Is there a in built Audit feature in SQL Server 2008 that you can advise? What is C2 audit mode? Is there any other options you can suggest?
Triggers aren't going to cause performance problems here if someone actually knows how to write them correctly. Also, some of the built in methods also use triggers that you're probably not aware of.

p.s. There's code out there that uses a generic CLR trigger for each table. DO NOT USE IT because then you'll have some serious performance problems, especially on wider tables. The problem with the CLR triggers is that INSERTED and DELETED will be out of scope for the code and so the CLR trigger first makes a copy of both in a temporary structure and then does it's thing. It was taking 4 minutes to update just 4 columns for just 10,000 rows on a wide table here at work. I wrote code to auto-magically create hardcoded T-SQL triggers and they run so fast that most people can't tell they ran. Things like Temporal Tables don't run any faster, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 6

Admingod - Thursday, September 13, 2018 3:11 PM
I need to save an audit trail of changes made to some tables.

Firstly I agree with Joe and Jeff that triggers with an associated audit table for each table are a great way to do this.
If you don't do it that way you could create two tables AuditLog and AuditLogDetail then programmably insert rows into each table when you update/insert/delete rows from the tables you want to audit.
They could look something like this:
CREATE TABLE [dbo].[AuditLog]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [DatabaseTableName] [nvarchar](80) NOT NULL, [ModificationIndicator] [nvarchar](1) NOT NULL, -- I, D, U [RowChangedKey] [nvarchar](100) NOT NULL, -- This is the PK column of the updated table [SystemUserId] [bigint] NOT NULL, [ChangedByFunction] [nvarchar](500) NOT NULL, [DateStamp] [datetime2](3) NOT NULL, CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED ( [Id] ASC )

CREATE TABLE [dbo].[AuditLogDetail]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [AuditLogId] [bigint] NOT NULL, -- FK to the related AudiLog table [DatabaseColumnName] [nvarchar](80) NOT NULL, [OldValue] [nvarchar](max) NOT NULL, [NewValue] [nvarchar](max) NOT NULL, [DateStamp] [datetime2](3) NOT NULL, CONSTRAINT [PK_AuditLogDetail] PRIMARY KEY CLUSTERED ( [Id] ASC )

ALTER TABLE [dbo].[AuditLogDetail] WITH NOCHECK ADD CONSTRAINT [FK_AuditLogDetail_AuditLog] FOREIGN KEY([AuditLogId]) REFERENCES [dbo].[AuditLog] ([Id])

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Admingod - Monday, September 17, 2018 10:07 AM
We don't want to implement this using triggers. This could impact performance.There are lot of tables in the database that needed to be Audited. Is there a in built Audit feature in SQL Server 2008 that you can advise? What is C2 audit mode? Is there any other options you can suggest?

The built in audit feature is explained in the last link in Grant's post. That's more of what you are looking for.
You can also search on "SQL Server audit" to get more information.
You don't need C2 auditing - it's deprecated anyway so you can ignore it since its going away on one of the future releases.

Sue