Microsoft seeks to make database management easier for users of its Azure SQL service and SQL Server through the addition of new features it calls Performance Management Intelligence. They are designed to provide easier database management for both DBAs and app developers.
InfoQ had the opportunity to attend a media event at PASS Summit 2017 presented by members of the Microsoft Azure SQL Database Engineering team. The presentation’s focus was on new features within Azure SQL Database designed to provide easier database management for both DBAs and app developers. Microsoft refers to these features as “Built-in Intelligence” as opposed to operations built on top of the Azure SQL Server platform.
The presentation covered three different categories of features, each intended to improve the operational effectiveness of databases: Performance Management Intelligence, Adaptive Query Processing, and Security Intelligence. In this article we will examine each in turn, noting where some services are in public preview and not yet generally available.
Performance Management Intelligence
Intelligent Insights is a process by which Azure SQL monitors query operations for disruptive events. Should a query demonstrate regression, this will be reported with suggestions on how to correct the problem. In the example given in the presentation, ad-hoc query A impacted the performance of existing query B by triggering resource limits. Azure SQL then suggested turning the off the ad-hoc query or increasing the Azure pricing tier to make more resources available. (Intelligent Insights is in Public Preview.)
When Automatic Tuning is enabled, it allows Azure SQL to monitor database performance. It will attempt to improve performance by creating missing indexes, or by dropping unused/duplicate indexes. Application-level changes are not required to take advantage of this benefit.
Service Tier Advisor is an administrative tool that provides a dashboard for application workloads and offers advice on how to optimize the pricing tiers currently being used in Azure. For example, a given database may be overpaying for the amount of resources it consumes, and STA will suggest a lower performance tier that is a better value. Conversely, a different database may be resource starved and benefit from a more expensive plan. This tool allows administrators to spend their database budget more effectively while still meeting the needs of their business.
Adaptive Query
Adaptive Query works on the principle of Learn – Adapt – Verify. Optimizing queries uses the cardinality estimation (CE) process to make intelligent decisions around the order-of-operations and physical algorithms to use for best query performance. When these estimates are wrong, numerous negative behaviors are evident: slow query response time, excessive resource utilization, and reduced throughput. This is to be avoided of course, and based on these estimates a decision is made as to which query type to execute.
Security Intelligence
Given the various data breaches and prominent website hacks, it should be obvious that keeping your databases protected is vitally important. Added to these concerns are the regulations imposed by various government and professional entities and it is clear database security cannot be ignored.
Vulnerability Assessment (currently in Public Preview) has been developed to help DBAs monitor and improve the security of their databases. When executed it scans available databases for unprotected data, misconfigurations, and provides detailed reporting that can be used for meeting compliance reporting requirements. Even better, it is available for both SQL Server on-premises and Azure SQL Database, providing protection for both environments.
VA benefits DBAs in a few different ways. First, it scans databases for sensitive data (i.e. SSNs) that is not secured and provides recommendations on how this can be protected. Second, it provides checks to ensure that various best practices are being applied (firewalls, auditing, encryption at rest, etc.). Finally it tracks changes over time so that when a security baseline is established it can be monitored to ensure that the system does not deviate from the settings in the future. (VA is currently in Public Preview).
Threat Detection is an always-on (when enabled) tool that monitors databases in real-time for SQL injection attacks, unusual behavior, and security vulnerabilities. When issues are detected, it provides alerts to the DBA so that corrective measures can be taken. This differs from Vulnerability Assessment in that it is not static, and so it can detect threats when they occur.
