Reaching For The Cloud With SQL Sentry & Managed Instance

Another month, another release of the SentryOne platform. Whereas the last release was led by my colleague Steve_Wright and delivered a wealth of enhancements around the BI Sentry solution. This release is focused on enhancements to SQL Sentry, you can find a full list of new features and fixes in the SQL Sentry Release Notes.

New SQL Sentry Features

The main addition to SQL Sentry with this version is the support for Azure SQL Database Managed Instance, both as a monitoring target and for hosting the SentryOne repository. Additionally, there is now the ability to give a target a friendly name in the navigator, support for the CXCONSUMER waits, process group for Machine Learning Services, and row goal showplan support for the Integrated Plan Explorer.

Managed Instance

In 2017 Microsoft announced that they were working on a new flavour of Azure SQL Database called Managed Instance, hinting that it would have a high degree of compatibility with retail SQL Server. Last week, Microsoft announced that this service is moving into Public Preview and is now available for customers to get their hands on. Due to the close working relationship that we have with Microsoft, we are able to announce support for Managed Instance with this release.

What is Azure SQL Database Managed Instance? Essentially, it is a new flavour of Azure SQL Database that brings many of the capabilities we associate with a SQL Server instance to a Platform as a Service (PaaS) offering. This means that we get features like SQL Server Agent, CLR, Linked Servers, Cross Database Queries and Transactions (within the instance) as well as existing features like Row Level Security, Graph Database, Query Store etc. All of this capability comes without the need to worry about Operating System or SQL Server patching as that is all handled by Microsoft. Likewise, High Availability and Backups are also handled meaning that there is a large reduction in cost and complexity when compared to IaaS VM or on-premises deployments.

From a SentryOne perspective, while Managed Instance is a new target type, it will be covered by a SQL Sentry license. This means that as you move capability to Managed Instance and shutdown existing SQL Servers you can just add the new Managed Instance as a target without the need to buy any new licenses. Once SentryOne is monitoring Managed Instance, you will notice some differences between your on-premises monitored servers and the Managed Instance. This is because as Managed Instance is a PaaS offering a lot of the underlying Operating System components, such as other processes and storage, are largely abstracted so they cannot be monitored.

We have our Performance Dashboard focused on the key SQL Server engine metrics such as Wait Stats, Database File IO, and Memory utilization. This is complemented with our full Top SQL experience as well as Query Plans, Blocking, and Deadlocks. Our Index Management feature is also fully supported for use with Managed Instance, giving you the ability to identify index usage, fragmentation levels, and perform index maintenance operations rebuild and reorganize.

Seeing as we can monitor Managed Instance, why would we not want to run our database on it? So we went ahead and did that. We support the deployment of the SentryOne repository to Managed Instance via the installer experience when you perform an initial setup. If you are an existing customer and want to move your SentryOne database to Managed Instance, then that is a supported scenario too using the method outlined in Relocating the SQL Sentry Database.

Hint!
Another great benefit of Managed Instance over singleton Azure SQL Database is the ability to perform a backup/restore to migrate databases!

Target Aliasing

A major part of my role as Product Manager here at SentryOne is speaking with and listening to customers, helping me to understand problems they face so that we can try to solve them. One of the things that has come up recently has been requests for giving monitored targets a friendly name in the navigator tree. Let's face it, we have all worked somewhere that has an obscure or complex naming convention for servers. To help in scenarios like this we have made the change to allow for a friendly name to be used in the navigator. You can do this simply by right clicking on the target you want in the Navigator, then selecting the "Manage Friendly Name" option. Once you have given the target a friendly name it will use that in the navigator, you are still able to see the full name of the target when you hover over the Target.

Currently this change is applied globally so that a friendly name set by one SentryOne administrator will be seen by all SentryOne users. I would be very interested in hearing your feedback as to whether you feel this should be a per-user configuration option or remain as a global setting. Remember, your feedback shapes the software that we build here at SentryOne.

New Wait Type Support

As announced by Pedro Lopes (b|t) in his post "Making Parallelism waits actionable", Microsoft added the CXCONSUMER wait type with SQL Server 2017 CU3 and SQL Server 2016 SP2. As Aaron Bertrand explains in this post, "Changes to CXPACKET waits in SQL Server". It is now a lot easier to understand when CXPACKET waits are actually a problem in the versions that support the differentiation.

With this addition, I can now see if parallelism waits are made up of actionable CXPACKET waits that I can try to tune out of my query. Or if they are the benign CXCONSUMER waits that are part and parcel of parallel query workloads.

What the future holds

In the short term, we are already starting to push hard on the heels of this release to get you something else soon. We have some very exciting enhancements planned around SQL Sentry and Plan Explorer that should really have an impact on the way you troubleshoot certain issues with your workloads. Don't worry, I will let you know as soon as that lands. Work continues apace on finalizing our use of partitioned Clustered Columnstore and In-Memory SQL Server technologies to aid performance and scale. I am hoping to have this baked into the solution in the near-term. If you are interested in learning more about this future feature and would be interested in testing it out, contact our support team (Support@SentryOne.com). They will be able to see if your system meets the prerequisites for making use of this.

Aside from that we are striving hard to look at usability and minimizng the admninistrative overheads associated with deploying and maintaining large-scale SentryOne intallations. We will continue listening to and working with our customers, as well as those people that have feedback for us. This will guide our product development as we move forward for both usability and for feature development. We really want to hear about the problems that you have to solve when managing Data Platform solutions, this way we can build software that helps you solve these problems.