November 28, 2019 at 2:23 pm
Our SQL-server versions are not all up to date.
For myself I am not up to date at all.
Although I have worked a lot with SQL-server engines, I normally work with constructions which work in the standard (and express) editions of SQL-server. So my question is:
What is needed for an Audit Log ?
(System/version ???)
What are the possibilities ?
In my version of SQL-server and SSMS I can not locate the Audit possibilities. (But I do not have the Enterprise edition and I do not have a recent SQL-server). **)
Ben
**)
One of the reasons with staying behind is that I limit myself not to use new functionality, some of our systems and the systems customers use are older systems and often the standard edition.
November 28, 2019 at 2:33 pm
what are you trying to audit?
you can audit data changes (you'll get lots of suggestions), maybe schema changes (that one is my speciality), configuration changes ?
in sql server management studio there Is a "schema changes history report" - but it is not too good - I've used Database triggers and logged them to a table for DDL and permission changes.
I might be able to help if you can give me more detail about what you are trying to do
MVDBA
November 28, 2019 at 3:12 pm
Mainly (???)
Data changes.
A requirement is that something similar to SQL Anywhere Auditing is implemented. For two main purposes:
1. To discover problems with the database and/or database software. (Debugging problems).
2. To track changes to inspect what users have done. (Mistakes or doing something correctly in the wrong way).
Schema are not the priority.
Ben
November 28, 2019 at 3:55 pm
I think on this one you will have to write an audit system that suits your own needs.
It's going to be triggers that write to a log table, but trust me... expect a lot of traffic
MVDBA
November 28, 2019 at 4:02 pm
I think Audit is available in Standard Edition (depending on version), just with reduced functionality. Or you can use an Extended Events session, which I think is what an Audit is behind the scenes.
John
November 28, 2019 at 4:13 pm
is CDC an option ?
MVDBA
December 2, 2019 at 10:51 am
Maybe I was not clear (sorry):
Maybe the answer is: "Use the most recent Enterprise edition."
At this moment I do not use the Enterprise edition, that's why I asked the question. If Enterprise Edition is the answer, that's ok. Only I can not check that for this moment. So the edition I am using at the moment is not the limitation, but is what I have access to. So If I/we/the customer wants audit logging and another edition is needed, that's a completely acceptable answer.
Sorry that the question was not clear on this point.
Ben
(This was written on Friday 29 november, but did not hit the Submit button correctly, oops) 🙁
December 2, 2019 at 11:05 am
HI Ben
for my own learning, why "the most recent version of enterprise" fixes your needs
MVDBA
December 2, 2019 at 1:18 pm
Prologue:
A large number of database systems provide something like an Auditing Trail or Log.
Sometimes primitive, sometimes difficult to read, sometimes based on data, sometimes based on command/instructions/scripts.
SQL-server lacked (at least in older standard editions) a feature to access the audit information on both data and scripts.
Why ?
Mike Vessey asked Why? (At least I think he asked why).
My answer to that : I think that there is at least some Auditing trail reading possibility in the latest (or more recent) Enterprise versions of SQLserver.
Would this be sufficient for my situation. At the moment we are looking to change a database from SQLanywhere to SQLserver. SQLanywhere has an Audit trail possibility where scripts can be generated to 'replay' actions. Is something available in SQLserver ?
In :
There is mention of an audit database engine within SQLserver, but I have no experience with this. Building up enough experience with something like an Audit trail takes time and might lead to disappointment.
Epiloge:
My question is, what are the possibilities with SQLserver for auditing? And more specific what is build into the engine.
(Not building your own triggers. Not specifically switching on the profiler.)What can be used to audit trail actions which have resulted into a problem, after the problem has occurred ?
Thanks for your time and attention,
Ben
December 2, 2019 at 1:30 pm
if you are looking at data auditing
CDC can be quite cool
it might not be your solution, but give it a look over
MVDBA
December 2, 2019 at 1:35 pm
I really do think that you should look at Change data capture (CDC)
MVDBA
December 2, 2019 at 3:05 pm
Regardless of which method you plan on, if you intend to audit every data change even in "just" user databases, you should immediately at least double the disk space and all the places you backup data to. Personally, I'd quadruple it. Even just auditing which code made changes is going to produce a hell of a lot of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2019 at 3:15 pm
it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+
https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/
December 2, 2019 at 3:24 pm
it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+
https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/
"It Depends". On my main production server, the default trace recycles so quickly that there's no way to count on it. There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2019 at 4:08 pm
Andrey wrote:it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+
https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/
"It Depends". On my main production server, the default trace recycles so quickly that there's no way to count on it. There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.
You're right, Jeff, that's why default trace was mentioned as "it's worth to check" but not as "the only source you can rely and trust".
Regards,
Andrey.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply