Azure Service Logging in the Modern Data Warehouse

[read this post on Mr. Fox SQL blog]

The “modern data platform” architecture is becoming more and more popular as organisations shift towards identifying, collecting and centralising their data assets and driving towards embracing a “data driven culture“.

Microsoft Azure has a suite of best-of-breed PaaS based services which can be plugged together by organisations wishing to create large scale Data Lake / Data Warehouse type platforms to host their critical corporate data.

When working with customers going down the Modern Data Platform path I often hear very similar questions;

  • What is the most suitable and scaleable architecture for my use case?
  • How should I logically structure my Data Lake or Data Warehouse?
  • What is the most efficient ETL/ELT tool to use?
  • How do I manage batch and streaming data simultaneously?
  • …etc

While these are all very valid questions, sorry, but that’s not what this blog is about! (one for another blog perhaps?)

In my view – what often doesn’t get enough attention up front are the critical aspects of monitoring, auditing and availability. Thankfully, these are generally not too difficult to plug-in at any point in the delivery cycle, but as like with most things in cloud there are just so many different options to consider!

So the purpose of this blog is to focus on the key areas of Azure Services Monitoring and Auditing for the Azure Modern Data Platform architecture.

.

What Exactly is an Azure Modern Data Platform Architecture?

Rather than try to explain this one, the diagram below spells out what a typical platform may look like once its been completely built out.

NOTE this is just a small simple cutdown version of what a platform like this may look like, often other components and data pathways are deployed including…

  • Data Streaming Pathway – which may deploy Azure Event Hubs, Azure Stream Analytics, and/or Azure Databricks (streaming)
  • Multiple Data Transformation Buckets – this only shows 2 layers, but there are often multiple including a conformed layer, data science scratchpad area and archive layer. (Argh, One for another blog post perhaps!)
  • Tabular Models and BI Reporting – including Azure Analysis Services and Power BI, Tableau, etc
  • Data Catalog and Lineage – including Azure Data Catalog v2

.

Where and What do we Log?

THE WHERE: First up regardless of which logs, audits, telemetry, or whatever is available from each of these deployed services, we first need somewhere to capture and store it. In Azure we have the very awesome Azure Log Analytics, which is part of the Azure Monitor suite of services. It provides sophisticated tools for collecting and analyzing telemetry that allow you to maximize the performance and availability of your cloud and on-premises resources and applications – https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/get-started-portal

What’s nice is that most Azure services have diagnostics and logging options which allow you to send this data direct to Azure Log Analytics, which makes our “monitoring” job just so much easier.

Now we have a view on where we send our data, lets break down each of these services into a single set of monitoring recommendations.

Now onto THE WAHT

.

Standard Logging to Enable on All Azure Services

Every Azure Service (with a few minor exceptions) will generate the following two log streams; Activity Log and Diagnostic Logs. Capturing these are essential to providing an overall view of the activity within the platform.

.

AZURE ACTIVITY LOG

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/activity-logs-overview

All Azure services have an activity log which captures activities performed on the service, when and by who. Activity Logs provide data about the Control Plane operations on an Azure resource. Activity logs capture events for Admin, Service Health, Resource Health, Alert, Autoscale, Recommendation, Security, Policy. Activity log data will take about 10-15 minutes to be sent to Log Analytics ingestion point. The Activity Log stores the logs in the Azure backend for 90 days.

The activity log event schema is here – https://docs.microsoft.com/en-us/azure/azure-monitor/platform/activity-log-schema
If you would like to retain the data beyond this then log retention can be defined here – https://docs.microsoft.com/en-us/azure/azure-monitor/platform/activity-logs-overview#export-the-activity-log-with-a-log-profile

Recommendations

  • Leverage the 90 days activity log retention into Azure Log Analytics
  • (optional) extend the retention

.

AZURE DIAGNOSTIC LOGS

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/diagnostic-logs-overview

Most (but not all) Azure services provide deeper diagnostic logs containing service specific information, metrics, events, etc.. This can be captured to either a Storage Account, Log Analytics or Event Hub. 

Diagnostics Logs are emitted by a resource and provide information about the operation of that resource at the “data plane” level of the service.  The content of these logs varies by the Azure service and resource type Data from diagnostic logs take 2-15 minutes, depending on the Azure service

The diagnostic log schema is here – https://docs.microsoft.com/en-us/azure/azure-monitor/platform/diagnostic-logs-schema

Recommendations

  • Enable for all Azure Services that support it which includes ADF, AKV, VM, AAS, SQL DB/DW, ADB into Azure Log Analytics
  • Currently only Azure Data Lake Storage Gen2 does not currently support diagnostic logs

.

Specific Logs to Enable on Each Azure Service

In addition to the standard logs above, each Azure Service will also have one or more service specific logs that can be enabled and captured. As above capturing these are essential to providing a deep dive service specific view of the activity within that service.

.

AZURE DATALAKE GEN2 STORAGE

Storage Analytics Logs (Metrics)

https://docs.microsoft.com/en-us/rest/api/storageservices/About-Storage-Analytics-Logging

Storage Analytics logs detailed information about successful and failed requests to a storage service. This information can be used to monitor individual requests and to diagnose issues with a storage service. Requests are logged on a best-effort basis.  Log entries are created only if there are requests made against the service endpoint.

All logs are stored in block blobs in a container named $logs, which is automatically created when Storage Analytics is enabled for a storage account. The $logs container is located in the blob namespace of the storage account, for example: http://<accountname&gt;.blob.core.windows.net/$logs. This container cannot be deleted once Storage Analytics has been enabled, though its contents can be deleted.

All data in the $logs container can be accessed by using the Blob service APIs, including the .NET APIs provided by the Azure managed library. Storage Analytics Logs can be sent to Azure Log Analytics for further analysis. 

There is no automated way to do this so therefore this must be created per storage account so the data is sent to Custom Logging into Azure Log Analytics as per this example here – https://azure.microsoft.com/en-us/blog/query-azure-storage-analytics-logs-in-azure-log-analytics/

Advanced Threat Detection

https://docs.microsoft.com/en-us/azure/storage/common/storage-advanced-threat-protection

Advanced Threat Protection for Azure Storage provides an additional layer of security intelligence that detects unusual and potentially harmful attempts to access or exploit storage accounts These security alerts are sent by default into the Azure Security Center, and are also sent via email to subscription administrators, with details of suspicious activity and recommendations on how to investigate and remediate threats.

Recommendations

  • Enable and Capture Storage Analytics Logs
  • Create automated step to send Storage Analytics Logs into Custom Logging into Azure Log Analytics
  • Enable Advanced Threat Detection

.

AZURE DATA FACTORY

Metrics

https://docs.microsoft.com/en-us/azure/data-factory/monitor-using-azure-monitor#metrics

You can use Azure Data Factory integration with Azure Monitor to route data to Azure Monitor. This integration is useful when you want to write complex queries on a rich set of metrics that is published by Data Factory to Azure Monitor. You can also create custom alerts on these queries via Azure Monitor.

Recommendations

  • Enable Diagnostic Metrics

.

AZURE DATABRICKS

Event Logs

https://docs.azuredatabricks.net/user-guide/clusters/event-log.html

The cluster event log displays important cluster life cycle events that you trigger manually or are automatically triggered by Azure Databricks. Such events affect the operation of a cluster as a whole and the jobs running in the cluster.  Events are stored and available from within the ADB cluster / Workspace for 60 days. Get data using the REST API – https://docs.azuredatabricks.net/api/latest/clusters.html#events

Spark Metrics Logs

https://docs.microsoft.com/en-us/azure/architecture/databricks-monitoring/configure-cluster

Connecting Azure Databricks with Log Analytics allows monitoring and tracing each layer within Spark workloads, including the performance and resource usage on the host and JVM, as well as Spark metrics and application-level logging.

Several types of metrics can be captured using this library extension on the ADB cluster. Spark metrics are automatically collected into the SparkMetric_CL Log Analytics custom log.

Streaming job metrics are automatically collected into the SparkListenerEvent_CL Log Analytics custom log Spark logs are available in the Databricks UI and can be delivered to a storage account. However, Log Analytics is a much more convenient log store since it indexes the logs at high scale and supports a powerful query language. Spark logs are automatically collected into the SparkLoggingEvent_CL Log Analytics custom log.

Recommendation

  • Regularly capture ADB Cluster Events using the API command. Suggested to capture this data every 5-15 minutes.
  • Create automated step to send ADB Cluster Events into Custom Logging into Azure Log Analytics
  • Review and consider optionally enabling detailed Spark cluster Logs and metrics for all pre-configured ADB clusters.  NOTE these can be very detailed logs.

.

AZURE SQL DATA WAREHOUSE

SQL Auditing

https://docs.microsoft.com/en-au/azure/sql-data-warehouse/sql-data-warehouse-auditing-overview

Auditing helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations, and enables and facilitates adherence to compliance standards, although it doesn’t guarantee compliance

Auditing captures all audit data and logs the captured data to the logging destination which can be loaded into analysis tooling for assessment.

If enabled Audit Logs will capture every single SQL batch sent to the database, which can generate a significant amount of access and logging data.  Therefore its recommended to only enable for sensitive data assets. Auditing can be enabled at the Azure SQL Server level which applies as the default setting to all existing and new Azure SQL Data Warehouses as hosted on the server, or Auditing can be enabled on any specific Azure SQL Data Warehouse directly. If Auditing is enabled on the Server level then it will always apply to the hosted databases regardless of the setting at the database level. If Server level Auditing is enabled and Database level Auditing is enabled, then the Audit occurs twice for that database.

Database – Data discovery & classification

https://docs.microsoft.com/en-au/azure/sql-database/sql-database-data-discovery-and-classification

Data Discovery & Classification (public preview) provides advanced capabilities built into Azure SQL Database and Data Warehouse for discovering, classifying, labeling, and protecting the sensitive data in your databases. Discovering and classifying your utmost sensitive data (business/financial, healthcare, personal data, etc.) can play a pivotal role in your organizational Information protection stature.

Data Discovery & Classification is enabled at the Azure SQL Data Warehouse database level and is not applied a the SQL Logical Server level

It can serve as infrastructure for:  Various security scenarios, such as monitoring (auditing) and alerting on anomalous access to sensitive data,  Controlling access to, and hardening the security of, databases with highly sensitive data,  and Helping meet data privacy standards and regulatory compliance requirements, such as GDPR.

SQL data discovery & classification comes with a default set of built-in set of sensitivity labels and a built-in set of information types and discovery logic. Definition/customization of your classification taxonomy is done in one central place for your entire Azure tenant in the Azure Security Center, defined as part of your Security Policy. Only someone with administrative rights on the Tenant root management group can perform this task.

The classification regularly engine scans your database for columns containing potentially sensitive data based on your classification taxonomy and provides a list of recommended column classifications.  You can also manually classify columns as an alternative.

An important aspect of the information protection paradigm is the ability to monitor access to sensitive data.  Therefore both the existing Auditing feature and the new Data Discovery & Classification feature have been built to closely operate together.  In order to monitor and alert on access to classified database fields, Azure SQL Database Auditing has been enhanced to include a new field in the audit log called “data_sensitivity_information”, which logs the sensitivity classifications (ie labels defined in Data Discovery & Classification) of the actual data that was returned by the query.  Therefore the classified data access logging is written to the same existing log files as the SQL Audit log which are stored in Azure Storage, Event Hubs (preview) or Log Analytics (preview). 

Vulnerability Assessment

https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment

Vulnerability assessment is an easy to configure service that can discover, track, and help remediate potential database vulnerabilities with the goal to proactively improve overall database security. Vulnerability assessment is enabled at the Azure Logical SQL Server level or at the SQL Data Warehouse database level

Vulnerability Assessment is a scanning service built into the Azure Database service. The service employs a knowledge base of rules that flag security vulnerabilities and highlight deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. The rules are based on Microsoft’s best practices and focus on the security issues that present the biggest risks to your database and its valuable data. They cover both database-level issues as well as server-level security issues, like server firewall settings and server-level permissions. These rules also represent many of the requirements from various regulatory bodies to meet their compliance standards.

Results of the scan are stored on Azure Storage only and include actionable steps to resolve each issue and provide customized remediation scripts where applicable. Vulnerability Assessment to automatically run a scan on your database once per week (every 7 days).  Vulnerability Assessment Logs can be sent to Azure Log Analytics for further analysis.  There is no automated way to do this so therefore this must be created per storage account so the data is sent to Custom Logging into Azure Log Analytics.  There is currently no available tooling so therefore this must be developed.

Advanced Threat Detection

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection-overview

SQL Threat Detection is a unified package for advanced SQL security capabilities and comes at additional cost over the base SQL Data Warehouse.

SQL Threat Detection includes functionality for managing your database vulnerabilities, and detecting anomalous activities that could indicate a threat to your database. It provides a single go-to location for enabling and managing these capabilities. It can detect anomalous activities indicating unusual and potentially harmful attempts to access or exploit your database. It continuously monitors your database for suspicious activities, and provides immediate security alerts on potential vulnerabilities, SQL injection attacks, and anomalous database access patterns.

Threat Detection can be enabled at the Azure SQL Server level and applied as the default to all Azure SQL Data Warehouse as hosted on the server, or Threat Detection can be enabled on any specific Azure SQL Data Warehouse directly. Threat Detection captures all threat data and sends real-time alerts via email only to the subscription owner, or any email account as configured in the Azure Portal.  Threat detection also integrates alerts with Azure Security Center, which includes details of suspicious activity and recommend action on how to investigate and mitigate the threat. 

Metrics and Queries

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-concept-resource-utilization-query-activity

You can use Azure SQL Datawarehouse integration with Azure Monitor to route data to Azure Monitor. This integration is useful when you want to write complex queries on a rich set of metrics and query history that is published by SQL Datawarehouse to Azure Monitor. You can also create custom alerts on these queries via Azure Monitor.

Recommendation

  • (Optional) Enable SQL Auditing to log to Log Analytics
  • (Optional) if SQL Auditing is enabled, then Enable Data Discovery and Classification which will also log to Log Analytics
  • Enable capture Vulnerability assessment Logs
  • Create automated step to send Vulnerability assessment Logs into Custom Logging into Azure Log Analytics
  • Enable Threat Detection for all SQL Servers / DB / DW
  • Enable Diagnostic Metrics/Queries

Conclusion

Well there you have it, a concise summary of the why’s, how’s, who’s and what’s of implementing your services logging strategy on your own Azure Modern Data Platform Architecture.

As you can imagine with this type of activity, it will depend, a lot, on the purpose and business drivers of the platform, and in particular how those services will be consumed, queried or integrated into or by other downstream business processes.

If you would like to build out your own Azure Data Platform architecture but are not sure where to start, or how it can work for your organisation, then have a look at this awesome fully featured end-to-end lab provided by one of my Microsoft Azure Cloud colleagues Fabio Bragahttps://github.com/fabragaMS/ADPE2E

Finally the Azure Data Architecture Guide is also an awesome resource to get ideas on how you can construct your own Data Platform on Azure – https://docs.microsoft.com/en-us/azure/architecture/data-guide/

So as usual, and as I always so, always give this a try yourself, as your own mileage may vary!

Advertisements

PASS 2015 Session Report – Azure SQL DW Integration with the Azure Ecosystem & End of PASS 2015

PASS 2015 continues (and finishes up today!) in Seattle.

Its been an amazing conference this year with a few things really hitting home;

  • Amazing technology announcements around SQL 2016 CTP3
  • Incredible advances in almost every component in Azure Data Services
  • Full and seamless SQL/Azure ecosystem integration – and by that I mean both On-Prem and/or within the Azure Cloud.  The story of either On-Prem or Azure Cloud is compelling enough individually, however the Hybrid story is now a reality for SQL and enables dynamic and flexible architectures well beyond what competitors can offer.
  • BUT what astounds me the most is actually the pace of change – barely a day goes by where I don’t receive a new services or feature update related to SQL 2016 CTP3 or Azure.
  • I don’t recall a time (in recent memory) where the step changes have come so thick/fast – its certainly changed from where I started as a DBA on RDB/VMS back in 1994 where patches arrived by mail on tape cartridge! 🙂
  • (As a quick aside a chief designer on RDB was Jim Gray, the same who joined Microsoft to lead the SQL Server architecture to stardom soon after Oracle bought-out and shelved DEC around 1995+)

 

Enough reminiscing already – moving along – Today I attended 5 back-back sessions, and again I cannot blog about all of them in the time I have (or want to spend), but the one which stands out the most was Azure SQL Data Warehouse and Integration with the Azure Ecosystem by Drew DiPalma of Microsoft.

This session focused specifically on the Azure ecosystem surrounding the Azure SQL Data Warehouse (SQL DW) and how it can seamlessly interact with other Azure components to create different operational solutions.  To me this was very compelling, not necessarily due to the SQL DW technology (which I know well already as the on-prem APS appliance), but more-so as it showed just how easily all parts of Azure can happily work together.

Continue reading

PASS 2015 Session Report – Understanding Real World Big Data Scenarios

PASS 2015 continues in Seattle, and today was my session at 1045am on Using Azure Machine Learning (ML) to Predict Seattle House Prices.  The background and info on my session is here http://www.sqlpass.org/summit/2015/Sessions/Details.aspx?sid=7794

Overall I was pretty happy with how it went - and I think everyone who attended had a lot of fun with some of the games and tests I injected into the presentation.  Everyone had a chance to be a Real Estate Agent :) - and at the same time learn some great methods around performing Azure ML Regression Predictive Analytics.

 

BUT – moving right along – I also attended 3 other sessions today, again I cannot blog about all of them in the time I have, but the one which made me think the most about technology implementations and how they can improves lives was Understanding Real World Big Data Scenarios by Nishant Thacker of Microsoft.

It wasn’t about use cases for big data (as this is a horse already bolted), but more around really innovative and interesting ways the ecosystem of Azure technologies could be deployed to solve some complex business problems, or moreso simply ways to make our lives better!

Continue reading

PASS 2015 Session Report – KeyNote and SQL Server on Azure Virtual Machines: Features, Best Practices & Roadmap

So PASS officially kicked off this morning leading into the next 3 days of back to back sessions.

You could certainly tell that the keynote was on… I mean the dining room was pumping…!

WhereTheBloodyHellAreYou

 

 

Oh that’s right, everyone is at the keynote!

KeyNoteDay1

 

 

So the Keynote session was hosted by Joseph Sirosh Group Vice President, Data Group.

The big tell for the key note was undoubtedly the SQL Server 2016 CTP3 and just whats packed to the rafters within the software.  If you want to learn more about that then I recommended step across to this link here http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx

Key Takeaways from the Keynote;

  • SQL 2016 is really a major release that really solidifies the Microsoft view of a solid foot in both the On Prem and In Cloud data platform camps.
  • “The future is both earth and sky!”
  • The release offers much On Prem capability like Polybase (to APS), R integration (advanced analytics), Always Encrypted, SSAS/SSRS improvements
  • The release also provides the ability to seamlessly integrate from On Prem to Azure Cloud – and/or back like Polybase (to HDInsight), Stretch Database – and SQL already has capability to use Azure VM’s for SQL AAG solutions and Azure backups.
  • An interesting takeaway – the human size of human genome is approx 1.5 Gigabytes, or about 2 CDs worth of storage space.  How small do you feel now?

 

I then attended 4 sessions, but today there is really only time to blog about this one, mostly for me it was the most impressive in regards to capability and just how far its come!

The session was SQL Server in Azure Virtual Machines – Features and Best Practices and was presented by Luis Vargas is a Senior Program Manager Lead in the SQL Server team.

Continue reading

PASS 2015 Session Report – Optimize “All Data” with a Modern Data Warehouse Solution

PASS 2015 has kicked off in Seattle, well the precon’s have anyway on Mon & Tue.  The actual conference starts on Wed-Fri!

I attended a precon session today called Optimize “All Data” with a Modern Data Warehouse Solution held by Bradley Ball and Josh Luedeman of Pragmatic Works.

The session had a focus on moderising the corporate data warehouse via focusing on Data Lifecycle Optimisation.

What does that mean?  

Well – It means focusing on a define set of critical technology and business areas around the corporate data warehouse and strategically implementing a managed approach to improving the corporate data warehouse via introduction of technologies and processes.  Specifically this looked at 6 areas around the corporate data warehouse to consider in your approach to modernisation;

  1. Architecture and Configuration
  2. Availability and Continuity
  3. Maintenance and Optimisation
  4. Enterprise BI
  5. Big Data Architecture and Deployment
  6. Business and Predictive Analytics

Continue reading