Want to create a service broker broker application for auditing my databse

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks.

    Can you provide more information about it?

  • sure!

    more information about SQL2008 Change Data Capture

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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]

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

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

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