April 3, 2012 at 2:47 am
We have a table which is used by many pages on our website.So it's an important table.
Now during development( I am talking about development environment here, no production), some developer will make bad updates to this table and everybody's work get hampered.This table doesn't have any userId type column which may tell who did the updates. Moreover since all developers use common login, it will be of no use to add such column to table.
What I need is a way to capture the hostname who does the update,without making any changes in application itself.
And without making changes in table structure.
One way coming to my mind is to create a copy table with extra column HOSTNAME. Then create a trigger on original table for updates and insert hostname in copy table as default using function host_name().
Can you guys suggest some other ways to achieve it?
I don't want to run a trace since I am never sure when the change might happen.
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 2:55 am
S_Kumar_S (4/3/2012)
Moreover since all developers use common login, it will be of no use to add such column to table.What I need is a way to capture the hostname who does the update,without making any changes in application itself.
You're SOL with this combination from the database perspective, you can't locate it except from the App tier. Especially if your app is hosted on IIS or the like.
One way coming to my mind is to create a copy table with extra column HOSTNAME. Then create a trigger on original table for updates and insert hostname in copy table as default using function host_name().
This assumes the apps are all developed on local sandboxes with direct access to the DB instead of from a shared root on a server somewhere. If that's the case, yes, this might get you there.
Another option might be the appname on the connection, but then each dev would have to fill in their own 'appname' to their personal builds.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 3:09 am
Evil Kraig, your statement is incorrect, the host_name() function retrieves the host name of the machine making the request, I have used it in the past for auditing of important tables.
In response to the OP, yes you can use the function. You would have to create a trigger on the table for updates and insert the host_name() into a table along with details from the inserted and deleted tables.
A quick google found the following example.
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Cheers
Mat
April 3, 2012 at 4:20 am
The development happens on local machines.I am finally thinking of going with trigger. I thought of using CDC, but that won't give hostname which is the main thing for me.
Any downside of creating a trigger in this scenario?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 4:27 am
April 3, 2012 at 4:29 am
Perfromance is one area you may need to look here. If the table is undergoing huge transactions(insert/update/delete) then it would be a concern area for you.
April 3, 2012 at 4:35 am
There are rare updates/deletes in this table. So performance is not an issue.Moreover it's a dev server.So no issue of performane again...
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 4:49 am
S_Kumar_S (4/3/2012)
There are rare updates/deletes in this table. So performance is not an issue.Moreover it's a dev server.So no issue of performane again...
Ok, I had an impression it would take it to production later point of time. Yes you can have that option. But for testing purpose also(load testing incase you are planning) you may need to disable/drop the trigger.).
April 3, 2012 at 4:54 am
For Dev environment, it's better to have any audat extra column having host_name as default value. I am using it at my environment and it's working fine for me. Even you can give one extra field with getdate() as well (just in case you are interested to capture time of change)
April 3, 2012 at 5:16 am
What about setting up an extended events session and just capturing changes over time. Then, if something goes wrong, you query the extended events results and see who did what and when. Nice and easy. No triggers, no added columns and a very light impact on the server.
"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
April 3, 2012 at 5:43 am
This is exactly what I did 🙂
Only difference is I created a copy table with these 2 extra columns an dpopulated it for update/delete.
sqlnaive (4/3/2012)
For Dev environment, it's better to have any audat extra column having host_name as default value. I am using it at my environment and it's working fine for me. Even you can give one extra field with getdate() as well (just in case you are interested to capture time of change)
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 5:56 am
This one sounds really exciting and elegent...And new to me 🙂
Can you pls tell which events I can use or any reference link you have?
I understand I can google for this, but knowing few things before hand will same me some time....
Thanks for this insight....
Grant Fritchey (4/3/2012)
What about setting up an extended events session and just capturing changes over time. Then, if something goes wrong, you query the extended events results and see who did what and when. Nice and easy. No triggers, no added columns and a very light impact on the server.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 6:03 am
Since you're trying to track data changes, batch_requests and rpc_complete are the two events I would track. For details on how to use extended events, hit the Books Online and Jonathan Kehayias' blog[/url].
"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
April 3, 2012 at 6:08 am
Thanks So much Grant! Good to see big guys helping out "lesser knowns" 🙂
Grant Fritchey (4/3/2012)
Since you're trying to track data changes, batch_requests and rpc_complete are the two events I would track. For details on how to use extended events, hit the Books Online and Jonathan Kehayias' blog[/url].
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 3, 2012 at 6:32 am
S_Kumar_S (4/3/2012)
Thanks So much Grant! Good to see big guys helping out "lesser knowns" 🙂
I'm not that big. I've lost 12 pounds. Only about 6 more to go til I'm medium sized & not big anymore.
"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
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply