SQL audit overhead

by Sarah   Last Updated November 09, 2018 00:06 AM

Today I started reading about the SQL Audit functionality and watched a couple of you tube videos. This is the microsoft article I read. https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

None of them I could find an answer to these questions. I am just starting off on it, so some questions might be very basic. Apologize for that.

1) If I put run it on production machine on databases that have 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb? Does that increase the .ldf log size of the database its monitoring?

2) Keeping the learning part aside, how do people normally use it in production environment, like is it normally on for 24 hours, 7 days a week or is it something that is only turned on case by case (as needed ) basis.

3) How is it different from SQL profiler?

4) We have regular backup and checkdb jobs running once a day, is that something that can be put in place without worrying about any issues?

5)What are some practical ways, the audit file is managed? does it need to be backed up on daily basis and then removed or no backup is necessarily, manually remove it after a week or something if there were no known issues.

6) Is it normal to audit ALL databases in the system or just the ones interested. What is a normal practice.


Related Questions

Updated July 21, 2015 13:02 PM

Updated December 07, 2017 20:06 PM

Updated December 05, 2017 17:06 PM

Updated March 03, 2016 01:02 AM

Updated January 23, 2019 04:06 AM