Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want to create a service broker broker application for auditing my databse Expand / Collapse
Author
Message
Posted Monday, June 6, 2011 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 12:13 AM
Points: 5, Visits: 26
Hi,
I am new to SQL service broker and I don't know anything about. I have to develop an application for my company such that each and every transaction, a entry will be made in audit table for the old value and new value. Our company has many database(though size each one is less). I have to maintain all transaction that is happening in each database in a master database plus each one of the database.
Can someone provide me with step by step process that i have to follow to make a robust and flexible application as soon as possible.
Any help will be welcome. Thanks in advance
Post #1120264
Posted Monday, June 6, 2011 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
SQL2008 introduced CDC...Change Data Capture...which is going to do everything you are asking and more, it's built in and it's documented, working and can be implemented on each table you need to audit with a couple of lines of code.

I'd recommend that before anything else.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120270
Posted Monday, June 6, 2011 10:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 12:13 AM
Points: 5, Visits: 26
Thanks.
Can you provide more information about it?
Post #1120723
Posted Monday, June 6, 2011 10:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
sure!
more information about SQL2008 Change Data Capture


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120728
Posted Monday, June 6, 2011 10:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 12:13 AM
Points: 5, Visits: 26
Hi,
I read about CDC. What information i got is that CDC creates a change table structure in the log for each source table and then for every data change in tables is recorded in this meta tables.
Thus though CDC is good for data warehousing, it's will not work in my case, as I require only one table in database which will maintain information about change in data in every tables in that data base.
Anyway thanks for the your help. It was truly appreciated.
Post #1120730
Posted Monday, June 6, 2011 10:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
a single, master table tracking all changes and the original values isn't really going to work well...think it through with me.\
if you want to keep old and new values, you also need to keep their datatype, right?
so your single, master tables going to need to either put everything in nVarchar columns, or have a suite of different columns,
one for each data type, so you can lnow that the column FirstName changed from 'Bob' to Robert, but also be able to track that the value Total Amount changed from 100 to 101.

that type of tables pretty difficult to work with, and gets a little unmanageable when you actually need to reverse a transaction out of it.
CDC puts the changes into a parallel table under the cdc schema, so changes to dbo.Invoices can be found in the mirrored table cdc.Invoices

it's very easy to query those tables in that case, and since they are tables, you could build a VIEW on those tables that could emulate the original structure you envisioned for the master table idea, but with all the advantages of CDC.

you mentioned robust and flexible, but what is the purpose....to recover/undo changes if needed, or to identify who made a change and when?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120732
Posted Monday, June 6, 2011 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 12:13 AM
Points: 5, Visits: 26
Hi,
Let me make myself clear.
I have a table with below table structure. It will just maintain the information that on which table with what primary(with its value) does the change occurred on what type(insert, update, delete) with old value of the column on which change occur and the new value of the column with added information about who made the changes and at what time?

Table Structure:

CREATE TABLE [dbo].[DataAudit](
[Pk_RowID] [int] IDENTITY(1,1) NOT NULL,
[AuditID] [int] NOT NULL,
[Type] [nchar](1) NULL,
[TableName] [nvarchar](128) NULL,
[PrimaryKeyField] [nvarchar](1000) NULL,
[PrimaryKeyValue] [nvarchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](max) NULL,
[NewValue] [varchar](max) NULL,
[UsrID] [varchar](20) NULL,
[Udt] [datetime] NULL,
[BusinessDt] [datetime] NULL,
[ConcurrencyID] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED
(
[AuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Post #1120735
Posted Monday, June 6, 2011 11:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
ok...i see the plan...but it goes back again to what will the table be used for? the purpose of the table has a lot to do with the best way to tackle this...
there's lots of built in options, but can you describe what you'd d0 with he data in the table after it exists?
is it just an audit(so you could use a trace instead?) so you can fingerpoint or otherwise know who did what?
is it to be able to undo unwanted changes?
is it to satisfy a SOX requirement or something?


but what about the following?

what about a table that doesn't have a single column for it's primary key? admittedly, depending on your biz, you might always have a single column PK, but i know i have quite a few that do not.
if you are consistent, and every table's PK happens to be an identity, you could use bigint instead of varchar(1000) for the definition.

what happens when, say 5 columns in a table were changed? you want 5 rows in the audit table added? very hard to write a script to undo that, because since the value is in a nvarchar(max0, you have to custom write the script , with CONVERT commands sometimes, and merge those 5 rows back into a single one, for all the rows affected.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1120737
Posted Monday, June 6, 2011 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 17, 2011 12:13 AM
Points: 5, Visits: 26
Hi
Table will only will be used for audit purpose so we could fingerpoint or otherwise know who did what.
To undo unwanted changes we are already following data replication and data backup. So that is not much a issue.
We can't use bigint instead of varhar for primary field as not every table will have primary key as identity. Instead we have quite a lot of tables with primary key as varchar.
As per our biz policy one table could have only one column as primary key. If more than one column is required, then we have to divide that table into parts and then create a master table to map these sub tables
Post #1120740
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse