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.