[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2507
  • Last Modified:

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?
0
marrowyung
Asked:
marrowyung
  • 4
  • 3
1 Solution
 
marrowyungAuthor Commented:
can we make it in the way that whenever somehting like this happened we will receive an email telling the detail information ?
0
 
Ryan McCauleyCommented:
It looks like the only option you have is the default data location:

http://msdn.microsoft.com/en-us/library/ms187634(v=sql.105).aspx

However, this "default" location doesn't have to actually be where your data files are located - you can set the default location to whatever folder you want in your server options, and then the C2 log files will be created there. If the disk holding those files fills up, the sql instance will go offline, though - it won't start up again until you either disable logging (using a command line switch) or you free up some disk space (see the article).

I've never used this in production, opting instead for a product from Idera called "SQL Compliance Manager" that collects the audit data centrally and does some reporting - two features missing from the C2 audit option in SQL Server. 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. The log files roll over ever 200MB, so it wouldn't be too hard to set up some kind of automated job (even a batch file) to run every few minutes and pick up any old files, FTP them to another location, and store them there for review later - that way, you're not risking the disk filling up, but still collecting the files somewhere safe.
0
 
marrowyungAuthor Commented:
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 ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ryan McCauleyCommented:
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.
0
 
marrowyungAuthor Commented:
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?
0
 
Ryan McCauleyCommented:
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.
0
 
marrowyungAuthor Commented:
any more input or I will close this ticket.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now