Please, can anyone help me with this problem? I have mssqlserver 2008 r2 standard edition, database 123, many tables with data, 5logins defined in security...and here is my problem - I have to continuous saving TXT (or something like that) log, with information, WHO and WHAT he write or login/out or edit what table... I found something like SQL Server profiler, but its seems to be comlicated for me...Any help or guide? Thanks!!
asked Apr 29, 2012 at 05:30 PM in Default
For what you are describing, I wouldn't use profiler. It sounds like you will be doing this report gathering often and you don't want profiler up & running all the time. Rather I would look towards using a server side trace or extend events. Both of these tools allow you to gather your data behind the scenes and are much less intrusive to sql server.
Jonathan Kehayias did an excellent series of blog posts regarging Extended Events: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/an-xevent-a-day-1-31-an-overview-of-extended-events.aspx
I will warn you that extended events are complicated (at least they are for me) so it might take some research to make it do what you want. From what I've know, Profiler and Server Side traces will be going away in future releases of SQL Server in favor of Extended Events, which is another reason that I mention the tool.
Server side traces are little bit easier to understand (in my opinion) and there are a number of excellent articles out on the web that describe how to use them.
Here's a starting point though: http://msdn.microsoft.com/en-us/library/ms191443%28v=sql.105%29.aspx
You can also use Profiler to generate your server side traces, which might things easier to get up and running.
Hope this helps!!
answered Apr 29, 2012 at 07:46 PM
how are the logins connecting to the data? I would recommend the application they are using is adapted to audit the user activity if possible. If not then Extended Events is the best way to go (as suggested by @john morehouse) as you are on SQL 2008 R2. They are much less burden on your server performance. How are you going to store the data collected, for how long, with what security? There are lots of questions you probably want to consider along with the method of collection
answered Apr 30, 2012 at 12:30 PM