Upload SQL Audit Data to Azure Blob Storage with SQL Server 2016 Integration Services Feature Pack


In SQL Server 2016 CTP 3 you can install additional SSIS Components:

image

https://msdn.microsoft.com/en-us/library/mt146770.aspx 

These new building blocks are nice to upload your SQL Auditlogs to Azure Blobstorage:

You can configure the SQL Auditing Specification to write Audit Files to a local Directory:

image

And created a SSIS Package to iterate, upload and delete all the files in this directory:

image

(Set the maximum Errorcount to 2 as the upload task will fail for the file that is currently written by SQL Server to the disk.)

The Azure Blob Upload Task just gets the storage account and storage key as parameters:

image

The package can be deployed to SQL Server 2016 (CTP3) SSIS Catalog only. It does not work in SQL 2014:

image

The files will appear in your storage container:

image

For further analysis you can create a Hadoop or Spark Cluster and point it to your Storage Account. The cool thing is that you pay what you use only. This way you can report if necessary to fulfill regulatory requirements / due diligence at minimum cost.


Comments (0)

Skip to main content