Azure SQL Managed Instance

Azure SQL managed instance is a relatively new Azure offering. It has nearly 100% feature compatibility with the latest on-premises SQL Server Enterprise Edition Database Engine and allows relatively easy lift and shift of on-premises applications to the cloud with minimal application and database changes. At the same time, Platform as a service capabilities such as automatic patching, version updates, backups, and high-availability drastically reduce required management overhead and total cost of ownership.

For customers running older versions of SQL Server, I'm happy to report that the Azure SQL managed instance supports backward compatibility to SQL 2008 databases. It supports direct migration from SQL 2005 database servers, however migrated SQL 2005 databases will have their compatibility level updated to SQL 2008.

A great benefit of choosing the managed instance deployment option is that it will always be up-to-date because patches and upgrades are automatically applied. Also, you don't have to purchase, manage or secure physical hardware.

Managed instance has High-availability built in and pre-configured using a technology similar to Always On Availability Groups, providing 99.99% uptime SLA. It also has automated backups and point in time restore.

With the managed instance you will spend less time on administrative tasks because the service either performs these tasks automatically for you or greatly simplifies them.

It supports Azure AD authentication as cloud alternative to Windows authentication, automatically manages XTP filegroup and files for databases containing In-Memory OLTP objects and supports SQL Server Integration Services (SSIS) and can host the SSIS catalog (SSISDB) that stores the packages. SSIS packages must be executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF). Unlike Azure SQL Database, it allows cross-database queries.

There are two service tiers for Managed Instance: General Purpose and Business Critical.

General purpose: Designed for applications with typical performance and IO latency requirements.
Business critical: Designed for applications with low IO latency requirements and minimal impact of underlying maintenance operations on the workload.

Server sizes range from 8 to 80 vCores depending upon service tier. Memory is 7 GB per vCore.
Maximum storage is 8 TB for general purpose and up to 4 TB for Business Critical

NEW Stuff in Azure SQL Managed Instance!

As of 11 April 2019, managed instance now has a public endpoint. (YAY!)
Here's a link to the official Microsoft article on it - with full instructions for implementing it via Powershell

Azure Data Studio - now with Notebooks

Azure Data Studio is a light-weight cross-platform database tool based upon Visual Studio Code. It runs on Windows, Mac, and Linux. It includes a SQL editor with IntelliSense, keyword completion, code snippets, code navigation and Git source control integration. Although SQL Server Management Studio still remains the champion for database administration tasks, I believe that for developers and Mac and Linux users, Azure Data Studio is the best choice. I also like the fact that Azure Data Studio uses far fewer resources on my machine than SSMS.

As of March 2019, Azure Data Studio connects to:

• SQL server 2014+
• Azure SQL database
• Azure SQL Data Warehouse
• Azure SQL Managed Instance
• PostgreSQL servers 

One of the newest and I think more notable features that has been added to Azure Data is its ability to create a SQL notebook.

Notebooks are type of document that combine formatted text with live code elements, images, and query results. You may have heard of them as "Python Jupyter Notebooks". Well, now they can be used with SQL in addition to other programming languages such as Python, Scala, and R.

To create a notebook, simply open Azure Data Studio, then select New Notebook from the file menu. Add a code type cell (or section), check that the Kernel is "SQL", set the connection to your favorite database, enter some SQL code and execute it.

The IntelliSense in Azure Data Studio simplifies code entry, and the right-click menu offers some excellent options, including formatting the document, changing all occurrences of the selected word, and taking a "Peek" at the definition of the selected SQL object (table or stored procedure name).

Just think of all the possibilities!

These notebooks will be great for technical presentations. I can prepare formatted text to describe the concepts presented in my session, and include executable code within the context of the document which the attendees can run at home on their own systems after they download the demos.

Another great scenario would be for troubleshooting or baselining a server. Because Notebooks allow you to save the query results inside the file, you can run diagnostic queries and save the results all in one neat package. Then, later on when you wish to compare to the original baseline, make a copy of the notebook, clear the results and re-run all the queries to see the differences.

For problem solving,  you could create a Notebook containing instructions and a series of queries, ask your client to run the queries and return it to you with the results populated.

I think these new features really make Azure Data Studio outshine old SSMS. I may not remove SSMS from my machine just yet, but I'm pretty certain it's not going to see very much use in the future.