A Look at Some of Azure SQL Database’s Intelligence Features

Today I’d like to tell you about some very cool intelligence features within the Azure SQL Database. Azure SQL Database technologies deliver intelligent capabilities through a range of built-in machine learning and adaptive technologies that monitor and manage performance and security for you.

Using telemetry from millions of databases running in Azure over the years, Microsoft has built this capability of training a truly intelligent and autonomous database that gives you the ability to learn and adapt to your workload. This intelligent performance gives you the deeper insight into database performance. Plus, it eliminates the hassle of making ongoing improvements, allowing you to focus more on driving your business and less on “chores”.

Features like query performance insights and automatic tuning continuously monitor database usage and detect disruptive events and then they take steps to improve performance.

Three examples of the intelligent performance that can collectively optimize your memory usage and improve overall query performance are things like:

  • Row mode memory grant feedback – this gives you the ability to expand on batch-mode memory grant feedback by adjusting memory grant sizes for both batch and row mode operators.
  • Approximate query processing – this is designed to provide aggregations across large datasets where responsiveness is more critical than absolute precision, and it will return an approximate value with the focus on performance.
  • Table variable deferred compilation – this improves plan quality and overall performance for queries, referencing table variable by propagating cardinality estimates that are based on actual table variable row counts. In turn, this optimizes your downstream plan operations.

Along all those features, Azure SQL Database intelligent protection allows you to efficiently and productively meet your data’s security and compliance requests by proactively monitoring for potential threats and vulnerabilities. You can flag things such as PII or a cross-scripting attack or something like that. There are detection mechanisms in there that can help you avoid these.

Through features like information protection, vulnerability assessment and threat detection, you can proactively discover and protect sensitive data, as well as uncover potential vulnerability and detect anomaly activities that could indicate a threat to your data.

In short, Microsoft has built these intelligent features over years of machine learning and is applying it to all their Platform as a Service, as well as some of their on-premises, offerings. These are really cool features and we’ve got great response about them and how well they work.

I recommend you give these features a try, but remember, always try them out in your test or dev environments prior to bringing them into production.

What is Azure Data Box Heavy?

You may have seen my previous Azure Every Day post on Azure Data Box and Azure Data Box Disk. These are a great option for getting smaller workloads, up to 80 terabytes of data, quickly up into Azure. Rather than moving it over the wire, you can send a box and bring it up.

The Data Box Heavy works the same, but you can use much larger amounts of data with up to a petabyte of space.

Let’s review the Data Box process:

  • You order the box through the Azure Portal and specify the region that you’re going to use.
  • Once you receive it, connect it into your network, set up network shares and then you copy your data over. It has fast performance with up to 40 gigabits/second transfer rates.
  • Then you return the box to Microsoft and they will load the data directly into your Azure tenant.
  • Lastly, they will securely erase the disk as per the National Institute of Standards and Technology (NIST) guidelines.

The Data Box Heavy is ideally suited to transfer data sizes larger than 500 terabytes. If you used a Data Box with it’s 80 terabytes, you’d need 5 or 6 of those in place of the Heavy. When you have those larger data sizes, it makes more sense to have it on one machine.

The data movement can be a one time or periodic thing, depending on the use case. So, if you want to do an initial bulk data load, you can move that over and then follow that up with periodic transfers.

Some scenarios or use cases would be:

  • You have a huge amount of data on prem and you want to move it up into Azure – maybe a media library of offline tapes or tape backups for some kind of online library.
  • You’re migrating an entire cabinet – you have a ton of data in there with your virtual machine farm, your SQL Server and applications – over to Azure. You can move that over into your tenant, migrate your virtual machines first, then you can do an incremental restore of data from there.
  • Moving historical data to Azure for doing deeper analysis using Databricks or HD Insight, etc.
  • A scenario where you have a massive amount of data and you want to do the initial bulk load to push it up, then from there you want to do incremental loads of additional data as it gets generated across the wire.
  • You have an organization that’s using IoT or video data with a drone – inspecting rail lines or power lines for instance. They are capturing tremendous amounts of data (video and graphic files can be huge) and they want to be able to move that up in batches. Data Box Heavy would be a great solution to quickly move these up rather than moving the files individually or over the wire.

This is a very cool technology and an exceptional solution for moving data up in a more efficient manner when you have huge, terabyte-scale amounts of data to push to Azure.

What is Azure Network Watcher?

Most of us are starting to deploy more and more cloud assets. When you think about how you deploy some assets in Azure, you basically build out a virtual network and you can set that up so it ties in with your on premises network through express route or VPN or you can run it independently in the cloud and have your virtual network set. The question is, how do you monitor and manage that virtual network, like some of the components and how the virtual machines interact? Here’s where Azure Network Watcher comes in.

Azure Network Watcher allows you to monitor, diagnose and gain insight into your network performance between various points in your network infrastructure.

Here’s a breakdown of some of the elements:

1. The Monitoring Element – You can monitor from one endpoint to another with connection monitor to ensure connectivity between 2 points, like a web application and a database for instance. You’ll be alerted with potential issues such as a disconnect between those two services.

It also monitors latency times for evaluation. When you look at those latency times over a period, you’ll know what the average latency is and the max and min. Then you can think about you possibly getting better service in a different Azure region.

2. The Network Performance Monitor – Allows monitoring between Azure and on-premises resources for hybrid scenarios using VPN or express route. It also has some advanced detection to traffic blackholing and routing errors – in other words, some advanced intelligence when it comes to these network issues.

Best of all, as you add more endpoints it will develop a visual diagram of your network with a topology tool which will look like a visio-diagram, showing IP addresses, host names, etc.

3. Diagnostic Tools – From a diagnostic standpoint there are several diagnostic tools that give you better insight into your virtual network by diagnosing possible causes of traffic issues.

IP Flow – Tells you which security rule allowed or denied traffic to or from a virtual machine in your virtual network for further inspection or remediation.

Another tool tests communication for routing rules by letting us add a source and destination IP, then shows the results of that route, again to investigate further or remediate.

The Connection Troubleshooting Tool – Enables you to test a connection between two VMs, FQDN, URI or IDP4 addresses and returns info like the Connection Monitor but only about that point and time latency, not over a span of time.

The Packet Capture Tool – Allows traffic to be captured to and from a virtual machine with some fine-grained filtering to be stored inn Azure storage and further analyzing with network encapture tools like Wire Shark, for instance.

4. Metrics Tools – There are some limitations as to how many resources you can deploy within an Azure network which can be based on subscriptions or regions. The Metric Tool gives you the visibility that you need to understand exactly where you are inside of those limitations. It shows you how many of those resources you’ve deployed and how many are still available that you can deploy – so it helps you set up planning for the future as you deploy more and more resources.

5. Logging – We’ve done some interesting things with log analytics. Log analytics provides the ability to capture data about a bunch of Azure networking components, like network security groups, public IP addresses, load balances, virtual networking and application gateways, to name a few.

All these logs can be captured and stored in Azure storage and further analyzed. Many can be fed into Operations Management Studio (OMS). This gives you a single pane of glass experience when you want to look at your environment at that “50,000-foot level”.

So, as you begin to deploy more and more assets into your Azure environment, this is a helpful service to monitor and manage your virtual network. You get a high-level overview of what that network looks like.