Share via

What is built-in intelligence in Azure SQL Database?

  • Article

Azure SQL Database is a fully managed latest stable version of SQL Server Database Engine hosted in Azure cloud that has several built-in features that protect and manage your data. These features may autonomously perform various maintenance tasks instead of you, so we call them built-in intelligence.

Currently, there are many services and products that are powered by some kind of built-in intelligence. This is the reason why is important to understand what is exactly considered as built-in intelligence in every service so you can know what are you getting.

Azure SQL Database has implemented built-in intelligence features that monitor and maintain a database without human intervention and escalate the issues that require your or Azure team intervention. Azure SQL database has the following categories of built-in functions that are considered as built-in intelligence:

  • Upgrading & patching - Azure SQL Database is able to find a way to upgrade itself to the latest version of code with minimal impact and downtime on your application.
  • Data protection - Azure SQL Database has built-in functionalities that identify and prevent security issues and attacks on your databases.
  • Data integrity - Azure SQL Database is able to identify issues in your data and objects and fix the problems without your intervention.
  • Automatic performance tuning - Azure SQL database is able to identify potential performance issues and opportunities to optimize your data structure.

Upgrading and patching

Functionalities in this category are trying to find the best way to keep your database code up-to-date with the minimal impact on your application. Azure SQL Database runs on the latest stable version of SQL Server database engine and Windows operating system with all necessary patches and security updates. If you have ever upgraded SQL Server to new version or updated underlying operating system, you probably know how hard is to complete it in minimal timeframe with minimal impact on the application.

Azure SQL Database needs to keep millions of databases up to date with minimal impact on the applications. Azure SQL Database must transparently update the database code and other software dependencies (OS, drivers) to the latest stable and recommended versions of software with the minimal impact on your application keeping high availability promise with 99.99% SLA.

In order to provide both latest versions and SLA, Azure SQL has two high availability models:

  • General purpose/Standard where compute (database engine) is separated from the storage layer (database files). Whenever the database engine code or underlying software needs to be upgraded, compute is just moved to the new available upgraded compute node that can just attach database files and start running.
  • Business Critical/Premium model has 3 secondary compute nodes with prepared and initialized databases. Whenever the database code or underlying system should be upgraded on primary node, one of the secondary nodes that is already upgraded becomes primary.

Azure SQL Database makes this process fully transparent in many cases by upgrading database nodes and switching the primary database node to upgrades node. As a result, your application will see just that the connections are broken for a short time while the transition from the primary database node is transferred to secondary. However, in most of the case you might not notice any connection break because connection resiliency and logic is built-in into the data access components such as Entity Framework and or drivers itself. If you have implemented retry logic as it is recommended by Azure connectivity guidelines the impact of upgrade on your application might be fully transparent.

Data protection

Azure SQL Database has built-in functionalities that analyze some parts of your schema and data access patterns in order to identify security issues. Vulnerability assessment is a feature that can analyze your database and identify potential weaknesses in your database code. Threat detection is another feature in this category that analyze your workload and send the alerts if some potentially dangerous action (such as SQL Injection attack) is detected.  You are getting reports with all listed vulnerabilities and email alerts that notify your that there are some potential threats.

Both features must be turned-on on-demand because Azure SQL Database will not analyze your schema and data without your explicit request.

Data integrity

Loosing and corrupting data is unfortunately reality in every system. Data in the files may land on bad disk sector, there might be error in communication that changes the data, data packets might be lost in the IO subsystem, there might be bugs in hardware or software that that can cause data integrity issues and data corruptions. These are some common risk that you need to handle in data storage layer and ensure that everything is fine with your data.

Azure SQL Database ensures data integrity by constant checks of your data structures, emitting telemetry and alerts with the description of the detected issues. These alerts are handled automatically by Azure SQL Database using various techniques such as rebuilding system tables, metadata, indexes, repairing pages using DBCC, automatic page repair, etc. All databases are by default set to verify pages with the CHECKSUM setting, calculating the checksum over the entire page and storing in the page header. Transport Layer Security (TLS) is also used for all communication in addition to the base TCP/IP transport level checksum.

There are automatic tests of the restore of automated database backups of databases across the service. Upon restore, databases also receive integrity checks using DBCC CHECKDB. Any issue found during the integrity check will result in an alert.

In case of any critical alert where Azure SQL Database cannot automatically fix the issue, alert is sent either to Azure SQL Database team or customer with the root cause analysis and description of recommended actions.

Automatic performance tuning

Automatic tuning is a set of built-in features that can tune the performance of your database without your intervention.

Database engine constantly monitors execution statistics of the query plans that are executed in your database and trying to find some case where performance of new query plan are worst than the performance of previous plans. In that case, Azure SQL Database automatically uses last known good plan instead of the new one.

Azure SQL Database monitors the usage statistics of your queries and identify potential indexes that could speed up your workload or indexes that are not used and slow-down your workload. Azure SQL Database can automatically create or drop indexes based on these information. In addition, there is a verification process that compares the performance statistics of all your queries before and after index create/drop action. This is probably the most complex part in built-in intelligence because every create/drop action can affect a large number of different queries and decision whether the action improves or degrades performance might be hard.

Besides query optimizer that can create the best possible plan for accessing your data, there is a set of intelligent query processing mechanisms that enable query optimizer to learn based on your workload and improve future plans for accessing data. Examples of these mechanisms are memory grant feedback that enables query optimizer to determine how much memory should be allocated to queries based on the past executions, interleaved execution that helps query optimizer to better estimate the number of rows coming from the functions, adaptive joins that can use index scan and seeks to find the optimal strategy to execute the queries at the run-time.

There are many issues that cannot be handled automatically by Azure SQL Database because they require your decision or some change in your code that would solve the issue. These kind of issues are escalated and reported to you so you can analyze them and decide are you going to fix the issue.  Even in this case, Azure SQL Database enables you to troubleshoot the issues using the root cause analysis performed by the rules built-in into the database engine.

Summary

Azure SQL Database has a large set of built-in rules and actions that maintains and manages your database without your intervention, and escalate the issues that cannot be handled without your action. In this post you have seen what are the basic built-in intelligence features that can help you easily manage your database.

Comments

  • Anonymous
    September 13, 2018
    Good one! Especially in the area of Automatic performance tuning, Azure SQL Database helps a lot where the traditional SQL Server fails.
  • Anonymous
    September 26, 2018
    Good set of features to migrate from traditional sql to azure sql database.