3

We are about to implement Availability Groups and it brought up a question. We currently use auditing to see which objects are no longer used in production. We also take a backup each night to use the next day for reporting. Obviously, once we go to Availability Groups we won't need this as we will use a read-only database for reporting. Because of our current setup, we don't know if an object is only used in the reporting database since the audit isn't on that database.

Question: Will auditing on an Availability Group register when someone uses an object on one of the secondary servers and not the primary?

  • I'm very interested about that. Did you tried setup auditing on secondary node only to catch SELECT only ? Btw. If all your tables have clusterd index. You can just check sys.dm_db_index_usage_stats it should have same result on all nodes. – Kuba Miazek Apr 10 '14 at 6:33
  • We will be testing this in the next few days I hope. You solution would work for the table but we are looking to deprecate unused Functions, Procedures and Views as well. Great idea though. I never thought of that. – SteveB Apr 10 '14 at 12:53
0

No, auditing only reflects the one server that is running the audits. If you want to centralize audit results from multiple servers, you'll need to combine that data yourself.

Keep in mind that if someone really wanted to circumvent this, they'd simply add another replica on any server they want, do the querying they need, and then destroy the replica. An audit on the primary would only capture the fact that a replica was added (and even then, only if you're auditing for those events.)

  • That's what we figured. Luckily we aren't tracking usage as a security feature. We only use this type of auditing for a short time to see if the developers have stopped using tables, views, etc. Thanks for verifying what we thought. – SteveB Jul 17 '14 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.