CODEWITHSUNDEEP
azureazure-sql-databaseauditing
Venkata Ratnam Kallakuri
Venkata Ratnam Kallakuri

Reputation: 51

SQL Server Azure Data Changes Auditing Techniques

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

Upvotes: 2

Views: 1247

Answers (2)

Venkata Ratnam Kallakuri
Venkata Ratnam Kallakuri

Reputation: 51

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

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 2

Related Questions