SQL Server Azure Data Changes Auditing Techniques

Question

I am new to SQL Azure and I have a task to Implementing Auditing Techniques on SQL Server Azure database. Can you someone please help me what are the different techniques available for Auditing data changes in SQL Azure. Any reference links will also helps.

I want to maintain Auditing for tables which has around 40-50 columns and I want to track all the column changes. I am also looking for reliability and performance factors.

Thanks

TheGameiswar · Answer 1 · 2016-03-08T07:59:54.727

2

Auditing in SQL Azure is very easy to set up.Below is the data that will be captured..

Access to data
Schema changes (DDL)
Data changes (DML)
Accounts, roles, and permissions (DCL)
Stored Procedure, Login and, Transaction Management.

Once you set up Auditing,the files will be stored in Storage account which you Can download as excel file...

Now Azure gives an option to monitor Audit logs using power BI as well..

We have configured auditing for tables which are inserted heavily(1 million inserts per day minimum)..we didnt saw any performance degradation.

Updated as per comments:
Auditing is at database level as of now,if you want to audit a single table only ,triggers can be your best bet

Few links which may help you..
https://powerbi.microsoft.com/en-us/blog/monitor-your-azure-sql-database-auditing-activity-with-power-bi/
https://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/#subheading-1

edited Mar 08 '16 at 07:59

answered Mar 07 '16 at 10:33

TheGameiswar

27,855
8
56
94

Thanks TheGameiswar, I have gone through the links and understood the Auditing can be enabled at database/sever levels. But can we enable auditing for a specific tables in a database? and in depth can I have auditing enabled for specific columns in a table as well? – Venkata Ratnam Kallakuri Mar 08 '16 at 05:47
No we cant enable specific to tables or columns,your best would be triggers – TheGameiswar Mar 08 '16 at 07:56
Can we create triggers in SQL Azure? – Venkata Ratnam Kallakuri Mar 08 '16 at 12:13
For per-table auditing, you may also want to look at the recent preview availability of Temporal Tables: https://azure.microsoft.com/updates/preview-temporal-tables-in-azure-sql-database/ – tmullaney Mar 08 '16 at 17:26
Thanks tmullaney for the response, I have started exploring temporal tables functionality. – Venkata Ratnam Kallakuri Mar 09 '16 at 10:51

score 1 · Answer 2 · answered Mar 23 '16 at 11:31

Thanks tmullaney for the response. After a deep analysis I have started using Temporal Tables for enabling Auditing in SQL Azure. We can enable Auditing on individual tables/entities using this feature. All the process will be done internally by SQL Server and no need to write even a single trigger to do the audit.

Here are couple of links that are useful to explore details on Temporal Tables in SQL Server, Channel 9 Video : Temporal in SQL Server 2016 : https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

Temporal Tables : https://msdn.microsoft.com/en-IN/library/dn935015.aspx

SQL Server Azure Data Changes Auditing Techniques

2 Answers2