marrowyung
asked on
the SQL server C2 audit mode
Dear al,,
As I know that there are SQL server C2 audit mode we can turn on to log information goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements.
Anyone turn it on before and how is it after using this?
I know the log will growth faster and the disk can be full easly, can we configure the C2 mode to save informatoin like insert/update/delete to other location ?
it only introduce the performance impact of the SQL server?
As I know that there are SQL server C2 audit mode we can turn on to log information goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements.
Anyone turn it on before and how is it after using this?
I know the log will growth faster and the disk can be full easly, can we configure the C2 mode to save informatoin like insert/update/delete to other location ?
it only introduce the performance impact of the SQL server?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I read that URL before and it said" f the audit log file reaches its size limit of 200 megabytes (MB), SQL Server will create a new file, close the old file,"
So you mean I just go and delete/archieve the old one? this is the only way we can do ?
So you mean I just go and delete/archieve the old one? this is the only way we can do ?
If you're just deleting the old file, there's no point to even enabling the logging - you're not consuming the information, so don't bother collecting it.
Ideally, you'd have some process that scoops up those log files and either collects them somewhere or scans them for events you're interested in and saves those. I found another article where the authors talks about how to actually view the events contained in the audit log file:
http://beyondrelational.com/modules/2/blogs/50/posts/10784/c2-level-auditing-with-sql-server.aspx
Maybe that's more helpful for what you're looking for. For SOX compliance, we have to record any DDL/DML statements executed against our databases and collect those in a central place for reporting, and then take backups of that database monthly (and clear out the old transactions so just the current month is offline). If you're looking to do something like that, then you should be able to accomplish it using C2 logging.
Ideally, you'd have some process that scoops up those log files and either collects them somewhere or scans them for events you're interested in and saves those. I found another article where the authors talks about how to actually view the events contained in the audit log file:
http://beyondrelational.com/modules/2/blogs/50/posts/10784/c2-level-auditing-with-sql-server.aspx
Maybe that's more helpful for what you're looking for. For SOX compliance, we have to record any DDL/DML statements executed against our databases and collect those in a central place for reporting, and then take backups of that database monthly (and clear out the old transactions so just the current month is offline). If you're looking to do something like that, then you should be able to accomplish it using C2 logging.
ASKER
ryanmccauley,
Which company offer the ""SQL Compliance Manager" you are talking about but it seem a bit familiar. Redgate ?
"We did some testing with it and the files didn't grow too quickly, but it was on a really, really lightly used server, so it's not really representative of production use."
You talking about the C2 mode but not the compliance manager, right? is that mean you are not sure if doing it on production and the impact of it ?
it doesn't matter and we can simple turn it off, right? turn it off need the restart of SQL server?
Which company offer the ""SQL Compliance Manager" you are talking about but it seem a bit familiar. Redgate ?
"We did some testing with it and the files didn't grow too quickly, but it was on a really, really lightly used server, so it's not really representative of production use."
You talking about the C2 mode but not the compliance manager, right? is that mean you are not sure if doing it on production and the impact of it ?
it doesn't matter and we can simple turn it off, right? turn it off need the restart of SQL server?
SQL Compliance Manager is an Idera product:
http://www.idera.com/SQL-Server/sql-compliance-manager/
I'm talking about C2, but the Idera product collects very similar data and it can grow large pretty quickly - I wouldn't be worried about the processing load on the servers, but I would be concerned about the side of the data is generates.
You're correct though - if it's too much data, you can just disable C2 logging and you're back to normal, but if you fill the drive up, the SQL Server will stop and refuse to start again until you clear some room for new logging.
http://www.idera.com/SQL-Server/sql-compliance-manager/
I'm talking about C2, but the Idera product collects very similar data and it can grow large pretty quickly - I wouldn't be worried about the processing load on the servers, but I would be concerned about the side of the data is generates.
You're correct though - if it's too much data, you can just disable C2 logging and you're back to normal, but if you fill the drive up, the SQL Server will stop and refuse to start again until you clear some room for new logging.
ASKER
any more input or I will close this ticket.
ASKER