April 9, 2012 at 8:07 am
Hello,
I have a design question about how most people deal with having to track users input changes to a sql server backend. Do you add a column or shadow table that the input tool is responsible for tracking user x changed this to that at whatever time? Or is there something built into sql server that i can use to do this and would i have to create a sql user for each input user to connect as in order to leverage it? Any thoughts would be appreciated.
April 9, 2012 at 8:45 am
Take a look at CDC (Change Data Capture).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 9:07 am
It also depends on what version of MS SQL Server you are running.
April 9, 2012 at 9:37 am
Right now its Sql Server 2008; our db guys are looking at what we can use in 2012 though. With the cdc enabled that still captures a sql user's transactions, correct? So how do most people implement tracking down to an input user? Do you use windows authentication for each person? Or does whatever input system they log into pass their username/info to the database? Or does each windows user have a corresponding sql user that they connect to the database as?
Basically our architecture will be an external (front facing) website hitting a wcf middle tier which will talk to the database. Originally we were going to the have the users login with windows authentication and active directory and connect to the database that way as-well, but that has been shot down. So we are trying to explore other solutions, but we need to be able to say this website user did this, at this time for every I/U/D and possibly select down the road. I know we could do all of this with custom code in the wcf but i don’t want to reinvent the wheel if the database can accommodate this. Also I was just curious how this is accomplished elsewhere.
Thanks for the comments so far.
April 9, 2012 at 9:43 am
Well since it is public facing Windows Authentication is not going to work. You could add a SysModUID column or something along those lines to each table, make it not null and have the front end pass to all your CRUD operations. Then CDC would pick up which UserID made the changes. If you use this approach keep in mind that you need to find something that will not break RI if/when users are deleted. I am sure there are some other ideas out there that other people use too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 9:56 am
I think the cdc might be good starting point for us. thanks.
April 9, 2012 at 10:26 am
Keep in mind that CDC cleans up data over time. Same with Change Tracking (CT). If you need to save data long term, you might want to think about saving this data off to a separate database/schema and archiving it periodically.
April 9, 2012 at 12:21 pm
Do you kno of any frameworks built to help support tracking? Like health monitoring in asp.net.
April 9, 2012 at 12:28 pm
CDC/CT will do this, but it has cleanup jobs that remove data over time. Auditing has not been a great strength in SQL Server over time, and I don't know any frameworks to do this easily.
many people look at triggers, and a shadow table in another schema to capture changes.
April 10, 2012 at 7:43 am
Keep in mind that CDC is only in Enterprise Edition (SQL 2008), and it doesn't track who made the change, just the changes that were made.
If each user logs in with a username and password, you could use their record ID for their login info and log that with the changes. Just save that data with the change date to the record.
If any changes are made or the record is deleted, I move a "before" copy to the audit table, which is essentially the same structurally as the main table (with a new identity value as the PK, and the old PK as the FK in the audit table, so multiple rows with the same old PK can be kept in the audit table).
This way, the main table record + all the same PK records in the audit table (a FK in that table) equals every change that has ever occured to that record. This is done in the trigger, with the updating of three columns in the new record - LCUser, LCHost, LCDate. These stand for Last Change User, etc.
That's just the way I do it. My databases are for individual departments, so they don't get very large, so this is very useful. If there were thousands of transactions per minute or hour, that could be problematic space-wise and speed-wise.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply